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; $$;
This code works well. The TX ID is the same (not changed).
The fy_txt table misses.
The first log ("Start") misses from the log table, but the error is visible.
NOTICE: TX 0: 2295889202
NOTICE: TX 1: 2295889202
NOTICE: Exc: 42P01 - relation "fy__tx" does not exist
NOTICE: TX EQU: t
NOTICE: TX EX 2: 2295889202
DO
Why didn't I get this error in the Exception block?
ERROR: current transaction is aborted, commands ignored until
end of transaction block
The TX ID is the same. So I really don't understand this.
I supposed I get an error, because the transaction rolled back on
select * from fy__tx;
line. This is why the "Start" record is missing from the log table.
But how can I insert a record into the log by using the same transaction
which rolled back before?
I supposed it is impossible, because the transaction is rolled back
automatically by the PGSQL.
Only it's possible if the transaction number is different.
So I am really confused.
I tried to use a direct transaction before and after that DO block. Same
result I got.
I tried to change PGAdmin's Exec settings to "No autocommit". Same result.
So maybe it has differences between client transaction handling?
Or only DO blocks have this effect?
Or does the PGAdmin do something differently?
Thank you for any explanation.
Best regards
dd
