Horton

RE: Added schema level support for publication.

tanghy.fnst@fujitsu.com <tanghy.fnst@fujitsu.com>

View thread View on postgr.es
From: "tanghy.fnst@fujitsu.com" <tanghy.fnst@fujitsu.com>
To: Greg Nancarrow <gregn4422@gmail.com>, vignesh C <vignesh21@gmail.com>
Cc: "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>, Masahiko Sawada <sawada.mshk@gmail.com>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, Tom Lane <tgl@sss.pgh.pa.us>, Peter Smith <smithpb2250@gmail.com>, Ajin Cherian <itsajin@gmail.com>, Rahila Syed <rahilasyed90@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>, Amit Kapila <amit.kapila16@gmail.com>
Date: 2021-10-13T08:57:00Z
Lists: pgsql-hackers 
On Wednesday, October 13, 2021 4:10 PM Greg Nancarrow <gregn4422@gmail.com> wrote:
> Also, I found the following scenario where the data is double-published:
> 
> (1) PUB:  CREATE PUBLICATION pub FOR TABLE sch1.sale_201901, TABLE
> sch1.sale_201902 WITH (publish_via_partition_root=true);
> (2) SUB:  CREATE SUBSCRIPTION sub CONNECTION 'dbname=postgres
> host=localhost port=5432' PUBLICATION pub;
> (3) PUB:  INSERT INTO sch.sale VALUES('2019-01-01', 'AU', 'cpu', 5),
> ('2019-01-02', 'AU', 'disk', 8);
> (4) SUB:  SELECT * FROM sch.sale;
> (5) PUB:  ALTER PUBLICATION pub ADD ALL TABLES IN SCHEMA sch;
> (6) SUB:  ALTER SUBSCRIPTION sub REFRESH PUBLICATION;
> (7) SUB:  SELECT * FROM sch.sale;
> 
> sale_date  | country_code | product_sku | units
> ------------+--------------+-------------+-------
>  2019-01-01 | AU           | cpu         |     5
>  2019-01-02 | AU           | disk        |     8
>  2019-01-01 | AU           | cpu         |     5
>  2019-01-02 | AU           | disk        |     8
> 
> 

I changed your test step in (5) and used "ADD TABLE" command as below:
ALTER PUBLICATION pub ADD TABLE sch.sale;

I could get the same result on HEAD.
So I think it's not a problem related to this patch.
Maybe we can post this issue in a new thread.

Regards
Tang

Commits

  1. Include schema/table publications even with exclude options in dump.

  2. Rename some enums to use TABLE instead of REL.

  3. Add tap tests for the schema publications.

  4. Allow publishing the tables of schema.

  5. In pg_dump, use simplehash.h to look up dumpable objects by OID.