From 542975a147525caa659ac08220b81c32a60f0a5c Mon Sep 17 00:00:00 2001 From: Stephen Frost Date: Wed, 21 Dec 2016 13:47:13 -0500 Subject: [PATCH] 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 --- src/bin/pg_dump/pg_dump.c | 46 ++++++++++++++---- src/bin/pg_dump/t/002_pg_dump.pl | 82 ++++++++++++++++++++------------ 2 files changed, 88 insertions(+), 40 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index bd2d977bf2..6b2a6c939b 100644 --- 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 37cbdcdaa5..bb2ea34ccb 100644 --- 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 -- 2.40.0