The following bug has been logged on the website:
Bug reference: 14699
Logged by: Konstantin Evteev
Email address: konst583@gmail.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_stmtafter insert on tblreferencing new table as newtblfor each statementexecute 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'
publicationpub 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.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs