----- Original Message -----=20
From: Donald Fraser=20
To: pgsql-bugs@postgresql.org=20
Sent: Thursday, February 06, 2003 11:27 AM
Subject: [BUGS] Trigger function not executing
OS: Redhat, kernel 2.4
PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC 2.96
I have a table that has two triggers functions applied to it
1) CREATE TRIGGER trig_delete_user BEFORE DELETE ON tbl_user FOR EACH ROW=
EXECUTE PROCEDURE delete_user();
2) CREATE TRIGGER trig_insert_sys_user AFTER INSERT OR DELETE OR UPDATE O=
N tbl_user FOR EACH ROW EXECUTE PROCEDURE insert_sys_user();
The BEFORE trigger function basically does nothing to modify the tuple in=
any way. It does run a SELECT on the same table to do some routine checks =
and before it can exit the function it sets a global flag to tell the AFTER=
trigger function what to do.
The AFTER trigger function resets the flag set by the BEFORE function and=
then performs some administration commands for users . In this instance it=
issues a "DROP USER" command.
I've included the above summary as an insight to where the problem may be.
Here is the problem:
If I run SQL such as DELETE FROM tbl_user WHERE id =3D '2'; then I don't =
get any problems.
If I run SQL such as DELETE FROM tbl_user WHERE id > '40'; and there is o=
nly one record that meets the condition (id > '40') then I don't get any pr=
oblems.
If I run SQL such as DELETE FROM tbl_user WHERE id > '20'; and there is m=
ore than one record that meets the condition (id > '20') then I get the fol=
lowing problem. The BEFORE trigger function is only being called on the fir=
st record that is being deleted yet the AFTER function is being called on a=
ll of the records - well in this case it only gets to the second record and=
fails because the flag has not been set by the BEFORE function. Just to re=
-iterate - the BFORE function is very simple - it cannot exit the function =
without setting the flag I have mentioned.
Have I misunderstood trigger functions or is this a legitimate bug?
Regards
Donald Fraser
----- Original Message -----=20
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Donald Fraser" <demolish@cwgsy.net>
Cc: <pgsql-bugs@postgresql.org>
Sent: Thursday, February 06, 2003 2:42 PM
Subject: Re: [BUGS] Trigger function not executing=20
> "Donald Fraser" <demolish@cwgsy.net> writes:
> > If I run SQL such as DELETE FROM tbl_user WHERE id > '20'; and there =
is mor=3D
> > e than one record that meets the condition (id > '20') then I get the=
follo=3D
> > wing problem. The BEFORE trigger function is only being called on the=
first=3D
> > record that is being deleted=20
>=20
> I don't believe it --- and you haven't provided any evidence to support
> your claim. More than likely, this is just an error in your own trigger
> code; but with no details it's pointless to speculate.
>=20
> regards, tom lane
>=20
Tom I have had another look at this problem and you were correct in that th=
e trigger functions were being called, just not in the order that I had exp=
ected. There were "no bugs" in my code as such, just a total misunderstandi=
ng on how trigger functions are processed in a SQL command that effects mor=
e than one record - hence the reason why I'm writing this email. I don't wa=
nt other people to be caught out with the same problem and it may well be w=
orth putting a note in the documentation under the trigger manager section =
and correcting the example in Chapter 16.4.=20
I made the following assumption (based on the documents): The sequence of e=
vents for trigger functions would be exactly the same regardless of the SQL=
statement/s to achieve the same result. That is I thought that for example=
: DELETE FROM tbl_user WHERE id =3D '20'; DELETE FROM tbl_user WHERE id =3D=
'21'; would produce exactly the same sequence of events as DELETE FROM tb=
l_user WHERE (id >=3D '20' AND id <=3D '21'); This is not the case.
The sequence of events for the first set of SQL statements is as follows:
1: Trigger BEFORE EVENT for record with id =3D 20;
2: Trigger AFTER EVENT for record with id =3D 20;
3: Trigger BEFORE EVENT for record with id =3D 21;
4: Trigger AFTER EVENT for record with id =3D 21;
The sequence of events for the second set of SQL statements is as follows:
1: Trigger BEFORE EVENT for record with id =3D 20;
2: Trigger BEFORE EVENT for record with id =3D 21;
3: Trigger AFTER EVENT for record with id =3D 20;
4: Trigger AFTER EVENT for record with id =3D 21;
According to the example in the documentation - 'Chapter 16.4 Examples', th=
e sequence of events for the DELETE is as follows:
vac=3D> DELETE FROM ttest;
INFO: trigf (fired before): there are 2 tuples in ttest
INFO: trigf (fired after ): there are 1 tuples in ttest
INFO: trigf (fired before): there are 1 tuples in ttest
INFO: trigf (fired after ): there are 0 tuples in ttest
So you can see that the documented sequence of events is not the same as wh=
at actually happens.
To prove this I created and ran the example from the documentation (cut and=
paste exactly from the documentation).
Here is the output that I got:
CISX=3D# INSERT INTO ttest VALUES('100');
NOTICE: trigf (fired before): there are 0 tuples in ttest
NOTICE: trigf (fired after ): there are 1 tuples in ttest
INSERT 17497 1
CISX=3D# INSERT INTO ttest VALUES('101');
NOTICE: trigf (fired before): there are 1 tuples in ttest
NOTICE: trigf (fired after ): there are 2 tuples in ttest
INSERT 17498 1
CISX=3D# INSERT INTO ttest VALUES('102');
NOTICE: trigf (fired before): there are 2 tuples in ttest
NOTICE: trigf (fired after ): there are 3 tuples in ttest
INSERT 17499 1
CISX=3D# INSERT INTO ttest VALUES('122');
NOTICE: trigf (fired before): there are 3 tuples in ttest
NOTICE: trigf (fired after ): there are 4 tuples in ttest
INSERT 17500 1
CISX=3D# DELETE FROM ttest WHERE x >=3D '100';
NOTICE: trigf (fired before): there are 4 tuples in ttest
NOTICE: trigf (fired before): there are 3 tuples in ttest
NOTICE: trigf (fired before): there are 2 tuples in ttest
NOTICE: trigf (fired before): there are 1 tuples in ttest
NOTICE: trigf (fired after ): there are 0 tuples in ttest
NOTICE: trigf (fired after ): there are 0 tuples in ttest
NOTICE: trigf (fired after ): there are 0 tuples in ttest
NOTICE: trigf (fired after ): there are 0 tuples in ttest
DELETE 4
As you can see the actual sequence of events differs to what is documented =
and what I had logically expected - hence why I was questioning whether the=
re was a bug with triggers, albeit that I was pointing the finger at the wr=
ong thing. Next time I want be so keen to come forward with a bug without i=
nvestigating it further - sorry for that.
Regards
Donald Fraser.
