BUG #17670: Logical Replication data may be lost on the subscription under certain scenarios

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: xyh(at)nvn(dot)xyz
Subject: BUG #17670: Logical Replication data may be lost on the subscription under certain scenarios
Date: 2022-10-28 07:48:50
Message-ID: 17670-8266c823f774a63f@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 17670
Logged by: Yunhe Xu
Email address: xyh(at)nvn(dot)xyz
PostgreSQL version: 14.4
Operating system: rhel 7
Description:

Logical Replication data may be lost on the subscription under certain
scenarios.
The following is review process.

* Logical Replication Information
t1=# select * from pg_publication_tables ;
pubname | schemaname | tablename
------------+------------+----------------
pub1 | public | t_test1
t2=# select srrelid::regclass,* from pg_subscription_rel ;
srrelid | srsubid | srrelid | srsubstate | srsublsn
---------+---------+---------+------------+-----------
t_test1 | 57551 | 41170 | r | 0/696E418
t1=# select application_name,state from pg_stat_replication where
application_name='test2_sub';
application_name | state
------------------+-----------
test2_sub | streaming

* Verify the status is normal
t1=# insert into t_test1 values (1);
INSERT 0 1
t1=# select * from t_test1;
id
----
1
t2=# select * from t_test1;
id
----
1

* Then delete this table on subscription :
t2=# alter table t_test1 rename TO t_test2;
ALTER TABLE

* Now,do DMLs
t1=# insert into t_test1 values (2);
INSERT 0 1
t1=# delete from t_test1 where id=1;
DELETE 1
t1=#
* The log gives some errors
2022-10-28 15:14:07.919 CST,,,2600,,635b813f.a28,2,,2022-10-28 15:14:07
CST,4/12,0,ERROR,55000,"logical replication target relation
""public.t_test1"" does not exist",,,,,,,,,"","logical replication
worker",,0

* OK,Let me create this table
t2=# create table t_test1 (id int PRIMARY KEY);
CREATE TABLE

* At this point, the log is no longer showing errors.But the incremental
data is lost.
t2=# select * from t_test1;
id
----
(0 rows)

t1=# insert into t_test1 values (3);
INSERT 0 1
t1=# insert into t_test1 values (4);
INSERT 0 1

t2=# select * from t_test1;
id
----
(0 rows)
t2=# select * from t_test2 ;
id
----
1
(1 row)

* Still no error.
* Now modify the previous table back

t2=# drop table t_test1 ;
DROP TABLE
t2=# alter table t_test2 rename TO t_test1;
ALTER TABLE

t1=# insert into t_test1 values (5);
INSERT 0 1
t1=# select * from t_test1;
id
----
2
3
4
5
(4 rows)

t2=# select * from t_test1;
id
----
1
5
(2 rows)

* The middle operation-delete id=1 and insert id=3,4-is loss.
*Please confirm if this is a BUG.

Thanks.
Yunhe Xu

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2022-10-28 13:43:00 Re: BUG #17669: Invalid TOAST pointer in PL/pgSQL variable
Previous Message Alexey Ermakov 2022-10-28 07:42:10 Re: BUG #17668: Query normalization generates multiple queryId:s for calls to the same procedure