On Thu, Aug 27, 2026 at 9:08 AM Durumdara <[email protected]> wrote:
> Dear All!
>
> PGSQL 11.x.
>
> When I start a Transaction from a Client (with PGDAC), and something
> fails, the PGSQL doesn't allow me to do anything else:
>
> ERROR: current transaction is aborted, commands ignored until
> end of transaction block
>
> For example:
> I insert data but after I make a mistake (wrong tablename).
> Then I get this error, and after that I cannot insert the details into a
> log table.
>
> I wanted to check this situation so I created a demo in PGAdmin.
>
> -- create temporary table tlog(id serial primary key, logtime timestamp
> default current_timestamp, msg text);
> -- alter table tlog add xt bigint;
>
> do $$
> declare
> tx bigint;
> txo bigint;
> db bigint;
> sErr text;
> sEC text;
> begin
> select txid_current() into tx;
> raise notice 'TX 0: %', tx;
> select count(*) into db from adm; -- Any table
> insert into tlog(msg, xt) values('start', tx);
> select txid_current() into tx;
> raise notice 'TX 1: %', tx;
> txo = tx;
> select * from fy__tx; -- This table does not exists
> exception
> when others then
> GET STACKED DIAGNOSTICS
> sErr = MESSAGE_TEXT,
> sEC = RETURNED_SQLSTATE;
> raise notice 'Exc: % - %', sEC, sErr;
> select txid_current() into tx;
> raise notice 'TX EQU: %', (tx = txo);
> insert into tlog(msg, xt) values('error ' || sEC || ' - ' || sErr, tx);
> select txid_current() into tx;
> raise notice 'TX EX 2: %', tx;
> end; $$;
>
>
>
To catch the exception then do something else you need to nest BEGIN's so
the exception does not rollback the outer transaction
DO $$
DECLARE
tx bigint;
txo bigint;
db bigint;
sErr text;
sEC text;
BEGIN
SELECT txid_current() INTO tx;
RAISE NOTICE 'TX 0: %', tx;
SELECT count(*) INTO db FROM adm;
INSERT INTO tlog(msg, xt) VALUES ('start', tx);
SELECT txid_current() INTO tx;
RAISE NOTICE 'TX 1: %', tx;
txo := tx;
-- NESTED SUBTRANSACTION BLOCK
BEGIN
-- Fails because table does not exist
PERFORM * FROM fy__tx;
EXCEPTION
WHEN OTHERS THEN
GET STACKED DIAGNOSTICS
sErr = MESSAGE_TEXT,
sEC = RETURNED_SQLSTATE;
RAISE NOTICE 'Nested Exc: % - %', sEC, sErr;
SELECT txid_current() INTO tx;
RAISE NOTICE 'TX EQU: %', (tx = txo);
-- Log the caught error
INSERT INTO tlog(msg, xt)
VALUES ('error ' || sEC || ' - ' || sErr, tx);
SELECT txid_current() INTO tx;
RAISE NOTICE 'TX EX 2: %', tx;
END;
------------------------------------------------------------------
-- We back to the outer transaction so we can now insert data.
INSERT INTO tlog(msg, xt) VALUES ('outer block continued', tx);
RAISE NOTICE 'Outer transaction';
END $$;
Going throw this out there .
be careful with savepoints because they consume multi-x-acts and if they
exceed PG buffer that tracks MultiXact\SLRU performance across the
entire database goes to a crawl. It does take a lot of active MultiXacts
to blow through the buffer. Depends on the traffic of the database
Really hard to identify why the database went to crawl.
PG 17 mitigates this issue allowing us to increase the buffers size
https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MULTIXACT-MEMBER-BUFFERS
