gnomon

gnomon, 7 hours ago

@bagder YOINK thank you

gnomon, 1 day ago

@johnefrancis @sundogplanets can confirm, this construction project took forever but its functional merging of the delivery and preparation stages is a strict improvement over its inspirational predecessor, the Alameda-Weehawken burrito tunnel¹.

¹: https://idlewords.com/2007/04/the_alameda_weehawken_burrito_tunnel.htm

gnomon, 1 day ago

@cks in this case there is an expectation of the number of coupon codes being strictly larger than the number of users, by a safety margin designed to exceed the number of new users who could even in theory onboard during the validity period of the coupons.

I feel like if I could just order the users in a stable way, say by sequential record ID, then order the coupons by record ID, and filter out mapped pairs by NOT EXISTS in the user→coupon mapping table... Ugh I don't know, I'm a bad DBA

gnomon, 1 day ago

@cks and I can write to the mapping table, just not back upstream to the user database or to the statically allocated list of coupon codes.

gnomon, 1 day ago

@glyph @jalefkowit it is not, there are a variety of other constraints applicable to user records in this case. (Sorry for being cagey, it's work stuff that I shouldn't disclose.) I have a sufficiently simple and performant query for this already which I could either use as a subquery for the insertion into the user→coupon table or as a CTE to accomplish the same thing, I'm just missing that last conceptual step. This isn't in my domain of expertise.

gnomon, 1 day ago

@hobs @cks absolutely, yeah. I have a schema and set of indices worked up already for that mapping table, including tracking redemptions, I'm just missing the proper concept for bulk mapping unused coupon codes to user records lacking an existing mapping.

gnomon, 1 day ago

@Rycaut @hobs @cks conceptually the mapping only needs to be materialized when the user is informed of it, yes.

The initial bulk mapping is intended to be a one-off, but the process will be repeated some as-yet undecided small number of times, and on each repetition there will be an additional mapping of all newly onboarded users since the last mapping, plus a (trivial, already implemented & tested) culling of redeemed coupons to avoid pestering users who have already taken action.

gnomon, 1 day ago

@Rycaut "cull" is perhaps too overloaded of a term. I just meant there is a column in the user→coupon mapping table that can be (bulk) updated to represent whether a code has been redeemed. Conceptually it can be a boolean, concretely it's a timestamp, in the current implementation it's just a IS NULL clause.

Assigning codes and informing users are definitely intended to be done as close together as possible to avoid missing user notifications, so a single transaction would be best, yes.

gnomon, 1 day ago

@Rycaut fortunately that next step about the mechanics of alerting users is comprehensively solved and very well tested already, and the business has very conclusively decided that multiple user databases will not be an eventuality this team will need to care about.

(That's where most of the heavy lifting went over the last couple of years, which is why I now have the luxury of puzzling over a nicer, cleaner way of doing this one small thing instead of using our existing hack-job tooling.)

gnomon, 1 day ago

@glyph @jalefkowit that is exactly it! Thank you so much for the functional solution but even more so for the terrific explanation!! I am gratefully in your debt.

gnomon, 1 day ago

@cks @glyph @jalefkowit update: the combination of CTEs and windowing functions did the trick. The underlying SELECT to pick out the applicable customer records takes about 30s - big data set, huge JOIN - but the user_id to unused coupon mapping adds about 200ms above that, about O(n) in the number of unmapped records, and then mostly because of the row writes.

I got exactly the solution I needed and I understand the underlying new-to-me concepts. Thank you again!!

gnomon, 2 days ago

@ottaross oh they attack, but that's in another movie

gnomon, 3 days ago

#monsterdon heh heh "Sneffels" gonna need to pack some extra handkerchiefs, eh? eh? get it?? is thrown off the train

gnomon, 3 days ago

#monsterdon "It is herring season, and every able-bodied man is either out fishing or home mending the nets."

We need to find a porter with a herring allergy.

gnomon, 3 days ago

#monsterdon Ten points to the first nerd who dubs the Lord of the Rings soundtrack over this mountain trekking scene.

(Anyone else having trouble accepting that Glauben's cute hat isn't immediately blowing off in this wind?)

gnomon, 3 days ago

#monsterdon MONSTER SIGHTING

gnomon, 3 days ago

#monsterdon "What possible progress could there be if science were a slave to democracy?"

I can only read that in Elon Musk's stupid, stupid voice while imagining it being repeated Greek chorus style by a bunch of TESCREAL cultists

gnomon, 3 days ago

#monsterdon oh no, it is dark and Glauben is about to be eaten by a grue

gnomon, 3 days ago

#monsterdon Welp there goes Axel, shame about that, nothing to be done about it

gnomon, 3 days ago

#monsterdon "How's that raft coming along?" ... "I hope that fossilized wood floats."

Fossilized wood is literally rock. Rock is known for many things but generally not its buoyancy..?

gnomon, 3 days ago

#monsterdon "Those look like giant mushrooms!"

quoth @k8eb: Matang-uh-oh

gnomon, 3 days ago

#monsterdon Oh no, Glauben just ate a bunch of fish with her bare hands and then pushed her hair back, now she has fish hair

gnomon, 3 days ago

#monsterdon MONSTER ROAR

gnomon, 2 days ago

#monsterdon Will somebody PLEASE give Hans a hand with the manual labour for just one scene, seeing him haul up all the logs of the raft that he also built on his own is not endearing me to Otto.