Am I in the same transaction block in complex PLPGSQL?

From: Durumdara <durumdara(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Am I in the same transaction block in complex PLPGSQL?
Date: 2022-03-11 08:53:44
Message-ID: CAEcMXhkYe02Fpzfgo4SFMjNUZzKCSbC1ses9drDvqpwNxs0f=A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ian Dauncey 2022-03-11 10:02:39 Postgres query
Previous Message Tom Lane 2022-03-10 17:38:27 Re: foreign key on delete cascade order?