Re: Trigger function not executing

----- Original Message -----
From: Donald Fraser
To: pgsql-bugs(at)postgresql(dot)org
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 ON 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 = '2'; then I don't get any problems.
If I run SQL such as DELETE FROM tbl_user WHERE id > '40'; and there is only one record that meets the condition (id > '40') then I don't get any problems.
If I run SQL such as DELETE FROM tbl_user WHERE id > '20'; and there is more than one record that meets the condition (id > '20') then I get the following problem. The BEFORE trigger function is only being called on the first record that is being deleted yet the AFTER function is being called on all 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 -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Donald Fraser" <demolish(at)cwgsy(dot)net>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Sent: Thursday, February 06, 2003 2:42 PM
Subject: Re: [BUGS] Trigger function not executing

> "Donald Fraser" <demolish(at)cwgsy(dot)net> writes:
> > If I run SQL such as DELETE FROM tbl_user WHERE id > '20'; and there is mor=
> > e than one record that meets the condition (id > '20') then I get the follo=
> > wing problem. The BEFORE trigger function is only being called on the first=
> > record that is being deleted
>
> 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.
>
> regards, tom lane
>
Tom I have had another look at this problem and you were correct in that the trigger functions were being called, just not in the order that I had expected. There were "no bugs" in my code as such, just a total misunderstanding on how trigger functions are processed in a SQL command that effects more than one record - hence the reason why I'm writing this email. I don't want other people to be caught out with the same problem and it may well be worth putting a note in the documentation under the trigger manager section and correcting the example in Chapter 16.4.

I made the following assumption (based on the documents): The sequence of events 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 = '20'; DELETE FROM tbl_user WHERE id = '21'; would produce exactly the same sequence of events as DELETE FROM tbl_user WHERE (id >= '20' AND id <= '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 = 20;
2: Trigger AFTER EVENT for record with id = 20;
3: Trigger BEFORE EVENT for record with id = 21;
4: Trigger AFTER EVENT for record with id = 21;

The sequence of events for the second set of SQL statements is as follows:
1: Trigger BEFORE EVENT for record with id = 20;
2: Trigger BEFORE EVENT for record with id = 21;
3: Trigger AFTER EVENT for record with id = 20;
4: Trigger AFTER EVENT for record with id = 21;

According to the example in the documentation - 'Chapter 16.4 Examples', the sequence of events for the DELETE is as follows:
vac=> 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 what 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=# 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=# 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=# 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=# 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=# DELETE FROM ttest WHERE x >= '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 there was a bug with triggers, albeit that I was pointing the finger at the wrong thing. Next time I want be so keen to come forward with a bug without investigating it further - sorry for that.

Regards
Donald Fraser.