Fix dumping of casts and transforms using built-in functions

In pg_dump.c dumpCast() and dumpTransform(), we would happily ignore the
cast or transform if it happened to use a built-in function because we
weren't including the information about built-in functions when querying
pg_proc from getFuncs().

Modify the query in getFuncs() to also gather information about
functions which are used by user-defined casts and transforms (where
"user-defined" means "has an OID >= FirstNormalObjectId").  This also
adds to the TAP regression tests for 9.6 and master to cover these
types of objects.

Back-patch all the way for casts, back to 9.5 for transforms.

Discussion: https://www.postgresql.org/message-id/flat/20160504183952.GE10850%40tamriel.snowman.net

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index bd2d977bf2035e34e3c500345b5971611c9871fd..6b2a6c939bbd7af5cf4ed8405e4792ee4566166c 100644 (file)
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -4978,8 +4978,11 @@ getFuncs(Archive *fout, int *numFuncs)
     * 3. Otherwise, we normally exclude functions in pg_catalog.  However, if
     * they're members of extensions and we are in binary-upgrade mode then
     * include them, since we want to dump extension members individually in
-    * that mode.  Also, in 9.6 and up, include functions in pg_catalog if
-    * they have an ACL different from what's shown in pg_init_privs.
+    * that mode.  Also, if they are used by casts or transforms then we need
+    * to gather the information about them, though they won't be dumped if
+    * they are built-in.  Also, in 9.6 and up, include functions in
+    * pg_catalog if they have an ACL different from what's shown in
+    * pg_init_privs.
     */
    if (fout->remoteVersion >= 90600)
    {
@@ -5013,12 +5016,21 @@ getFuncs(Archive *fout, int *numFuncs)
                          "\n  AND ("
                          "\n  pronamespace != "
                          "(SELECT oid FROM pg_namespace "
-                         "WHERE nspname = 'pg_catalog')",
+                         "WHERE nspname = 'pg_catalog')"
+                         "\n  OR EXISTS (SELECT 1 FROM pg_cast"
+                         "\n  WHERE pg_cast.oid > %u "
+                         "\n  AND p.oid = pg_cast.castfunc)"
+                         "\n  OR EXISTS (SELECT 1 FROM pg_transform"
+                         "\n  WHERE pg_transform.oid > %u AND "
+                         "\n  (p.oid = pg_transform.trffromsql"
+                         "\n  OR p.oid = pg_transform.trftosql))",
                          acl_subquery->data,
                          racl_subquery->data,
                          initacl_subquery->data,
                          initracl_subquery->data,
-                         username_subquery);
+                         username_subquery,
+                         g_last_builtin_oid,
+                         g_last_builtin_oid);
        if (dopt->binary_upgrade)
            appendPQExpBufferStr(query,
                               "\n  OR EXISTS(SELECT 1 FROM pg_depend WHERE "
@@ -5052,11 +5064,24 @@ getFuncs(Archive *fout, int *numFuncs)
                               "\n  AND NOT EXISTS (SELECT 1 FROM pg_depend "
                                 "WHERE classid = 'pg_proc'::regclass AND "
                                 "objid = p.oid AND deptype = 'i')");
-       appendPQExpBufferStr(query,
+       appendPQExpBuffer(query,
                             "\n  AND ("
                             "\n  pronamespace != "
                             "(SELECT oid FROM pg_namespace "
-                            "WHERE nspname = 'pg_catalog')");
+                            "WHERE nspname = 'pg_catalog')"
+                            "\n  OR EXISTS (SELECT 1 FROM pg_cast"
+                            "\n  WHERE pg_cast.oid > '%u'::oid"
+                            "\n  AND p.oid = pg_cast.castfunc)",
+                            g_last_builtin_oid);
+
+       if (fout->remoteVersion >= 90500)
+           appendPQExpBuffer(query,
+                                "\n  OR EXISTS (SELECT 1 FROM pg_transform"
+                                "\n  WHERE pg_transform.oid > '%u'::oid"
+                                "\n  AND (p.oid = pg_transform.trffromsql"
+                                "\n  OR p.oid = pg_transform.trftosql))",
+                                g_last_builtin_oid);
+
        if (dopt->binary_upgrade && fout->remoteVersion >= 90100)
            appendPQExpBufferStr(query,
                               "\n  OR EXISTS(SELECT 1 FROM pg_depend WHERE "
@@ -11871,7 +11896,8 @@ dumpCast(Archive *fout, CastInfo *cast)
    {
        funcInfo = findFuncByOid(cast->castfunc);
        if (funcInfo == NULL)
-           return;
+           exit_horribly(NULL, "unable to find function definition for OID %u",
+                         cast->castfunc);
    }
 
    /*
@@ -11980,13 +12006,15 @@ dumpTransform(Archive *fout, TransformInfo *transform)
    {
        fromsqlFuncInfo = findFuncByOid(transform->trffromsql);
        if (fromsqlFuncInfo == NULL)
-           return;
+           exit_horribly(NULL, "unable to find function definition for OID %u",
+                         transform->trffromsql);
    }
    if (OidIsValid(transform->trftosql))
    {
        tosqlFuncInfo = findFuncByOid(transform->trftosql);
        if (tosqlFuncInfo == NULL)
-           return;
+           exit_horribly(NULL, "unable to find function definition for OID %u",
+                         transform->trftosql);
    }
 
    /* Make sure we are in proper schema (needed for getFormattedTypeName) */

diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 37cbdcdaa5990e374baea8fee7902eab8d0da95e..bb2ea34ccbe36969cfe1c5de16a2d5c5bd3cbae4 100644 (file)
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -913,6 +913,32 @@ my %tests = (
            section_pre_data         => 1,
            section_post_data        => 1,
            test_schema_plus_blobs   => 1, }, },
