MIME-Version: 1.0
References: <202502131716.7mgkcnrem2hn@alvherre.pgsql> <2939991.1740089974@sss.pgh.pa.us>
 <CA+TgmoauoiW4ydDhdrseg+DD4Kwha=+TSZp18BrJeHKx3o1Fdw@mail.gmail.com>
 <179448.1772033773@sss.pgh.pa.us> <CA+TgmoYFc1x11Y7AHxn6Jb0mvJ9-aPttmjqezj=YKnHmqVb-Wg@mail.gmail.com>
 <265501.1772038216@sss.pgh.pa.us> <CA+TgmobNKFc34nj+w6iX0zWJ1=mfYWqQS91PqsJrm4VD4o1yOw@mail.gmail.com>
 <296083.1772041154@sss.pgh.pa.us> <CA+TgmoYJ8Y1G1r9U7pRwnyw=8dmM0xygLguE+bdihkN8GwPuCQ@mail.gmail.com>
 <301798.1772044229@sss.pgh.pa.us> <CA+TgmobZRL02-R7k_i3ENYz8CmqCPUZv6cHY51BejqPy7OhFJw@mail.gmail.com>
 <304751.1772045891@sss.pgh.pa.us> <1103272.1772227345@sss.pgh.pa.us> <1104379.1772227731@sss.pgh.pa.us>
In-Reply-To: <1104379.1772227731@sss.pgh.pa.us>
From: Robert Haas <robertmhaas@gmail.com>
Date: Fri, 27 Feb 2026 16:42:30 -0500
Message-ID: <CA+TgmobMUVTj=5csi-vT=w731H5PBj+0SpCS_AndZEv_eum-jA@mail.gmail.com>
Subject: Re: Major Version Upgrade failure due to orphan roles entries in catalog
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@alvh.no-ip.org>, 
	Virender Singla <virender.cse@gmail.com>, pgsql-bugs@lists.postgresql.org, 
	Aniket Jha <aniketkumarj@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BTgmobMUVTj%3D5csi-vT%3Dw731H5PBj%2B0SpCS_AndZEv_eum-jA%40mail.gmail.com>
Precedence: bulk

On Fri, Feb 27, 2026 at 4:28=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I wrote:
> > So I end with the attached draft patch.
>
> Sigh, this time with it really attached.

I suggest that having both a variable called dump_grantor and one
called dump_grantors is a little bit subtle, but other than that this
looks good on a quick read-through.

Regarding this point:

> I'm inclined to
> think that is an overreaction to the possible unreliability of the
> data (and from your comment upthread you might agree).

I think this is my code, so I certainly believed I had the right idea
at the time, but we could revisit that. One thing to keep in mind is
that in v15-, regardless of the notional grantor, in effect all grants
are independent of the existence of any other user. In v16+, they form
a tree structure, with grants depending on their grantors. So, when
upgrading from v15- to v16+, we have to end up with a valid tree
structure, but there's absolutely no reason to think that we already
have one. If we don't, it must be better to discard the grantor than
to fail the dump-and-restore altogether. Note that it's perfectly
possible not to have a tree structure, e.g. because we have circular
grants, even if all the roles involved still exist and have existed
continuously. I believe my thought process at the time was there
wasn't really any reason to imagine that whatever we were upgrading
from v15- was intended as a tree structure, and therefore it made
logical sense to treat it as though all of those grants directly
emanated from the superuser, i.e. the tree is just 1 level deep. Now,
that does mean losing the grantor information, but we're arguably
preserving the semantics, since any it reproduces the v15- state where
any of those roles are free to be dropped (assuming no other
blockers). Anyway, again, we can rethink that, but if we do it without
repairing any structural "tree defects," we'll end up with a dump that
we can't reload on v16+.

--=20
Robert Haas
EDB: http://www.enterprisedb.com