Re: Support EXCEPT for TABLES IN SCHEMA publications

On Tue, Apr 14, 2026 at 5:01 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> +1 for this.
> Few comments for the first patch:

Thank you Vignesh for the review.

> 1) This should be collected only if except relation was specified, we
> can skip it if it is not specified:
> + /*
> + * Collect explicit table OIDs now, before we
> close the relation
> + * list, so that except-table validation below
> can check for
> + * contradictions without relying on a catalog
> scan that might not
> + * yet see the just-inserted rows.
> + */
> + foreach(lc, rels)
> + {
> + PublicationRelInfo *pri =
> (PublicationRelInfo *) lfirst(lc);
> +
> + explicitrelids = lappend_oid(explicitrelids,
> +
> RelationGetRelid(pri->relation));
> + }
>

Fixed

> 2) Tab completion for except table of that particular schema lists
> other schema, but currently specifying other schema tables is not
> allowed:
> + else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR",
> "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "("))
> + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
>
> Can we list only the tables from the specified schema.
>

Fixed. Required a new query to fetch schema tables.

> 3) Can this check be done at parser itself, it can be done in
> preprocess_pubobj_list parser function to detect the error early:
> + /*
> + * For TABLES IN SCHEMA publications,
> require schema-qualified
> + * names to avoid ambiguity when
> multiple schemas in the
> + * publication have identically-named tables.
> + */
> + foreach(lc, exceptrelations)
> + {
> + PublicationTable *t =
> (PublicationTable *) lfirst(lc);
> +
> + if (t->relation->schemaname == NULL)
> + ereport(ERROR,
> +
> errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> +
> errmsg("table \"%s\" in EXCEPT clause must be schema-qualified",
> +
> t->relation->relname));
> + }
>

Fixed

> 4) This error message does not seems to be conveying the correct error message:
> postgres=# create publication pub1 for tables in schema sch3 except (
> sch3.t1 ) ;
> CREATE PUBLICATION
> postgres=# alter publication pub1 add table sch3.t1 ;
> ERROR: relation "t1" is already member of publication "pub1"
>
> How about an error message like:
> ERROR: table "sch3.t1" cannot be added explicitly because it is listed
> in the EXCEPT clause of schema "sch3" in publication "pub1"
>

Fixed with a bit short error message -
postgres=# alter publication pub8 add table s1.t1;
ERROR: table "s1.t1" cannot be added because it is listed in EXCEPT
clause of publication "pub8"

> 5) This change is not related to this patch:
> @@ -5279,7 +5315,7 @@ foreach my $run (sort keys %pgdump_runs)
> #
> # Either "all_runs" should be set or there should be a
> "like" list,
> # even if it is empty. (This makes the test more
> self-documenting.)
> - if (!defined($tests{$test}->{all_runs})
> + if ( !defined($tests{$test}->{all_runs})
> && !defined($tests{$test}->{like}))
> {
> die "missing \"like\" in test \"$test\"";
>

Fixed

> 6) There is an indentation issue here:
> - pub_except_obj_list opt_pub_except_clause
> + pub_except_obj_list pub_schema_except_obj_list
> + opt_pub_except_clause opt_pub_schema_except_clause
>

Fixed

Please find the updated patches (v2) attached.
Patch-001: addressed above comments
Patch-002: addressed Vignesh's comments from [1]
Patch-003: updated the documentation and also added tab-completion for
the SET TABLES IN SCHEMA EXCEPT

[1] https://www.postgresql.org/message-id/CALDaNm3pBWnJJ9ynVj3KeZ%2BNQQXboU-goObvF9fZ0GYzyuJFhQ%40mail.gmail.com

--
Thanks,
Nisha