BUG #15784: logical replication replicate data of type array for operation insert but update/delete/truncate

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #15784: logical replication replicate data of type array for operation insert but update/delete/truncate

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      15784
Logged by:          DamionZ Zhao
Email address:      [hidden email]
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?

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15784: logical replication replicate data of type array for operation insert but update/delete/truncate

Jeff Janes
On Sun, Apr 28, 2019 at 2:48 AM PG Bug reporting form <[hidden email]> 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