Re: Basic question about structuring SQL

[Jason Wang <jasonwang.public@xxxxxxxxx>]

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@xxxxxxxxxxxxxxxxx> 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