Re: Support EXCEPT for TABLES IN SCHEMA publications - Mailing list pgsql-hackers

On Tue, Apr 14, 2026 at 5:01 PM vignesh C <vignesh21@gmail.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