Re: CREATE SUBSCRIPTION ... SERVER vs. pg_dump, etc.

On Fri, 2026-07-10 at 12:59 -0700, Noah Misch wrote:
> An Opus 4.8 review of commit 8185bb5 found two pg_dump+restore
> failure
> scenarios, visible in the attached test patch.  (The patch also tests
> a
> REASSIGN OWNED finding, for which I started a distinct thread
> postgr.es/m/flat/20260710192533(dot)4f(dot)noahmisch(at)microsoft(dot)com).
>
> Opus also emitted the attached report about these findings and
> others.  I
> didn't examine the others closely.  Finding-19, about invalidation
> callbacks,
> stood out as perhaps most exciting if true.

Patch attached.

Generating and validating the connection requires the subscription
owner to be set correctly, the foreign server ACLs to be set, and the
user mapping to exist. The checks at DDL time are were a convenient way
to catch errors, but end up being too strict because those things can
change before the connection is actually needed. In particular, pg_dump
does the DDL in parts (first creating the subscription, then changing
the owner), and we need the first part to succeed.

It would be nice to expand the pg_dump tests to cover this, but that
would require a test dependency on postgres_fdw (or some kind of built-
in test FDW), and I don't think we want that. So I just included SQL
tests.

I think there's a remaining bug involving retaindeadtuples
(228c3708685) where it still tries to connect during binary upgrade. 

That can be seen if you add a $publisher->stop to line 317 (right
before the pg_upgrade that's supposed to succeed) in
004_subscription.pl.

Regards,
Jeff Davis