Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL Data Migration Tips

engineering.tilt.com

Post reply on HN
Print view View original

11–20 of 22 posts

Re: PostgreSQL Data Migration Tips

#11

Post by 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)

Re: PostgreSQL Data Migration Tips

#12

Post by bremac »

I'm not sure I understand the purpose of the loop in the last example. AFAIK top-level plpgsql statements (including DO blocks run in psql) execute in a single transaction, so it seems like you end up slowly locking the entire table, as the transaction won't commit until the loop completes. (I learned this the hard way by trying to "batch"-update a table with tens of millions of rows in production.)

The normal way to handle batch updates is to perform the loop outside of postgresql, so that each batch is in its own transaction.

Re: PostgreSQL Data Migration Tips

#13

Post by bhahn »

bremac wrote: post #12

I'm not sure I understand the purpose of the loop in the last example. AFAIK top-level plpgsql statements (including DO blocks run in psql) execute in a single transaction, so it seems like you end up slowly locking the entire table, as the transaction won't commit until the loop completes. (I learned this the hard way by trying to "batch"-update a table with tens of millions of rows in production.) The normal way to…

  AFAIK top-level plpgsql statements (including DO blocks run in psql) execute in a single transaction
This is true according to the docs. Anonymous code blocks are "transient anonymous functions", and functions are executed within a transaction.

  it seems like you end up slowly locking the entire table
The selected rows would be locked for update, delete, and select for updates, but not for regular reads. Perhaps his users table is used primarily for reads, which made this command run with negligible consequences?

http://www.postgresql.org/docs/9.4/static/sql-do.html

http://www.postgresql.org/docs/9.4/static/plpgsql-structure....

Re: PostgreSQL Data Migration Tips

#14

Post by jpitz »

steventhedev wrote: post #11

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 u…

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.

Re: PostgreSQL Data Migration Tips

#15

Post by aanari »

bhahn wrote: post #13
bremac wrote: post #12

I'm not sure I understand the purpose of the loop in the last example. AFAIK top-level plpgsql statements (including DO blocks run in psql) execute in a single transaction, so it seems like you end up slowly locking the entire table, as the transaction won't commit until the loop completes. (I learned this the hard way by trying to "batch"-update a table with tens of millions of rows in production.) The normal way to…

AFAIK top-level plpgsql statements (including DO blocks run in psql) execute in a single transaction This is true according to the docs. Anonymous code blocks are "transient anonymous functions", and functions are executed within a transaction. it seems like you end up slowly locking the entire table The selected rows would be locked for update, delete, and select for updates, but not for regular reads. Perhaps his u…

Author here. That's a great point bhahn, I just updated my gist to properly handle the case that you just outlined:

https://gist.github.com/aanari/349c7d97ed50c6f69930#file-bat...

By creating a separate function for the locking and updating of rows, we ensure that the `BEGIN/END` transaction is handled per iteration rather than at the very end, so we only lock rows while they are being processed. Since Postgres does not support nested transaction blocks, calling a defined function from within an anonymous function block seemed to be the easiest and clearest path to achieve this.

Re: PostgreSQL Data Migration Tips

#16

Post by aanari »

jpitz wrote: post #14
steventhedev wrote: post #11

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 u…

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.

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.

Re: PostgreSQL Data Migration Tips

#17

Post by jpitz »

aanari wrote: post #16
jpitz wrote: post #14

Earlier quoted context omitted.

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.

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.

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

Re: PostgreSQL Data Migration Tips

#19

Post by bhahn »

aanari wrote: post #15
bhahn wrote: post #13

Earlier quoted context omitted.

AFAIK top-level plpgsql statements (including DO blocks run in psql) execute in a single transaction This is true according to the docs. Anonymous code blocks are "transient anonymous functions", and functions are executed within a transaction. it seems like you end up slowly locking the entire table The selected rows would be locked for update, delete, and select for updates, but not for regular reads. Perhaps his u…

Author here. That's a great point bhahn, I just updated my gist to properly handle the case that you just outlined: https://gist.github.com/aanari/349c7d97ed50c6f69930#file-bat... By creating a separate function for the locking and updating of rows, we ensure that the `BEGIN/END` transaction is handled per iteration rather than at the very end, so we only lock rows while they are being processed. Since Postgres does…

https://gist.github.com/aanari/349c7d97ed50c6f69930#file-bat...

The anonymous block is implicitly called in a transaction, so all the calls to batch_at_will will be executed in the parent transaction; there's no way around this (except for using db_link but that's pretty smelly imo).

The only way to batch update while only locking rows in the batch is to run the loop outside of postgres like OP suggested.

Re: PostgreSQL Data Migration Tips

#20

Post by steventhedev »

jpitz wrote: post #17
aanari wrote: post #16

Earlier quoted context omitted.

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.

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

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

Post reply on HN