implicit transaction changes trigger behaviour

From: Willy-Bas Loos <willybas(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: implicit transaction changes trigger behaviour
Date: 2019-08-29 12:15:55
Message-ID: CAHnozThxLxCanw=UjSnRaefg20SP+HUq_y3-u0+gP3n8yRti_Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I currently have a fairly complex use case to solve and one thing i tried
was a deferred constraint trigger. I'm not sure if this solution is the way
to go, but anyway: As i was testing my code, i noticed that the trigger
behaves differently depending on whether or not i explicitly use BEGIN and
COMMIT, even though there is only 1 query in the transaction.
I am wondering if this is a bug in postgresql?
I'm using postgresql 10.10 on Debian.

Here's an example that reproduces the behaviour:

/*
https://www.postgresql.org/docs/10/sql-createtrigger.html
Constraint triggers must be AFTER ROW triggers on plain tables (not foreign
tables). They can be fired either at the end of the statement causing the
triggering event, or at the end of the containing transaction; in the
latter case they are said to be deferred. A pending deferred-trigger firing
can also be forced to happen immediately by using SET CONSTRAINTS.
Constraint triggers are expected to raise an exception when the constraints
they implement are violated.
*/

create table a(a_id serial primary key);
create table b(b_id serial primary key, a_id integer not null, type integer
not null);
create or replace function has_1b_type1() returns trigger as $$
declare
n_b_type1 integer; --the number of records in table b with type 1 that
correspond to OLD.id
begin
select count(*) into n_b_type1
from b
join a on b.a_id = a.a_id
where b.type = 1;
if n_b_type1 != 1 then
raise exception 'Each record of a must have exactly 1 corresponding records
in b of type 1. But after this delete the a-record with id % would have %
b-records of type 1, so the operation has been cancelled.', OLD.a_id,
n_b_type1;
else
--The return value is ignored for row-level triggers fired after an
operation, and so they can return NULL.
return null;
end if;
end
$$ language plpgsql stable;
create constraint trigger tr_has_1b_type1_del
after delete on b
deferrable initially deferred for each row
execute procedure has_1b_type1();

begin;
insert into a (a_id)
values(nextval('a_a_id_seq'));
insert into b(a_id, type)
values(currval('a_a_id_seq'), 1);
--also some other data, just to illustrate
insert into b(a_id, type)
values(currval('a_a_id_seq'), 2);
insert into b(a_id, type)
values(nextval('a_a_id_seq'), 3);
commit;

begin;
delete from b;
commit;
--ERROR: Each record of a must have exactly 1 corresponding records in b
of type 1. But after this delete the a-record with id 1 would have 0
b-records of type 1, so the operation has been cancelled.

delete from b;
--DELETE 3
--Query returned successfully in 91 msec.

--
Willy-Bas Loos

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Magnus Hagander 2019-08-29 12:17:14 Re: Security patch older releases
Previous Message francis picabia 2019-08-29 11:43:18 Re: How to log 'user time' in postgres logs