Re: Logical replication - schema change not invalidating the relation cache

On Sat, Jul 3, 2021 at 11:23 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Fri, Jul 2, 2021 at 12:03 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> >
> > Yeah, this looks like a bug. I will look at the patch.
> >
>
> While looking into this, I think the main cause of the problem is that
> schema rename does not invalidate the relation cache right? I also
> tried other cases e.g. if there is an open cursor and we rename the
> schema
>
> CREATE SCHEMA sch1;
> CREATE TABLE sch1.t1(c1 int);
> insert into sch1.t1 values(1);
> insert into sch1.t1 values(2);
> insert into sch1.t1 values(3);
> BEGIN;
> DECLARE mycur CURSOR FOR SELECT * FROM sch1.t1;
> FETCH NEXT FROM mycur ;
> ----------At this point rename sch1 to sch2 from another session------
> FETCH NEXT FROM mycur ;
> UPDATE sch2.t1 SET c1 = 20 WHERE CURRENT OF mycur;
> select * from sch2.t1 ;
>
> So even after the schema rename the cursor is able to fetch and its
> also able to update on the same table in the new schema, ideally using
> CURRENT OF CUR, you can update the same table for which you have
> declared the cursor. I am giving this example because this behavior
> also looks somewhat similar.

It works in this case because it uses the relation id for performing
the next fetch and the relation id does not get changed after renaming
the schema. Also since it holds a lock on the relation, alter/drop
operations will not be allowed. I felt this behavior might be ok. But
the original scenario reported is an issue because it replicates the
data of both the original table and the renamed schema's table.

Regards,
Vignesh