repro.sql

application/sql

Download

CREATE DATABASE pub;
CREATE DATABASE sub;

\c pub
CREATE TABLE part (a int) PARTITION BY RANGE (a);
CREATE TABLE part_def PARTITION OF part DEFAULT;
CREATE TABLE other(a int);

INSERT INTO part VALUES (1), (2), (3);
CREATE PUBLICATION pub_all FOR ALL TABLES;
CREATE PUBLICATION pub_other FOR TABLE other WITH (publish_via_partition_root);
SELECT pg_create_logical_replication_slot('sub', 'pgoutput');

\c sub
CREATE TABLE part (a int) PARTITION BY RANGE (a);
CREATE TABLE part_def PARTITION OF part DEFAULT;
CREATE TABLE other(a int);

CREATE SUBSCRIPTION sub CONNECTION 'dbname=pub' PUBLICATION pub_all, pub_other
  WITH (create_slot = false);
-- avoid race; wait for sync
DO $$
  DECLARE count int = 1;
  BEGIN LOOP
    SELECT count(1)
      FROM pg_subscription_rel WHERE srsubstate NOT IN ('r', 's')
      INTO count;
	IF count = 0 THEN EXIT; END IF;
  END LOOP; END;
$$;

SELECT * FROM part;

\c pub
ALTER PUBLICATION pub_other ADD TABLE part; -- triggers duplication bug

\c sub
ALTER SUBSCRIPTION sub REFRESH PUBLICATION;
DO $$
  DECLARE count int = 1;
  BEGIN LOOP
    SELECT count(1)
      FROM pg_subscription_rel WHERE srsubstate NOT IN ('r', 's')
      INTO count;
	IF count = 0 THEN EXIT; END IF;
  END LOOP; END;
$$;

SELECT * FROM part;