Re: Logical Replication WIP

On 05/09/16 23:35, Steve Singer wrote:
> On 09/05/2016 03:58 PM, Steve Singer wrote:
>> On 08/31/2016 04:51 PM, Petr Jelinek wrote:
>>> Hi,
>>>
>>> and one more version with bug fixes, improved code docs and couple
>>> more tests, some general cleanup and also rebased on current master
>>> for the start of CF.
>>>
>>>
>>>
>>
>
> A few more things I noticed when playing with the patches
>
> 1, Creating a subscription to yourself ends pretty badly,
> the 'CREATE SUBSCRIPTION' command seems to get stuck, and you can't kill
> it. The background process seems to be waiting for a transaction to
> commit (I assume the create subscription command). I had to kill -9 the
> various processes to get things to stop. Getting confused about
> hostnames and ports is a common operator error.
>

Hmm I guess there is missing interrupts check, will look. It would be
great to detect it properly but I am not really sure how to do that as
afaik there is no accurate way to detect that the connection is to yourself.

> 2. Failures during the initial subscription aren't recoverable
>
> For example
>
> on db1
> create table a(id serial4 primary key,b text);
> insert into a(b) values ('1');
> create publication testpub for table a;
>
> on db2
> create table a(id serial4 primary key,b text);
> insert into a(b) values ('1');
> create subscription testsub connection 'host=localhost port=5440
> dbname=test' publication testpub;
>
> I then get in my db2 log
>
> ERROR: duplicate key value violates unique constraint "a_pkey"
> DETAIL: Key (id)=(1) already exists.
> LOG: worker process: logical replication worker 16396 sync 16387 (PID
> 10583) exited with exit code 1
> LOG: logical replication sync for subscription testsub, table a started
> ERROR: could not crate replication slot "testsub_sync_a": ERROR:
> replication slot "testsub_sync_a" already exists
>
>
> LOG: worker process: logical replication worker 16396 sync 16387 (PID
> 10585) exited with exit code 1
> LOG: logical replication sync for subscription testsub, table a started
> ERROR: could not crate replication slot "testsub_sync_a": ERROR:
> replication slot "testsub_sync_a" already exists
>
>
> and it keeps looping.
> If I then truncate "a" on db2 it doesn't help. (I'd expect at that point
> the initial subscription to work)

Hmm, looks like the error case does not cleanup correctly after itself.

>
> If I then do on db2
> drop subscription testsub cascade;
>
> I still see a slot in use on db1
>
> select * FROM pg_replication_slots ;
> slot_name | plugin | slot_type | datoid | database | active |
> active_pid | xmin | catalog_xmin | rest
> art_lsn | confirmed_flush_lsn
> ----------------+----------+-----------+--------+----------+--------+------------+------+--------------+-----
>
> --------+---------------------
> testsub_sync_a | pgoutput | logical | 16384 | test | f
> | | | 1173 | 0/15
> 66E08 | 0/1566E40
>

Same as above.

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services