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

On Fri, Oct 28, 2022 at 2:51 PM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> 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.
>

If you do "Alter Subscription test2_sub Refresh Publication;" then the
data should be synced.

--
With Regards,
Amit Kapila.