4 ms·
One Weird Trick to avoid waiting ~forever to acquire exclusive locks when making Postgres schema changes (e.g. attaching/detaching partitions, which is where I
by derefr 5y ago
One Weird Trick to avoid waiting ~forever to acquire exclusive locks when making Postgres schema changes (e.g. attaching/detaching partitions, which is where I usually need this):
1. begin a transaction;
2. rename your table (or the entire schema) to some temporary/anonymous name;
3. perform the DDL operation;
4. rename your table/schema back;
5. commit the transaction.
I have no idea why this works, but it does!
- ComputerGuru 5y agoI have an “I don’t know what I’m talking about but…” gut feeling if it works differently the way you explain then you are running into a bug and something isn’t working the way it should (as in, it should be slow and being fast means something is wrong).
- AlisdairO 5y agojust a note that in newer versions of PG I believe partition changes no longer require an access exclusive lock on the parent table, which I'm looking forward to when we upgrade...
- deleted 5y ago[deleted]