Question on notifications

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

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?

Am 30.04.2012 um 09:53 schrieb Albe Laurenz:

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?

Am 30.04.2012 um 09:53 schrieb Albe Laurenz:

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: