Thread: [BUGS] BUG #14699: Statement trigger and logical replication

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

On 6/9/17 12:01, konst583@gmail.com wrote:
> 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.

The inserting of rows from the stream is not a "statement".  Therefore,
statement triggers are not fired for that.

The initial data copy is implemented as a COPY command, which is why
statement triggers fire for that.

I think this is all working correctly and as intended.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs