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

2 files changed, 90 insertions, 41 deletions

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 83e172e7283..affe73a7c36 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -4711,8 +4711,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)
 	{
@@ -4746,12 +4749,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 "
@@ -4785,11 +4797,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 "
@@ -10966,7 +10991,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);
 	}
 
 	/*
@@ -11075,13 +11101,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 f8955228cf7..59191ccecd3 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -1108,6 +1108,33 @@ my %tests = (
 			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' => {
 		all_runs => 1,
 		regexp => qr/^
@@ -1856,38 +1883,32 @@ my %tests = (
 			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_blobs                => 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_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 LANGUAGE pltestlang' => {
 		all_runs => 1,