Re: [GENERAL] Potential bug with pg_notify

On 02/10/2017 02:54 PM, François Beaulieu wrote:
>
> Hi all,
>
> I’m trying to feed a worker process on another server using pg_notify in a trigger. I’m running pgsql 9.4 and hitting
somebehaviour that I’m hoping is just a bug that can be solved with an upgrade, but I’m not finding any references to
itbeing a known bug and the uptime on my database server is critical so I can’t upgrade on a whim. Basically, the
triggerruns on every row insert and notifies me with a few fields from the row. This works perfectly most of the time,
butevery few hundred rows, the notify will return null values. Here are the details: 
>
> My database uses time-based partitioning (and subpartitioning) with pg_partman. This is done with a before insert
triggeron the main parent table. pg_partman creates subtables dynamically, so I can’t easily run my trigger on each
childtable. I also can’t run it after insert on my parent table because the row never makes it there. Thus, I ave
electedto run my trigger before insert on the parent table, and have named it so that it is alphabetically first and
willrun before the partitioning trigger. Works perfectly most of the time. Here are the trigger and associated plpgsql
function:
>
> —CODE---
> CREATE OR REPLACE FUNCTION notify_trigger() RETURNS trigger AS $$
> DECLARE
> BEGIN
>   PERFORM pg_notify('watchers', TG_TABLE_NAME || ',' || NEW._id || ',' || NEW.userfield);
>   RETURN new;
> END;
> $$ LANGUAGE plpgsql;
>
> CREATE TRIGGER aaa_notify BEFORE INSERT ON aaa FOR EACH ROW execute procedure notify_trigger();
> —/CODE—
>
> On my listener, every once in a while, this returns rows such as this:
>
> AAA,17947227,XXX
> AAA, ,
> AAA,17947229,ZZZ
>
> Notice the second line, which appears to have even the autoincrement ‘_id' empty. It would seem to match _id =
17947228and that row does exist in the table and has data populated for all fields. 
>
> Has anyone ever seen anything like this?

Can't say I have. I think this is going to need more information:

1) What is the schema for the parent table?

2) What is the pg_partman trigger function definition, as well the
CREATE TRIGGER definition that calls the function?

3) Are the first row and the second row in the same partition?


Just wondering if the pg_partman partitioning is delaying the allocation
of _id from the sequence in a way that your notify_trigger() does not
get it in time. It is just that TG_TABLE_NAME and NEW.userfield are part
of the INSERT, while NEW._id is actually a request for information from
another object.

>
> Thanks,
> -=François Beaulieu
> SBK Telecom
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com