From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com>
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: pg_restore depending on user functions
In-reply-to: 
 <CAPL5KHrjG4TEWupR0+TBAw4dYJa+qdvfCuGdcoD0S0W38gapXw@mail.gmail.com>
References: 
 <CAPL5KHoGa3uvyKp6z6m48LwCnTsK+LRQ_mcA4uKGfqAVSEjV_A@mail.gmail.com>
 <2011525.1636911069@sss.pgh.pa.us>
 <CAPL5KHrkVKVp22XE4S0ByA94-3OvH+9HC19WFFMePK2_c1VF0g@mail.gmail.com>
 <2016240.1636915579@sss.pgh.pa.us>
 <CAPL5KHoiNP8Z4Jq=EP0WBKr+07bfwk4sdH9k-+RQSJvYFVsC1A@mail.gmail.com>
 <2048100.1636934771@sss.pgh.pa.us>
 <CAPL5KHpN=pz97X+7vThsSn1V0ag8dSBrhMxjpWr1Z=wwU2BPow@mail.gmail.com>
 <2124696.1636991365@sss.pgh.pa.us>
 <CAPL5KHo46S6kRRiaTfNdm63eR3u72gcnS9zMa2DYOjqZyPrFsw@mail.gmail.com>
 <2157331.1637014065@sss.pgh.pa.us>
 <CAPL5KHpT4rZyt0U4tVCbMOxhsaTbYNY9b5XGr-cQnHGz1mPOzA@mail.gmail.com>
 <2230909.1637080154@sss.pgh.pa.us>
 <CAPL5KHouYG+Bi4DWYdzixUwJuL6HK9uruW5JKqU+1CJfB39G0Q@mail.gmail.com>
 <2254203.1637093046@sss.pgh.pa.us>
 <CAPL5KHpnvkoch1ViCKWmPoNcSQM3j671ohGwtwRWOrOWwGMVPA@mail.gmail.com>
 <2361578.1637113664@sss.pgh.pa.us>
 <CAPL5KHp-R6JV_NRLbyxLQe_7sq9oyS+vcEP83bRvSjJLqfo2Uw@mail.gmail.com>
 <2915502.1637249556@sss.pgh.pa.us> <CAPL5KHrjG4TEWupR0+TBAw4dYJa+qdvfCuGdcoD!
 0S0W38gapXw@mail.gmail.com>
Comments: In-reply-to =?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?=
 <firstdismay@gmail.com>
	message dated "Fri, 19 Nov 2021 09:45:45 +0500"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <3317817.1637439882.0@sss.pgh.pa.us>
Date: Sat, 20 Nov 2021 15:25:47 -0500
Message-ID: <3317884.1637439947@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3317884.1637439947%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3317817.1637439882.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable

=3D?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?=3D <firstdismay@gmail.co=
m> writes:
> I hope this helps you.

Okay, that's better.

The problem seems to be that the view using the cast is itself depended
on (indirectly through another view) by another function a_ext_by_id(),
which is using the intermediate view's rowtype as its output type.
So that causes the dependency sort to hoist those two views above the
function a_ext_by_id(), and now they're ahead of the cast, which is
just left at its initial priority-driven location.

I think we can fix this by adjusting the sort priority order as per
the attached patch.  This fixes this toy test case anyway.  Can you
check to see if it fixes your real database?

(Note that as given, the patch will only apply to v14 not earlier
branches.)

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/x-diff; name="change-dump-priority-of-casts.patch";
	charset="us-ascii"
Content-ID: <3317817.1637439882.2@sss.pgh.pa.us>
Content-Description: change-dump-priority-of-casts.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort=
.c
index 410d1790ee..5399ec1cd9 100644
--- a/src/bin/pg_dump/pg_dump_sort.c
+++ b/src/bin/pg_dump/pg_dump_sort.c
@@ -34,6 +34,15 @@
  * restore state).  If you think to change this, see also the RestorePass
  * mechanism in pg_backup_archiver.c.
  *
+ * On the other hand, casts are intentionally sorted earlier than you mig=
ht
+ * expect; logically they should come after functions, since they usually
+ * depend on those.  This works around the backend's habit of recording
+ * views that use casts as dependent on the cast's underlying function.
+ * We initially sort casts first, and then any functions used by casts
+ * will be hoisted above the casts, and in turn views that those function=
s
+ * depend on will be hoisted above the functions.  But views not used tha=
t
+ * way won't be hoisted.
+ *
  * NOTE: object-type priorities must match the section assignments made i=
n
  * pg_dump.c; that is, PRE_DATA objects must sort before DO_PRE_DATA_BOUN=
DARY,
  * POST_DATA objects must sort after DO_POST_DATA_BOUNDARY, and DATA obje=
cts
@@ -49,12 +58,12 @@ enum dbObjectTypePriorities
 	PRIO_TRANSFORM,
 	PRIO_EXTENSION,
 	PRIO_TYPE,					/* used for DO_TYPE and DO_SHELL_TYPE */
+	PRIO_CAST,
 	PRIO_FUNC,
 	PRIO_AGG,
 	PRIO_ACCESS_METHOD,
 	PRIO_OPERATOR,
 	PRIO_OPFAMILY,				/* used for DO_OPFAMILY and DO_OPCLASS */
-	PRIO_CAST,
 	PRIO_CONVERSION,
 	PRIO_TSPARSER,
 	PRIO_TSTEMPLATE,

------- =_aaaaaaaaaa0--