BUG #16481: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events
От | PG Bug reporting form |
---|---|
Тема | BUG #16481: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events |
Дата | |
Msg-id | 16481-8bbe054e7bc0cb3c@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #16481: Stored Procedure Triggered by Logical Replicationis Unable to use Notification Events
(Kyotaro Horiguchi <horikyota.ntt@gmail.com>)
Re: BUG #16481: Stored Procedure Triggered by Logical Replicationis Unable to use Notification Events (Kyotaro Horiguchi <horikyota.ntt@gmail.com>) |
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 16481 Logged by: Fabio Vianello Email address: fabio.vianello@salvagninigroup.com PostgreSQL version: 12.3 Operating system: Windows 10 Description: About the bug BUG #15293, on PostgreSQL version 10.4 and 11.2 as describe below, we found the same issue on the PostgreSQL version 12.3. Do you think to solve the issue? Is it a feature? Becasue in the documentation we didn't found any constraint that says that we can not use NOTIFY/LISTEN on logical replication tables. "When using logical replication a stored procedure executed on the replica is unable to use NOTIFY to send messages to other listeners. The stored procedure does execute as expected but the pg_notify() doesn't appear to have any effect. If an insert is run on the replica side the trigger executes the stored procedure as expected and the NOTIFY correctly notifies listeners. Steps to Reproduce: Set up Master: CREATE TABLE test (id SERIAL PRIMARY KEY, msg TEXT NOT NULL); CREATE PUBLICATION testpub FOR TABLE test; Set up Replica: CREATE TABLE test (id SERIAL PRIMARY KEY, msg TEXT NOT NULL); CREATE SUBSCRIPTION testsub CONNECTION 'host=192.168.0.136 user=test password=test' PUBLICATION testpub; CREATE OR REPLACE FUNCTION notify_channel() RETURNS trigger AS $$ BEGIN RAISE LOG 'Notify Triggered'; PERFORM pg_notify('testchannel', 'Testing'); RETURN NEW; END; $$ LANGUAGE 'plpgsql'; DROP TRIGGER queue_insert ON TEST; CREATE TRIGGER queue_insert AFTER INSERT ON test FOR EACH ROW EXECUTE PROCEDURE notify_channel(); ALTER TABLE test ENABLE ALWAYS TRIGGER queue_insert; LISTEN testchannel; Run the following insert on the master: INSERT INTO test (msg) VALUES ('test'); In postgresql-10-main.log I get the following: 2018-07-24 07:45:15.705 EDT [6701] LOG: 00000: Notify Triggered 2018-07-24 07:45:15.705 EDT [6701] CONTEXT: PL/pgSQL function notify_channel() line 3 at RAISE 2018-07-24 07:45:15.705 EDT [6701] LOCATION: exec_stmt_raise, pl_exec.c:3337 But no listeners receive the message. However if an insert is run directly on the replica: INSERT INTO test VALUES (99999, 'test'); INSERT 0 1 Asynchronous notification "testchannel" with payload "Testing" received from server process with PID 6701. Asynchronous notification "testchannel" with payload "Testing" received from server process with PID 6701. Asynchronous notification "testchannel" with payload "Testing" received from server process with PID 6701. Asynchronous notification "testchannel" with payload "Testing" received from server process with PID 6701. Asynchronous notification "testchannel" with payload "Testing" received from server process with PID 6701. Asynchronous notification "testchannel" with payload "Testing" received from server process with PID 9992. Backed up notifications are received for previous NOTIFY's."
В списке pgsql-bugs по дате отправления:
Следующее
От: Kyle KingsburyДата:
Сообщение: Re: Potential G2-item cycles under serializable isolation