On Tue, 2020-07-07 at 12:40 +0100, Robert Inder wrote: > NOW, I want to do the same for a BIGGER task. > BUT I realise that if I create bigger-task.sql like this... > BEGIN; > <<preparatory operations>> > \i do-something-useful.sql > <<tidy up code>> > COMMIT; > ...the COMMIT inside "do-something-useful.sql" closes the transaction started > in "bigger-task.sql" > So I can get some things committed even if other things (in tidy-up) fail. > > So how SHOULD I tackle this? > PostgreSQL does not do nested transactions (right?)
You can use subtransactions. In the inner scripts, don't use BEGIN, but SAVEPOINT <name>. Instead of ROLLBACK in the inner script, use ROLLBACK TO SAVEPOINT <name>. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
