Re: Skipping schema changes in publication

On Mon, Apr 20, 2026 at 5:43 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> Hi,
>
> When changing a table to UNLOGGED, tables that appear in publications
> via EXCEPT clauses (prexcept = true) are currently allowed, but their
> entries remain in pg_publication_rel.
>
> For example:
> postgres=# create table t1(c1 int);
> CREATE TABLE
> postgres=# create publication pub1 for all tables except (table t1);
> CREATE PUBLICATION
> postgres=# alter table t1 set unlogged;
> ALTER TABLE
> postgres=# \d t1
> Unlogged table "public.t1"
> Column | Type | Collation | Nullable | Default
> --------+---------+-----------+----------+---------
> c1 | integer | | |
> Except publications:
> "pub1"
>
> Since UNLOGGED tables are not supported in publications, this leaves
> stale catalog entries. This patch removes such entries from
> pg_publication_rel when the table is changed to UNLOGGED, and emits a
> NOTICE to inform the user.
>
> Another option considered was to throw an error when setting such
> tables to UNLOGGED. However, allowing the operation was preferred,
> since UNLOGGED tables do not generate WAL and are not replicated
> anyway, so blocking the operation would be unnecessarily restrictive.
>
> Attached patch has the changes for the same.
>

The main concern of removal table form EXCEPT-list is that once the
table is changed back to LOGGED, there is no internal way to add it to
the EXCEPT list again.

OTOH, raising an ERROR does not seem like a good solution either. When
a user converts a table to UNLOGGED, they are effectively excluding it
from publications. Therefore, throwing an error for the purpose that
"table is part of EXCEPT, cannot convert it to UNLOGGED" does not
appear appropriate, since both actions ultimately exclude the table
from publication. I think we can keep the current behavior unchanged
as it causes no harm.

Alternatively, if we want to remove the table from the EXCEPT list:
1) may be a WARNING is better?
2) the error message can be improved; the current one ([1]) seems uninformative.
3) And we can also add DETAIL saying add it to EXCEPT list manually if
needed later(something on this line).

Thoughts?

[1]:
+ ereport(NOTICE,
+ errmsg("relation \"%s\" removed from publication \"%s\" due to being
changed to UNLOGGED",
+ RelationGetRelationName(rel), pubname));

thanks
Shveta