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
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!
