Thread: BUG #15255: notification from trigger is not delivered in a timelyfashion on subscriber
BUG #15255: notification from trigger is not delivered in a timelyfashion on subscriber
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 15255 Logged by: Daniel Knight-gaynor Email address: dan.knight-gaynor@osirium.com PostgreSQL version: 10.4 Operating system: ubuntu 16.04 Description: Hello all, I'm observing an issue when using two databases with logical replication publisher-subscriber setup. The two databases are on separate postgresql instances. When a record is inserted on the publisher it is replicated to the subscriber. However notifications generated by an 'after insert' trigger on on the subscribers table are not delivered to any listeners until a notification originating locally is generated. By experimentation I have confirmed that the trigger on the subscriber is firing, and that the issue is solely with the delivery of the notify. The setup looks something like this: /* on both nodes */ CREATE EXTENSION 'uuid-ossp'; CREATE TABLE user_events ( id UUID PRIMARY KEY DEFAULT uuid_generate_v1mc(), user_id int NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, type TEXT NOT NULL ); CREATE OR REPLACE FUNCTION send_notify_on_user_disabled() RETURNS trigger as $$ BEGIN UPDATE users SET updated_at=CURRENT_TIMESTAMP; IF NEW.type = 'disabled' THEN EXECUTE 'NOTIFY user_disabled'; RETURN NEW; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER user_disabled_event_trigger AFTER INSERT ON user_events FOR EACH ROW EXECUTE PROCEDURE send_notify_on_user_disabled(); ALTER TABLE user_events ENABLE ALWAYS TRIGGER user_disabled_event_trigger; /* on publisher*/ CREATE PUBLICATION shared_tables FOR table user_events; /* on subscriber */ CREATE SUBSCRIPTION shared_tables CONNECTION 'host=publisher dbname=users port=5432' PUBLICATION shared_tables; LISTEN user_disabled; /*on publisher */ INSERT into user_events(user_id, type) VALUES(3, 'disabled'); You can see the row arrive on the subscribe (SELECT * FROM user_events) but no notify is received by any listener. If you issue a 'NOTIFY whatever;' on the subscriber the notify message arrives. To me this looks like a bug. If I'm messing up in the setup at all, please let me know! Any help would be much appreciated! Many thanks, Daniel Knight-Gaynor
Re: BUG #15255: notification from trigger is not delivered in atimely fashion on subscriber
From
Andres Freund
Date:
Hi, On 2018-06-28 13:21:46 +0000, PG Bug reporting form wrote: > The following bug has been logged on the website: > > Bug reference: 15255 > Logged by: Daniel Knight-gaynor > Email address: dan.knight-gaynor@osirium.com > PostgreSQL version: 10.4 > Operating system: ubuntu 16.04 > Description: > > Hello all, > > I'm observing an issue when using two databases with logical replication > publisher-subscriber setup. The two databases are on separate postgresql > instances. When a record is inserted on the publisher it is replicated to > the subscriber. However notifications generated by an 'after insert' trigger > on on the subscribers table are not delivered to any listeners until a > notification originating locally is generated. By experimentation I have > confirmed that the trigger on the subscriber is firing, and that the issue > is solely with the delivery of the notify. > > The setup looks something like this: > > /* on both nodes */ > CREATE EXTENSION 'uuid-ossp'; > > CREATE TABLE user_events ( > id UUID PRIMARY KEY DEFAULT uuid_generate_v1mc(), > user_id int NOT NULL, > created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, > type TEXT NOT NULL > ); > > CREATE OR REPLACE FUNCTION send_notify_on_user_disabled() RETURNS trigger as > $$ > BEGIN > UPDATE users SET updated_at=CURRENT_TIMESTAMP; > IF NEW.type = 'disabled' THEN > EXECUTE 'NOTIFY user_disabled'; > RETURN NEW; > END IF; > RETURN NEW; > END; > $$ LANGUAGE plpgsql; > > CREATE TRIGGER user_disabled_event_trigger AFTER INSERT ON user_events FOR > EACH ROW EXECUTE PROCEDURE send_notify_on_user_disabled(); > > ALTER TABLE user_events ENABLE ALWAYS TRIGGER user_disabled_event_trigger; > > /* on publisher*/ > > CREATE PUBLICATION shared_tables FOR table user_events; > > > /* on subscriber */ > > CREATE SUBSCRIPTION shared_tables CONNECTION 'host=publisher dbname=users > port=5432' PUBLICATION shared_tables; > LISTEN user_disabled; > > /*on publisher */ > INSERT into user_events(user_id, type) VALUES(3, 'disabled'); > > You can see the row arrive on the subscribe (SELECT * FROM user_events) but > no notify is received by any listener. If you issue a 'NOTIFY whatever;' on > the subscriber the notify message arrives. > > To me this looks like a bug. If I'm messing up in the setup at all, please > let me know! Petr, Peter? Greetings, Andres Freund
Re: BUG #15255: notification from trigger is not delivered in atimely fashion on subscriber
From
Dan Knight-Gaynor
Date:
Hello!
Just a minor correction to that sql. The function should read like this:
CREATE OR REPLACE FUNCTION send_notify_on_user_disabled() RETURNS trigger as
$$
BEGIN
IF NEW.type = 'disabled' THEN
EXECUTE 'NOTIFY user_disabled';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
This way it wont reference the non-existent table.
Many thanks,
Dan
From: Andres Freund <andres@anarazel.de>
Sent: 28 June 2018 17:20:08
To: Dan Knight-Gaynor; pgsql-bugs@lists.postgresql.org; Petr Jelinek; Peter Eisentraut
Subject: Re: BUG #15255: notification from trigger is not delivered in a timely fashion on subscriber
Sent: 28 June 2018 17:20:08
To: Dan Knight-Gaynor; pgsql-bugs@lists.postgresql.org; Petr Jelinek; Peter Eisentraut
Subject: Re: BUG #15255: notification from trigger is not delivered in a timely fashion on subscriber
Hi,
On 2018-06-28 13:21:46 +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 15255
> Logged by: Daniel Knight-gaynor
> Email address: dan.knight-gaynor@osirium.com
> PostgreSQL version: 10.4
> Operating system: ubuntu 16.04
> Description:
>
> Hello all,
>
> I'm observing an issue when using two databases with logical replication
> publisher-subscriber setup. The two databases are on separate postgresql
> instances. When a record is inserted on the publisher it is replicated to
> the subscriber. However notifications generated by an 'after insert' trigger
> on on the subscribers table are not delivered to any listeners until a
> notification originating locally is generated. By experimentation I have
> confirmed that the trigger on the subscriber is firing, and that the issue
> is solely with the delivery of the notify.
>
> The setup looks something like this:
>
> /* on both nodes */
> CREATE EXTENSION 'uuid-ossp';
>
> CREATE TABLE user_events (
> id UUID PRIMARY KEY DEFAULT uuid_generate_v1mc(),
> user_id int NOT NULL,
> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
> type TEXT NOT NULL
> );
>
> CREATE OR REPLACE FUNCTION send_notify_on_user_disabled() RETURNS trigger as
> $$
> BEGIN
> UPDATE users SET updated_at=CURRENT_TIMESTAMP;
> IF NEW.type = 'disabled' THEN
> EXECUTE 'NOTIFY user_disabled';
> RETURN NEW;
> END IF;
> RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
>
> CREATE TRIGGER user_disabled_event_trigger AFTER INSERT ON user_events FOR
> EACH ROW EXECUTE PROCEDURE send_notify_on_user_disabled();
>
> ALTER TABLE user_events ENABLE ALWAYS TRIGGER user_disabled_event_trigger;
>
> /* on publisher*/
>
> CREATE PUBLICATION shared_tables FOR table user_events;
>
>
> /* on subscriber */
>
> CREATE SUBSCRIPTION shared_tables CONNECTION 'host=publisher dbname=users
> port=5432' PUBLICATION shared_tables;
> LISTEN user_disabled;
>
> /*on publisher */
> INSERT into user_events(user_id, type) VALUES(3, 'disabled');
>
> You can see the row arrive on the subscribe (SELECT * FROM user_events) but
> no notify is received by any listener. If you issue a 'NOTIFY whatever;' on
> the subscriber the notify message arrives.
>
> To me this looks like a bug. If I'm messing up in the setup at all, please
> let me know!
Petr, Peter?
Greetings,
Andres Freund
On 2018-06-28 13:21:46 +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 15255
> Logged by: Daniel Knight-gaynor
> Email address: dan.knight-gaynor@osirium.com
> PostgreSQL version: 10.4
> Operating system: ubuntu 16.04
> Description:
>
> Hello all,
>
> I'm observing an issue when using two databases with logical replication
> publisher-subscriber setup. The two databases are on separate postgresql
> instances. When a record is inserted on the publisher it is replicated to
> the subscriber. However notifications generated by an 'after insert' trigger
> on on the subscribers table are not delivered to any listeners until a
> notification originating locally is generated. By experimentation I have
> confirmed that the trigger on the subscriber is firing, and that the issue
> is solely with the delivery of the notify.
>
> The setup looks something like this:
>
> /* on both nodes */
> CREATE EXTENSION 'uuid-ossp';
>
> CREATE TABLE user_events (
> id UUID PRIMARY KEY DEFAULT uuid_generate_v1mc(),
> user_id int NOT NULL,
> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
> type TEXT NOT NULL
> );
>
> CREATE OR REPLACE FUNCTION send_notify_on_user_disabled() RETURNS trigger as
> $$
> BEGIN
> UPDATE users SET updated_at=CURRENT_TIMESTAMP;
> IF NEW.type = 'disabled' THEN
> EXECUTE 'NOTIFY user_disabled';
> RETURN NEW;
> END IF;
> RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
>
> CREATE TRIGGER user_disabled_event_trigger AFTER INSERT ON user_events FOR
> EACH ROW EXECUTE PROCEDURE send_notify_on_user_disabled();
>
> ALTER TABLE user_events ENABLE ALWAYS TRIGGER user_disabled_event_trigger;
>
> /* on publisher*/
>
> CREATE PUBLICATION shared_tables FOR table user_events;
>
>
> /* on subscriber */
>
> CREATE SUBSCRIPTION shared_tables CONNECTION 'host=publisher dbname=users
> port=5432' PUBLICATION shared_tables;
> LISTEN user_disabled;
>
> /*on publisher */
> INSERT into user_events(user_id, type) VALUES(3, 'disabled');
>
> You can see the row arrive on the subscribe (SELECT * FROM user_events) but
> no notify is received by any listener. If you issue a 'NOTIFY whatever;' on
> the subscriber the notify message arrives.
>
> To me this looks like a bug. If I'm messing up in the setup at all, please
> let me know!
Petr, Peter?
Greetings,
Andres Freund