Re: Single transaction in the tablesync worker?

Another failure I see in my testing

On publisher create a big enough table:
publisher:
postgres=# CREATE TABLE tab_rep (a int primary key);CREATE TABLE
postgres=# INSERT INTO tab_rep SELECT generate_series(1,1000000);
INSERT 0 1000000
postgres=# CREATE PUBLICATION tap_pub FOR ALL TABLES;
CREATE PUBLICATION

Subscriber:
postgres=# CREATE TABLE tab_rep (a int primary key);
CREATE TABLE
postgres=# CREATE SUBSCRIPTION tap_sub CONNECTION 'host=localhost
dbname=postgres port=6972' PUBLICATION tap_pub WITH (enabled = false);

Create the subscription but do not enable it:
The below two commands on the subscriber should be issued quickly with
no delay between them.

postgres=# ALTER SUBSCRIPTION tap_sub enable;
ALTER SUBSCRIPTION
postgres=# ALTER SUBSCRIPTION tap_sub disable;
ALTER SUBSCRIPTION

This leaves the below state for the pg_subscription rel:
postgres=# select * from pg_subscription_rel;
 srsubid | srrelid | srsubstate | srsublsn
---------+---------+------------+----------
   16395 |   16384 | f          |
(1 row)

The rel is in the SUBREL_STATE_FINISHEDCOPY state.

Meanwhile on the publisher, looking at the slots created:

postgres=# select * from pg_replication_slots;
      slot_name      |  plugin  | slot_type | datoid | database |
temporary | active | active_pid | x
min | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status |
safe_wal_size
---------------------+----------+-----------+--------+----------+-----------+--------+------------+--
----+--------------+-------------+---------------------+------------+---------------
 tap_sub             | pgoutput | logical   |  13859 | postgres | f
     | f      |            |
    |          517 | 0/9303660   | 0/9303698           | reserved   |
 pg_16395_sync_16384 | pgoutput | logical   |  13859 | postgres | f
     | f      |            |
    |          517 | 0/9303660   | 0/9303698           | reserved   |
(2 rows)


There are two slots, the main slot as well as the tablesync slot, drop
the table, re-enable the subscription and then drop the subscription

Now on the subscriber:
postgres=# drop table tab_rep;
DROP TABLE
postgres=# ALTER SUBSCRIPTION tap_sub enable;
ALTER SUBSCRIPTION
postgres=# drop subscription tap_sub ;
NOTICE:  dropped replication slot "tap_sub" on publisher
DROP SUBSCRIPTION

We see the tablesync slot dangling in the publisher:
postgres=# select * from pg_replication_slots;
      slot_name      |  plugin  | slot_type | datoid | database |
temporary | active | active_pid | x
min | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status |
safe_wal_size
---------------------+----------+-----------+--------+----------+-----------+--------+------------+--
----+--------------+-------------+---------------------+------------+---------------
  pg_16395_sync_16384 | pgoutput | logical   |  13859 | postgres | f
      | f      |            |
    |          517 | 0/9303660   | 0/9303698           | reserved   |
(1 row)

The dropping of the table, meant that after the tablesync is
restarted, the worker has no idea about the old slot created as its
name uses the relid of the dropped table.

regards,
Ajin Cherian
Fujitsu Australia