Thread: triggers and NOTIFY

My Problem:
I would like to notify a front-end process when changes are made to a
particular table (table A) and would also like to communicate information
about the changes (ie: which record changed) to the "listening" front end
process via a second table (table B).

My Attempted Solution:
The front-end listening process issues a LISTEN and then calls pqWait() to
wait for a notification.  The way I'm issuing the NOTIFY is through a
trigger which fires when table A is modified.  When the trigger simply
issues the NOTIFY, the front-end process recieves the notification just
fine.  However, when I also try to INSERT a record into table B from within
the trigger (either before or after issuing the NOTIFY), the record gets
inserted into table B, but the front-end application does not recieve a
notification.

What's going on?  Any advice?


Example trigger/function:
 create function f_notify() returns opaque as ' begin
 insert into B values (...); notify wake_up; return NULL;
end;
' language 'plpgsql';


create trigger t_notify after update on A
for each row
execute procedure f_notify();



Thanks in advance...










I am trying to send a NOTIFY to a "listening" process and am using a table
to communicate information about the notific


Steven Bradley
Lawrence Livermore National Laboratory
PO Box 808
Livermore, California 94550
(925) 423-2101       sbradley@llnl.gov

Steven Bradley <sbradley@llnl.gov> writes:
> When the trigger simply
> issues the NOTIFY, the front-end process recieves the notification just
> fine.  However, when I also try to INSERT a record into table B from within
> the trigger (either before or after issuing the NOTIFY), the record gets
> inserted into table B, but the front-end application does not recieve a
> notification.

What version are you using?  The test case you gave causes a repeatable
core dump in 6.5 for me, so it was pretty easy to find the cause.
I have committed a fix for 6.5.1, but if you are in a hurry:

*** pl/plpgsql/src/pl_exec.c.orig    Tue May 25 12:15:17 1999
--- pl/plpgsql/src/pl_exec.c    Sat Jul  3 21:03:01 1999
***************
*** 2486,2491 ****
--- 2486,2494 ----      * 2. It must be a RESULT plan --> no scan's required      * ----------      */
+     if (plan == NULL)            /* utility statement produces this */
+         return;
+      if (nodeTag(plan) != T_Result)         return; 

This might be the same problem that someone (Vince, maybe?) was
complaining about a couple months ago --- I didn't look into it
at the time.
        regards, tom lane