Thread: BUG #15784: logical replication replicate data of type array for operation insert but update/delete/truncate
BUG #15784: logical replication replicate data of type array for operation insert but update/delete/truncate
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 15784 Logged by: DamionZ Zhao Email address: zhq651@126.com PostgreSQL version: 11.2 Operating system: linux Description: in publisher ======== --1. create table & publication and insert some data create table HA_LOGICAL_REPLICATION_TAB_005(id int primary key, name text, array1 text[], array2 integer[3][3]); CREATE TABLE insert into HA_LOGICAL_REPLICATION_TAB_005 values (generate_series(1,3),'a','{a,b,c,d}','{{1,2,3},{4,5,6},{7,8,9}}'); INSERT 0 3 create publication ha_logical_replication_pub_005 for table HA_LOGICAL_REPLICATION_TAB_005; CREATE PUBLICATION select pubname,rolname,puballtables,pubinsert,pubupdate,pubdelete,pubtruncate from pg_publication,pg_authid where pubname='ha_logical_replication_pub_005' and pubowner = pg_authid.oid; pubname | rolname | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate --------------------------------+---------+--------------+-----------+-----------+-----------+------------- ha_logical_replication_pub_005 | replica | f | t | t | t | t (1 row) in subscriber ======== --2.create same table as in publisher create table HA_LOGICAL_REPLICATION_TAB_005(id int, name text, array1 text[], array2 integer[3][3]); CREATE TABLE --3.create publication for ha_logical_replication_pub_005 create subscription ha_logical_replication_sub_005 connection 'hostaddr=${master_ip} port=${master_port} user=replica dbname=REGRESSION' publication ha_logical_replication_pub_005; CREATE SUBSCRIPTION --4. check initial data has coped.-------yes,it is ok. select * from HA_LOGICAL_REPLICATION_TAB_005 order by id;" id | name | array1 | array2 ----+------+-----------+--------------------------- 1 | a | {a,b,c,d} | {{1,2,3},{4,5,6},{7,8,9}} 2 | a | {a,b,c,d} | {{1,2,3},{4,5,6},{7,8,9}} 3 | a | {a,b,c,d} | {{1,2,3},{4,5,6},{7,8,9}} (3 rows) in publisher ======== --5.try more in publisher insert into HA_LOGICAL_REPLICATION_TAB_005 values (generate_series(5,7),'b','{x,y,z}','{{11,12,13},{14,15,16}}'); INSERT 0 3 update HA_LOGICAL_REPLICATION_TAB_005 set array1 = '{o,p,q}' where id=1; UPDATE 1 delete from HA_LOGICAL_REPLICATION_TAB_005 where id=3; DELETE 1 select * from HA_LOGICAL_REPLICATION_TAB_005 order by id; id | name | array1 | array2 ----+------+-----------+--------------------------- 1 | a | {o,p,q} | {{1,2,3},{4,5,6},{7,8,9}} 2 | a | {a,b,c,d} | {{1,2,3},{4,5,6},{7,8,9}} 5 | b | {x,y,z} | {{11,12,13},{14,15,16}} 6 | b | {x,y,z} | {{11,12,13},{14,15,16}} 7 | b | {x,y,z} | {{11,12,13},{14,15,16}} in subscriber ======== --6.what happend in subscriber? will be same as in publisher??? select * from HA_LOGICAL_REPLICATION_TAB_005 order by id; id | name | array1 | array2 ----+------+-----------+--------------------------- 1 | a | {a,b,c,d} | {{1,2,3},{4,5,6},{7,8,9}} 2 | a | {a,b,c,d} | {{1,2,3},{4,5,6},{7,8,9}} 3 | a | {a,b,c,d} | {{1,2,3},{4,5,6},{7,8,9}} 5 | b | {x,y,z} | {{11,12,13},{14,15,16}} 6 | b | {x,y,z} | {{11,12,13},{14,15,16}} 7 | b | {x,y,z} | {{11,12,13},{14,15,16}} (6 rows) Let me summarize what I test, *insert* operation can be replicated to subscriber,but *update/delete/truncate* operation not. Please make sure it is ok?
Re: BUG #15784: logical replication replicate data of type array foroperation insert but update/delete/truncate
From
Jeff Janes
Date:
On Sun, Apr 28, 2019 at 2:48 AM PG Bug reporting form <noreply@postgresql.org> wrote:
in subscriber
========
--2.create same table as in publisher
create table HA_LOGICAL_REPLICATION_TAB_005(id int, name text, array1
text[], array2 integer[3][3]);
Note that you did not declare "id" to be a primary key on the subscriber.
If you look in the subscriber log, you should find:
ERROR: logical replication target relation "public.ha_logical_replication_tab_005" has neither REPLICA IDENTITY index nor PRIMARY KEY and published relation does not have REPLICA IDENTITY FULL
The array type is not relevant to the problem.
Cheers,
Jeff