BUG #14699: Statement trigger and logical replication

From: konst583(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14699: Statement trigger and logical replication
Date: 2017-06-09 16:01:09
Message-ID: 20170609160109.26502.14118@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

The following bug has been logged on the website:

Bug reference: 14699
Logged by: Konstantin Evteev
Email address: konst583(at)gmail(dot)com
PostgreSQL version: 10beta1
Operating system: Debian GNU/Linux 8 (jessie)
Description:

Hello!
I have found a bug in logical replication and statement trigger on
subscriber.
Statement trigger works on initialisation table statement.
But then it doesn't work.
I'm using postgres 10 compiled from master branch
commit af51fea039bb8e00066d68d919312df1701dc03e

-- create database test_src;
-- create database test_dst;

\c test_src

create table tbl(id int primary key, v text);

create publication pub
for table tbl;

\c test_dst

create table tbl(id int primary key, v text);

create function show_stmt() returns trigger language plpgsql as
$$
declare
r record;
begin
raise notice 'role: %, level: %, name: %, newtbl:',
current_setting('session_replication_role'), tg_level, tg_name;
for r in select * from newtbl
loop
raise notice ' %', r;
end loop;
return null;
end
$$;

create trigger show_stmt
after insert on tbl
referencing new table as newtbl
for each statement
execute procedure show_stmt();

alter table tbl enable always trigger show_stmt;

----

create subscription sub
connection 'dbname=test_src host=postgres-test01 port=5420'
publication pub
with (create_slot = false);

--------------
in log - we can see that trigger works:
--------------
2017-06-09 18:29:31.377 MSK [27517] STATEMENT: create subscription sub
connection 'dbname=test_src'
publication pub
with (create_slot = false);
2017-06-09 18:30:04.019 MSK [27517] ERROR: invalid connection string
syntax: missing "=" after "-h" in connection info string

2017-06-09 18:30:04.019 MSK [27517] STATEMENT: create subscription sub
connection 'dbname=test_src -h postgres-test01 -p 5420'
publication pub
with (create_slot = false);
2017-06-09 18:30:31.616 MSK [27517] NOTICE: synchronized table states
2017-06-09 18:30:31.619 MSK [28822] LOG: logical replication apply worker
for subscription "sub" has started
2017-06-09 18:30:31.626 MSK [28823] LOG: logical replication table
synchronization worker for subscription "sub", table "tbl" has started
2017-06-09 18:30:31.638 MSK [28823] NOTICE: role: replica, level:
STATEMENT, name: show_stmt, newtbl:
2017-06-09 18:30:31.638 MSK [28823] CONTEXT: PL/pgSQL function show_stmt()
line 5 at RAISE
2017-06-09 18:30:32.625 MSK [28823] LOG: logical replication table
synchronization worker for subscription "sub", table "tbl" has finished
--------------

\c test_src

insert into tbl values (1, 'one'), (2, 'two');
insert into tbl values (3, 'three');

--------------
log on dst db did not changed
--------------

but all rows were successfully replicated:
--------------
\c test_dst
test_dst=# select * From tbl;
id | v
----+-------
1 | one
2 | two
3 | three
(3 rows)

--
Konstantin Evteev.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message psuderevsky 2017-06-09 16:31:44 BUG #14700: pg_restore doesn't declare schema in 'create function' statements
Previous Message Tom Lane 2017-06-09 15:59:10 Re: Re: [BUGS] BUG #14695: Documentation is not accurate

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2017-06-09 16:06:36 Re: partial aggregation with internal state type
Previous Message Tom Lane 2017-06-09 15:46:34 Re: strcmp() tie-breaker for identical ICU-collated strings