Обсуждение: Question on notifications
Hi, From the documentation I was able to build a trigger firing upon deletion of a record a function that delivers tablename_operationas a notification one needs to subscribe to. So in terminal I can say LISTEN persons_delete and instantlywill receive Asynchronous notification "persons_delete" received from server process with PID 54790. if there was a delete. But what I don't fully understand is how to do this with PQnotifies. Following the docu I get no notificationseven though I subscribe to them after successfully connecting to the server the same way I do using terminal. Googling didn't give me examples I was able to use. Please, can someone help? Thanks Alex
Вложения
Alexander Reichstadt wrote: > Thanks, I had checked the example before but couldn't make sense out of it in terms of wrapping it in > Objective-C. I left it in C now and it works fine. > > The trigger I am using now looks like this: > > CREATE FUNCTION notify_trigger() RETURNS trigger AS $$ > > > DECLARE > > > BEGIN > IF ( TG_OP = 'INSERT' ) THEN > execute 'NOTIFY ' || TG_TABLE_NAME || '_' || TG_OP || ', ' || NEW.oid; > ELSE > execute 'NOTIFY ' || TG_TABLE_NAME || '_' || TG_OP || ', ' || OLD.oid; > END IF; > return NULL; > END; > > > $$ LANGUAGE plpgsql; > > it works if I remove transmission of the OID. If I do transmit the OID I get > > > DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. > CONTEXT: PL/pgSQL function "notify_trigger" line 1 at EXECUTE statement > > > The trigger is firing above function AFTER the TG_OP took place. The error is thrown upon insertion. > Doesn't the record exist given I trigger AFTER and not BEFORE the operation took palce? Right, and I cannot reproduce the error you get. Here's what I do (on PostgreSQL 9.1.3): CREATE OR REPLACE FUNCTION notify_trigger() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN EXECUTE 'NOTIFY ' || TG_TABLE_NAME || '_' || TG_OP || ', ''' || CASE WHEN TG_OP = 'INSERT' THEN NEW.oid ELSE OLD.oid END || ''''; RETURN NEW; END$$; Observe that the second argument to NOTIFY is a string. CREATE TABLE t1(val text, PRIMARY KEY (oid)) WITH OIDS; CREATE TRIGGER t1_trig AFTER INSERT OR UPDATE OR DELETE ON t1 FOR EACH ROW EXECUTE PROCEDURE notify_trigger(); INSERT INTO t1 (val) VALUES ('test'); UPDATE t1 SET val=NULL; DELETE FROM t1; A second session subscribed to the events gets: Asynchronous notification "t1_insert" with payload "46728" received from server process with PID 18687. Asynchronous notification "t1_update" with payload "46728" received from server process with PID 18687. Asynchronous notification "t1_delete" with payload "46728" received from server process with PID 18687. Yours, Laurenz Albe
Thank you. You don't get the error, because you called FOR EACH ROW EXECUTE, which I didn't. I fixed it, it all works now!
Am 30.04.2012 um 14:58 schrieb Albe Laurenz:
CREATE TRIGGER t1_trig AFTER INSERT OR UPDATE OR DELETE ON t1
FOR EACH ROW EXECUTE PROCEDURE notify_trigger();
Вложения
Thanks, I had checked the example before but couldn't make sense out of it in terms of wrapping it in Objective-C. I left it in C now and it works fine.
The trigger is firing above function AFTER the TG_OP took place. The error is thrown upon insertion. Doesn't the record exist given I trigger AFTER and not BEFORE the operation took palce?
The trigger I am using now looks like this:
CREATE FUNCTION notify_trigger() RETURNS trigger AS $$
DECLARE
BEGIN
IF ( TG_OP = 'INSERT' ) THEN
execute 'NOTIFY ' || TG_TABLE_NAME || '_' || TG_OP || ', ' || NEW.oid;
ELSE
execute 'NOTIFY ' || TG_TABLE_NAME || '_' || TG_OP || ', ' || OLD.oid;
END IF;
return NULL;
END;
$$ LANGUAGE plpgsql;
it works if I remove transmission of the OID. If I do transmit the OID I get
DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT: PL/pgSQL function "notify_trigger" line 1 at EXECUTE statement
Am 30.04.2012 um 09:53 schrieb Albe Laurenz:
Alexander Reichstadt wrote:From the documentation I was able to build a trigger firing upondeletion of a record a function thatdelivers tablename_operation as a notification one needs to subscribeto. So in terminal I can sayLISTEN persons_delete and instantly will receiveAsynchronous notification "persons_delete" received from serverprocess with PID 54790.if there was a delete. But what I don't fully understand is how to dothis with PQnotifies. Followingthe docu I get no notifications even though I subscribe to them aftersuccessfully connecting to theserver the same way I do using terminal.Googling didn't give me examples I was able to use. Please, cansomeone help?
Did you look at the example in the documentation?
http://www.postgresql.org/docs/current/static/libpq-example.html#LIBPQ-E
XAMPLE-2
Can you post relevant parts of your code?
Yours,
Laurenz Albe
Вложения
Alexander Reichstadt wrote: > From the documentation I was able to build a trigger firing upon deletion of a record a function that > delivers tablename_operation as a notification one needs to subscribe to. So in terminal I can say > LISTEN persons_delete and instantly will receive > > Asynchronous notification "persons_delete" received from server process with PID 54790. > > if there was a delete. But what I don't fully understand is how to do this with PQnotifies. Following > the docu I get no notifications even though I subscribe to them after successfully connecting to the > server the same way I do using terminal. > > Googling didn't give me examples I was able to use. Please, can someone help? Did you look at the example in the documentation? http://www.postgresql.org/docs/current/static/libpq-example.html#LIBPQ-E XAMPLE-2 Can you post relevant parts of your code? Yours, Laurenz Albe