Horton

Thread

Commits

  1. pg_createsubscriber: Fix duplicate publication name rejection.

  1. pg_createsubscriber: allow duplicate publication names

    Chao Li <li.evan.chao@gmail.com> — 2026-06-01T13:12:17Z

    Hi,

While testing “Support existing publications in pg_createsubscriber”, I noticed that it doesn’t allow the same publication name even when the publications are from different databases.

A simple repro:
```
% createdb db1
% createdb db2
% psql db1 -c 'CREATE PUBLICATION app_pub FOR ALL TABLES'
% psql db2 -c 'CREATE PUBLICATION app_pub FOR ALL TABLES'
CREATE PUBLICATION
CREATE PUBLICATION
% pg_createsubscriber --dry-run --pgdata <data-dir> --publisher-server postgres --database db1 --database db2 --publication app_pub --publication app_pub
pg_createsubscriber: error: publication "app_pub" specified more than once for --publication
```

The rejection is not introduced by this feature, but previously it was not a real problem because the specified publication names would always be created. After this feature, it may become an issue, since the feature intends to use existing publications, and users may have defined publications with the same name in different databases. So the current restriction becomes a limitation of the new feature. I don’t think we should leave this limitation in PG19.

Since pg_createsubscriber already ensures that duplicate database names cannot be specified, we can just remove the duplicate publication name check.

See the attached patch for details.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

  2. Re: pg_createsubscriber: allow duplicate publication names

    Shlok Kyal <shlok.kyal.oss@gmail.com> — 2026-06-01T18:36:04Z

    On Mon, 1 Jun 2026 at 18:43, Chao Li <li.evan.chao@gmail.com> wrote:
>
> Hi,
>
> While testing “Support existing publications in pg_createsubscriber”, I noticed that it doesn’t allow the same publication name even when the publications are from different databases.
>
> A simple repro:
> ```
> % createdb db1
> % createdb db2
> % psql db1 -c 'CREATE PUBLICATION app_pub FOR ALL TABLES'
> % psql db2 -c 'CREATE PUBLICATION app_pub FOR ALL TABLES'
> CREATE PUBLICATION
> CREATE PUBLICATION
> % pg_createsubscriber --dry-run --pgdata <data-dir> --publisher-server postgres --database db1 --database db2 --publication app_pub --publication app_pub
> pg_createsubscriber: error: publication "app_pub" specified more than once for --publication
> ```
>
> The rejection is not introduced by this feature, but previously it was not a real problem because the specified publication names would always be created. After this feature, it may become an issue, since the feature intends to use existing publications, and users may have defined publications with the same name in different databases. So the current restriction becomes a limitation of the new feature. I don’t think we should leave this limitation in PG19.
>
> Since pg_createsubscriber already ensures that duplicate database names cannot be specified, we can just remove the duplicate publication name check.
>
> See the attached patch for details.
>

Hi Chao-san,

I was able to reproduce the scenario and agree with your analysis.

Each --database option is associated with a corresponding
--publication option, and duplicate database names are not allowed.
And also, since publications are database-local objects, there is no
need to reject duplicate publication names.

The proposed changes look good to me.

Thanks,
Shlok Kyal

  3. Re: pg_createsubscriber: allow duplicate publication names

    Amit Kapila <amit.kapila16@gmail.com> — 2026-06-02T06:14:46Z

    On Mon, Jun 1, 2026 at 6:42 PM Chao Li <li.evan.chao@gmail.com> wrote:
>
> While testing “Support existing publications in pg_createsubscriber”, I noticed that it doesn’t allow the same publication name even when the publications are from different databases.
>
> A simple repro:
> ```
> % createdb db1
> % createdb db2
> % psql db1 -c 'CREATE PUBLICATION app_pub FOR ALL TABLES'
> % psql db2 -c 'CREATE PUBLICATION app_pub FOR ALL TABLES'
> CREATE PUBLICATION
> CREATE PUBLICATION
> % pg_createsubscriber --dry-run --pgdata <data-dir> --publisher-server postgres --database db1 --database db2 --publication app_pub --publication app_pub
> pg_createsubscriber: error: publication "app_pub" specified more than once for --publication
> ```
>
> The rejection is not introduced by this feature, but previously it was not a real problem because the specified publication names would always be created. After this feature, it may become an issue, since the feature intends to use existing publications, and users may have defined publications with the same name in different databases. So the current restriction becomes a limitation of the new feature. I don’t think we should leave this limitation in PG19.
>
> Since pg_createsubscriber already ensures that duplicate database names cannot be specified, we can just remove the duplicate publication name check.
>

