Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

On Wed, 2026-06-24 at 14:19 +0200, Hüseyin Demir wrote:
> 
> This v6 patch adds a SAFE_INITPRIVS macro that filters aclitem[]
> arrays server-side by checking that each entry's grantor and grantee
> OID still exists in pg_roles. It is applied in exactly two queries:
> 
> 1. getAdditionalACLs() -- the one-time fetch of pg_init_privs at startup
> 2. dumpTable() column ACL prepared statement -- per-table column initprivs

No, that's not good.  If you are running the complicated subquery for
every table dumped, you are re-introducing the performance regression
from the v4 patch that Tom justly complained about.

On the other hand, I agree with you that Tom's idea to make this fix
depend on a minor update of the source server that fixes the string
representation of aclitems is not so great.  Few people undergo the
hassle of applying the latest minor update to a server they are about
to update (and I am not even speaking about the users who keep running
on the 14.3 they went into production with).  Yes, the problem that
the present patch is trying to address is a rare one, and we should
keep the maintenance and performance burden incurred moderate.
But what good is a fix that won't work for a good percentage of the
affected cases, even if they are few?

Here is my latest idea (hold your noses):
Instead of having pg_dump query "FROM pg_catalog.pg_init_privs pip",
how about writing "(FROM (VALUES (...), (...), ...) AS pip", where the
VALUES clause is composed from a query against pg_init_privs run once
at the beginning of pg_dump that excludes the bad entries?
Critizism I forsee is that a) this is ugly and b) very long VALUES
statements might also constitute a performance regression.
However, I have yet to see an extension that produces a hundred
initial privilege entries.

Yours,
Laurenz Albe