Basic question about structuring SQL

From: Robert Inder <robert(at)interactive(dot)co(dot)uk>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Basic question about structuring SQL
Date: 2020-07-07 11:40:55
Message-ID: CAKqjJm8Da8V-T0+kpg8jCqWHejKPiMbqqOKgCeSpeMkPWjV7cw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jason Wang 2020-07-07 13:14:18 Re: Basic question about structuring SQL
Previous Message Chris Sterritt 2020-07-07 11:13:42 Transaction control in SECURITY DEFINER procedures