+   'CREATE CAST FOR timestamptz' => {
+       create_order => 51,
+       create_sql => 'CREATE CAST (timestamptz AS interval) WITH FUNCTION age(timestamptz) AS ASSIGNMENT;',
+       regexp => qr/CREATE CAST \(timestamp with time zone AS interval\) WITH FUNCTION pg_catalog\.age\(timestamp with time zone\) AS ASSIGNMENT;/m,
+       like => {
+           binary_upgrade => 1,
+           clean => 1,
+           clean_if_exists => 1,
+           createdb => 1,
+           defaults => 1,
+           exclude_dump_test_schema => 1,
+           exclude_test_table => 1,
+           exclude_test_table_data => 1,
+           no_blobs                => 1,
+           no_privs => 1,
+           no_owner => 1,
+           pg_dumpall_dbprivs       => 1,
+           schema_only => 1,
+           section_pre_data => 1,
+       },
+       unlike => {
+           only_dump_test_schema => 1,
+           only_dump_test_table => 1,
+           pg_dumpall_globals => 1,
+           section_post_data => 1,
+           test_schema_plus_blobs => 1, }, },
    'CREATE DATABASE postgres' => {
        regexp => qr/^
            \QCREATE DATABASE postgres WITH TEMPLATE = template0 \E
@@ -1515,37 +1541,31 @@ my %tests = (
            pg_dumpall_globals_clean => 1,
            section_post_data        => 1,
            test_schema_plus_blobs   => 1, }, },
-#######################################
-   # Currently broken.
-#######################################
-#
-#  'CREATE TRANSFORM FOR int' => {
-#      create_order => 34,
-#      create_sql => 'CREATE TRANSFORM FOR int LANGUAGE SQL (FROM SQL WITH FUNCTION varchar_transform(internal), TO SQL WITH FUNCTION int4recv(internal));',
-#      regexp => qr/CREATE TRANSFORM FOR int LANGUAGE SQL \(FROM SQL WITH FUNCTION varchar_transform\(internal\), TO SQL WITH FUNCTION int4recv\(internal\)\);/m,
-#      like => {
-#          binary_upgrade => 1,
-#          clean => 1,
-#          clean_if_exists => 1,
-#          createdb => 1,
-#          defaults => 1,
-#          exclude_dump_test_schema => 1,
-#          exclude_test_table => 1,
-#          exclude_test_table_data => 1,
-#          no_privs => 1,
-#          no_owner => 1,
-#          pg_dumpall_dbprivs       => 1,
-#          schema_only => 1,
-#          section_post_data => 1,
-#      },
-#      unlike => {
-#          section_pre_data => 1,
-#          only_dump_test_schema => 1,
-#          only_dump_test_table => 1,
-#          pg_dumpall_globals => 1,
-#          test_schema_plus_blobs => 1,
-#      },
-#  },
+   'CREATE TRANSFORM FOR int' => {
+       create_order => 34,
+       create_sql => 'CREATE TRANSFORM FOR int LANGUAGE SQL (FROM SQL WITH FUNCTION varchar_transform(internal), TO SQL WITH FUNCTION int4recv(internal));',
+       regexp => qr/CREATE TRANSFORM FOR integer LANGUAGE sql \(FROM SQL WITH FUNCTION pg_catalog\.varchar_transform\(internal\), TO SQL WITH FUNCTION pg_catalog\.int4recv\(internal\)\);/m,
+       like => {
+           binary_upgrade => 1,
+           clean => 1,
+           clean_if_exists => 1,
+           createdb => 1,
+           defaults => 1,
+           exclude_dump_test_schema => 1,
+           exclude_test_table => 1,
+           exclude_test_table_data => 1,
+           no_privs => 1,
+           no_owner => 1,
+           pg_dumpall_dbprivs       => 1,
+           schema_only => 1,
+           section_pre_data => 1,
+       },
+       unlike => {
+           only_dump_test_schema => 1,
+           only_dump_test_table => 1,
+           pg_dumpall_globals => 1,
+           section_post_data => 1,
+           test_schema_plus_blobs => 1, }, },
    'CREATE LANGUAGE pltestlang' => {
        create_order => 18,
        create_sql   => 'CREATE LANGUAGE pltestlang