pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedtriggers and NOTIFY
2+ messages / 2 participants
[nested] [flat]
* triggers and NOTIFY
@ 1999-07-02 00:03 Steven Bradley <sbradley@llnl.gov>
0 siblings, 0 replies; 2+ messages in thread
From: Steven Bradley @ 1999-07-02 00:03 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: triggers and NOTIFY
@ 1999-07-04 01:20 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 1999-07-04 01:20 UTC (permalink / raw)
To: Steven Bradley <sbradley@llnl.gov>; +Cc: pgsql-interfaces@hub.org
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:
inline patch 1: pl/plpgsql/src/pl_exec.c (+3/-0 lines) download inline-1.patch
*** 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;
end of thread, other threads:[~1999-07-04 01:20 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-02 00:03 triggers and NOTIFY Steven Bradley <sbradley@llnl.gov>
1999-07-04 01:20 Re: triggers and NOTIFY Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox