Horton

Thread

  1. Restrictions of channel arg of pg_notofy

    Axel Rau <axel.rau@chaos1.de> — 2019-04-27T17:51:46Z

    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

  2. Re: Restrictions of channel arg of pg_notofy

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-04-27T18:51:57Z

    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

  3. Re: Restrictions of channel arg of pg_notofy

    Axel Rau <axel.rau@chaos1.de> — 2019-04-28T16:22:12Z

    
> 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

  4. Re: Restrictions of channel arg of pg_notofy

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-04-28T17:02:06Z

    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

  5. Re: Restrictions of channel arg of pg_notofy

    Axel Rau <axel.rau@chaos1.de> — 2019-04-28T17:46:12Z

    
> 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

  6. Re: Restrictions of channel arg of pg_notofy

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-04-28T18:23:16Z

    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

  7. [RESOLVED] Re: Restrictions of channel arg of pg_notofy

    Axel Rau <axel.rau@chaos1.de> — 2019-04-29T08:23:50Z

    
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