Adjust pg_dump's priority ordering for casts.

When a stored expression depends on a user-defined cast, the backend
records the dependency as being on the cast's implementation function
--- or indeed, if there's no cast function involved but just
RelabelType or CoerceViaIO, no dependency is recorded at all.  This
is problematic for pg_dump, which is at risk of dumping things in the
wrong order leading to restore failures.  Given the lack of previous
reports, the risk isn't that high, but it can be demonstrated if the
cast is used in some view whose rowtype is then used as an input or
result type for some other function.  (That results in the view
getting hoisted into the functions portion of the dump, ahead of
the cast.)

A logically bulletproof fix for this would require including the
cast's OID in the parsed form of the expression, whence it could be
extracted by dependency.c, and then the stored dependency would force
pg_dump to do the right thing.  Such a change would be fairly invasive,
and certainly not back-patchable.  Moreover, since we'd prefer that
an expression using cast syntax be equal() to one doing the same
thing by explicit function call, the cast OID field would have to
have special ignored-by-comparisons semantics, making things messy.

So, let's instead fix this by a very simple hack in pg_dump: change
the object-type priority order so that casts are initially sorted
before functions, immediately after types.  This fixes the problem
in a fairly direct way for casts that have no implementation function.
For those that do, the implementation function will be hoisted to just
before the cast by the dependency sorting step, so that we still have
a valid dump order.  (I'm not sure that this provides a full guarantee
of no problems; but since it's been like this for many years without
any previous reports, this is probably enough to fix it in practice.)

Per report from Дмитрий Иванов.
Back-patch to all supported branches.

Discussion: https://postgr.es/m/CAPL5KHoGa3uvyKp6z6m48LwCnTsK+LRQ_mcA4uKGfqAVSEjV_A@mail.gmail.com

diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c
index 43fccfff56e4b90791516ce93068dd3154132f6f..bb3d5d9bd2aa889dc6865250f2c9d0b0b1838feb 100644 (file)
--- a/src/bin/pg_dump/pg_dump_sort.c
+++ b/src/bin/pg_dump/pg_dump_sort.c
@@ -38,6 +38,15 @@ static const char *modulename = gettext_noop("sorter");
  * 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 might
+ * 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 functions
+ * depend on will be hoisted above the functions.  But views not used that
+ * way won't be hoisted.
+ *
  * NOTE: object-type priorities must match the section assignments made in
  * pg_dump.c; that is, PRE_DATA objects must sort before DO_PRE_DATA_BOUNDARY,
  * POST_DATA objects must sort after DO_POST_DATA_BOUNDARY, and DATA objects
@@ -53,12 +62,12 @@ static const int dbObjectTypePriority[] =
    4,                          /* DO_EXTENSION */
    5,                          /* DO_TYPE */
    5,                          /* DO_SHELL_TYPE */
-   6,                          /* DO_FUNC */
-   7,                          /* DO_AGG */
-   8,                          /* DO_OPERATOR */
-   8,                          /* DO_ACCESS_METHOD */
-   9,                          /* DO_OPCLASS */
-   9,                          /* DO_OPFAMILY */
+   7,                          /* DO_FUNC */
+   8,                          /* DO_AGG */
+   9,                          /* DO_OPERATOR */
+   9,                          /* DO_ACCESS_METHOD */
+   10,                         /* DO_OPCLASS */
+   10,                         /* DO_OPFAMILY */
    3,                          /* DO_COLLATION */
    11,                         /* DO_CONVERSION */
    18,                         /* DO_TABLE */
@@ -71,7 +80,7 @@ static const int dbObjectTypePriority[] =
    27,                         /* DO_CONSTRAINT */
    33,                         /* DO_FK_CONSTRAINT */
    2,                          /* DO_PROCLANG */
-   10,                         /* DO_CAST */
+   6,                          /* DO_CAST */
    23,                         /* DO_TABLE_DATA */
    24,                         /* DO_SEQUENCE_SET */
    19,                         /* DO_DUMMY_TYPE */