Re: Alter subscription..SET - NOTICE message is coming for table which is already removed

On Thu, May 25, 2017 at 6:08 AM, tushar <tushar(dot)ahuja(at)enterprisedb(dot)com> wrote:
> Hi,
>
> While performing - Alter subscription..SET , I found that NOTICE message is
> coming duplicate next time , which is not needed anymore.
>
> X cluster=
> create 100 tables
> create publication ( create publication pub for all tables;)
>
> Y cluster=
> create 100 tables
> create subscription ( create subscription sub connection 'dbname=postgres
> user=centos host=localhost ) publication pub;
>
> X cluster =
> create 1 more table (create table r(n int));
> create publication for this above table only (create publication pub1 for
> table r;)
>
> Y cluster=
> create table r - create table r(n int);
> alter publication -alter subscription sub set publication pub1 refresh;
>
> in the notice message -> table 'r' added / 100 tables removed from the
> subscription

I think you did ALTER SUBSCRIPTION while table sync for 100 tables is
running, right?

>
> postgres=# alter subscription sub set publication pub1 refresh;
> NOTICE: added subscription for table public.r
> NOTICE: removed subscription for table public.t1
> NOTICE: removed subscription for table public.t2
> NOTICE: removed subscription for table public.t3
> NOTICE: removed subscription for table public.t4
> NOTICE: removed subscription for table public.t5
> NOTICE: removed subscription for table public.t6
> --
> --
> --
> ALTER SUBSCRIPTION
>
> now again fire the same sql query
>
> postgres=# alter subscription sub set publication pub1 refresh;
> NOTICE: removed subscription for table public.t78
> ALTER SUBSCRIPTION
>
> This notice message should not come as t.78 is already removed from
> earlier same command.
>

I'm investigating the cause of this issue but it seems to me that
first ALTER SUBSCRIPTION surely removed t78 record from
pg_subscription_rel but table sync worker inserts new record of t78
when updating its state. SetSubscriptionRelState inserts a new record
if the table doesn't exist otherwise updates the record but we can
divide it into two different functions and call appropriate function
in each place.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center