Doesn't the same point apply to subscription names? The subscription
names can be specified by "--subscription=name"

-- 
With Regards,
Amit Kapila.

  4. Re: pg_createsubscriber: allow duplicate publication names

    Chao Li <li.evan.chao@gmail.com> — 2026-06-02T06:59:12Z

    
> On Jun 2, 2026, at 14:14, Amit Kapila <amit.kapila16@gmail.com> wrote:
> 
> On Mon, Jun 1, 2026 at 6:42 PM Chao Li <li.evan.chao@gmail.com> wrote:
>> 
>> While testing “Support existing publications in pg_createsubscriber”, I noticed that it doesn’t allow the same publication name even when the publications are from different databases.
>> 
>> A simple repro:
>> ```
>> % createdb db1
>> % createdb db2
>> % psql db1 -c 'CREATE PUBLICATION app_pub FOR ALL TABLES'
>> % psql db2 -c 'CREATE PUBLICATION app_pub FOR ALL TABLES'
>> CREATE PUBLICATION
>> CREATE PUBLICATION
>> % pg_createsubscriber --dry-run --pgdata <data-dir> --publisher-server postgres --database db1 --database db2 --publication app_pub --publication app_pub
>> pg_createsubscriber: error: publication "app_pub" specified more than once for --publication
>> ```
>> 
>> The rejection is not introduced by this feature, but previously it was not a real problem because the specified publication names would always be created. After this feature, it may become an issue, since the feature intends to use existing publications, and users may have defined publications with the same name in different databases. So the current restriction becomes a limitation of the new feature. I don’t think we should leave this limitation in PG19.
>> 
>> Since pg_createsubscriber already ensures that duplicate database names cannot be specified, we can just remove the duplicate publication name check.
>> 
> 
> Doesn't the same point apply to subscription names? The subscription
> names can be specified by "--subscription=name"
> 
> -- 
> With Regards,
> Amit Kapila.

In theory, yes, since subscription names are also unique per database. I didn’t include that in v1 because the publication-name issue affects the new PG19 feature for reusing existing publications, whereas allowing duplicate subscription names seems like an improvement that could be a material of v20.

As you ask, I put the subscription-name change in 0002. Please decide how to proceed. If you decide to accept both, they can be squashed.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

  5. RE: pg_createsubscriber: allow duplicate publication names

    Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com> — 2026-06-02T07:32:59Z

    Dear Chao, Amit,

> In theory, yes, since subscription names are also unique per database. I didn’t
> include that in v1 because the publication-name issue affects the new PG19
> feature for reusing existing publications, whereas allowing duplicate subscription
> names seems like an improvement that could be a material of v20.
> 
> As you ask, I put the subscription-name change in 0002. Please decide how to
> proceed. If you decide to accept both, they can be squashed.

For subscription side, I had a concern that name duplication error can be more likely
to happen if we specify the same subscription name for two databases. Because the
same will be reused for the slot name. Reproducer:

```
$ pg_createsubscriber -D data_sta/ -P 'user=postgres port=5432' -d postgres --subscription sub -d testdb --subscription sub -U postgres
...
pg_createsubscriber: error: could not create replication slot "sub" in database "testdb": ERROR:  replication slot "sub" already exists
```

Personally -1 to accept the idea as-is.

Best regards,
Hayato Kuroda
FUJITSU LIMITED

  6. Re: pg_createsubscriber: allow duplicate publication names

    Chao Li <li.evan.chao@gmail.com> — 2026-06-02T07:58:24Z

    
> On Jun 2, 2026, at 15:32, Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com> wrote:
> 
> Dear Chao, Amit,
> 
>> In theory, yes, since subscription names are also unique per database. I didn’t
>> include that in v1 because the publication-name issue affects the new PG19
>> feature for reusing existing publications, whereas allowing duplicate subscription
>> names seems like an improvement that could be a material of v20.
>> 
>> As you ask, I put the subscription-name change in 0002. Please decide how to
>> proceed. If you decide to accept both, they can be squashed.
> 
> For subscription side, I had a concern that name duplication error can be more likely
> to happen if we specify the same subscription name for two databases. Because the
> same will be reused for the slot name. Reproducer:
> 
> ```
> $ pg_createsubscriber -D data_sta/ -P 'user=postgres port=5432' -d postgres --subscription sub -d testdb --subscription sub -U postgres
> ...
> pg_createsubscriber: error: could not create replication slot "sub" in database "testdb": ERROR:  replication slot "sub" already exists
> ```
> 
> Personally -1 to accept the idea as-is.
> 
> Best regards,
> Hayato Kuroda
> FUJITSU LIMITED
> 

