Am I in the same transaction block in complex PLPGSQL?
Hello!
A critical question for me because of future planning.
In autocommit mode, when I start a simple update or select, it is one
transaction, so if something fails, the whole modification is rolled back
(there is no "half update", or "only first record updated").
What will happen with complex statements, like PLPGSQL stored procedure, or
trigger?
Pseudo:
BEGIN
select a from b ... into X;
update b set a = X + 1 where ...;
update b set mod_date = current_timestamp where ...;
IF bla THEN raise Exception 'Wrong';
select x from y;
call stored procedure N;
...
update b set mod_date = current_timestamp where ...;
END;
Is it also one statement logically (doesn't matter it would be recursive)?
Is this executed in one transaction? Or each substatement is a new
transaction?
So if the half executed but we got an exception in the center:
a.) the whole rolled back,
b.) or it is halfly finished and only the last substatement rolled back.
The main question is:
Do I need to embed the whole call into a directly started transaction block
to keep consistency - or this isn't needed because this is handled by PG as
one big statement with many sub statements?
This is also important for triggers to...
Triggers (insert/update/delete) also could start subprocedures with
substatements, so if I don't wrap around a directly started transaction,
maybe I will get the wrong result (not ALL OK/ALL FAILS).
Thank you for your help!
Best regards
dd
Durumdara <durumdara@gmail.com> writes:
In autocommit mode, when I start a simple update or select, it is one
transaction, so if something fails, the whole modification is rolled back
(there is no "half update", or "only first record updated").
What will happen with complex statements, like PLPGSQL stored procedure, or
trigger?
It's one transaction unless you take steps to make it something else.
In plpgsql, you can make "sub transactions" with BEGIN/EXCEPTION blocks
(an exception rolls back only side-effects that happened since BEGIN).
Or, if it's a procedure not a function, you can explicitly COMMIT
what's been done so far, and then your next action starts a new
transaction. But without such measures, all actions executed by
a function are part of the same transaction as the calling statement.
regards, tom lane
Dear Tom!
Ok, very-very thanks for the answer! I have the same experience with txid:
DO $$DECLARE tx bigint;
BEGIN
select txid_current() into tx;
raise notice ''TXID: %'', tx;
insert into a values (26);
select txid_current() into tx;
raise notice ''TXID: %'', tx;
insert into a values (27);
select txid_current() into tx;
raise notice ''TXID: %'', tx;
insert into a values (28);
select txid_current() into tx;
raise notice ''TXID: %'', tx;
call test_trx_value(30);
select txid_current() into tx;
raise notice ''TXID: %'', tx;
END$$;
All of them are the same.
Then back to my previous question:
How to log to client (port native code to stored procedure) + to db tables
The main problem that the code is local now. A Win32 app, with many logs.
Some of them stored into the database log tables for easily get.
But: when the transaction is same, I can't log in *PGSQL 9.6* - only to
client with "notices".
F. e. I have a stored procedure which has an exception handler block to
catch the log and error, and pass back to the caller.
On exception this catches the error, and returns with error + log + stored
them in a table record.
But the caller also must raise an exception, for rollback and stop. In this
time the exception rollbacks my table level logs too (log table rows).
Only client notices could help me what happened.
Same problem if the process started by a trigger (for example).
So if any problem happens, I will stand without any help why it happened. I
can't see from db log tables, because of the posted log records will vanish
on rollback!
I have access only to a database - not to the server (and it's real logs).
Hmmm....
Do you have any idea? Or we must upgrade to min. PGSQL 11 for access
transaction handling and could post the logs through another transaction?
F. e.
...
call SubProc(oError, oLog);
if oError > '' then
rollback; <--- on Error we must roll back
end if;
insert mylog values(oError, oLog); <--- post log everytime
if oError > '' then
commit; <--- on error we save the logs with commit + raise an error
again
raise Exception oError;
end if;
...
Thanks for it!
dd
Tom Lane <tgl@sss.pgh.pa.us> ezt írta (időpont: 2022. márc. 11., P, 16:01):
Show quoted text
Durumdara <durumdara@gmail.com> writes:
In autocommit mode, when I start a simple update or select, it is one
transaction, so if something fails, the whole modification is rolled back
(there is no "half update", or "only first record updated").
What will happen with complex statements, like PLPGSQL stored procedure,or
trigger?
It's one transaction unless you take steps to make it something else.
In plpgsql, you can make "sub transactions" with BEGIN/EXCEPTION blocks
(an exception rolls back only side-effects that happened since BEGIN).
Or, if it's a procedure not a function, you can explicitly COMMIT
what's been done so far, and then your next action starts a new
transaction. But without such measures, all actions executed by
a function are part of the same transaction as the calling statement.regards, tom lane
On Fri, Mar 11, 2022 at 9:24 AM Durumdara <durumdara@gmail.com> wrote:
Do you have any idea? Or we must upgrade to min. PGSQL 11 for access
transaction handling and could post the logs through another transaction?
You really do need to open a second session somehow if you want the first
session to be able to fail while still allowing for stuff happening during
its execution to commit.
You can either do this in client-side code by simply opening up two
connections and doing the main work on one while doing the logging on the
other.
As far as I know to do this in-database you would need to use the dblink
extension:
https://www.postgresql.org/docs/current/dblink.html
While much of that module's purpose was subsumed by the addition of FOREIGN
DATA WRAPPERS the ability to have a transaction independent connection back
into the database was not one of those things - using FDW the remote work
is done in the same transaction context as the local database.
David J.