undefined

points

[-]

The biggest problem with that right now is that postgres doesn't allow explicit lock acquisitions (via the LOCK stmt) for all the object types. I've been thinking we should change that for a while, albeit partially just because it is useful for writing tests. With that added, a mode that refuses new lock acquisitions wouldn't be that hard...

I invite you to start a discussion on the lists about that feature, I've wished for it before.

by nijave9 hours ago|

prev|

[-]

I think you could automate this with 2 transactions

- connection A, lock timeout=0, acquire unwanted lock

- connection B, lock timeout=0, run migration

- collection A, rollback

Then connection B will fail if it tries to acquire an undesirable lock since it will conflict with A. You'd be adding a very small window when you're actually holding the undesirable lock, though

by orf8 hours ago|

parent|

[-]

This is essentially how my tool discovers locks for a given arbitrary DDL statement.

by mxey10 hours ago|

prev|

[-]

That’s an interesting idea but not all locks are held for the duration of the statement. A lot of them take a less intrusive lock for the whole statement and take an exclusive lock for a very short time when they finish up.

Edit: Looking this up, I’m not sure this is correct.

by dathinab9 hours ago|

parent|

[-]

> whole statement

simplified you can think of a statement outside of a transaction as starting an implicit transaction just for itself

and (normal) locks are in general hold until the end of the transaction (while also allowing re-entrance from subsequent queries on the same transaction)

practically

- there are edge cases (e.g. Advisory Locks, but in general you don't want to use them)

- you normally(^1) would want to run your pg migration as a single transaction (but there are edge cases). And in turn the OPs idea of pre-acquiring locks would be for the whole transaction anyway. Plus it was just a general idea, so the end result could be more like an "expect lock" statement maybe with some scan ahead ability then an "acquire lock".

(^1): Exceptions include certain operations which need to be in different transactions, and some painful situations where too much data is touched/changed/computed and you need a lot of very careful handling you common small-ish PG DB use-case isn't exposed to (and in turn a lot of "naive but often good enough" migration setups can't handle either...)