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_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.
--
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
2017-06-13 5:57 GMT+03:00 Peter Eisentraut <peter.eisentraut@2ndquadrant.com
:
I think this is all working correctly and as intended.
But then, why data copy for init logical replication fires statement
trigger. May be it is also not nedeed?
Or this feature needs to be mentioned in documentation?
On 6/16/17 09:13, Константин Евтеев wrote:
2017-06-13 5:57 GMT+03:00 Peter Eisentraut
<peter.eisentraut@2ndquadrant.com
<mailto:peter.eisentraut@2ndquadrant.com>>:I think this is all working correctly and as intended.
But then, why data copy for init logical replication fires statement
trigger. May be it is also not nedeed?
Or this feature needs to be mentioned in documentation?
I don't know. Hackers?
The issue is that the logical replication initial data copy fires a
statement trigger for INSERT, because it's implemented as a COPY internally.
By contrast, the normal apply worker does not fire any statement
triggers (because they are not "statements").
We could adjust one or the other or leave it as is.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2017-06-16 21:08:44 -0400, Peter Eisentraut wrote:
On 6/16/17 09:13, Константин Евтеев wrote:
2017-06-13 5:57 GMT+03:00 Peter Eisentraut
<peter.eisentraut@2ndquadrant.com
<mailto:peter.eisentraut@2ndquadrant.com>>:I think this is all working correctly and as intended.
But then, why data copy for init logical replication fires statement
trigger. May be it is also not nedeed?
Or this feature needs to be mentioned in documentation?I don't know. Hackers?
The issue is that the logical replication initial data copy fires a
statement trigger for INSERT, because it's implemented as a COPY internally.By contrast, the normal apply worker does not fire any statement
triggers (because they are not "statements").We could adjust one or the other or leave it as is.
Leave it as is.
- Andres
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sat, Jun 17, 2017 at 1:22 PM, Andres Freund <andres@anarazel.de> wrote:
On 2017-06-16 21:08:44 -0400, Peter Eisentraut wrote:
On 6/16/17 09:13, Константин Евтеев wrote:
2017-06-13 5:57 GMT+03:00 Peter Eisentraut
<peter.eisentraut@2ndquadrant.com
<mailto:peter.eisentraut@2ndquadrant.com>>:I think this is all working correctly and as intended.
But then, why data copy for init logical replication fires statement
trigger. May be it is also not nedeed?
Or this feature needs to be mentioned in documentation?I don't know. Hackers?
The issue is that the logical replication initial data copy fires a
statement trigger for INSERT, because it's implemented as a COPY internally.By contrast, the normal apply worker does not fire any statement
triggers (because they are not "statements").We could adjust one or the other or leave it as is.
Leave it as is.
I also noticed this while working on the open items for transition
tables. One of my patches adds a comment to execReplication.c about
the need to do a bit more work if/when statement triggers are fired
here in future. I assume that we'll eventually want statement
triggers to fire if eager incremental matviews depend on that (as is
proposed), or if that set-oriented FK check idea goes somewhere.
Transition tables make statement triggers a lot more powerful
(something like batch-mode after row triggers).
--
Thomas Munro
http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2017-06-16 22:08 GMT-03:00 Peter Eisentraut <peter.eisentraut@2ndquadrant.
com>:
The issue is that the logical replication initial data copy fires a
statement trigger for INSERT, because it's implemented as a COPY
internally.We should document such behavior. AFAICS we discuss later if we should
provide an option to fire statement triggers during initial copy.
By contrast, the normal apply worker does not fire any statement
triggers (because they are not "statements").+1.
We could adjust one or the other or leave it as is.
Let's leave it as is. At least until 11.
--
Euler Taveira Timbira -
http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
<http://www.timbira.com.br>
On 6/17/17 08:29, Euler Taveira wrote:
2017-06-16 22:08 GMT-03:00 Peter Eisentraut
<peter.eisentraut@2ndquadrant.com
<mailto:peter.eisentraut@2ndquadrant.com>>:The issue is that the logical replication initial data copy fires a
statement trigger for INSERT, because it's implemented as a COPY
internally.We should document such behavior. AFAICS we discuss later if we should
provide an option to fire statement triggers during initial copy.
I have added the documentation.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers