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

2017-05-30 Thread Masahiko Sawada 
On Tue, May 30, 2017 at 10:12 AM, Masahiko Sawada  wrote:
> On Thu, May 25, 2017 at 8:15 PM, tushar  wrote:
>> On 05/25/2017 04:40 PM, Masahiko Sawada wrote:
>>>
>>> I think you did ALTER SUBSCRIPTION while table sync for 100 tables is
>>> running, right?
>>
>> Yes, i didn't wait too much while executing the commands.
>>
>
> I think it's better to add this to open items so that it doesn't get
> missed. Barring any objections I'll add it.
>

Added.

Regards,

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2017-05-29 Thread Masahiko Sawada 
On Thu, May 25, 2017 at 8:15 PM, tushar  wrote:
> On 05/25/2017 04:40 PM, Masahiko Sawada wrote:
>>
>> I think you did ALTER SUBSCRIPTION while table sync for 100 tables is
>> running, right?
>
> Yes, i didn't wait too much while executing the commands.
>

I think it's better to add this to open items so that it doesn't get
missed. Barring any objections I'll add it.

Regards,

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2017-05-25 Thread tushar 

On 05/25/2017 04:40 PM, Masahiko Sawada wrote:

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

Yes, i didn't wait too much while executing the commands.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2017-05-25 Thread Masahiko Sawada 
On Thu, May 25, 2017 at 6:08 AM, tushar  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Alter subscription..SET - NOTICE message is coming for table which is already removed

2017-05-25 Thread tushar 

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


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.


--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers