Thread: implicit transaction changes trigger behaviour
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.
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
On Thu, Aug 29, 2019 at 2:16 PM Willy-Bas Loos <willybas@gmail.com> wrote: > delete from b; > --DELETE 3 Here the trigger is fired 3 times (for each row), and on every single test it finds a row in 'a', that is your variable n_b_type1 is always 1, that causes the trigger (fired on each row) to not abort. If you delete first the row that makes the trigger fail, you will not be able to do the deletion happen outside an explicit transaction: testdb=# delete from b where type = 1; DELETE testdb=# delete from b; 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 5 would have 0 b-records of type 1, so the operation has been cancelled. So it seems to me a problem within the trigger: when executing outside the transaction your row is deleted as last, and this makes the deletion "iterate" and remove all the rows. Within the transaction, when the trigger fires, no rows are there, so it fails. Either this is what you have to do or your query within the trigger is wrong. Luca
Willy-Bas Loos <willybas@gmail.com> writes:
> 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 think the issue is that you marked the trigger as STABLE. That causes
it to use the calling query's snapshot so it doesn't see the updates,
if it's fired during the delete query and not during the subsequent
COMMIT. If I remove the STABLE label then it works as you expect.
This is probably under-documented but I'm not sure that it should be
considered a bug.
The trigger seems a bit broken besides that, in that the comments claim it
has something to do with the OLD row's id field(s) but the query is not in
fact taking that into account.
regards, tom lane
Thank you so much, the "stable" thing was it.
I'm not sure if it is underdocumented, i clearly didn't adhere to the rule that a stable function " is guaranteed to return the same results given the same arguments for all rows within a single statement".
BTW in my example i made a mistake too, but that was beside the point really.
Cheers,
Willy-Bas
On Thu, Aug 29, 2019 at 3:35 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Willy-Bas Loos <willybas@gmail.com> writes:
> 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 think the issue is that you marked the trigger as STABLE. That causes
it to use the calling query's snapshot so it doesn't see the updates,
if it's fired during the delete query and not during the subsequent
COMMIT. If I remove the STABLE label then it works as you expect.
This is probably under-documented but I'm not sure that it should be
considered a bug.
The trigger seems a bit broken besides that, in that the comments claim it
has something to do with the OLD row's id field(s) but the query is not in
fact taking that into account.
regards, tom lane
--
Willy-Bas Loos