agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedRestrictions of channel arg of pg_notofy
7+ messages / 3 participants
[nested] [flat]
* Restrictions of channel arg of pg_notofy
@ 2019-04-27 17:51 Axel Rau <Axel.Rau@Chaos1.DE>
0 siblings, 1 reply; 7+ messages in thread
From: Axel Rau @ 2019-04-27 17:51 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Restrictions of channel arg of pg_notofy
@ 2019-04-27 18:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Axel Rau <Axel.Rau@Chaos1.DE>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2019-04-27 18:51 UTC (permalink / raw)
To: Axel Rau <Axel.Rau@Chaos1.DE>; +Cc: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Restrictions of channel arg of pg_notofy
@ 2019-04-28 16:22 Axel Rau <Axel.Rau@chaos1.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Axel Rau @ 2019-04-28 16:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql@lists.postgresql.org
> 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?
Both the INSERT and the LISTEN are one-statement transactions.
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Restrictions of channel arg of pg_notofy
@ 2019-04-28 17:02 Tom Lane <tgl@sss.pgh.pa.us>
parent: Axel Rau <Axel.Rau@chaos1.de>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2019-04-28 17:02 UTC (permalink / raw)
To: Axel Rau <Axel.Rau@chaos1.de>; +Cc: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Restrictions of channel arg of pg_notofy
@ 2019-04-28 17:46 Axel Rau <Axel.Rau@Chaos1.DE>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Axel Rau @ 2019-04-28 17:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql@lists.postgresql.org
> Am 28.04.2019 um 19:30 schrieb Axel Rau <Axel.Rau@Chaos1.DE>:
>
>
>
>> 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;
> 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.
>
More details:
pg_catalog in not in search_path.
I just tried the same test on another instance (DB fresh created): Same result.
Axel
---
PGP-Key:29E99DD6 ☀ computing @ chaos claudius
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Restrictions of channel arg of pg_notofy
@ 2019-04-28 18:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Axel Rau <Axel.Rau@Chaos1.DE>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2019-04-28 18:23 UTC (permalink / raw)
To: Axel Rau <Axel.Rau@Chaos1.DE>; +Cc: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* [RESOLVED] Re: Restrictions of channel arg of pg_notofy
@ 2019-04-29 08:23 Axel Rau <Axel.Rau@chaos1.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Axel Rau @ 2019-04-29 08:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2019-04-29 08:23 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-04-27 17:51 Restrictions of channel arg of pg_notofy Axel Rau <Axel.Rau@Chaos1.DE>
2019-04-27 18:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-04-28 16:22 ` Axel Rau <Axel.Rau@chaos1.de>
2019-04-28 17:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-04-28 17:46 ` Axel Rau <Axel.Rau@Chaos1.DE>
2019-04-28 18:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-04-29 08:23 ` Axel Rau <Axel.Rau@chaos1.de>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox