Basic question about structuring SQL
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
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@interactive.co.uk> wrote:
Show quoted text
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
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
On Tue, Jul 7, 2020 at 4:41 AM Robert Inder <robert@interactive.co.uk>
wrote:
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?
While there are more advanced constructs that may aid here I would suggest
just following two rules:
top-level scripts handle transactions - usually just a single BEGIN/COMMIT
at the top/bottom of the script respectively. These are scripts you are
allowed to name on the psql command line.
support scripts don't handle transactions - these are scripts you include
into the main script with \i or \ir
David J.