Support EXCEPT for TABLES IN SCHEMA publications

From: Nisha Moond <nisha(dot)moond412(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Support EXCEPT for TABLES IN SCHEMA publications
Date: 2026-04-14 06:30:12
Message-ID: CABdArM5sw4Q1ZU8HGdo4BSc1A_+8xtUNq17j6wcir=yMUy19Cg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,

Following earlier work to support EXCEPT for FOR ALL TABLES [1]
publications, starting this thread to extend the same capability to
schema-level publications (TABLES IN SCHEMA).

Currently, TABLES IN SCHEMA publishes all tables in a schema with no
way to exclude a subset. Users who want to skip a few tables must
switch to an explicit FOR TABLE list, which loses the convenience of
schema-level publishing and requires ongoing maintenance as tables are
added.

Proposed syntax:
------------------------
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (s1.t1, s1.t2);
ALTER PUBLICATION pub ADD TABLES IN SCHEMA s1 EXCEPT (s1.t1);
ALTER PUBLICATION pub SET TABLES IN SCHEMA s1 EXCEPT (s1.t1);

Note: Tables in the EXCEPT clause must be schema-qualified to avoid
ambiguity and must belong to the published schema; otherwise, an error
is raised.

Rules and behavior:
----------------------------
1) TABLES IN SCHEMA can be combined with FOR TABLE, but EXCEPT applies
only to the schema clause and must appear immediately after it.

Supported:
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (s1.t1), TABLE s2.t1;
CREATE PUBLICATION pub FOR TABLE s2.t1, TABLES IN SCHEMA s1 EXCEPT (s1.t1);
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (s1.t1), s2
EXCEPT (s2.t1)

Not supported:
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1, TABLE s2.t1 EXCEPT (s1.t1);

-- This same rule applies to ALTER PUBLICATION ... ADD/SET.

2) Conflicting definitions
Specifying the same table both in the EXCEPT clause and explicitly in
the TABLE clause results in an error, as this creates a conflicting
definition for the publication.

3) "ALTER PUBLICATION ... DROP TABLES IN SCHEMA" does not support
EXCEPT clause. Whereas, dropping a schema also removes any associated
entries from the EXCEPT list of the publication.
-- To only remove/update except list entries, use SET instead.

4) Consistency with ALL TABLES EXCEPT rules:
4a) Excluding a partitioned root excludes all its partitions
4b) Individual partitions cannot be excluded directly; exclude the root table.
4c) Excluding an inheritance parent (without ONLY) also excludes its children.

The patches are divided into three parts to simplify review:
Patch-001: Basic framework to support EXCEPT in CREATE PUBLICATION
... TABLES IN SCHEMA
Patch-002: Extend support to ALTER PUBLICATION ... ADD TABLES IN SCHEMA
Patch-003: Extend support to ALTER PUBLICATION ... SET TABLES IN SCHEMA

The patches are attached, feedback and suggestions are welcome.

[1] https://www.postgresql.org/message-id/CALDaNm3%3DJrucjhiiwsYQw5-PGtBHFONa6F7hhWCXMsGvh%3DtamA%40mail.gmail.com

--
Thanks,
Nisha

Attachment Content-Type Size
v1-0001-Support-EXCEPT-clause-for-schema-level-publicatio.patch application/octet-stream 37.7 KB
v1-0002-Add-EXCEPT-support-to-ALTER-PUBLICATION-ADD-TABLE.patch application/octet-stream 15.5 KB
v1-0003-Add-EXCEPT-support-to-ALTER-PUBLICATION-SET-TABLE.patch application/octet-stream 14.8 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Geier 2026-04-14 07:02:10 Re: Reduce build times of pg_trgm GIN indexes
Previous Message Peter Smith 2026-04-14 06:28:37 Re: Add missing period to DETAIL messages