Re: Trigger function not executing - Mailing list pgsql-bugs

----- 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.