Skip to content

fix(publication): ensure PostgreSQL 13+ compatibility for multi-table publications#8888

Merged
gbartolini merged 3 commits intomainfrom
dev/8588
Nov 1, 2025
Merged

fix(publication): ensure PostgreSQL 13+ compatibility for multi-table publications#8888
gbartolini merged 3 commits intomainfrom
dev/8588

Conversation

@armru
Copy link
Member

@armru armru commented Oct 20, 2025 •
edited
Loading

The previous implementation generated SQL that only worked on PostgreSQL 15+:
CREATE PUBLICATION "pub" FOR TABLE "t1", TABLE "t2"

While this syntax is valid in PostgreSQL 15+, it fails in PostgreSQL 13+ with a syntax error. The fix now generates backward-compatible SQL:
CREATE PUBLICATION "pub" FOR TABLE "t1", "t2"

The implementation groups consecutive tables under a single TABLE keyword and properly handles mixed scenarios with TABLES IN SCHEMA, ensuring full compatibility across all supported PostgreSQL versions (13+).

Fixes #8588

Note for reviewers

  • this still doesn't solve the issue of mixed tables and schemas on pg13-14 and aims only to fix the issue reported
  • minimal implementation, we could have refactored the cnpg publication cmd to reuse the same logic but the diff would have gone way bigger

@armru armru requested a review from a team as a code owner October 20, 2025 15:06
@dosubot dosubot bot added the size:L This PR changes 100-499 lines, ignoring generated files. label Oct 20, 2025
@cnpg-bot cnpg-bot added backport-requested ◀️ This pull request should be backported to all supported releases release-1.25 release-1.26 release-1.27 labels Oct 20, 2025
@github-actions
Copy link
Contributor

github-actions bot commented Oct 20, 2025

❗ By default, the pull request is configured to backport to all release branches.

  • To stop backporting this pr, remove the label: backport-requested ◀️ or add the label 'do not backport'
  • To stop backporting this pr to a certain release branch, remove the specific branch label: release-x.y

@armru
Copy link
Member Author

armru commented Oct 20, 2025

/test limit=local

@dosubot dosubot bot added the bug 🐛 Something isn't working label Oct 20, 2025
@github-actions
Copy link
Contributor

github-actions bot commented Oct 20, 2025

@armru, here's the link to the E2E on CNPG workflow run: https://github.com/cloudnative-pg/cloudnative-pg/actions/runs/18656354632

@armru armru force-pushed the dev/8588 branch 2 times, most recently from f63ada9 to 834348f Compare October 21, 2025 07:41
@armru
Copy link
Member Author

armru commented Oct 21, 2025

/test limit=local

@github-actions
Copy link
Contributor

github-actions bot commented Oct 21, 2025

@armru, here's the link to the E2E on CNPG workflow run: https://github.com/cloudnative-pg/cloudnative-pg/actions/runs/18676589645

@mnencia
Copy link
Member

mnencia commented Oct 23, 2025

/test

@github-actions
Copy link
Contributor

github-actions bot commented Oct 23, 2025

@mnencia, here's the link to the E2E on CNPG workflow run: https://github.com/cloudnative-pg/cloudnative-pg/actions/runs/18741963356

@mnencia
Copy link
Member

mnencia commented Oct 23, 2025

/test

@github-actions
Copy link
Contributor

github-actions bot commented Oct 23, 2025

@mnencia, here's the link to the E2E on CNPG workflow run: https://github.com/cloudnative-pg/cloudnative-pg/actions/runs/18741991753

@leonardoce leonardoce force-pushed the dev/8588 branch 2 times, most recently from 42a8889 to df6a91b Compare October 30, 2025 15:58
@dosubot dosubot bot added the lgtm This PR has been approved by a maintainer label Oct 30, 2025
@leonardoce
Copy link
Contributor

leonardoce commented Oct 30, 2025

/test

@github-actions
Copy link
Contributor

github-actions bot commented Oct 30, 2025

@leonardoce, here's the link to the E2E on CNPG workflow run: https://github.com/cloudnative-pg/cloudnative-pg/actions/runs/18947070276

@armru @gbartolini
fix(publication): ensure PostgreSQL 13+ compatibility for multi-table…
7d8935d 
… publications

Fix SQL generation for publications with multiple tables to support
PostgreSQL 13+. The previous implementation generated SQL that only
worked on PostgreSQL 15+:
  CREATE PUBLICATION "pub" FOR TABLE "t1", TABLE "t2"

While this syntax is valid in PostgreSQL 15+, it fails in PostgreSQL 13+
with a syntax error. The fix now generates backward-compatible SQL:
  CREATE PUBLICATION "pub" FOR TABLE "t1", "t2"

The implementation groups consecutive tables under a single TABLE keyword
and properly handles mixed scenarios with TABLES IN SCHEMA, ensuring full
compatibility across all supported PostgreSQL versions (13+).

Fixes #8588

