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

On Fri, Jun 9, 2017 at 10:50 PM, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> On 5/30/17 13:25, Masahiko Sawada wrote:
>> However there is one more problem here; if the relation status entry
>> is deleted while corresponding table sync worker is waiting to be
>> changed its status, the table sync worker can be orphaned in waiting
>> status. In this case, should table sync worker check the relation
>> status and exits if the relation status record gets removed? Or should
>> ALTER SUBSCRIPTION update status of table sync worker to UNKNOWN?
>
> I think this would be fixed with the attached patch.
>

Thank you for the patch. The patch fixes this issue but it takes a
long time to done ALTER SUBSCRIPTION SET PUBLICATION when
max_sync_workers_per_subscription is set high value. Because the
removing entry from pg_subscription_rel and launching a new table sync
worker select a subscription relation state in the same order, the
former doesn't catch up with latter.
For example in my environment, when I test the following step with
max_sync_workers_per_subscription = 15, all table sync workers were
launched once and then killed. How about removing the entry from
pg_subscription_rel in the inverse order?

X cluster ->
=# select 'create table t' || generate_series(1,100) || '(c
int);';\gexec -- create 100 tables
=# create table t (c int); -- create one more table
=# create publication all_pub for all tables;
=# create publication one_pub for table t;

Y Cluster ->
(create the same 101 tables as well)
=# create subscription hoge_sub connection 'host=localhost port=5432'
publication all_pub;
=# alter subscription hoge_sub set publication one_pub; -- execute
this during synchronizing the table, it takes a long time

Regards,

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