Thread: Restrictions of channel arg of pg_notofy
Hi everyone! Can the channel argument derived from the NEW pseudo arg of an INSERT TRIGGER? In the following trigger function, the PERFORM pg_notify(fac); does not work (LISTEN in psql shows no notification). Any help welcome. Thanks, Axel CREATE TRIGGER new_event_trigger AFTER INSERT OR UPDATE ON syslog.event FOR EACH ROW EXECUTE PROCEDURE syslog.new_event_action(); CREATE OR REPLACE FUNCTION syslog.new_event_action() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE fac TEXT := format('f0%s', NEW.facility); BEGIN IF NEW.facility > 9 THEN fac := format('f%s', NEW.facility); END IF; IF NEW.facility = 8 THEN INSERT INTO pf_event (id) VALUES (NEW.id); END IF; PERFORM pg_notify(fac); RETURN NEW; END; $$; --- PGP-Key:29E99DD6 ☀ computing @ chaos claudius
Axel Rau <Axel.Rau@Chaos1.DE> writes: > Can the channel argument derived from the NEW pseudo arg of an INSERT TRIGGER? > In the following trigger function, the > PERFORM pg_notify(fac); > does not work (LISTEN in psql shows no notification). That should work. I think more likely what you're running into is that the NOTIFY won't be delivered until end of transaction? regards, tom lane
Am 27.04.2019 um 20:51 schrieb Tom Lane <tgl@sss.pgh.pa.us>:I think more likely what you're running into is that the NOTIFY won't
be delivered until end of transaction?
I have investigated further:
pg_notify does not work at all, even with a constant string channel arg.
NOTIFY works.
This is release 11.2.
What am I doing wrong?
Axel
---
PGP-Key:29E99DD6 ☀ computing @ chaos claudius
PGP-Key:29E99DD6 ☀ computing @ chaos claudius
Axel Rau <Axel.Rau@chaos1.de> writes: > I have investigated further: > pg_notify does not work at all, even with a constant string channel arg. > NOTIFY works. > This is release 11.2. [ shrug... ] Works for me. > What am I doing wrong? Hard to tell when you haven't provided a complete example. Just looking at the code you did show, though, I notice that you have PERFORM pg_notify(fac); but there's no single-argument form of pg_notify in standard PG: regression=# \df pg_notify List of functions Schema | Name | Result data type | Argument data types | Type ------------+-----------+------------------+---------------------+------ pg_catalog | pg_notify | void | text, text | func (1 row) Maybe whatever shim you've got for that doesn't work right? regards, tom lane
More details:Am 28.04.2019 um 19:30 schrieb Axel Rau <Axel.Rau@Chaos1.DE>:This is my test case with constant string:Am 28.04.2019 um 19:02 schrieb Tom Lane <tgl@sss.pgh.pa.us>:Hard to tell when you haven't provided a complete example.CREATE OR REPLACE FUNCTION syslog.new_event_action() RETURNS triggerLANGUAGE plpgsqlAS $$BEGINRAISE WARNING 'syslog.new_event_action() called.';PERFORM pg_notify('INSERTED', '');RETURN NEW;END$$;The warning is being logged.In psql session 1, I run a LISTEN INSERTED;In psql session 2, I run my INSERT (getting one row inserted)In psql session 1, I run SELECT now(); do not get asyncronous notification.
pg_catalog in not in search_path.
Axel
---
PGP-Key:29E99DD6 ☀ computing @ chaos claudius
PGP-Key:29E99DD6 ☀ computing @ chaos claudius
Axel Rau <Axel.Rau@Chaos1.DE> writes: >> Am 28.04.2019 um 19:02 schrieb Tom Lane <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>: >>> Hard to tell when you haven't provided a complete example. >> This is my test case with constant string: >> CREATE OR REPLACE FUNCTION syslog.new_event_action() RETURNS trigger >> LANGUAGE plpgsql >> AS $$ >> BEGIN >> RAISE WARNING 'syslog.new_event_action() called.'; >> PERFORM pg_notify('INSERTED', ''); >> RETURN NEW; >> END >> $$; >> >> The warning is being logged. >> >> In psql session 1, I run a LISTEN INSERTED; If you're typing it exactly like that, you have a case-folding problem. Try LISTEN "INSERTED"; instead, or make the pg_notify argument lower-case. However, that doesn't seem like it would explain your original problem, since that didn't involve upper-case letters. regards, tom lane
Am 28.04.2019 um 20:23 schrieb Tom Lane <tgl@sss.pgh.pa.us>: > > If you're typing it exactly like that, you have a case-folding problem. > Try > LISTEN "INSERTED"; > instead, or make the pg_notify argument lower-case. Oh, I see. The constant string case now works if both LISTEN and pg_notify args are lower case. This was an important hint. > > However, that doesn't seem like it would explain your original problem, > since that didn't involve upper-case letters. The original problem could also be resolved by ensuring same case and listen for currect facility. (-; Thanks for your patience, Axel --- PGP-Key:29E99DD6 ☀ computing @ chaos claudius