Tom, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost <sfr...@snowman.net> writes: > > That's a good point, we might be doing things wrong in other places in > > the code by using FirstNormalObjectId on pre-8.1 servers. > > > What I suggest then is an independent patch which uses a different > > variable than FirstNormalObjectId and sets it correctly based on the > > version of database that we're connecting to, > > +1
Alright, here we go- patches for every currently supported branch, each tested from that version back to 7.1, back to 7.3 with a user-defined CAST, and back to 9.5 with a user-defined TRANSFORM. Also includes regression tests for the TAP test structure in master and 9.6. > pg_dump never intended to support pre-7.0 servers. I do have 7.0-7.3 > servers in captivity and can do testing if you like. You are certainly welcome to test and make sure I didn't break anything for 7.0 servers, but I don't *think* I changed any code paths which have differences between 7.0 and 7.1 (which I did test against). That said, I'm honestly not entirely sure what getCasts() is doing querying out the "casts" from a 7.1 or 7.0 database. The query does work, but none of the "casts" returned have an OID beyond datlastsysoid and I'm not really sure how to create one or if creating one is really a supported operation. If someone was able to create something that getCasts() would try to dump out, and it used only built-in functions, they will at least get an error now letting them know that it failed, instead of having that "cast" silently ignored. Trying to adjust the query in getFuncs() to account for that case makes me concerned that we'd actually break more than fix things, and I really don't like the idea of trying to blindly fix it for 7.0. Thanks! Stephen
From 74bdd8fb687b6c182d6ce3fcc8b6bba01e7bbbcc Mon Sep 17 00:00:00 2001 From: Stephen Frost <sfr...@snowman.net> 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 <sfr...@snowman.net> 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
From 3ee8993c8073d2af288f5f0385c27a3de79c28b5 Mon Sep 17 00:00:00 2001 From: Stephen Frost <sfr...@snowman.net> 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 <sfr...@snowman.net> 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
From 06a0989d754b8c6629ff54b7667f2c917fc84665 Mon Sep 17 00:00:00 2001 From: Stephen Frost <sfr...@snowman.net> Date: Fri, 9 Dec 2016 16:48:53 -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 | 36 +++++++++++++++++++++++------------- 1 file changed, 23 insertions(+), 13 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 0353140..5b9f575 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -94,7 +94,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 */ /* @@ -673,17 +676,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) @@ -1440,7 +1450,7 @@ selectDumpableCast(CastInfo *cast, DumpOptions *dopt) if (checkExtensionMembership(&cast->dobj, dopt)) return; /* extension membership overrides all else */ - if (cast->dobj.catId.oid < (Oid) FirstNormalObjectId) + if (cast->dobj.catId.oid <= (Oid) g_last_builtin_oid) cast->dobj.dump = false; else cast->dobj.dump = dopt->include_everything; @@ -1460,7 +1470,7 @@ selectDumpableProcLang(ProcLangInfo *plang, DumpOptions *dopt) if (checkExtensionMembership(&plang->dobj, dopt)) return; /* extension membership overrides all else */ - if (plang->dobj.catId.oid < (Oid) FirstNormalObjectId) + if (plang->dobj.catId.oid <= (Oid) g_last_builtin_oid) plang->dobj.dump = false; else plang->dobj.dump = dopt->include_everything; @@ -1479,7 +1489,7 @@ selectDumpableProcLang(ProcLangInfo *plang, DumpOptions *dopt) static void selectDumpableExtension(ExtensionInfo *extinfo, DumpOptions *dopt) { - 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 = false; else extinfo->dobj.dump = dopt->include_everything; @@ -8630,8 +8640,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); @@ -14946,10 +14956,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 @@ -14971,7 +14981,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 ca857ea49aa16767c71fb61d8c44210cfde2d3d6 Mon Sep 17 00:00:00 2001 From: Stephen Frost <sfr...@snowman.net> Date: Mon, 12 Dec 2016 10:39:19 -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 | 30 ++++++++++++++++++++++++------ 1 file changed, 24 insertions(+), 6 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 5b9f575..cd67082 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -4506,7 +4506,9 @@ 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. + * 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. */ if (fout->remoteVersion >= 70300) @@ -4524,11 +4526,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 " @@ -10835,7 +10850,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); } /* @@ -10939,13 +10955,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) */ -- 2.7.4
From c16a3e32b8abed30c9b75562c0cb4a27fb0d85a8 Mon Sep 17 00:00:00 2001 From: Stephen Frost <sfr...@snowman.net> Date: Mon, 12 Dec 2016 11:15:05 -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 | 36 +++++++++++++++++++++++------------- 1 file changed, 23 insertions(+), 13 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 3592796..3e7b689 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -95,7 +95,10 @@ static const char *lockWaitTimeout; /* 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 */ /* @@ -705,17 +708,24 @@ main(int argc, char **argv) "Run with --no-synchronized-snapshots instead if you do not need\n" "synchronized snapshots.\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) @@ -1429,7 +1439,7 @@ selectDumpableCast(CastInfo *cast) if (checkExtensionMembership(&cast->dobj)) return; /* extension membership overrides all else */ - if (cast->dobj.catId.oid < (Oid) FirstNormalObjectId) + if (cast->dobj.catId.oid <= (Oid) g_last_builtin_oid) cast->dobj.dump = false; else cast->dobj.dump = include_everything; @@ -1449,7 +1459,7 @@ selectDumpableProcLang(ProcLangInfo *plang) if (checkExtensionMembership(&plang->dobj)) return; /* extension membership overrides all else */ - if (plang->dobj.catId.oid < (Oid) FirstNormalObjectId) + if (plang->dobj.catId.oid <= (Oid) g_last_builtin_oid) plang->dobj.dump = false; else plang->dobj.dump = include_everything; @@ -1468,7 +1478,7 @@ selectDumpableProcLang(ProcLangInfo *plang) static void selectDumpableExtension(ExtensionInfo *extinfo) { - if (binary_upgrade && extinfo->dobj.catId.oid < (Oid) FirstNormalObjectId) + if (binary_upgrade && extinfo->dobj.catId.oid <= (Oid) g_last_builtin_oid) extinfo->dobj.dump = false; else extinfo->dobj.dump = include_everything; @@ -8159,8 +8169,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); @@ -14257,10 +14267,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 @@ -14282,7 +14292,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 4c05cedac564463d3f870337802bfccd6b5ca125 Mon Sep 17 00:00:00 2001 From: Stephen Frost <sfr...@snowman.net> Date: Mon, 12 Dec 2016 11:28:50 -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 | 15 +++++++++++---- 1 file changed, 11 insertions(+), 4 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 3e7b689..7fa2d5e 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -4243,7 +4243,9 @@ 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. + * that mode. Also, if they are used by casts then we need to gather the + * information about them, though they won't be dumped if they are + * built-in. */ if (fout->remoteVersion >= 70300) @@ -4261,11 +4263,15 @@ 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 (binary_upgrade && fout->remoteVersion >= 90100) appendPQExpBufferStr(query, "\n OR EXISTS(SELECT 1 FROM pg_depend WHERE " @@ -10296,7 +10302,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); } /* -- 2.7.4
From 5ca459947757e922ee6097a4b79ed7b474bb8961 Mon Sep 17 00:00:00 2001 From: Stephen Frost <sfr...@snowman.net> Date: Mon, 12 Dec 2016 12:04:16 -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 | 36 +++++++++++++++++++++++------------- 1 file changed, 23 insertions(+), 13 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 68cef24..2f1ef6c 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -99,7 +99,10 @@ const char *lockWaitTimeout; /* 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 */ /* @@ -697,17 +700,24 @@ main(int argc, char **argv) "Run with --no-synchronized-snapshots instead if you do not need\n" "synchronized snapshots.\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) @@ -1419,7 +1429,7 @@ selectDumpableCast(CastInfo *cast) if (checkExtensionMembership(&cast->dobj)) return; /* extension membership overrides all else */ - if (cast->dobj.catId.oid < (Oid) FirstNormalObjectId) + if (cast->dobj.catId.oid <= (Oid) g_last_builtin_oid) cast->dobj.dump = false; else cast->dobj.dump = include_everything; @@ -1439,7 +1449,7 @@ selectDumpableProcLang(ProcLangInfo *plang) if (checkExtensionMembership(&plang->dobj)) return; /* extension membership overrides all else */ - if (plang->dobj.catId.oid < (Oid) FirstNormalObjectId) + if (plang->dobj.catId.oid <= (Oid) g_last_builtin_oid) plang->dobj.dump = false; else plang->dobj.dump = include_everything; @@ -1458,7 +1468,7 @@ selectDumpableProcLang(ProcLangInfo *plang) static void selectDumpableExtension(ExtensionInfo *extinfo) { - if (binary_upgrade && extinfo->dobj.catId.oid < (Oid) FirstNormalObjectId) + if (binary_upgrade && extinfo->dobj.catId.oid <= (Oid) g_last_builtin_oid) extinfo->dobj.dump = false; else extinfo->dobj.dump = include_everything; @@ -8032,8 +8042,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); @@ -13920,10 +13930,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 @@ -13945,7 +13955,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 5004fee29c47341d693789aad57d428316c08cc9 Mon Sep 17 00:00:00 2001 From: Stephen Frost <sfr...@snowman.net> Date: Mon, 12 Dec 2016 11:28:50 -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 | 13 ++++++++++--- 1 file changed, 10 insertions(+), 3 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 2f1ef6c..466a373 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -4204,7 +4204,9 @@ 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. + * that mode. Also, if they are used by casts then we need to gather the + * information about them, though they won't be dumped if they are + * built-in. */ if (fout->remoteVersion >= 70300) @@ -4226,7 +4228,11 @@ 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'::oid" + "\n AND p.oid = pg_cast.castfunc)", + g_last_builtin_oid); if (binary_upgrade && fout->remoteVersion >= 90100) appendPQExpBuffer(query, "\n OR EXISTS(SELECT 1 FROM pg_depend WHERE " @@ -10170,7 +10176,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); } /* -- 2.7.4
From b272e0dc45582d203fa00f36ddf63765858823d7 Mon Sep 17 00:00:00 2001 From: Stephen Frost <sfr...@snowman.net> Date: Mon, 12 Dec 2016 12:32:25 -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 | 36 +++++++++++++++++++++++------------- 1 file changed, 23 insertions(+), 13 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index ec690b0..36c6236 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -97,7 +97,10 @@ const char *lockWaitTimeout; /* 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 */ /* @@ -657,17 +660,24 @@ main(int argc, char **argv) else username_subquery = "SELECT usename FROM pg_user WHERE usesysid ="; - /* 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) @@ -1275,7 +1285,7 @@ selectDumpableCast(CastInfo *cast) if (checkExtensionMembership(&cast->dobj)) return; /* extension membership overrides all else */ - if (cast->dobj.catId.oid < (Oid) FirstNormalObjectId) + if (cast->dobj.catId.oid <= (Oid) g_last_builtin_oid) cast->dobj.dump = false; else cast->dobj.dump = include_everything; @@ -1295,7 +1305,7 @@ selectDumpableProcLang(ProcLangInfo *plang) if (checkExtensionMembership(&plang->dobj)) return; /* extension membership overrides all else */ - if (plang->dobj.catId.oid < (Oid) FirstNormalObjectId) + if (plang->dobj.catId.oid <= (Oid) g_last_builtin_oid) plang->dobj.dump = false; else plang->dobj.dump = include_everything; @@ -1314,7 +1324,7 @@ selectDumpableProcLang(ProcLangInfo *plang) static void selectDumpableExtension(ExtensionInfo *extinfo) { - if (binary_upgrade && extinfo->dobj.catId.oid < (Oid) FirstNormalObjectId) + if (binary_upgrade && extinfo->dobj.catId.oid <= (Oid) g_last_builtin_oid) extinfo->dobj.dump = false; else extinfo->dobj.dump = include_everything; @@ -7513,8 +7523,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); @@ -13330,10 +13340,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 @@ -13355,7 +13365,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 a5a57c57a23806dd5c8c30a4ecc7265207000e57 Mon Sep 17 00:00:00 2001 From: Stephen Frost <sfr...@snowman.net> Date: Mon, 12 Dec 2016 12:34:02 -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 | 13 ++++++++++--- 1 file changed, 10 insertions(+), 3 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 36c6236..68468e3 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -3856,7 +3856,9 @@ 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. + * that mode. Also, if they are used by casts then we need to gather the + * information about them, though they won't be dumped if they are + * built-in. */ if (fout->remoteVersion >= 70300) @@ -3878,7 +3880,11 @@ 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'::oid " + "\n AND p.oid = pg_cast.castfunc)", + g_last_builtin_oid); if (binary_upgrade && fout->remoteVersion >= 90100) appendPQExpBuffer(query, "\n OR EXISTS(SELECT 1 FROM pg_depend WHERE " @@ -9651,7 +9657,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); } /* -- 2.7.4
signature.asc
Description: Digital signature