Good point. Allowing duplicate subscription names would require additional work, so let's leave that for v20. I'm going to withdraw 0002. I also updated 0001's commit message to explain why subscription names are excluded.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

  7. Re: pg_createsubscriber: allow duplicate publication names

    Amit Kapila <amit.kapila16@gmail.com> — 2026-06-02T08:57:13Z

    On Tue, Jun 2, 2026 at 1:29 PM Chao Li <li.evan.chao@gmail.com> wrote:
>
> > On Jun 2, 2026, at 15:32, Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com> wrote:
> >
> > Dear Chao, Amit,
> >
> >> In theory, yes, since subscription names are also unique per database. I didn’t
> >> include that in v1 because the publication-name issue affects the new PG19
> >> feature for reusing existing publications, whereas allowing duplicate subscription
> >> names seems like an improvement that could be a material of v20.
> >>
> >> As you ask, I put the subscription-name change in 0002. Please decide how to
> >> proceed. If you decide to accept both, they can be squashed.
> >
> > For subscription side, I had a concern that name duplication error can be more likely
> > to happen if we specify the same subscription name for two databases. Because the
> > same will be reused for the slot name. Reproducer:
> >
> > ```
> > $ pg_createsubscriber -D data_sta/ -P 'user=postgres port=5432' -d postgres --subscription sub -d testdb --subscription sub -U postgres
> > ...
> > pg_createsubscriber: error: could not create replication slot "sub" in database "testdb": ERROR:  replication slot "sub" already exists
> > ```
> >

Can't we use --replication-slot=name to avoid this ERROR? I mean use
different replication slot names.

> > Personally -1 to accept the idea as-is.
> >
> > Best regards,
> > Hayato Kuroda
> > FUJITSU LIMITED
> >
>
> Good point. Allowing duplicate subscription names would require additional work, so let's leave that for v20. I'm going to withdraw 0002. I also updated 0001's commit message to explain why subscription names are excluded.
>

I was not suggesting to get the subscription work for PG19. Otherwise,
your patch for publication names looks good to me.

-- 
With Regards,
Amit Kapila.

  8. Re: pg_createsubscriber: allow duplicate publication names

    Chao Li <li.evan.chao@gmail.com> — 2026-06-02T09:17:18Z

    
> On Jun 2, 2026, at 16:57, Amit Kapila <amit.kapila16@gmail.com> wrote:
> 
> On Tue, Jun 2, 2026 at 1:29 PM Chao Li <li.evan.chao@gmail.com> wrote:
>> 
>>> On Jun 2, 2026, at 15:32, Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com> wrote:
>>> 
>>> Dear Chao, Amit,
>>> 
>>>> In theory, yes, since subscription names are also unique per database. I didn’t
>>>> include that in v1 because the publication-name issue affects the new PG19
>>>> feature for reusing existing publications, whereas allowing duplicate subscription
>>>> names seems like an improvement that could be a material of v20.
>>>> 
>>>> As you ask, I put the subscription-name change in 0002. Please decide how to
>>>> proceed. If you decide to accept both, they can be squashed.
>>> 
>>> For subscription side, I had a concern that name duplication error can be more likely
>>> to happen if we specify the same subscription name for two databases. Because the
>>> same will be reused for the slot name. Reproducer:
>>> 
>>> ```
>>> $ pg_createsubscriber -D data_sta/ -P 'user=postgres port=5432' -d postgres --subscription sub -d testdb --subscription sub -U postgres
>>> ...
>>> pg_createsubscriber: error: could not create replication slot "sub" in database "testdb": ERROR:  replication slot "sub" already exists
>>> ```
>>> 
> 
> Can't we use --replication-slot=name to avoid this ERROR? I mean use
> different replication slot names.

Yes, we can.

The additional work I was thinking of is that, to support duplicate subscription names, when there are duplicate subscription names and the --replication-slot option is omitted, we may need an extra sanity check to prevent creating slots with the same name and then failing.

> 
>>> Personally -1 to accept the idea as-is.
>>> 
>>> Best regards,
>>> Hayato Kuroda
>>> FUJITSU LIMITED
>>> 
>> 
>> Good point. Allowing duplicate subscription names would require additional work, so let's leave that for v20. I'm going to withdraw 0002. I also updated 0001's commit message to explain why subscription names are excluded.
>> 
> 
> I was not suggesting to get the subscription work for PG19. Otherwise,
> your patch for publication names looks good to me.
> 

Got it. I can put the subscription work on my TODO list for v20.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

  9. RE: pg_createsubscriber: allow duplicate publication names

    Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com> — 2026-06-02T10:01:07Z

    Dear Amit,

