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