Horton
Menu

Re: Column Filtering in Logical Replication

Euler Taveira <euler@eulerto.com>

View thread
From: "Euler Taveira" <euler@eulerto.com>
To: "vignesh C" <vignesh21@gmail.com>, "Alvaro Herrera" <alvherre@alvh.no-ip.org>
Cc: "Amit Kapila" <amit.kapila16@gmail.com>, "Rahila Syed" <rahilasyed90@gmail.com>, "Tomas Vondra" <tomas.vondra@enterprisedb.com>, "Peter Smith" <smithpb2250@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2021-09-15T14:46:34Z
Lists: pgsql-hackers 
On Wed, Sep 15, 2021, at 9:19 AM, vignesh C wrote:
> I have extracted the parser code and attached it here, so that it will
> be easy to go through. We wanted to support the following syntax as in
> [1]:
> CREATE PUBLICATION pub1 FOR
> TABLE t1,t2,t3, ALL TABLES IN SCHEMA s1,s2,
> SEQUENCE seq1,seq2, ALL SEQUENCES IN SCHEMA s3,s4;
I don't like this syntax. It seems too much syntax for the same purpose in a
single command. If you look at GRANT command whose ALL TABLES IN SCHEMA syntax
was extracted, you can use ON TABLE or ON ALL TABLES IN SCHEMA; you cannot use
both. This proposal allows duplicate objects (of course, you can ignore it but
the current code prevent duplicates -- see publication_add_relation).

IMO you should mimic the GRANT grammar and have multiple commands for row
filtering, column filtering, and ALL FOO IN SCHEMA. The filtering patches only
use the FOR TABLE syntax. The later won't have filtering syntax. Having said
that the grammar should be:

CREATE PUBLICATION name
    [ FOR TABLE [ ONLY ] table_name [ * ] [ (column_name [, ...] ) ] [ WHERE (expression) ] [, ...]
      | FOR ALL TABLES
      | FOR ALL TABLES IN SCHEMA schema_name, [, ...]
      | FOR ALL SEQUENCES IN SCHEMA schema_name, [, ...] ]
    [ WITH ( publication_parameter [= value] [, ... ] ) ]

ALTER PUBLICATION name ADD TABLE [ ONLY ] table_name [ * ] [ (column_name [, ...] ) ] [ WHERE (expression) ]
ALTER PUBLICATION name ADD ALL TABLES IN SCHEMA schema_name, [, ...]
ALTER PUBLICATION name ADD ALL SEQUENCES IN SCHEMA schema_name, [, ...]

ALTER PUBLICATION name SET TABLE [ ONLY ] table_name [ * ] [ (column_name [, ...] ) ] [ WHERE (expression) ]
ALTER PUBLICATION name SET ALL TABLES IN SCHEMA schema_name, [, ...]
ALTER PUBLICATION name SET ALL SEQUENCES IN SCHEMA schema_name, [, ...]

ALTER PUBLICATION name DROP TABLE [ ONLY ] table_name [ * ]
ALTER PUBLICATION name DROP ALL TABLES IN SCHEMA schema_name, [, ...]
ALTER PUBLICATION name DROP ALL SEQUENCES IN SCHEMA schema_name, [, ...]

Opinions?


--
Euler Taveira
EDB   https://www.enterprisedb.com/

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Doc: Explain about Column List feature.

  2. Doc: fix column list vs. replica identity rules.

  3. Prohibit combining publications with different column lists.

  4. Fix the check to limit sync workers.

  5. Wait for subscription to sync in t/031_column_list.sql

  6. Move prattrs to the pg_publication_rel section in docs

  7. Allow specifying column lists for logical replication

  8. Fix row filters with multiple publications

  9. Fix publish_as_relid with multiple publications

  10. Add some additional tests for row filters in logical replication.

  11. Add index on pg_publication_rel.prpubid

  12. Avoid using DefElemAction in AlterPublicationStmt

  13. Small cleanups related to PUBLICATION framework code

  14. Add PublicationTable and PublicationRelInfo structs

  15. Fix various concurrency issues in logical replication worker launching