> Can't we use --replication-slot=name to avoid this ERROR? I mean use
> different replication slot names.

Are you saying the below case, right? It can work.

```
$ pg_createsubscriber -D data_sta/ -P 'user=postgres port=5432' -d postgres --subscription sub --replication-slot=slot1 -d testdb --subscription sub --replication-slot=slot2 -U postgres
```

BTW; slot creations are tried before the standby promotes, thus there may be a chance
user read the error message, change the subscription name, and retry. But I still
think we should guard the combination, which means it's too late for PG19.

Best regards,
Hayato Kuroda
FUJITSU LIMITED

  10. Re: pg_createsubscriber: allow duplicate publication names

    Chao Li <li.evan.chao@gmail.com> — 2026-06-03T02:25:28Z

    
> On Jun 2, 2026, at 18:01, Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com> wrote:
> 
> Dear Amit,
> 
>> Can't we use --replication-slot=name to avoid this ERROR? I mean use
>> different replication slot names.
> 
> Are you saying the below case, right? It can work.
> 
> ```
> $ pg_createsubscriber -D data_sta/ -P 'user=postgres port=5432' -d postgres --subscription sub --replication-slot=slot1 -d testdb --subscription sub --replication-slot=slot2 -U postgres
> ```
> 
> BTW; slot creations are tried before the standby promotes, thus there may be a chance
> user read the error message, change the subscription name, and retry. But I still
> think we should guard the combination, which means it's too late for PG19.
> 

Hi Hayato-san, thanks for the comment. I’ll keep it in my notes for later reference.

BTW, I just saw someone has added this patch to the Open Items list.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

  11. Re: pg_createsubscriber: allow duplicate publication names

    Amit Kapila <amit.kapila16@gmail.com> — 2026-06-08T09:30:29Z

    On Wed, Jun 3, 2026 at 7:56 AM Chao Li <li.evan.chao@gmail.com> wrote:
>
> > On Jun 2, 2026, at 18:01, Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com> wrote:
> >
> > Dear Amit,
> >
> >> Can't we use --replication-slot=name to avoid this ERROR? I mean use
> >> different replication slot names.
> >
> > Are you saying the below case, right? It can work.
> >
> > ```
> > $ pg_createsubscriber -D data_sta/ -P 'user=postgres port=5432' -d postgres --subscription sub --replication-slot=slot1 -d testdb --subscription sub --replication-slot=slot2 -U postgres
> > ```
> >
> > BTW; slot creations are tried before the standby promotes, thus there may be a chance
> > user read the error message, change the subscription name, and retry. But I still
> > think we should guard the combination, which means it's too late for PG19.
> >
>
> Hi Hayato-san, thanks for the comment. I’ll keep it in my notes for later reference.
>
> BTW, I just saw someone has added this patch to the Open Items list.
>

Pushed the fix. We can close the open item tomorrow morning if
buildfarm doesn't complain and we didn't see any other comments
related to this.

-- 
With Regards,
Amit Kapila.

  12. Re: pg_createsubscriber: allow duplicate publication names

    Chao Li <li.evan.chao@gmail.com> — 2026-06-09T00:38:02Z

    
> On Jun 8, 2026, at 17:30, Amit Kapila <amit.kapila16@gmail.com> wrote:
> 
> On Wed, Jun 3, 2026 at 7:56 AM Chao Li <li.evan.chao@gmail.com> wrote:
>> 
>>> On Jun 2, 2026, at 18:01, Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com> wrote:
>>> 
>>> Dear Amit,
>>> 
>>>> Can't we use --replication-slot=name to avoid this ERROR? I mean use
>>>> different replication slot names.
>>> 
>>> Are you saying the below case, right? It can work.
>>> 
>>> ```
>>> $ pg_createsubscriber -D data_sta/ -P 'user=postgres port=5432' -d postgres --subscription sub --replication-slot=slot1 -d testdb --subscription sub --replication-slot=slot2 -U postgres
>>> ```
>>> 
>>> BTW; slot creations are tried before the standby promotes, thus there may be a chance
>>> user read the error message, change the subscription name, and retry. But I still
>>> think we should guard the combination, which means it's too late for PG19.
>>> 
>> 
>> Hi Hayato-san, thanks for the comment. I’ll keep it in my notes for later reference.
>> 
>> BTW, I just saw someone has added this patch to the Open Items list.
>> 
> 
> Pushed the fix. We can close the open item tomorrow morning if
> buildfarm doesn't complain and we didn't see any other comments
> related to this.
> 
> -- 
> With Regards,
> Amit Kapila.

Thanks for pushing. I have moved the item to the “resolved” section.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/