Re: Basic question about structuring SQL

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