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 some behaviour that I’m hoping is
just a bug that can be solved with an upgrade, but I’m not finding any
references to it being a known bug and the uptime on my database server is
critical so I can’t upgrade on a whim. Basically, the trigger runs on every row
insert and notifies me with a few fields from the row. This works perfectly
most of the time, but every 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 trigger on the main parent table. pg_partman
creates subtables dynamically, so I can’t easily run my trigger on each child
table. I also can’t run it after insert on my parent table because the row
never makes it there. Thus, I ave elected to run my trigger before insert on
the parent table, and have named it so that it is alphabetically first and will
run 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 = 17947228 and that row does exist in the
table and has data populated for all fields.
Has anyone ever seen anything like this?
Thanks,
-=François Beaulieu
SBK Telecom
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
