[HACKERS] REPLICA IDENTITY FULL - Mailing list pgsql-hackers
From | Tatsuo Ishii |
---|---|
Subject | [HACKERS] REPLICA IDENTITY FULL |
Date | |
Msg-id | 20170619.121104.1777280213434338308.t-ishii@sraoss.co.jp Whole thread Raw |
Responses |
Re: [HACKERS] REPLICA IDENTITY FULL
(Peter Eisentraut <peter.eisentraut@2ndquadrant.com>)
|
List | pgsql-hackers |
While playing around with logical replication, I am confused by the behavior of REPLICA IDENTITY FULL. First I created a table having 2 INT columns with no keys. If I execute ALTER TABLE REPLICA IDENTITY FULL, replication for UPDATE/DELETE works. In the session below, port 11002 is the publisher side, while 11003 is the subscriber side. + psql -e -p 11002 -c create table t3(i int, j int); test create table t3(i int, j int); CREATE TABLE + psql -e -p 11003 -c create table t3(i int, j int); test create table t3(i int, j int); CREATE TABLE + psql -e -p 11002 -c alter table t3 replica identity full test alter table t3 replica identity full ALTER TABLE + psql -e -p 11002 -c insert into t3 values(1,1); test insert into t3 values(1,1); INSERT 0 1 + psql -e -p 11002 -c insert into t3 values(2,2); test insert into t3 values(2,2); INSERT 0 1 + psql -e -p 11002 -c insert into t3 values(2,2); test insert into t3 values(2,2); INSERT 0 1 + psql -e -p 11003 -c ALTER SUBSCRIPTION mysub REFRESH PUBLICATION; test ALTER SUBSCRIPTION mysub REFRESH PUBLICATION; NOTICE: added subscription for table public.t3 ALTER SUBSCRIPTION + sleep 3 + psql -e -p 11003 -c select * from t3; test select * from t3;i | j ---+---1 | 12 | 22 | 2 (3 rows) + psql -e -p 11002 -c update t3 set j = 10 where i = 2 and j = 2; test update t3 set j = 10 where i = 2 and j = 2; UPDATE 2 + psql -e -p 11003 -c select * from t3; test select * from t3;i | j ---+----1 | 12 | 102 | 10 (3 rows) + psql -e -p 11002 -c delete from t3 where i = 2; test delete from t3 where i = 2; DELETE 2 + psql -e -p 11003 -c select * from t3; test Pager usage is off. select * from t3;i | j ---+---1 | 1 (1 row) However, if a table has text columns, UPDATE/DELETE replication does not work any more. Am I missing something? + psql -e -p 11002 -c create table t4(i text, j text); test create table t4(i text, j text); CREATE TABLE + psql -e -p 11003 -c create table t4(i text, j text); test create table t4(i text, j text); CREATE TABLE + psql -e -p 11002 -c alter table t4 replica identity full test alter table t4 replica identity full ALTER TABLE + psql -e -p 11002 -c insert into t4 values('a','a'); test insert into t4 values('a','a'); INSERT 0 1 + psql -e -p 11002 -c insert into t4 values('b','b'); test insert into t4 values('b','b'); INSERT 0 1 + psql -e -p 11002 -c insert into t4 values('b','b'); test insert into t4 values('b','b'); INSERT 0 1 + psql -e -p 11003 -c ALTER SUBSCRIPTION mysub REFRESH PUBLICATION; test ALTER SUBSCRIPTION mysub REFRESH PUBLICATION; NOTICE: added subscription for table public.t4 ALTER SUBSCRIPTION + sleep 3 + psql -e -p 11003 -c select * from t4; test select * from t4;i | j ---+---a | ab | bb | b (3 rows) + psql -e -p 11002 -c update t4 set j = 'c' where i = 'b' and j = 'b'; test update t4 set j = 'c' where i = 'b' and j = 'b'; UPDATE 2 + psql -e -p 11003 -c select * from t4; test select * from t4;i | j ---+---a | ab | bb | b (3 rows) + psql -e -p 11002 -c delete from t4 where i = 'b'; test delete from t4 where i = 'b'; DELETE 2 + psql -e -p 11003 -c select * from t4; test select * from t4;i | j ---+---a | ab | bb | b (3 rows) Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
pgsql-hackers by date: