Jonah E. Safar (points@vfocus.com) reports a bug with a severity of 3
The lower the number the more severe it is.
Short Description
after trigger not working in begin-commit block
Long Description
As part of a registration system, we use a couple of before insert or update triggers to check certain cross-table
values,and then an after insert or update trigger to maintain per-row timestamps based on a number of conditions that
thetrigger code handles. When in an automatic commit, everything works as expected, however, when we used BEGIN; ...
COMMIT;blocks, only the first insert or update actually triggers our 'after' code. We've added notification to let us
knowif the code is actually even being called (and not just erroring out for some reason), but it appears that the
triggernever seems to happen at all. I searched through the mailing lists, documentation and bug database for any
informationon this sort of behavior, but was unable to find any information at all.
select version();
PostgreSQL 7.0.2 on i586-pc-linux-gnu, compiled by gcc egcs-2.91.66
Linux 2.2.12-20, heavily modified RedHat 6.1.
Sample Code
<Please contact me if you'd like the C code and compilation instructions used.>
CREATE FUNCTION datetrig() RETURNS opaque AS '/usr/local/pgsql/datetrig.so' LANGUAGE 'c';
CREATE TRIGGER tb_person_trigger AFTER INSERT OR UPDATE
ON tb_Person FOR EACH ROW
EXECUTE PROCEDURE datetrig('Person_ID');
CREATE TRIGGER tb_membership_trigger AFTER INSERT OR UPDATE
ON tb_Membership FOR EACH ROW
EXECUTE PROCEDURE datetrig('Person_ID','Year');
This works as expected in psql --
insert tb_person(person_id) values (61);
insert tb_membership(person_id,year) values (61,1992);
This does not work --
BEGIN;
insert tb_person(person_id) values (61);
insert tb_membership(person_id,year) values (61,1992);
COMMIT;
No file was uploaded with this report
