[steventhedev]

Bone to pick with the CTE that archives users. I'd rather see it insert them into the archive table first, then delete them. Not much of a difference, but my instinct is to cover all possible failures, and be especially careful around deleting rows.

It also means that god forbid it did die halfway through, and PG isn't smart enough to pick up where it left off safely, you won't lose any data, and at worst would end up with a duplicate archived row (easy enough to catch with some maintenance scripts and origin ids)

[jpitz]

Should be a transaction. I bet if you could a way to corrupt data on safe hardware with this query and a power-plug test, the pg developers would treat it as a high-priority bug.

[aanari]

That's correct, as jpitz mentioned if the code is run inside a transaction block, then we don't have to worry about the failing DELETE causing the INSERTs to fail.

[jpitz]

The transaction is implict - in fact, I don't know of a way to do this outside the scope of a transaction. Is there?

[steventhedev]

My instinct here is to write the query in such a way that even if pg changes in the future, or if I migrate to an almost compatible alternative tomorrow, it should work the same way. Perhaps more importantly, it sends the message to future devs (including me) that rows should only be deleted after they've already been archived. It also covers that 0.000000001% chance that everything else went wrong and the intern did it manually and managed to screw it up.

In the end, queries are code, and code is our way to communicate or intent to the next developer, so it's better to do the delete after the insert

[dragonwriter]

> My instinct here is to write the query in such a way that even if pg changes in the future, or if I migrate to an almost compatible alternative tomorrow, it should work the same way.

Transaction semantics are a fundamental feature of pg, of SQL, and of ACID databases generally. Anything that doesn't preserve them is not an "almost compatible alternative".

> Perhaps more importantly, it sends the message to future devs (including me) that rows should only be deleted after they've already been archived.

Using an explicit transaction, even if it is not necessary, communicates to future devs the actual intent, which isn't "rows deleted after they are archived" but "deletion and archive should occur in a single atomic step, and either both happen or neither happen." The ordered approach is just a way to express the least harm alternative in an environment that doesn't provide atomicity guarantees, but that's inappropriate when working in an environment that actually provides atomic transactions.

One of the biggest problems that programmers with a stronger background outside of databases often have in writing SQL DB code is that they keep using ingrained workarounds for the fact that many programming environments lack convenient support for grouping operations into atomic units in SQL, which supports such grouping.

[jpitz]

> My instinct here is to write the query in such a way that even if pg changes in the future, or if I migrate to an almost compatible alternative tomorrow, it should work the same way.

I agree, but, to nitpick, if transaction semantics change that much, you aren't dealing with an almost compatible alternative. Further, I'll wager vast sums of money that pg will not change in that way, ever.

> In the end, queries are code, and code is our way to communicate or intent to the next developer, so it's better to do the delete after the insert

I wholeheartedly agree, so long as your code correctly captures the requirements. For ( contrived, uncommon ) example if the requirement states that, transactionally, the row must either be in either the live table or the archive table, but not both ( for reporting non-duplication, ) then the form that performs the insert-delete is the correct capture of that requirement.