Signed-off-by: Armando Ruocco <armando.ruocco@enterprisedb.com>
@gbartolini
docs: modified
ba91a83 
Signed-off-by: Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>
@gbartolini gbartolini requested a review from jsilvela as a code owner November 1, 2025 15:31
@gbartolini
docs: fixed explicit version
135a40a 
Signed-off-by: Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>
@gbartolini gbartolini added the ok to merge 👌 This PR can be merged label Nov 1, 2025
@gbartolini gbartolini merged commit 514a43d into main Nov 1, 2025
35 of 36 checks passed
@gbartolini gbartolini deleted the dev/8588 branch November 1, 2025 16:53
cnpg-bot pushed a commit that referenced this pull request Nov 1, 2025
@armru
fix(publication): ensure compatibility with PostgreSQL 13+ for multi-…
22f87e3 
…table publications (#8888)

The previous implementation generated SQL valid only in PostgreSQL 15+, for
example:

    CREATE PUBLICATION "pub" FOR TABLE "t1", TABLE "t2";

This syntax causes a syntax error in PostgreSQL 13 and 14. The fix updates the
SQL generation to produce backward-compatible statements such as:

    CREATE PUBLICATION "pub" FOR TABLE "t1", "t2";

The new implementation groups consecutive tables under a single `TABLE` keyword
and correctly handles mixed cases involving `TABLES IN SCHEMA`, ensuring full
compatibility across all supported PostgreSQL versions (13+).

Fixes #8588.

Signed-off-by: Armando Ruocco [armando.ruocco@enterprisedb.com](mailto:armando.ruocco@enterprisedb.com)
Signed-off-by: Gabriele Bartolini [gabriele.bartolini@enterprisedb.com](mailto:gabriele.bartolini@enterprisedb.com)
Co-authored-by: Gabriele Bartolini [gabriele.bartolini@enterprisedb.com](mailto:gabriele.bartolini@enterprisedb.com)
(cherry picked from commit 514a43d)
cnpg-bot pushed a commit that referenced this pull request Nov 1, 2025
@armru
fix(publication): ensure compatibility with PostgreSQL 13+ for multi-…
1186b27 
…table publications (#8888)

The previous implementation generated SQL valid only in PostgreSQL 15+, for
example:

    CREATE PUBLICATION "pub" FOR TABLE "t1", TABLE "t2";

This syntax causes a syntax error in PostgreSQL 13 and 14. The fix updates the
SQL generation to produce backward-compatible statements such as:

    CREATE PUBLICATION "pub" FOR TABLE "t1", "t2";

The new implementation groups consecutive tables under a single `TABLE` keyword
and correctly handles mixed cases involving `TABLES IN SCHEMA`, ensuring full
compatibility across all supported PostgreSQL versions (13+).

Fixes #8588.

Signed-off-by: Armando Ruocco [armando.ruocco@enterprisedb.com](mailto:armando.ruocco@enterprisedb.com)
Signed-off-by: Gabriele Bartolini [gabriele.bartolini@enterprisedb.com](mailto:gabriele.bartolini@enterprisedb.com)
Co-authored-by: Gabriele Bartolini [gabriele.bartolini@enterprisedb.com](mailto:gabriele.bartolini@enterprisedb.com)
(cherry picked from commit 514a43d)
cnpg-bot pushed a commit that referenced this pull request Nov 1, 2025
@armru
fix(publication): ensure compatibility with PostgreSQL 13+ for multi-…
6262003 
…table publications (#8888)

The previous implementation generated SQL valid only in PostgreSQL 15+, for
example:

    CREATE PUBLICATION "pub" FOR TABLE "t1", TABLE "t2";

This syntax causes a syntax error in PostgreSQL 13 and 14. The fix updates the
SQL generation to produce backward-compatible statements such as:

    CREATE PUBLICATION "pub" FOR TABLE "t1", "t2";

The new implementation groups consecutive tables under a single `TABLE` keyword
and correctly handles mixed cases involving `TABLES IN SCHEMA`, ensuring full
compatibility across all supported PostgreSQL versions (13+).

Fixes #8588.

Signed-off-by: Armando Ruocco [armando.ruocco@enterprisedb.com](mailto:armando.ruocco@enterprisedb.com)
Signed-off-by: Gabriele Bartolini [gabriele.bartolini@enterprisedb.com](mailto:gabriele.bartolini@enterprisedb.com)
Co-authored-by: Gabriele Bartolini [gabriele.bartolini@enterprisedb.com](mailto:gabriele.bartolini@enterprisedb.com)
(cherry picked from commit 514a43d)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@gbartolini gbartolini gbartolini approved these changes

@leonardoce leonardoce leonardoce approved these changes

@jsilvela jsilvela Awaiting requested review from jsilvela jsilvela is a code owner

Assignees

No one assigned

Labels

backport-requested ◀️ This pull request should be backported to all supported releases bug 🐛 Something isn't working lgtm This PR has been approved by a maintainer ok to merge 👌 This PR can be merged release-1.25 release-1.26 release-1.27 size:L This PR changes 100-499 lines, ignoring generated files.

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

[Bug]: Publication incorrectly compiles SQL when used with multiple tables

5 participants

@armru @mnencia @leonardoce @gbartolini @cnpg-bot