From 3ee8993c8073d2af288f5f0385c27a3de79c28b5 Mon Sep 17 00:00:00 2001 From: Stephen Frost Date: Fri, 9 Dec 2016 15:58:44 -0500 Subject: [PATCH 1/2] For 8.0 servers, get last built-in oid from pg_database We didn't start ensuring that all built-in objects had OIDs less than 16384 until 8.1, so for 8.0 servers we still need to query the value out of pg_database. We need this, in particular, to distinguish which casts were built-in and which were user-defined. For HEAD, we only worry about going back to 8.0, for the back-branches, we also ensure that 7.0-7.4 work. Discussion: https://www.postgresql.org/message-id/flat/20160504183952.GE10850%40tamriel.snowman.net --- src/bin/pg_dump/pg_dump.c | 38 ++++++++++++++++++++++++-------------- 1 file changed, 24 insertions(+), 14 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index fde7f59..bd2d977 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -96,7 +96,10 @@ bool g_verbose; /* User wants verbose narration of our /* subquery used to convert user ID (eg, datdba) to user name */ static const char *username_subquery; -/* obsolete as of 7.3: */ +/* + * For 8.0 and earlier servers, pulled from pg_database, for 8.1+ we use + * FirstNormalObjectId - 1. + */ static Oid g_last_builtin_oid; /* value of the last builtin oid */ /* The specified names/patterns should to match at least one entity */ @@ -683,17 +686,24 @@ main(int argc, char **argv) exit_horribly(NULL, "Exported snapshots are not supported by this server version.\n"); - /* Find the last built-in OID, if needed */ - if (fout->remoteVersion < 70300) + /* + * Find the last built-in OID, if needed (prior to 8.1) + * + * With 8.1 and above, we can just use FirstNormalObjectId - 1. + */ + if (fout->remoteVersion < 80100) { if (fout->remoteVersion >= 70100) g_last_builtin_oid = findLastBuiltinOid_V71(fout, PQdb(GetConnection(fout))); else g_last_builtin_oid = findLastBuiltinOid_V70(fout); - if (g_verbose) - write_msg(NULL, "last built-in OID is %u\n", g_last_builtin_oid); } + else + g_last_builtin_oid = FirstNormalObjectId - 1; + + if (g_verbose) + write_msg(NULL, "last built-in OID is %u\n", g_last_builtin_oid); /* Expand schema selection patterns into OID lists */ if (schema_include_patterns.head != NULL) @@ -1507,7 +1517,7 @@ selectDumpableCast(CastInfo *cast, Archive *fout) * This would be DUMP_COMPONENT_ACL for from-initdb casts, but they do not * support ACLs currently. */ - if (cast->dobj.catId.oid < (Oid) FirstNormalObjectId) + if (cast->dobj.catId.oid <= (Oid) g_last_builtin_oid) cast->dobj.dump = DUMP_COMPONENT_NONE; else cast->dobj.dump = fout->dopt->include_everything ? @@ -1539,7 +1549,7 @@ selectDumpableProcLang(ProcLangInfo *plang, Archive *fout) plang->dobj.dump = DUMP_COMPONENT_NONE; else { - if (plang->dobj.catId.oid < (Oid) FirstNormalObjectId) + if (plang->dobj.catId.oid <= (Oid) g_last_builtin_oid) plang->dobj.dump = fout->remoteVersion < 90600 ? DUMP_COMPONENT_NONE : DUMP_COMPONENT_ACL; else @@ -1565,7 +1575,7 @@ selectDumpableAccessMethod(AccessMethodInfo *method, Archive *fout) * This would be DUMP_COMPONENT_ACL for from-initdb access methods, but * they do not support ACLs currently. */ - if (method->dobj.catId.oid < (Oid) FirstNormalObjectId) + if (method->dobj.catId.oid <= (Oid) g_last_builtin_oid) method->dobj.dump = DUMP_COMPONENT_NONE; else method->dobj.dump = fout->dopt->include_everything ? @@ -1590,7 +1600,7 @@ selectDumpableExtension(ExtensionInfo *extinfo, DumpOptions *dopt) * change permissions on those objects, if they wish to, and have those * changes preserved. */ - if (dopt->binary_upgrade && extinfo->dobj.catId.oid < (Oid) FirstNormalObjectId) + if (dopt->binary_upgrade && extinfo->dobj.catId.oid <= (Oid) g_last_builtin_oid) extinfo->dobj.dump = extinfo->dobj.dump_contains = DUMP_COMPONENT_ACL; else extinfo->dobj.dump = extinfo->dobj.dump_contains = @@ -9571,8 +9581,8 @@ dumpExtension(Archive *fout, ExtensionInfo *extinfo) /* * We unconditionally create the extension, so we must drop it if it * exists. This could happen if the user deleted 'plpgsql' and then - * readded it, causing its oid to be greater than FirstNormalObjectId. - * The FirstNormalObjectId test was kept to avoid repeatedly dropping + * readded it, causing its oid to be greater than g_last_builtin_oid. + * The g_last_builtin_oid test was kept to avoid repeatedly dropping * and recreating extensions like 'plpgsql'. */ appendPQExpBuffer(q, "DROP EXTENSION IF EXISTS %s;\n", qextname); @@ -16284,10 +16294,10 @@ dumpTableConstraintComment(Archive *fout, ConstraintInfo *coninfo) } /* - * findLastBuiltInOid - + * findLastBuiltinOid - * find the last built in oid * - * For 7.1 and 7.2, we do this by retrieving datlastsysoid from the + * For 7.1 through 8.0, we do this by retrieving datlastsysoid from the * pg_database entry for the current database */ static Oid @@ -16309,7 +16319,7 @@ findLastBuiltinOid_V71(Archive *fout, const char *dbname) } /* - * findLastBuiltInOid - + * findLastBuiltinOid - * find the last built in oid * * For 7.0, we do this by assuming that the last thing that initdb does is to -- 2.7.4 From 1f642813d3b26f8c8be677288ce063c470194f3f Mon Sep 17 00:00:00 2001 From: Stephen Frost Date: Fri, 9 Dec 2016 16:05:39 -0500 Subject: [PATCH 2/2] 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 bd2d977..6b2a6c9 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 37cbdcd..bb2ea34 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.7.4