Re: Basic question about structuring SQL

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 <robert(at)interactive(dot)co(dot)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
>
>