Re: BUG #17670: Logical Replication data may be lost on the subscription under certain scenarios - Mailing list pgsql-bugs

On Fri, 28 Oct 2022 at 15:48, PG Bug reporting form <noreply@postgresql.org> wrote:
> The following bug has been logged on the website:
>
> Bug reference:      17670
> Logged by:          Yunhe Xu
> Email address:      xyh@nvn.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.
>

I can reproduce it on HEAD.  Here is my analysis:

When we rename the t_test1 to t_test2, the subscriber doesn't have a
table matched publication table name, so the logical replication throw
an error.  Then, we create a new t_test1 on subscriber, the logical
replication worker can find the table that matches the published name.
However, the pg_subscription_rel hasn't updated, and when we try to get
the subscription state through GetSubscriptionRelState(), it cannot find
a matched subscription relation mapping, so the WAL cannot apply to the
new table t_test1.

-- 
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.