I don't think nested commit is supported however you might want to put logic in do-something-useful.sql into a stored procedure without commit and your BIGGER task just calls this SP and commits at the end; you can run the SP by itself to have transaction protected.
/Jason On Tue, 7 Jul 2020 at 21:41, Robert Inder <rob...@interactive.co.uk> wrote: > I'm an experienced programmer but really new to SQL, > and I'm wanting a pointer to "the SQL way" to structure/organise chunks of > code. > > A while back, I wrote a chunk of SQL to Do Something Useful. > I put it in a file (do-something-useful.sql). > And, to protect against getting into a weird state, I wrapped the code in > my file with > BEGIN; > UPDATE.... > DELETE... > COMMIT; > With the idea that I can do > psql my_database > \i do-something-useful.sql > And be sure that either my task will be have been completed, or nothing > with have changed. > > 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?) > > So how should I structure my chunks of SQL so that I can have "safe" > (all-or-nothing) blocks, > AND use them from within one another? > > Robert > >
