22 ms·
It would be nice if you added a how it works section to the readme. Knowing that it does `begin; ${query}; rollback;` under the hood is important. The example w
by fovc 3y ago
It would be nice if you added a how it works section to the readme. Knowing that it does `begin; ${query}; rollback;` under the hood is important. The example with truncate in fact made me think the opposite was true, since truncate violates MVCC and cannot be rolled back.
I’m often worrying about locks in migrations that could be long running, so executing the query to figure out the locks defeats the purpose. Or at least I need to know to use a test DB.
- admtal 3y agoGreat suggestion, thank you! Done - https://github.com/AdmTal/PostgreSQL-Query-Lock-Explainer/commit/da1c5fcfe7b1d5f8755c0203ad66ea0e3828c00f https://github.com/AdmTal/PostgreSQL-Query-Lock-Explainer/co...
- LunaSea 3y agoAs far as I know, truncate can be rolled back but it is also visible, even before the transaction is commited, to other transactions that started before: https://www.postgresql.org/docs/current/sql-truncate.html https://www.postgresql.org/docs/current/sql-truncate.html
- fovc 3y agoAh thank you! Quoting the link for others; > TRUNCATE is not MVCC-safe. After truncation, the table will appear empty to concurrent transactions, if they are using a snapshot taken before the truncation occurred. See Section 13.6 for more details. > TRUNCATE is transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit.
- mad0 3y agoMaybe emitting a warning would be good. I'm no DBA so remembering all operations that cannot be rolledback might be tough. (Though maybe I should know all of them if they aren't rollbackable :P )