Re: Add support for specifying tables in pg_createsubscriber.

From: Shubham Khanna <khannashubham1197(at)gmail(dot)com>
To: "Zhijie Hou (Fujitsu)" <houzj(dot)fnst(at)fujitsu(dot)com>
Cc: Peter Smith <smithpb2250(at)gmail(dot)com>, vignesh C <vignesh21(at)gmail(dot)com>, "Hayato Kuroda (Fujitsu)" <kuroda(dot)hayato(at)fujitsu(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Add support for specifying tables in pg_createsubscriber.
Date: 2025-09-02 11:42:04
Message-ID: CAHv8RjJZBdEj+znQ3k8Lh4RCtKAHgK8pnQ4-+ooAuf23CtLH-Q@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Mon, Aug 25, 2025 at 12:53 PM Zhijie Hou (Fujitsu)
<houzj(dot)fnst(at)fujitsu(dot)com> wrote:
>
> On Monday, August 25, 2025 8:08 AM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
> >
> > IIUC, the only purpose of the proposed '--table' spec is to allow some users the
> > ability to tweak the default "CREATE PUBLICATION p FOR ALL TABLES;" that
> > the 'pg_createsubscriber' tool would otherwise construct.
> >
> > But --table is introducing problems too: e.g.
> > - tricky option ordering rules (e.g. most recent --database if dbname not
> > specified?)
> > - too many --table may be needed; redundant repeating of databases and
> > schemas also seems very verbose.
> > - limitations for syntax (e.g. what about FOR TABLES IN SCHEMA?)
> > - limitations for future syntax (e.g. what about SEQUENCES?; what about
> > EXCEPT?)
> >
> > Can these problems disappear just by slightly changing the meaning of the
> > existing '--publication' option to allow specifying more than just the
> > publication name: e.g.
> > --publication = 'pub1' ==> "CREATE PUBLICATION pub1 FOR ALL TABLES;"
> > --publication = 'pub1 FOR TABLE t1,t2(c1,c2),t3' ==> "CREATE
> > PUBLICATION pub1 FOR TABLE t1,t2(c1,c2),t3;"
> > --publication = 'pub1 FOR TABLES IN SCHEMA s1' ==> "CREATE
> > PUBLICATION
> > pub1 FOR TABLES IN SCHEMA s1;"
> > --publication = 'pub1 FOR ALL TABLES WITH (publish_via_partition_root)'
> > ==> "CREATE PUBLICATION pub1 FOR ALL TABLES WITH
> > (publish_via_partition_root);"
>
> I think embedding commands directly in the option value poses SQL injection
> risks, so is not very safe. We could directly allow users to use an existing
> publication (as mentioned by Euler[1]).
>
> [1] https://stream.boxwatch.ru/message-id/30cc34eb-07a0-4b55-b4fe-6c526886b2c4%40app.fastmail.com
>

I have incorporated the latest approach suggested by Hou-san at [1]
and added a new --existing-publication option. This enhancement allows
users to specify existing publications instead of always creating new
ones. The attached patch includes the corresponding changes.

[1] - https://stream.boxwatch.ru/message-id/TY4PR01MB169075A23F8D5EED4154A4AF3943EA%40TY4PR01MB16907.jpnprd01.prod.outlook.com

Thanks and regards,
Shubham Khanna.

Attachment Content-Type Size
v4-0001-Support-existing-publications-in-pg_createsubscri.patch application/octet-stream 18.0 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Shlok Kyal 2025-09-02 12:12:13 Re: POC: enable logical decoding when wal_level = 'replica' without a server restart
Previous Message David Geier 2025-09-02 11:38:17 Re: Performance issues with parallelism and LIMIT