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