Re: BUG #15255: notification from trigger is not delivered in a timely fashion on subscriber

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(at)anarazel(dot)de>
Sent: 28 June 2018 17:20:08
To: Dan Knight-Gaynor; pgsql-bugs(at)lists(dot)postgresql(dot)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(dot)knight-gaynor(at)osirium(dot)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