Anyone who uses psql to update production db could get a nasty surprise

If you updating your schema using something like psql -d postgres -f migrations.sql or even docker exec -i postgrestest psql -h localhost -d postgres -f migrations.sql — please stop! And it’s not about best practices — it’s about specifics of executing scripts that way. If you have some number of transactions in the script, something like

START TRANSACTION;
--transaction A
COMMIT;
START TRANSACTION;
--transaction B destine to failure
COMMIT;
START TRANSACTION;
--transaction C
COMMIT;

And if you will get errors in some transactions in the middle (say transaction B), then psql will execute the rest of the migrations still. So the state of the database is unknown. If your script depends on sequential execution — this will get you into trouble. Of cause using Liquibase, Flyway or some other special tool most certainly will save you from troubles like this one.

If you want to see steps to reproduce, here is a repo.

References:

GitHub - Sa1Gur/psql-nasty-surprise: the repo to illustrate medium article