PostgreSQL Data Migration Tips

It was bugging me that the target of the "rosser" link was not pointing to the comment he shared his bulk update technique so I dug it up for anyone else interested:

https://news.ycombinator.com/item?id=9018756

Re: PostgreSQL Data Migration Tips

#5

Post by ngoel36 » Thu, Mar 19, 2015, 4:23 PM UTC

NDizzle wrote: post #2

I like how SQL is becoming cool again.

Did it ever really go out of style?

Re: PostgreSQL Data Migration Tips

#6

Post by bhahn » Thu, Mar 19, 2015, 4:24 PM UTC

  FOR UPDATE NOWAIT immediately locks the rows being retrieved in the first step (as if they were to be updated)

Technically "FOR UPDATE" only makes an attempt to lock rows, and the "NOWAIT" instructs postgres, in the case that another transaction already has a lock on the row, to raise an error immediately instead of the default behavior of waiting for the lock to become available.

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

Re: PostgreSQL Data Migration Tips

#7

Post by aanari » Thu, Mar 19, 2015, 4:49 PM UTC

xtrumanx wrote: post #4

It was bugging me that the target of the "rosser" link was not pointing to the comment he shared his bulk update technique so I dug it up for anyone else interested: https://news.ycombinator.com/item?id=9018756

Author here. Great point, I just updated this link to point directly to his comment rather than his HN profile.

Re: PostgreSQL Data Migration Tips

#8

Post by gdulli » Thu, Mar 19, 2015, 4:50 PM UTC

ngoel36 wrote: post #5

NDizzle wrote: post #2

I like how SQL is becoming cool again.

Did it ever really go out of style?

Not for me. I've used ORMs for the simplest of CRUD operations from web apps but couldn't imagine ever working without SQL for everything else.

Re: PostgreSQL Data Migration Tips

#9

Post by aanari » Thu, Mar 19, 2015, 4:53 PM UTC

bhahn wrote: post #6

FOR UPDATE NOWAIT immediately locks the rows being retrieved in the first step (as if they were to be updated) Technically "FOR UPDATE" only makes an attempt to lock rows, and the "NOWAIT" instructs postgres, in the case that another transaction already has a lock on the row, to raise an error immediately instead of the default behavior of waiting for the lock to become available. http://www.postgresql.org/docs/9.4/s…

That's a good clarification. I think it's better for the data migration to error out when acquiring the lock on rows and then retry, rather than waiting indefinitely, but YMMV (your mileage may vary).

Re: PostgreSQL Data Migration Tips

#10

Post by aanari » Thu, Mar 19, 2015, 4:54 PM UTC

mizerable wrote: post #3

nice ! thank you for this

No problem mizerable! Glad you enjoyed it.