From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nikhil Shetty <nikhil.dba04@gmail.com>
cc: Laurenz Albe <laurenz.albe@cybertec.at>,
        Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: pg_upgrade failure due to dependencies
In-reply-to: 
 <CAFpL5VwgA2ae3c3pvTy=-PKfpa-BxmbrmF+ryux2HdJkhRPxOA@mail.gmail.com>
References: 
 <CAFpL5Vw+EK4UN6mHXRqZU+WLyFiSLqPiDAZnA3VX65tNFpyPyA@mail.gmail.com>
 <396f2fb636248f329c152c6eca189f4348fec726.camel@cybertec.at>
 <3165866.1751383802@sss.pgh.pa.us>
 <CAFpL5VwgA2ae3c3pvTy=-PKfpa-BxmbrmF+ryux2HdJkhRPxOA@mail.gmail.com>
Comments: In-reply-to Nikhil Shetty <nikhil.dba04@gmail.com>
	message dated "Tue, 01 Jul 2025 21:49:20 +0530"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3248809.1751389646.1@sss.pgh.pa.us>
Date: Tue, 01 Jul 2025 13:07:26 -0400
Message-ID: <3248810.1751389646@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3248810.1751389646%40sss.pgh.pa.us>
Precedence: bulk

Nikhil Shetty <nikhil.dba04@gmail.com> writes:
> Yes both the extension and function are created by the extension but when
> restoring, extension and associated functions or tables are created
> separately and in different order .

Ah, right, I'd momentarily forgotten that binary-upgrade mode dumps
the extensions' contents as separate objects.

I think that switching the function(s) to new-style syntax, as already
mentioned, might get you pretty far.  It'd be enough for pg_dump to get
the function-vs-spatial_ref_sys-existence ordering right at least.
What it will not do is ensure that the *contents* of spatial_ref_sys
are restored early.  But I'm not sure that that's fatal in a
binary-upgrade situation, because we're not dumping/restoring data:
the tables' disk files are just moved verbatim.  There might still be
some edge cases that fail, but I think basic scenarios ought to work.

			regards, tom lane