From 74bdd8fb687b6c182d6ce3fcc8b6bba01e7bbbcc Mon Sep 17 00:00:00 2001 From: Stephen Frost Date: Fri, 9 Dec 2016 15:28:03 -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 | 60 ++++++++++++++++++++++++++++++++++++++++++----- 1 file changed, 54 insertions(+), 6 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 7949aad..12eb018 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -96,6 +96,12 @@ 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; +/* + * 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 */ static int strict_names = 0; @@ -233,6 +239,7 @@ static char *convertRegProcReference(Archive *fout, const char *proc); static char *convertOperatorReference(Archive *fout, const char *opr); static char *convertTSFunction(Archive *fout, Oid funcOid); +static Oid findLastBuiltinOid_V71(Archive *fout, const char *); static void selectSourceSchema(Archive *fout, const char *schemaName); static char *getFormattedTypeName(Archive *fout, Oid oid, OidOptions opts); static void getBlobs(Archive *fout); @@ -684,6 +691,20 @@ 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 (prior to 8.1) + * + * With 8.1 and above, we can just use FirstNormalObjectId - 1. + */ + if (fout->remoteVersion < 80100) + g_last_builtin_oid = findLastBuiltinOid_V71(fout, + PQdb(GetConnection(fout))); + 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) { @@ -1494,7 +1515,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 ? @@ -1526,7 +1547,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 @@ -1552,7 +1573,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 ? @@ -1577,7 +1598,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 = @@ -8820,8 +8841,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); @@ -15325,6 +15346,33 @@ dumpTableConstraintComment(Archive *fout, ConstraintInfo *coninfo) } /* + * findLastBuiltinOid_V71 - + * + * find the last built in oid + * + * For 7.1 through 8.0, we do this by retrieving datlastsysoid from the + * pg_database entry for the current database. + */ +static Oid +findLastBuiltinOid_V71(Archive *fout, const char *dbname) +{ + PGresult *res; + Oid last_oid; + PQExpBuffer query = createPQExpBuffer(); + + resetPQExpBuffer(query); + appendPQExpBufferStr(query, "SELECT datlastsysoid from pg_database where datname = "); + appendStringLiteralAH(query, dbname, fout); + + res = ExecuteSqlQueryForSingleRow(fout, query->data); + last_oid = atooid(PQgetvalue(res, 0, PQfnumber(res, "datlastsysoid"))); + PQclear(res); + destroyPQExpBuffer(query); + + return last_oid; +} + +/* * dumpSequence * write the declaration (not data) of one user-defined sequence */ -- 2.7.4 From 0486844d8879e13a703bb59c1c6219dbfdd51c14 Mon Sep 17 00:00:00 2001 From: Stephen Frost Date: Wed, 7 Dec 2016 14:59:44 -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 | 85 +++++++++++++++++++++++++--------------- 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 12eb018..28e3375 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 f895522..59191cc 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, -- 2.7.4