Noah, all, * Stephen Frost (sfr...@snowman.net) wrote: > The test suite is now covering 57% of pg_dump.c. I was hoping to get > that number higher, but time marches on and more tests can certainly be > added later.
I've managed to get the test suite up another 10%, to 67% of pg_dump.c. Still quite a bit to do, but it turned up a bug in CREATE TRANSFORM, which lead to Peter discovering a similar issue in CREATE CAST, so I'd say it's certainly showing that it's worthwhile to have. Updated patch-set which includes the additional tests and also a patch to avoid LOCK'ing tables when we don't need to, as discussed with Tom and Robert on another thread. Thanks! Stephen
From cea33c3fdbdfa4ff27c0ff6ed7b708705dfa17fe Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfr...@snowman.net>
Date: Sun, 24 Apr 2016 23:59:23 -0400
Subject: [PATCH 1/4] Correct pg_dump WHERE clause for functions/aggregates
The query to grab the function/aggregate information is now joining
to pg_init_privs, so we can simplify (and correct) the WHERE clause
used to determine if a given function's ACL has changed from the
initial ACL on the function.
Bug found by Noah, patch by me.
---
src/bin/pg_dump/pg_dump.c | 12 ++----------
1 file changed, 2 insertions(+), 10 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index d3f5157..bb33075 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -4673,11 +4673,7 @@ getAggregates(Archive *fout, int *numAggs)
"p.pronamespace != "
"(SELECT oid FROM pg_namespace "
"WHERE nspname = 'pg_catalog') OR "
- "EXISTS (SELECT * FROM pg_init_privs pip "
- "WHERE p.oid = pip.objoid AND pip.classoid = "
- "(SELECT oid FROM pg_class "
- "WHERE relname = 'pg_proc') "
- "AND p.proacl IS DISTINCT FROM pip.initprivs)",
+ "p.proacl IS DISTINCT FROM pip.initprivs",
username_subquery,
acl_subquery->data,
racl_subquery->data,
@@ -4923,11 +4919,7 @@ getFuncs(Archive *fout, int *numFuncs)
"pronamespace != "
"(SELECT oid FROM pg_namespace "
"WHERE nspname = 'pg_catalog') OR "
- "EXISTS (SELECT * FROM pg_init_privs pip "
- "WHERE p.oid = pip.objoid AND pip.classoid = "
- "(SELECT oid FROM pg_class "
- "WHERE relname = 'pg_proc') "
- "AND p.proacl IS DISTINCT FROM pip.initprivs)",
+ "p.proacl IS DISTINCT FROM pip.initprivs",
acl_subquery->data,
racl_subquery->data,
initacl_subquery->data,
--
2.5.0
From 707707ba1c2ee601ce893258eaaf5c54341806f6 Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfr...@snowman.net>
Date: Mon, 25 Apr 2016 00:00:15 -0400
Subject: [PATCH 2/4] pg_dump performance and other fixes
Do not try to dump objects which do not have ACLs when only ACLs are
being requested. This results in a significant performance improvement
as we can avoid querying for further information on these objects when
we don't need to.
When limiting the components to dump for an extension, consider what
components have been requested. Initially, we incorrectly hard-coded
the components of the extension objects to dump, which would mean that
we wouldn't dump some components even with they were asked for and in
other cases we would dump components which weren't requested.
Correct defaultACLs to use 'dump_contains' instead of 'dump'. The
defaultACL is considered a member of the namespace and should be
dumped based on the same set of components that the other objects in
the schema are, not based on what we're dumping for the namespace
itself (which might not include ACLs, if the namespace has just the
default or initial ACL).
Use DUMP_COMPONENT_ACL for from-initdb objects, to allow users to
change their ACLs, should they wish to. This just extends what we
are doing for the pg_catalog namespace to objects which are not
members of namespaces.
---
src/bin/pg_dump/pg_dump.c | 176 +++++++++++++++++++++++++++++++++++++++-------
1 file changed, 149 insertions(+), 27 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index bb33075..d826b4d 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1297,14 +1297,17 @@ checkExtensionMembership(DumpableObject *dobj, Archive *fout)
* contents rather than replace the extension contents with something
* different.
*/
- if (!fout->dopt->binary_upgrade && fout->remoteVersion >= 90600)
- dobj->dump = DUMP_COMPONENT_ACL |
- DUMP_COMPONENT_SECLABEL |
- DUMP_COMPONENT_POLICY;
- else if (!fout->dopt->binary_upgrade)
- dobj->dump = DUMP_COMPONENT_NONE;
- else
+ if (fout->dopt->binary_upgrade)
dobj->dump = ext->dobj.dump;
+ else
+ {
+ if (fout->remoteVersion < 90600)
+ dobj->dump = DUMP_COMPONENT_NONE;
+ else
+ dobj->dump = ext->dobj.dump_contains & (DUMP_COMPONENT_ACL |
+ DUMP_COMPONENT_SECLABEL | DUMP_COMPONENT_POLICY);
+
+ }
return true;
}
@@ -1452,7 +1455,7 @@ selectDumpableDefaultACL(DefaultACLInfo *dinfo, DumpOptions *dopt)
if (dinfo->dobj.namespace)
/* default ACLs are considered part of the namespace */
- dinfo->dobj.dump = dinfo->dobj.namespace->dobj.dump;
+ dinfo->dobj.dump = dinfo->dobj.namespace->dobj.dump_contains;
else
dinfo->dobj.dump = dopt->include_everything ?
DUMP_COMPONENT_ALL : DUMP_COMPONENT_NONE;
@@ -1473,6 +1476,10 @@ selectDumpableCast(CastInfo *cast, Archive *fout)
if (checkExtensionMembership(&cast->dobj, fout))
return; /* extension membership overrides all else */
+ /*
+ * This would be DUMP_COMPONENT_ACL for from-initdb casts, but they do not
+ * support ACLs currently.
+ */
if (cast->dobj.catId.oid < (Oid) FirstNormalObjectId)
cast->dobj.dump = DUMP_COMPONENT_NONE;
else
@@ -1494,11 +1501,23 @@ selectDumpableProcLang(ProcLangInfo *plang, Archive *fout)
if (checkExtensionMembership(&plang->dobj, fout))
return; /* extension membership overrides all else */
- if (plang->dobj.catId.oid < (Oid) FirstNormalObjectId)
+ /*
+ * Only include procedural languages when we are dumping everything.
+ *
+ * For from-initdb procedural languages, only include ACLs, as we do for
+ * the pg_catalog namespace. We need this because procedural languages do
+ * not live in any namespace.
+ */
+ if (!fout->dopt->include_everything)
plang->dobj.dump = DUMP_COMPONENT_NONE;
else
- plang->dobj.dump = fout->dopt->include_everything ?
- DUMP_COMPONENT_ALL : DUMP_COMPONENT_NONE;
+ {
+ if (plang->dobj.catId.oid < (Oid) FirstNormalObjectId)
+ plang->dobj.dump = fout->remoteVersion < 90600 ?
+ DUMP_COMPONENT_NONE : DUMP_COMPONENT_ACL;
+ else
+ plang->dobj.dump = DUMP_COMPONENT_ALL;
+ }
}
/*
@@ -1515,6 +1534,10 @@ selectDumpableAccessMethod(AccessMethodInfo *method, Archive *fout)
if (checkExtensionMembership(&method->dobj, fout))
return; /* extension membership overrides all else */
+ /*
+ * 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)
method->dobj.dump = DUMP_COMPONENT_NONE;
else
@@ -1535,11 +1558,17 @@ selectDumpableAccessMethod(AccessMethodInfo *method, Archive *fout)
static void
selectDumpableExtension(ExtensionInfo *extinfo, DumpOptions *dopt)
{
+ /*
+ * Use DUMP_COMPONENT_ACL for from-initdb extensions, to allow users
+ * to change permissions on those objects, if they wish to, and have
+ * those changes preserved.
+ */
if (dopt->binary_upgrade && extinfo->dobj.catId.oid < (Oid) FirstNormalObjectId)
- extinfo->dobj.dump = DUMP_COMPONENT_NONE;
+ extinfo->dobj.dump = DUMP_COMPONENT_ACL;
else
- extinfo->dobj.dump = dopt->include_everything ?
- DUMP_COMPONENT_ALL : DUMP_COMPONENT_NONE;
+ extinfo->dobj.dump = extinfo->dobj.dump_contains =
+ dopt->include_everything ? DUMP_COMPONENT_ALL :
+ DUMP_COMPONENT_NONE;
}
/*
@@ -4175,6 +4204,9 @@ getOperators(Archive *fout, int *numOprs)
/* Decide whether we want to dump it */
selectDumpableObject(&(oprinfo[i].dobj), fout);
+ /* Operators do not currently have ACLs. */
+ oprinfo[i].dobj.dump &= ~DUMP_COMPONENT_ACL;
+
if (strlen(oprinfo[i].rolname) == 0)
write_msg(NULL, "WARNING: owner of operator \"%s\" appears to be invalid\n",
oprinfo[i].dobj.name);
@@ -4259,6 +4291,9 @@ getCollations(Archive *fout, int *numCollations)
/* Decide whether we want to dump it */
selectDumpableObject(&(collinfo[i].dobj), fout);
+
+ /* Collations do not currently have ACLs. */
+ collinfo[i].dobj.dump &= ~DUMP_COMPONENT_ACL;
}
PQclear(res);
@@ -4340,6 +4375,9 @@ getConversions(Archive *fout, int *numConversions)
/* Decide whether we want to dump it */
selectDumpableObject(&(convinfo[i].dobj), fout);
+
+ /* Conversions do not currently have ACLs. */
+ convinfo[i].dobj.dump &= ~DUMP_COMPONENT_ACL;
}
PQclear(res);
@@ -4413,6 +4451,9 @@ getAccessMethods(Archive *fout, int *numAccessMethods)
/* Decide whether we want to dump it */
selectDumpableAccessMethod(&(aminfo[i]), fout);
+
+ /* Access methods do not currently have ACLs. */
+ aminfo[i].dobj.dump &= ~DUMP_COMPONENT_ACL;
}
PQclear(res);
@@ -4506,6 +4547,9 @@ getOpclasses(Archive *fout, int *numOpclasses)
/* Decide whether we want to dump it */
selectDumpableObject(&(opcinfo[i].dobj), fout);
+ /* Op Classes do not currently have ACLs. */
+ opcinfo[i].dobj.dump &= ~DUMP_COMPONENT_ACL;
+
if (fout->remoteVersion >= 70300)
{
if (strlen(opcinfo[i].rolname) == 0)
@@ -4594,6 +4638,9 @@ getOpfamilies(Archive *fout, int *numOpfamilies)
/* Decide whether we want to dump it */
selectDumpableObject(&(opfinfo[i].dobj), fout);
+ /* Extensions do not currently have ACLs. */
+ opfinfo[i].dobj.dump &= ~DUMP_COMPONENT_ACL;
+
if (fout->remoteVersion >= 70300)
{
if (strlen(opfinfo[i].rolname) == 0)
@@ -5123,6 +5170,7 @@ getTables(Archive *fout, int *numTables)
int i_toastreloptions;
int i_reloftype;
int i_relpages;
+ int i_changed_acl;
/* Make sure we are in proper schema */
selectSourceSchema(fout, "pg_catalog");
@@ -5154,6 +5202,11 @@ getTables(Archive *fout, int *numTables)
PQExpBuffer initacl_subquery = createPQExpBuffer();
PQExpBuffer initracl_subquery = createPQExpBuffer();
+ PQExpBuffer attacl_subquery = createPQExpBuffer();
+ PQExpBuffer attracl_subquery = createPQExpBuffer();
+ PQExpBuffer attinitacl_subquery = createPQExpBuffer();
+ PQExpBuffer attinitracl_subquery = createPQExpBuffer();
+
/*
* Left join to pick up dependency info linking sequences to their
* owning column, if any (note this dependency is AUTO as of 8.2)
@@ -5167,6 +5220,10 @@ getTables(Archive *fout, int *numTables)
"CASE WHEN c.relkind = 'S' THEN 's' ELSE 'r' END::\"char\"",
dopt->binary_upgrade);
+ buildACLQueries(attacl_subquery, attracl_subquery, attinitacl_subquery,
+ attinitracl_subquery, "at.attacl", "c.relowner", "'c'",
+ dopt->binary_upgrade);
+
appendPQExpBuffer(query,
"SELECT c.tableoid, c.oid, c.relname, "
"%s AS relacl, %s as rrelacl, "
@@ -5188,7 +5245,17 @@ getTables(Archive *fout, int *numTables)
"array_remove(array_remove(c.reloptions,'check_option=local'),'check_option=cascaded') AS reloptions, "
"CASE WHEN 'check_option=local' = ANY (c.reloptions) THEN 'LOCAL'::text "
"WHEN 'check_option=cascaded' = ANY (c.reloptions) THEN 'CASCADED'::text ELSE NULL END AS checkoption, "
- "tc.reloptions AS toast_reloptions "
+ "tc.reloptions AS toast_reloptions, "
+ "EXISTS (SELECT 1 FROM pg_attribute at LEFT JOIN pg_init_privs pip ON"
+ "(c.oid = pip.objoid AND pip.classoid = "
+ "(SELECT oid FROM pg_class WHERE relname = 'pg_class') AND pip.objsubid = at.attnum)"
+ "WHERE at.attrelid = c.oid AND ("
+ "%s IS NOT NULL "
+ "OR %s IS NOT NULL "
+ "OR %s IS NOT NULL "
+ "OR %s IS NOT NULL"
+ "))"
+ "AS changed_acl "
"FROM pg_class c "
"LEFT JOIN pg_depend d ON "
"(c.relkind = '%c' AND "
@@ -5207,6 +5274,10 @@ getTables(Archive *fout, int *numTables)
initacl_subquery->data,
initracl_subquery->data,
username_subquery,
+ attacl_subquery->data,
+ attracl_subquery->data,
+ attinitacl_subquery->data,
+ attinitracl_subquery->data,
RELKIND_SEQUENCE,
RELKIND_RELATION, RELKIND_SEQUENCE,
RELKIND_VIEW, RELKIND_COMPOSITE_TYPE,
@@ -5216,6 +5287,11 @@ getTables(Archive *fout, int *numTables)
destroyPQExpBuffer(racl_subquery);
destroyPQExpBuffer(initacl_subquery);
destroyPQExpBuffer(initracl_subquery);
+
+ destroyPQExpBuffer(attacl_subquery);
+ destroyPQExpBuffer(attracl_subquery);
+ destroyPQExpBuffer(attinitacl_subquery);
+ destroyPQExpBuffer(attinitracl_subquery);
}
else if (fout->remoteVersion >= 90500)
{
@@ -5245,7 +5321,8 @@ getTables(Archive *fout, int *numTables)
"array_remove(array_remove(c.reloptions,'check_option=local'),'check_option=cascaded') AS reloptions, "
"CASE WHEN 'check_option=local' = ANY (c.reloptions) THEN 'LOCAL'::text "
"WHEN 'check_option=cascaded' = ANY (c.reloptions) THEN 'CASCADED'::text ELSE NULL END AS checkoption, "
- "tc.reloptions AS toast_reloptions "
+ "tc.reloptions AS toast_reloptions, "
+ "NULL AS changed_acl "
"FROM pg_class c "
"LEFT JOIN pg_depend d ON "
"(c.relkind = '%c' AND "
@@ -5290,7 +5367,8 @@ getTables(Archive *fout, int *numTables)
"array_remove(array_remove(c.reloptions,'check_option=local'),'check_option=cascaded') AS reloptions, "
"CASE WHEN 'check_option=local' = ANY (c.reloptions) THEN 'LOCAL'::text "
"WHEN 'check_option=cascaded' = ANY (c.reloptions) THEN 'CASCADED'::text ELSE NULL END AS checkoption, "
- "tc.reloptions AS toast_reloptions "
+ "tc.reloptions AS toast_reloptions, "
+ "NULL AS changed_acl "
"FROM pg_class c "
"LEFT JOIN pg_depend d ON "
"(c.relkind = '%c' AND "
@@ -5335,7 +5413,8 @@ getTables(Archive *fout, int *numTables)
"array_remove(array_remove(c.reloptions,'check_option=local'),'check_option=cascaded') AS reloptions, "
"CASE WHEN 'check_option=local' = ANY (c.reloptions) THEN 'LOCAL'::text "
"WHEN 'check_option=cascaded' = ANY (c.reloptions) THEN 'CASCADED'::text ELSE NULL END AS checkoption, "
- "tc.reloptions AS toast_reloptions "
+ "tc.reloptions AS toast_reloptions, "
+ "NULL AS changed_acl "
"FROM pg_class c "
"LEFT JOIN pg_depend d ON "
"(c.relkind = '%c' AND "
@@ -5378,7 +5457,8 @@ getTables(Archive *fout, int *numTables)
"d.refobjsubid AS owning_col, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, "
"c.reloptions AS reloptions, "
- "tc.reloptions AS toast_reloptions "
+ "tc.reloptions AS toast_reloptions, "
+ "NULL AS changed_acl "
"FROM pg_class c "
"LEFT JOIN pg_depend d ON "
"(c.relkind = '%c' AND "
@@ -5421,7 +5501,8 @@ getTables(Archive *fout, int *numTables)
"d.refobjsubid AS owning_col, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, "
"c.reloptions AS reloptions, "
- "tc.reloptions AS toast_reloptions "
+ "tc.reloptions AS toast_reloptions, "
+ "NULL AS changed_acl "
"FROM pg_class c "
"LEFT JOIN pg_depend d ON "
"(c.relkind = '%c' AND "
@@ -5463,7 +5544,8 @@ getTables(Archive *fout, int *numTables)
"d.refobjsubid AS owning_col, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, "
"c.reloptions AS reloptions, "
- "tc.reloptions AS toast_reloptions "
+ "tc.reloptions AS toast_reloptions, "
+ "NULL AS changed_acl "
"FROM pg_class c "
"LEFT JOIN pg_depend d ON "
"(c.relkind = '%c' AND "
@@ -5505,7 +5587,8 @@ getTables(Archive *fout, int *numTables)
"d.refobjsubid AS owning_col, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, "
"c.reloptions AS reloptions, "
- "NULL AS toast_reloptions "
+ "NULL AS toast_reloptions, "
+ "NULL AS changed_acl "
"FROM pg_class c "
"LEFT JOIN pg_depend d ON "
"(c.relkind = '%c' AND "
@@ -5546,7 +5629,8 @@ getTables(Archive *fout, int *numTables)
"d.refobjsubid AS owning_col, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, "
"NULL AS reloptions, "
- "NULL AS toast_reloptions "
+ "NULL AS toast_reloptions, "
+ "NULL AS changed_acl "
"FROM pg_class c "
"LEFT JOIN pg_depend d ON "
"(c.relkind = '%c' AND "
@@ -5586,7 +5670,8 @@ getTables(Archive *fout, int *numTables)
"d.refobjsubid AS owning_col, "
"NULL AS reltablespace, "
"NULL AS reloptions, "
- "NULL AS toast_reloptions "
+ "NULL AS toast_reloptions, "
+ "NULL AS changed_acl "
"FROM pg_class c "
"LEFT JOIN pg_depend d ON "
"(c.relkind = '%c' AND "
@@ -5623,7 +5708,8 @@ getTables(Archive *fout, int *numTables)
"NULL::int4 AS owning_col, "
"NULL AS reltablespace, "
"NULL AS reloptions, "
- "NULL AS toast_reloptions "
+ "NULL AS toast_reloptions, "
+ "NULL AS changed_acl "
"FROM pg_class "
"WHERE relkind IN ('%c', '%c', '%c') "
"ORDER BY oid",
@@ -5655,7 +5741,8 @@ getTables(Archive *fout, int *numTables)
"NULL::int4 AS owning_col, "
"NULL AS reltablespace, "
"NULL AS reloptions, "
- "NULL AS toast_reloptions "
+ "NULL AS toast_reloptions, "
+ "NULL AS changed_acl "
"FROM pg_class "
"WHERE relkind IN ('%c', '%c', '%c') "
"ORDER BY oid",
@@ -5695,7 +5782,8 @@ getTables(Archive *fout, int *numTables)
"NULL::int4 AS owning_col, "
"NULL AS reltablespace, "
"NULL AS reloptions, "
- "NULL AS toast_reloptions "
+ "NULL AS toast_reloptions, "
+ "NULL AS changed_acl "
"FROM pg_class c "
"WHERE relkind IN ('%c', '%c') "
"ORDER BY oid",
@@ -5754,6 +5842,7 @@ getTables(Archive *fout, int *numTables)
i_checkoption = PQfnumber(res, "checkoption");
i_toastreloptions = PQfnumber(res, "toast_reloptions");
i_reloftype = PQfnumber(res, "reloftype");
+ i_changed_acl = PQfnumber(res, "changed_acl");
if (dopt->lockWaitTimeout && fout->remoteVersion >= 70300)
{
@@ -5835,6 +5924,21 @@ getTables(Archive *fout, int *numTables)
else
selectDumpableTable(&tblinfo[i], fout);
+ /*
+ * If the table-level and all column-level ACLs for this table are
+ * unchanged, then we don't need to worry about including the ACLs
+ * for this table. If any column-level ACLs have been changed, the
+ * 'changed_acl' column from the query will indicate that.
+ *
+ * This can result in a significant performance improvement in cases
+ * where we are only looking to dump out the ACL (eg: pg_catalog).
+ */
+ if (PQgetisnull(res, i, i_relacl) && PQgetisnull(res, i, i_rrelacl) &&
+ PQgetisnull(res, i, i_initrelacl) &&
+ PQgetisnull(res, i, i_initrrelacl) &&
+ strcmp(PQgetvalue(res, i, i_changed_acl), "f") == 0)
+ tblinfo[i].dobj.dump &= ~DUMP_COMPONENT_ACL;
+
tblinfo[i].interesting = tblinfo[i].dobj.dump ? true : false;
tblinfo[i].postponed_def = false; /* might get set during sort */
@@ -6989,6 +7093,9 @@ getEventTriggers(Archive *fout, int *numEventTriggers)
/* Decide whether we want to dump it */
selectDumpableObject(&(evtinfo[i].dobj), fout);
+
+ /* Event Triggers do not currently have ACLs. */
+ evtinfo[i].dobj.dump &= ~DUMP_COMPONENT_ACL;
}
PQclear(res);
@@ -7350,6 +7457,9 @@ getCasts(Archive *fout, int *numCasts)
/* Decide whether we want to dump it */
selectDumpableCast(&(castinfo[i]), fout);
+
+ /* Casts do not currently have ACLs. */
+ castinfo[i].dobj.dump &= ~DUMP_COMPONENT_ACL;
}
PQclear(res);
@@ -8143,6 +8253,9 @@ getTSParsers(Archive *fout, int *numTSParsers)
/* Decide whether we want to dump it */
selectDumpableObject(&(prsinfo[i].dobj), fout);
+
+ /* Text Search Parsers do not currently have ACLs. */
+ prsinfo[i].dobj.dump &= ~DUMP_COMPONENT_ACL;
}
PQclear(res);
@@ -8228,6 +8341,9 @@ getTSDictionaries(Archive *fout, int *numTSDicts)
/* Decide whether we want to dump it */
selectDumpableObject(&(dictinfo[i].dobj), fout);
+
+ /* Text Search Dictionaries do not currently have ACLs. */
+ dictinfo[i].dobj.dump &= ~DUMP_COMPONENT_ACL;
}
PQclear(res);
@@ -8305,6 +8421,9 @@ getTSTemplates(Archive *fout, int *numTSTemplates)
/* Decide whether we want to dump it */
selectDumpableObject(&(tmplinfo[i].dobj), fout);
+
+ /* Text Search Templates do not currently have ACLs. */
+ tmplinfo[i].dobj.dump &= ~DUMP_COMPONENT_ACL;
}
PQclear(res);
@@ -8383,6 +8502,9 @@ getTSConfigurations(Archive *fout, int *numTSConfigs)
/* Decide whether we want to dump it */
selectDumpableObject(&(cfginfo[i].dobj), fout);
+
+ /* Text Search Configurations do not currently have ACLs. */
+ cfginfo[i].dobj.dump &= ~DUMP_COMPONENT_ACL;
}
PQclear(res);
--
2.5.0
From 32645eac291b546df2acb23685c0a5f123131efa Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfr...@snowman.net>
Date: Wed, 4 May 2016 13:40:36 -0400
Subject: [PATCH 3/4] Only issue LOCK TABLE commands when necessary
Reviewing the cases where we need to LOCK a given table during a dump,
it was pointed out by Tom that we really don't need to LOCK a table if
we are only looking to dump the ACL for it, or certain other
components. After reviewing the queries run for all of the component
pieces, a list of components were determined to not require LOCK'ing
of the table.
This implements a check to avoid LOCK'ing those tables.
Initial complaint from Rushabh Lathia, discussed with Robert and Tom,
the patch is mine.
---
src/bin/pg_dump/pg_dump.c | 25 ++++++++++++++++++++++++-
1 file changed, 24 insertions(+), 1 deletion(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index d826b4d..2c75b70 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -5947,14 +5947,37 @@ getTables(Archive *fout, int *numTables)
* Read-lock target tables to make sure they aren't DROPPED or altered
* in schema before we get around to dumping them.
*
+ * Note that some components only require looking at the information
+ * in the pg_catalog tables and, for those components, we do not need
+ * to lock the table. Be careful here though- some components use
+ * server-side functions which pull the latest information from
+ * SysCache and in those cases we *do* need to lock the table.
+ *
* Note that we don't explicitly lock parents of the target tables; we
* assume our lock on the child is enough to prevent schema
* alterations to parent tables.
*
* NOTE: it'd be kinda nice to lock other relations too, not only
* plain tables, but the backend doesn't presently allow that.
+ *
+ * We do not need locks for the COMMENT and SECLABEL components as
+ * those simply query their associated tables without using any
+ * server-side functions. We do not need locks for the ACL component
+ * as we pull that information from pg_class without using any
+ * server-side functions that use SysCache. The USERMAP component
+ * is only relevant for FOREIGN SERVERs and not tables, so no sense
+ * locking a table for that either (that can happen if we are going
+ * to dump "ALL" components for a table).
+ *
+ * We DO need locks for DEFINITION, due to various server-side
+ * functions that are used and POLICY due to pg_get_expr(). We set
+ * this up to grab the lock except in the cases we know to be safe.
*/
- if (tblinfo[i].dobj.dump && tblinfo[i].relkind == RELKIND_RELATION)
+ if (tblinfo[i].dobj.dump && tblinfo[i].relkind == RELKIND_RELATION &&
+ (tblinfo[i].dobj.dump & ~(DUMP_COMPONENT_COMMENT |
+ DUMP_COMPONENT_SECLABEL |
+ DUMP_COMPONENT_ACL |
+ DUMP_COMPONENT_USERMAP)))
{
resetPQExpBuffer(query);
appendPQExpBuffer(query,
--
2.5.0
From 2da8870c61431d6ec4586b350ec56a8a947afe8a Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfr...@snowman.net>
Date: Mon, 25 Apr 2016 00:01:38 -0400
Subject: [PATCH 4/4] Add TAP tests for pg_dump
This TAP test suite will create a new cluster, populate it based on
the 'create_sql' values in the '%tests' hash, run all of the runs
defined in the '%pgdump_runs' hash, and then for each test in the
'%tests' hash, compare each run's output the the regular expression
defined for the test under the 'like' and 'unlike' functions, as
appropriate.
While this test suite covers a fair bit of ground (67% of pg_dump.c
and quite a bit of the other files in src/bin/pg_dump), there is
still quite a bit which remains to be added to provide better code
coverage. Still, this is quite a bit better than we had, and has
found a few bugs already (note that the CREATE TRANSFORM test is
commented out, as it is currently failing).
Idea for using the TAP system from Tom, though all of the code is mine.
---
src/bin/pg_dump/Makefile | 3 +
src/bin/pg_dump/pg_dump.c | 2 +-
src/bin/pg_dump/t/001_basic.pl | 42 +
src/bin/pg_dump/t/002_pg_dump.pl | 2466 ++++++++++++++++++++++++++++++++++++++
4 files changed, 2512 insertions(+), 1 deletion(-)
create mode 100644 src/bin/pg_dump/t/001_basic.pl
create mode 100644 src/bin/pg_dump/t/002_pg_dump.pl
diff --git a/src/bin/pg_dump/Makefile b/src/bin/pg_dump/Makefile
index 9596789..260804b 100644
--- a/src/bin/pg_dump/Makefile
+++ b/src/bin/pg_dump/Makefile
@@ -42,6 +42,9 @@ install: all installdirs
installdirs:
$(MKDIR_P) '$(DESTDIR)$(bindir)'
+check:
+ $(prove_check)
+
uninstall:
rm -f $(addprefix '$(DESTDIR)$(bindir)'/, pg_dump$(X) pg_restore$(X) pg_dumpall$(X))
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 2c75b70..7cd62ca 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1564,7 +1564,7 @@ selectDumpableExtension(ExtensionInfo *extinfo, DumpOptions *dopt)
* those changes preserved.
*/
if (dopt->binary_upgrade && extinfo->dobj.catId.oid < (Oid) FirstNormalObjectId)
- extinfo->dobj.dump = DUMP_COMPONENT_ACL;
+ extinfo->dobj.dump = extinfo->dobj.dump_contains = DUMP_COMPONENT_ACL;
else
extinfo->dobj.dump = extinfo->dobj.dump_contains =
dopt->include_everything ? DUMP_COMPONENT_ALL :
diff --git a/src/bin/pg_dump/t/001_basic.pl b/src/bin/pg_dump/t/001_basic.pl
new file mode 100644
index 0000000..1411ef7
--- /dev/null
+++ b/src/bin/pg_dump/t/001_basic.pl
@@ -0,0 +1,42 @@
+use strict;
+use warnings;
+
+use Config;
+use PostgresNode;
+use TestLib;
+use Test::More tests => 15;
+
+my $tempdir = TestLib::tempdir;
+my $tempdir_short = TestLib::tempdir_short;
+
+#########################################
+# Basic checks
+
+program_help_ok('pg_dump');
+program_version_ok('pg_dump');
+program_options_handling_ok('pg_dump');
+
+#########################################
+# Test various invalid options and disallowed combinations
+# Doesn't require a PG instance to be set up, so do this first.
+
+command_exit_is([ 'pg_dump', 'qqq', 'abc' ],
+ 1, 'pg_dump: too many command-line arguments (first is "asd")');
+
+command_exit_is([ 'pg_dump', '-s', '-a' ],
+ 1, 'pg_dump: options -s/--schema-only and -a/--data-only cannot be used together');
+
+command_exit_is([ 'pg_dump', '-c', '-a' ],
+ 1, 'pg_dump: options -c/--clean and -a/--data-only cannot be used together');
+
+command_exit_is([ 'pg_dump', '--inserts', '-o' ],
+ 1, 'pg_dump: options --inserts/--column-inserts and -o/--oids cannot be used together');
+
+command_exit_is([ 'pg_dump', '--if-exists' ],
+ 1, 'pg_dump: option --if-exists requires option -c/--clean');
+
+command_exit_is([ 'pg_dump', '-j' ],
+ 1, 'pg_dump: option requires an argument -- \'j\'');
+
+command_exit_is([ 'pg_dump', '-j3' ],
+ 1, 'pg_dump: parallel backup only supported by the directory format');
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
new file mode 100644
index 0000000..0d072b3
--- /dev/null
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -0,0 +1,2466 @@
+use strict;
+use warnings;
+
+use Config;
+use PostgresNode;
+use TestLib;
+use Test::More;
+
+my $tempdir = TestLib::tempdir;
+my $tempdir_short = TestLib::tempdir_short;
+
+###############################################################
+# Definition of the pg_dump runs to make.
+#
+# Each of these runs are named and those names are used below
+# to define how each test should (or shouldn't) treat a result
+# from a given run.
+#
+# test_key indicates that a given run should simply use the same
+# set of like/unlike tests as another run, and which run that is.
+#
+# dump_cmd is the pg_dump command to run, which is an array of
+# the full command and arguments to run. Note that this is run
+# using $node->command_ok(), so the port does not need to be
+# specified and is pulled from $PGPORT, which is set by the
+# PostgresNode system.
+#
+# restore_cmd is the pg_restore command to run, if any. Note
+# that this should generally be used when the pg_dump goes to
+# a non-text file and that the restore can then be used to
+# generate a text file to run through the tests from the
+# non-text file generated by pg_dump.
+#
+# TODO: Have pg_restore actually restore to an independent
+# database and then pg_dump *that* database (or something along
+# those lines) to validate that part of the process.
+
+my %pgdump_runs = (
+ binary_upgrade => {
+ dump_cmd => [
+ 'pg_dump',
+ '-f', "$tempdir/binary_upgrade.sql",
+ '--schema-only',
+ '--binary-upgrade',
+ '-d', 'postgres', # alternative way to specify database
+ ],
+ },
+ clean => {
+ dump_cmd => [
+ 'pg_dump',
+ '-f', "$tempdir/clean.sql",
+ '-c',
+ '-d', 'postgres', # alternative way to specify database
+ ],
+ },
+ clean_if_exists => {
+ dump_cmd => [
+ 'pg_dump',
+ '-f', "$tempdir/clean_if_exists.sql",
+ '-c',
+ '--if-exists',
+ '-E', 'UTF8', # no-op, just tests that option is accepted
+ 'postgres',
+ ],
+ },
+ column_inserts => {
+ dump_cmd => [
+ 'pg_dump',
+ '-f', "$tempdir/column_inserts.sql",
+ '-a',
+ '--column-inserts',
+ 'postgres',
+ ],
+ },
+ createdb => {
+ dump_cmd => [
+ 'pg_dump',
+ '-f', "$tempdir/createdb.sql",
+ '-C',
+ '-R', # no-op, just for testing
+ 'postgres',
+ ],
+ },
+ data_only => {
+ dump_cmd => [
+ 'pg_dump',
+ '-f', "$tempdir/data_only.sql",
+ '-a',
+ '-v', # no-op, just make sure it works
+ 'postgres',
+ ],
+ },
+ defaults => {
+ dump_cmd => [
+ 'pg_dump',
+ '-f', "$tempdir/defaults.sql",
+ 'postgres',
+ ],
+ },
+ defaults_custom_format => {
+ test_key => 'defaults',
+ dump_cmd => [
+ 'pg_dump',
+ '-Fc',
+ '-Z6',
+ '-f', "$tempdir/defaults_custom_format.dump",
+ 'postgres',
+ ],
+ restore_cmd => [
+ 'pg_restore',
+ '-f', "$tempdir/defaults_custom_format.sql",
+ "$tempdir/defaults_custom_format.dump",
+ ],
+ },
+ defaults_dir_format => {
+ test_key => 'defaults',
+ dump_cmd => [
+ 'pg_dump',
+ '-Fd',
+ '-f', "$tempdir/defaults_dir_format",
+ 'postgres',
+ ],
+ restore_cmd => [
+ 'pg_restore',
+ '-f', "$tempdir/defaults_dir_format.sql",
+ "$tempdir/defaults_dir_format",
+ ],
+ },
+ defaults_parallel => {
+ test_key => 'defaults',
+ dump_cmd => [
+ 'pg_dump',
+ '-Fd',
+ '-j2',
+ '-f', "$tempdir/defaults_parallel",
+ 'postgres',
+ ],
+ restore_cmd => [
+ 'pg_restore',
+ '-f', "$tempdir/defaults_parallel.sql",
+ "$tempdir/defaults_parallel",
+ ],
+ },
+ defaults_tar_format => {
+ test_key => 'defaults',
+ dump_cmd => [
+ 'pg_dump',
+ '-Ft',
+ '-f', "$tempdir/defaults_tar_format.tar",
+ 'postgres',
+ ],
+ restore_cmd => [
+ 'pg_restore',
+ '-f', "$tempdir/defaults_tar_format.sql",
+ "$tempdir/defaults_tar_format.tar",
+ ],
+ },
+ exclude_dump_test_schema => {
+ dump_cmd => [
+ 'pg_dump',
+ '-f', "$tempdir/exclude_dump_test_schema.sql",
+ '-N', 'dump_test',
+ 'postgres',
+ ],
+ },
+ exclude_test_table => {
+ dump_cmd => [
+ 'pg_dump',
+ '-f', "$tempdir/exclude_test_table.sql",
+ '-T', 'dump_test.test_table',
+ 'postgres',
+ ],
+ },
+ exclude_test_table_data => {
+ dump_cmd => [
+ 'pg_dump',
+ '-f', "$tempdir/exclude_test_table_data.sql",
+ '--exclude-table-data=dump_test.test_table',
+ 'postgres',
+ ],
+ },
+ pg_dumpall_globals => {
+ dump_cmd => [
+ 'pg_dumpall',
+ '-f', "$tempdir/pg_dumpall_globals.sql",
+ '-g',
+ ],
+ },
+ no_privs => {
+ dump_cmd => [
+ 'pg_dump',
+ '-f', "$tempdir/no_privs.sql",
+ '-x',
+ 'postgres',
+ ],
+ },
+ no_owner => {
+ dump_cmd => [
+ 'pg_dump',
+ '-f', "$tempdir/no_owner.sql",
+ '-O',
+ 'postgres',
+ ],
+ },
+ only_dump_test_schema => {
+ dump_cmd => [
+ 'pg_dump',
+ '-f', "$tempdir/only_dump_test_schema.sql",
+ '-n', 'dump_test',
+ 'postgres',
+ ],
+ },
+ only_dump_test_table => {
+ dump_cmd => [
+ 'pg_dump',
+ '-f', "$tempdir/only_dump_test_table.sql",
+ '-t', 'dump_test.test_table',
+ '--lock-wait-timeout=1000000',
+ 'postgres',
+ ],
+ },
+ role => {
+ dump_cmd => [
+ 'pg_dump',
+ '-f', "$tempdir/role.sql",
+ '--role=dump_test',
+ '--schema=dump_test_second_schema',
+ 'postgres',
+ ],
+ },
+ schema_only => {
+ dump_cmd => [
+ 'pg_dump',
+ '-f', "$tempdir/schema_only.sql",
+ '-s',
+ 'postgres',
+ ],
+ },
+ section_pre_data => {
+ dump_cmd => [
+ 'pg_dump',
+ '-f', "$tempdir/section_pre_data.sql",
+ '--section=pre-data',
+ 'postgres',
+ ],
+ },
+ section_data => {
+ dump_cmd => [
+ 'pg_dump',
+ '-f', "$tempdir/section_data.sql",
+ '--section=data',
+ 'postgres',
+ ],
+ },
+ section_post_data => {
+ dump_cmd => [
+ 'pg_dump',
+ '-f', "$tempdir/section_post_data.sql",
+ '--section=post-data',
+ 'postgres',
+ ],
+ },
+ test_schema_plus_blobs => {
+ dump_cmd => [
+ 'pg_dump',
+ '-f', "$tempdir/test_schema_plus_blobs.sql",
+ '-n', 'dump_test',
+ '-b',
+ 'postgres',
+ ],
+ },
+);
+
+###############################################################
+# Definition of the tests to run.
+#
+# Each test is defined using the log message that will be used.
+#
+# A regexp should be defined for each test which provides the
+# basis for the test. That regexp will be run against the output
+# file of each of the runs which the test is to be run against
+# and the success of the result will depend on if the regexp
+# result matches the expected 'like' or 'unlike' case.
+#
+# For each test, there are two sets of runs defined, one for
+# the 'like' tests and one for the 'unlike' tests. 'like'
+# essentially means "the regexp for this test must match the
+# output file". 'unlike' is the opposite.
+#
+# There are a few 'catch-all' tests which can be used to have
+# a single, simple, test to over a range of other tests. For
+# example, there is a '^CREATE ' test, which is used for the
+# 'data-only' test as there should never be any kind of CREATE
+# statement in a 'data-only' run. Without the catch-all, we
+# would have to list the 'data-only' run in each and every
+# 'CREATE xxxx' test, which would be a lot of additional tests.
+#
+# Note that it makes no sense for the same run to ever be listed
+# in both 'like' and 'unlike' categories.
+#
+# There can then be a 'create_sql' and 'create_order' for a
+# given test. The 'create_sql' commands are collected up in
+# 'create_order' and then run against the database prior to any
+# of the pg_dump runs happening. This is what "seeds" the
+# system with objects to be dumped out.
+#
+# Building of this hash takes a bit of time as all of the regexps
+# included in it are compiled. This greatly improves performance
+# as the regexps are used for each run the test applies to.
+
+my %tests = (
+ 'ALTER DEFAULT PRIVILEGES FOR ROLE dump_test' => {
+ create_order => 14,
+ create_sql => 'ALTER DEFAULT PRIVILEGES FOR ROLE dump_test IN SCHEMA dump_test GRANT SELECT ON TABLES TO dump_test;',
+ regexp => qr/^ALTER DEFAULT PRIVILEGES FOR ROLE dump_test IN SCHEMA dump_test GRANT SELECT ON TABLES TO dump_test;$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_post_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ no_privs => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_pre_data => 1,
+ section_data => 1,
+ },
+ },
+ 'ALTER ROLE dump_test' => {
+ regexp => qr/^ALTER ROLE dump_test WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;$/m,
+ like => {
+ pg_dumpall_globals => 1,
+ },
+ unlike => {
+ 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,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ section_post_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ },
+ 'ALTER FUNCTION dump_test.pltestlang_call_handler() OWNER TO' => {
+ regexp => qr/^ALTER FUNCTION dump_test\.pltestlang_call_handler\(\) OWNER TO .*;$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ },
+ },
+ 'ALTER PROCEDURAL LANGUAGE pltestlang OWNER TO' => {
+ regexp => qr/^ALTER PROCEDURAL LANGUAGE pltestlang OWNER TO .*;$/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,
+ schema_only => 1,
+ section_pre_data => 1,
+ },
+ unlike => {
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ test_schema_plus_blobs => 1,
+ },
+ },
+ 'ALTER SCHEMA dump_test OWNER TO' => {
+ regexp => qr/^ALTER SCHEMA dump_test OWNER TO .*;$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ },
+ },
+ 'ALTER SCHEMA dump_test_second_schema OWNER TO' => {
+ regexp => qr/^ALTER SCHEMA dump_test_second_schema OWNER TO .*;$/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,
+ schema_only => 1,
+ section_pre_data => 1,
+ },
+ unlike => {
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ test_schema_plus_blobs => 1,
+ },
+ },
+ 'ALTER TABLE ONLY test_table ADD CONSTRAINT test_table_pkey PRIMARY KEY' => {
+ regexp => qr/^ALTER TABLE ONLY test_table\n^\s+ADD CONSTRAINT test_table_pkey PRIMARY KEY \(col1\);$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ schema_only => 1,
+ section_post_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_pre_data => 1,
+ section_data => 1,
+ },
+ },
+ 'ALTER TABLE test_table OWNER TO' => {
+ regexp => qr/^ALTER TABLE test_table OWNER TO .*;$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ },
+ },
+ 'ALTER TABLE test_table ENABLE ROW LEVEL SECURITY' => {
+ create_order => 23,
+ create_sql => 'ALTER TABLE dump_test.test_table ENABLE ROW LEVEL SECURITY;',
+ regexp => qr/^ALTER TABLE test_table ENABLE ROW LEVEL SECURITY;$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ schema_only => 1,
+ section_post_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ section_pre_data => 1,
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ },
+ },
+ 'ALTER TABLE test_second_table OWNER TO' => {
+ regexp => qr/^ALTER TABLE test_second_table OWNER TO .*;$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ },
+ },
+ 'ALTER TABLE test_third_table OWNER TO' => {
+ regexp => qr/^ALTER TABLE test_third_table OWNER TO .*;$/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,
+ schema_only => 1,
+ section_pre_data => 1,
+ },
+ unlike => {
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ test_schema_plus_blobs => 1,
+ },
+ },
+ 'ALTER ... OWNER commands (except LARGE OBJECTs)' => { # catch-all for ALTER ... OWNER (except LARGE OBJECTs)
+ regexp => qr/^ALTER (?!LARGE OBJECT)(.*) OWNER TO .*;$/m,
+ like => { }, # use more-specific options above
+ unlike => {
+ column_inserts => 1,
+ data_only => 1,
+ section_data => 1,
+ },
+ },
+ 'ALTER ... OWNER commands' => { # catch-all for ALTER ... OWNER
+ regexp => qr/^ALTER .* OWNER TO .*;$/m,
+ like => { }, # use more-specific options above
+ unlike => {
+ no_owner => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'BLOB load (contents are of test_table)' => {
+ create_order => 14,
+ create_sql =>
+ "\\o '$tempdir/large_object_test.sql'\n"
+ . "table dump_test.test_table;\n"
+ . "\\o\n"
+ . "\\lo_import '$tempdir/large_object_test.sql'\n",
+ regexp => qr/^SELECT pg_catalog\.lo_open\('\d+', \d+\);\nSELECT pg_catalog\.lowrite\(0, '\\x310a320a330a340a350a360a370a380a390a\'\);\nSELECT pg_catalog\.lo_close\(0\);$/m,
+ like => {
+ 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,
+ section_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ binary_upgrade => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ schema_only => 1,
+ section_post_data => 1,
+ },
+ },
+ 'COMMENT ON DATABASE postgres' => {
+ regexp => qr/^COMMENT ON DATABASE postgres IS .*;$/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,
+ schema_only => 1,
+ section_pre_data => 1,
+ },
+ unlike => {
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ },
+ },
+ 'COMMENT ON EXTENSION plpgsql' => {
+ regexp => qr/^COMMENT ON EXTENSION plpgsql IS .*;$/m,
+ like => {
+ 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,
+ schema_only => 1,
+ section_pre_data => 1,
+ },
+ unlike => {
+ binary_upgrade => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ },
+ },
+ 'COMMENT ON TABLE dump_test.test_table' => {
+ create_order => 36,
+ create_sql => 'COMMENT ON TABLE dump_test.test_table IS \'comment on table\';',
+ regexp => qr/^COMMENT ON TABLE test_table IS 'comment on table';$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ },
+ },
+ 'COMMENT ON COLUMN dump_test.test_table.col1' => {
+ create_order => 36,
+ create_sql => 'COMMENT ON COLUMN dump_test.test_table.col1 IS \'comment on column\';',
+ regexp => qr/^COMMENT ON COLUMN test_table\.col1 IS 'comment on column';$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ },
+ },
+ 'COMMENT ON COLUMN dump_test.composite.f1' => {
+ create_order => 44,
+ create_sql => 'COMMENT ON COLUMN dump_test.composite.f1 IS \'comment on column of type\';',
+ regexp => qr/^COMMENT ON COLUMN composite\.f1 IS 'comment on column of type';$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ },
+ },
+ 'COMMENT commands' => { # catch-all for COMMENTs
+ regexp => qr/^COMMENT ON /m,
+ like => { }, # use more-specific options above
+ unlike => {
+ column_inserts => 1,
+ data_only => 1,
+ pg_dumpall_globals => 1,
+ section_data => 1,
+ section_post_data => 1,
+ },
+ },
+ 'COPY test_table' => {
+ create_order => 4,
+ create_sql =>
+ 'INSERT INTO dump_test.test_table (col1) '
+ . 'SELECT generate_series FROM generate_series(1,9);',
+ regexp => qr/^COPY test_table \(col1\) FROM stdin;\n(?:\d\n){9}\\\.\n$/m,
+ like => {
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ data_only => 1,
+ defaults => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ section_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ },
+ },
+ 'COPY fk_reference_test_table' => {
+ create_order => 22,
+ create_sql =>
+ 'INSERT INTO dump_test.fk_reference_test_table (col1) '
+ . 'SELECT generate_series FROM generate_series(1,5);',
+ regexp => qr/^COPY fk_reference_test_table \(col1\) FROM stdin;\n(?:\d\n){5}\\\.\n$/m,
+ like => {
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ data_only => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ section_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ },
+ },
+ 'COPY fk_reference_test_table second' => {
+ regexp => qr/^COPY test_table \(col1\) FROM stdin;\n(?:\d\n){9}\\\.\n.*COPY fk_reference_test_table \(col1\) FROM stdin;\n(?:\d\n){5}\\\.\n$/ms,
+ like => {
+ data_only => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ },
+ },
+ 'COPY test_second_table' => {
+ create_order => 7,
+ create_sql =>
+ 'INSERT INTO dump_test.test_second_table (col1, col2) '
+ . 'SELECT generate_series, generate_series::text FROM generate_series(1,9);',
+ regexp => qr/^COPY test_second_table \(col1, col2\) FROM stdin;\n(?:\d\t\d\n){9}\\\.\n$/m,
+ like => {
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ data_only => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ section_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ },
+ },
+ 'COPY test_third_table' => {
+ create_order => 12,
+ create_sql =>
+ 'INSERT INTO dump_test_second_schema.test_third_table (col1) '
+ . 'SELECT generate_series FROM generate_series(1,9);',
+ regexp => qr/^COPY test_third_table \(col1\) FROM stdin;\n(?:\d\n){9}\\\.\n$/m,
+ like => {
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ data_only => 1,
+ defaults => 1,
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ section_data => 1,
+ },
+ unlike => {
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ test_schema_plus_blobs => 1,
+ },
+ },
+ 'INSERT INTO test_table' => {
+ regexp => qr/^(?:INSERT INTO test_table \(col1\) VALUES \(\d\);\n){9}$/m,
+ like => {
+ column_inserts => 1,
+ },
+ unlike => {
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ data_only => 1,
+ defaults => 1,
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ section_data => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ test_schema_plus_blobs => 1,
+ },
+ },
+ 'INSERT INTO test_second_table' => {
+ regexp => qr/^(?:INSERT INTO test_second_table \(col1, col2\) VALUES \(\d, '\d'\);\n){9}$/m,
+ like => {
+ column_inserts => 1,
+ },
+ unlike => {
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ data_only => 1,
+ defaults => 1,
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ section_data => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ test_schema_plus_blobs => 1,
+ },
+ },
+ 'INSERT INTO test_third_table' => {
+ regexp => qr/^(?:INSERT INTO test_third_table \(col1\) VALUES \(\d\);\n){9}$/m,
+ like => {
+ column_inserts => 1,
+ },
+ unlike => {
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ data_only => 1,
+ defaults => 1,
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ section_data => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ test_schema_plus_blobs => 1,
+ },
+ },
+ 'COPY ... commands' => { # catch-all for COPY
+ regexp => qr/^COPY /m,
+ like => { }, # use more-specific options above
+ unlike => {
+ binary_upgrade => 1,
+ column_inserts => 1,
+ pg_dumpall_globals => 1,
+ schema_only => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE ROLE dump_test' => {
+ create_order => 1,
+ create_sql => 'CREATE ROLE dump_test;',
+ regexp => qr/^CREATE ROLE dump_test;$/m,
+ like => {
+ pg_dumpall_globals => 1,
+ },
+ unlike => {
+ 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,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ section_post_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ },
+ 'CREATE DATABASE postgres' => {
+ regexp => qr/^CREATE DATABASE postgres WITH TEMPLATE = template0 .*;$/m,
+ like => {
+ createdb => 1,
+ },
+ unlike => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ defaults => 1,
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ section_post_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ },
+ 'CREATE EXTENSION ... plpgsql' => {
+ regexp => qr/^CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;$/m,
+ like => {
+ 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,
+ schema_only => 1,
+ section_pre_data => 1,
+ },
+ unlike => {
+ binary_upgrade => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ },
+ 'CREATE AGGREGATE dump_test.newavg' => {
+ create_order => 25,
+ create_sql => 'CREATE AGGREGATE dump_test.newavg (sfunc = int4_avg_accum, basetype = int4, stype = _int8, finalfunc = int8_avg, initcond1 = \'{0,0}\');',
+ regexp => qr/^CREATE AGGREGATE newavg\(integer\) \(\n\s+SFUNC = int4_avg_accum,\n\s+STYPE = bigint\[\],\n\s+INITCOND = \'\{0,0\}\',\n\s+FINALFUNC = int8_avg\n\);$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE DOMAIN dump_test.us_postal_code' => {
+ create_order => 29,
+ create_sql => 'CREATE DOMAIN dump_test.us_postal_code AS TEXT CHECK(VALUE ~ \'^\d{5}$\' OR VALUE ~ \'^\d{5}-\d{4}$\');',
+ regexp => qr/CREATE DOMAIN us_postal_code AS text\n\s+CONSTRAINT us_postal_code_check CHECK \(\(\(VALUE ~ \'\^\\d\{5\}\$\'::text\) OR \(VALUE ~ \'\^\\d\{5\}-\\d\{4\}\$\'::text\)\)\);$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE FUNCTION dump_test.pltestlang_call_handler' => {
+ create_order => 17,
+ create_sql => 'CREATE FUNCTION dump_test.pltestlang_call_handler() RETURNS LANGUAGE_HANDLER AS \'$libdir/plpgsql\', \'plpgsql_call_handler\' LANGUAGE C;',
+ regexp => qr/^CREATE FUNCTION pltestlang_call_handler\(\) RETURNS language_handler\n\s+LANGUAGE c\n\s+AS '\$libdir\/plpgsql', 'plpgsql_call_handler';$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE FUNCTION dump_test.trigger_func' => {
+ create_order => 30,
+ create_sql => 'CREATE FUNCTION dump_test.trigger_func() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN RETURN NULL; END;$$;',
+ regexp => qr/^CREATE FUNCTION trigger_func\(\) RETURNS trigger\n\s+LANGUAGE plpgsql\n\s+AS \$\$ BEGIN RETURN NULL; END;\$\$;$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE FUNCTION dump_test.event_trigger_func' => {
+ create_order => 32,
+ create_sql => 'CREATE FUNCTION dump_test.event_trigger_func() RETURNS event_trigger LANGUAGE plpgsql AS $$ BEGIN RETURN; END;$$;',
+ regexp => qr/^CREATE FUNCTION event_trigger_func\(\) RETURNS event_trigger\n\s+LANGUAGE plpgsql\n\s+AS \$\$ BEGIN RETURN; END;\$\$;$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE EVENT TRIGGER test_event_trigger' => {
+ create_order => 33,
+ create_sql => 'CREATE EVENT TRIGGER test_event_trigger ON ddl_command_start EXECUTE PROCEDURE dump_test.event_trigger_func();',
+ regexp => qr/^CREATE EVENT TRIGGER test_event_trigger ON ddl_command_start\n\s+EXECUTE PROCEDURE dump_test\.event_trigger_func\(\);$/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,
+ 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 TRIGGER test_trigger' => {
+ create_order => 31,
+ create_sql => 'CREATE TRIGGER test_trigger BEFORE INSERT ON dump_test.test_table FOR EACH ROW WHEN (NEW.col1 > 10) EXECUTE PROCEDURE dump_test.trigger_func();',
+ regexp => qr/^CREATE TRIGGER test_trigger BEFORE INSERT ON test_table FOR EACH ROW WHEN \(\(new.col1 > 10\)\) EXECUTE PROCEDURE trigger_func\(\);$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ schema_only => 1,
+ section_post_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ section_pre_data => 1,
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ pg_dumpall_globals => 1,
+ },
+ },
+ 'CREATE TYPE dump_test.planets AS ENUM' => {
+ create_order => 37,
+ create_sql => 'CREATE TYPE dump_test.planets AS ENUM ( \'venus\', \'earth\', \'mars\' );',
+ regexp => qr/^CREATE TYPE planets AS ENUM \(\n\s+'venus',\n\s+'earth',\n\s+'mars'\n\);$/m,
+ like => {
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE TYPE dump_test.planets AS ENUM pg_upgrade' => {
+ regexp => qr/^CREATE TYPE planets AS ENUM \(\n\);.*^ALTER TYPE dump_test\.planets ADD VALUE 'venus';\n.*^ALTER TYPE dump_test\.planets ADD VALUE 'earth';\n.*^ALTER TYPE dump_test\.planets ADD VALUE 'mars';\n$/ms,
+ like => {
+ binary_upgrade => 1,
+ },
+ unlike => {
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE TYPE dump_test.textrange AS RANGE' => {
+ create_order => 38,
+ create_sql => 'CREATE TYPE dump_test.textrange AS RANGE (subtype=text, collation="C");',
+ regexp => qr/^CREATE TYPE textrange AS RANGE \(\n\s+subtype = text,\n\s+collation = pg_catalog\."C"\n\);/m,
+ like => {
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE TYPE dump_test.int42' => {
+ create_order => 39,
+ create_sql => 'CREATE TYPE dump_test.int42;',
+ regexp => qr/^CREATE TYPE int42;/m,
+ like => {
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE FUNCTION dump_test.int42_in' => {
+ create_order => 40,
+ create_sql => 'CREATE FUNCTION dump_test.int42_in(cstring) RETURNS dump_test.int42 AS \'int4in\' LANGUAGE internal STRICT IMMUTABLE;',
+ regexp => qr/^CREATE FUNCTION int42_in\(cstring\) RETURNS int42\n\s+LANGUAGE internal IMMUTABLE STRICT\n\s+AS \$\$int4in\$\$;$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE FUNCTION dump_test.int42_out' => {
+ create_order => 40,
+ create_sql => 'CREATE FUNCTION dump_test.int42_out(dump_test.int42) RETURNS cstring AS \'int4out\' LANGUAGE internal STRICT IMMUTABLE;',
+ regexp => qr/^CREATE FUNCTION int42_out\(int42\) RETURNS cstring\n\s+LANGUAGE internal IMMUTABLE STRICT\n\s+AS \$\$int4out\$\$;$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE TYPE dump_test.int42 populated' => {
+ create_order => 42,
+ create_sql => 'CREATE TYPE dump_test.int42 (internallength = 4, input = dump_test.int42_in, output = dump_test.int42_out, alignment = int4, default = 42, passedbyvalue);',
+ regexp => qr/^CREATE TYPE int42 \(\n\s+INTERNALLENGTH = 4,\n\s+INPUT = int42_in,\n\s+OUTPUT = int42_out,\n\s+DEFAULT = '42',\n\s+ALIGNMENT = int4,\n\s+STORAGE = plain,\n\s+PASSEDBYVALUE\n\);/m,
+ like => {
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE TYPE dump_test.composite' => {
+ create_order => 43,
+ create_sql => 'CREATE TYPE dump_test.composite AS (f1 int, f2 dump_test.int42);',
+ regexp => qr/^CREATE TYPE composite AS \(\n\s+f1 integer,\n\s+f2 int42\n\);/m,
+ like => {
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE FOREIGN DATA WRAPPER dummy' => {
+ create_order => 35,
+ create_sql => 'CREATE FOREIGN DATA WRAPPER dummy;',
+ regexp => qr/CREATE FOREIGN DATA WRAPPER dummy;/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,
+ 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 SERVER s1 FOREIGN DATA WRAPPER dummy' => {
+ create_order => 36,
+ create_sql => 'CREATE SERVER s1 FOREIGN DATA WRAPPER dummy;',
+ regexp => qr/CREATE SERVER s1 FOREIGN DATA WRAPPER dummy;/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,
+ 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,
+ },
+ },
+#######################################
+# 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,
+# 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 LANGUAGE pltestlang' => {
+ create_order => 18,
+ create_sql => 'CREATE LANGUAGE pltestlang HANDLER dump_test.pltestlang_call_handler;',
+ regexp => qr/^CREATE PROCEDURAL LANGUAGE pltestlang HANDLER pltestlang_call_handler;$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ only_dump_test_schema => 1,
+ test_schema_plus_blobs => 1,
+ },
+ },
+ 'CREATE MATERIALIZED VIEW matview' => {
+ create_order => 20,
+ create_sql => 'CREATE MATERIALIZED VIEW dump_test.matview (col1) AS SELECT * FROM dump_test.test_table;',
+ regexp => qr/^CREATE MATERIALIZED VIEW matview AS\n\s+SELECT test_table\.col1\n\s+FROM test_table\n\s+WITH NO DATA;$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE MATERIALIZED VIEW matview_second' => {
+ create_order => 21,
+ create_sql => 'CREATE MATERIALIZED VIEW dump_test.matview_second (col1) AS SELECT * FROM dump_test.matview;',
+ regexp => qr/^CREATE MATERIALIZED VIEW matview_second AS\n\s+SELECT matview\.col1\n\s+FROM matview\n\s+WITH NO DATA;$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE POLICY p1 ON test_table' => {
+ create_order => 22,
+ create_sql => 'CREATE POLICY p1 ON dump_test.test_table USING (true) WITH CHECK (true);',
+ regexp => qr/^CREATE POLICY p1 ON test_table FOR ALL TO PUBLIC USING \(true\) WITH CHECK \(true\);$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ schema_only => 1,
+ section_post_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ section_pre_data => 1,
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ pg_dumpall_globals => 1,
+ },
+ },
+ 'CREATE POLICY p2 ON test_table FOR SELECT' => {
+ create_order => 24,
+ create_sql => 'CREATE POLICY p2 ON dump_test.test_table FOR SELECT TO dump_test USING (true);',
+ regexp => qr/^CREATE POLICY p2 ON test_table FOR SELECT TO dump_test USING \(true\);$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ schema_only => 1,
+ section_post_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ section_pre_data => 1,
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ pg_dumpall_globals => 1,
+ },
+ },
+ 'CREATE POLICY p3 ON test_table FOR INSERT' => {
+ create_order => 25,
+ create_sql => 'CREATE POLICY p3 ON dump_test.test_table FOR INSERT TO dump_test WITH CHECK (true);',
+ regexp => qr/^CREATE POLICY p3 ON test_table FOR INSERT TO dump_test WITH CHECK \(true\);$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ schema_only => 1,
+ section_post_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ section_pre_data => 1,
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ pg_dumpall_globals => 1,
+ },
+ },
+ 'CREATE POLICY p4 ON test_table FOR UPDATE' => {
+ create_order => 26,
+ create_sql => 'CREATE POLICY p4 ON dump_test.test_table FOR UPDATE TO dump_test USING (true) WITH CHECK (true);',
+ regexp => qr/^CREATE POLICY p4 ON test_table FOR UPDATE TO dump_test USING \(true\) WITH CHECK \(true\);$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ schema_only => 1,
+ section_post_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ section_pre_data => 1,
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ pg_dumpall_globals => 1,
+ },
+ },
+ 'CREATE POLICY p5 ON test_table FOR DELETE' => {
+ create_order => 27,
+ create_sql => 'CREATE POLICY p5 ON dump_test.test_table FOR DELETE TO dump_test USING (true);',
+ regexp => qr/^CREATE POLICY p5 ON test_table FOR DELETE TO dump_test USING \(true\);$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ schema_only => 1,
+ section_post_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ section_pre_data => 1,
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ pg_dumpall_globals => 1,
+ },
+ },
+ 'CREATE SCHEMA dump_test' => {
+ create_order => 2,
+ create_sql => 'CREATE SCHEMA dump_test;',
+ regexp => qr/^CREATE SCHEMA dump_test;$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE SCHEMA dump_test_second_schema' => {
+ create_order => 9,
+ create_sql => 'CREATE SCHEMA dump_test_second_schema;',
+ regexp => qr/^CREATE SCHEMA dump_test_second_schema;$/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,
+ 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 TABLE test_table' => {
+ create_order => 3,
+ create_sql => 'CREATE TABLE dump_test.test_table (col1 serial primary key, CHECK (col1 <= 1000));',
+ regexp => qr/^CREATE TABLE test_table \(\n\s+col1 integer NOT NULL,\n\s+CONSTRAINT test_table_col1_check CHECK \(\(col1 <= 1000\)\)\n\);$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE TABLE fk_reference_test_table' => {
+ create_order => 21,
+ create_sql => 'CREATE TABLE dump_test.fk_reference_test_table (col1 int primary key references dump_test.test_table);',
+ regexp => qr/^CREATE TABLE fk_reference_test_table \(\n\s+col1 integer NOT NULL\n\);$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE TABLE test_second_table' => {
+ create_order => 6,
+ create_sql => 'CREATE TABLE dump_test.test_second_table (col1 int, col2 text);',
+ regexp => qr/^CREATE TABLE test_second_table \(\n\s+col1 integer,\n\s+col2 text\n\);$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE TABLE test_third_table' => {
+ create_order => 11,
+ create_sql => 'CREATE TABLE dump_test_second_schema.test_third_table (col1 serial);',
+ regexp => qr/^CREATE TABLE test_third_table \(\n\s+col1 integer NOT NULL\n\);$/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,
+ 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 SEQUENCE test_table_col1_seq' => {
+ regexp => qr/^CREATE SEQUENCE test_table_col1_seq\n\s+START WITH 1\n\s+INCREMENT BY 1\n\s+NO MINVALUE\n\s+NO MAXVALUE\n\s+CACHE 1;$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ pg_dumpall_globals => 1,
+ section_post_data => 1,
+ },
+ },
+ 'CREATE SEQUENCE test_third_table_col1_seq' => {
+ regexp => qr/^CREATE SEQUENCE test_third_table_col1_seq\n\s+START WITH 1\n\s+INCREMENT BY 1\n\s+NO MINVALUE\n\s+NO MAXVALUE\n\s+CACHE 1;$/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,
+ 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 UNIQUE INDEX test_third_table_idx ON test_third_table' => {
+ create_order => 13,
+ create_sql => 'CREATE UNIQUE INDEX test_third_table_idx ON dump_test_second_schema.test_third_table (col1);',
+ regexp => qr/^CREATE UNIQUE INDEX test_third_table_idx ON test_third_table USING btree \(col1\);$/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,
+ schema_only => 1,
+ section_post_data => 1,
+ },
+ unlike => {
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ test_schema_plus_blobs => 1,
+ },
+ },
+ 'CREATE ... commands' => { # catch-all for CREATE
+ regexp => qr/^CREATE /m,
+ like => { }, # use more-specific options above
+ unlike => {
+ column_inserts => 1,
+ data_only => 1,
+ section_data => 1,
+ },
+ },
+ 'DROP EXTENSION plpgsql' => {
+ regexp => qr/^DROP EXTENSION plpgsql;$/m,
+ like => {
+ clean => 1,
+ },
+ unlike => {
+ clean_if_exists => 1,
+ },
+ },
+ 'DROP FUNCTION dump_test.pltestlang_call_handler()' => {
+ regexp => qr/^DROP FUNCTION dump_test\.pltestlang_call_handler\(\);$/m,
+ like => {
+ clean => 1,
+ },
+ unlike => {
+ clean_if_exists => 1,
+ },
+ },
+ 'DROP LANGUAGE pltestlang' => {
+ regexp => qr/^DROP PROCEDURAL LANGUAGE pltestlang;$/m,
+ like => {
+ clean => 1,
+ },
+ unlike => {
+ clean_if_exists => 1,
+ },
+ },
+ 'DROP SCHEMA dump_test' => {
+ regexp => qr/^DROP SCHEMA dump_test;$/m,
+ like => {
+ clean => 1,
+ },
+ unlike => {
+ clean_if_exists => 1,
+ },
+ },
+ 'DROP SCHEMA dump_test_second_schema' => {
+ regexp => qr/^DROP SCHEMA dump_test_second_schema;$/m,
+ like => {
+ clean => 1,
+ },
+ unlike => {
+ clean_if_exists => 1,
+ },
+ },
+ 'DROP TABLE test_table' => {
+ regexp => qr/^DROP TABLE dump_test\.test_table;$/m,
+ like => {
+ clean => 1,
+ },
+ unlike => {
+ clean_if_exists => 1,
+ },
+ },
+ 'DROP TABLE fk_reference_test_table' => {
+ regexp => qr/^DROP TABLE dump_test\.fk_reference_test_table;$/m,
+ like => {
+ clean => 1,
+ },
+ unlike => {
+ clean_if_exists => 1,
+ },
+ },
+ 'DROP TABLE test_second_table' => {
+ regexp => qr/^DROP TABLE dump_test\.test_second_table;$/m,
+ like => {
+ clean => 1,
+ },
+ unlike => {
+ clean_if_exists => 1,
+ },
+ },
+ 'DROP TABLE test_third_table' => {
+ regexp => qr/^DROP TABLE dump_test_second_schema\.test_third_table;$/m,
+ like => {
+ clean => 1,
+ },
+ unlike => {
+ clean_if_exists => 1,
+ },
+ },
+ 'DROP EXTENSION IF EXISTS plpgsql' => {
+ regexp => qr/^DROP EXTENSION IF EXISTS plpgsql;$/m,
+ like => {
+ clean_if_exists => 1,
+ },
+ unlike => {
+ clean => 1,
+ },
+ },
+ 'DROP FUNCTION IF EXISTS dump_test.pltestlang_call_handler()' => {
+ regexp => qr/^DROP FUNCTION IF EXISTS dump_test\.pltestlang_call_handler\(\);$/m,
+ like => {
+ clean_if_exists => 1,
+ },
+ unlike => {
+ clean => 1,
+ },
+ },
+ 'DROP LANGUAGE IF EXISTS pltestlang' => {
+ regexp => qr/^DROP PROCEDURAL LANGUAGE IF EXISTS pltestlang;$/m,
+ like => {
+ clean_if_exists => 1,
+ },
+ unlike => {
+ clean => 1,
+ },
+ },
+ 'DROP SCHEMA IF EXISTS dump_test' => {
+ regexp => qr/^DROP SCHEMA IF EXISTS dump_test;$/m,
+ like => {
+ clean_if_exists => 1,
+ },
+ unlike => {
+ clean => 1,
+ },
+ },
+ 'DROP SCHEMA IF EXISTS dump_test_second_schema' => {
+ regexp => qr/^DROP SCHEMA IF EXISTS dump_test_second_schema;$/m,
+ like => {
+ clean_if_exists => 1,
+ },
+ unlike => {
+ clean => 1,
+ },
+ },
+ 'DROP TABLE IF EXISTS test_table' => {
+ regexp => qr/^DROP TABLE IF EXISTS dump_test\.test_table;$/m,
+ like => {
+ clean_if_exists => 1,
+ },
+ unlike => {
+ clean => 1,
+ },
+ },
+ 'DROP TABLE IF EXISTS test_second_table' => {
+ regexp => qr/^DROP TABLE IF EXISTS dump_test\.test_second_table;$/m,
+ like => {
+ clean_if_exists => 1,
+ },
+ unlike => {
+ clean => 1,
+ },
+ },
+ 'DROP TABLE IF EXISTS test_third_table' => {
+ regexp => qr/^DROP TABLE IF EXISTS dump_test_second_schema\.test_third_table;$/m,
+ like => {
+ clean_if_exists => 1,
+ },
+ unlike => {
+ clean => 1,
+ },
+ },
+ 'DROP ... commands' => { # catch-all for DROP
+ regexp => qr/^DROP /m,
+ like => { }, # use more-specific options above
+ unlike => {
+ binary_upgrade => 1,
+ column_inserts => 1,
+ createdb => 1,
+ data_only => 1,
+ defaults => 1,
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ schema_only => 1,
+ },
+ },
+ 'GRANT USAGE ON SCHEMA dump_test_second_schema' => {
+ create_order => 10,
+ create_sql => 'GRANT USAGE ON SCHEMA dump_test_second_schema TO dump_test;',
+ regexp => qr/^GRANT USAGE ON SCHEMA dump_test_second_schema TO dump_test;$/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_owner => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ },
+ unlike => {
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ test_schema_plus_blobs => 1,
+ },
+ },
+ 'GRANT SELECT ON TABLE test_table' => {
+ create_order => 5,
+ create_sql => 'GRANT SELECT ON TABLE dump_test.test_table TO dump_test;',
+ regexp => qr/^GRANT SELECT ON TABLE test_table TO dump_test;$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table_data => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ pg_dumpall_globals => 1,
+ },
+ },
+ 'GRANT SELECT ON TABLE test_third_table' => {
+ create_order => 19,
+ create_sql => 'GRANT SELECT ON TABLE dump_test_second_schema.test_third_table TO dump_test;',
+ regexp => qr/^GRANT SELECT ON TABLE test_third_table TO dump_test;$/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_owner => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ },
+ unlike => {
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ test_schema_plus_blobs => 1,
+ },
+ },
+ 'GRANT ALL ON SEQUENCE test_third_table_col1_seq' => {
+ create_order => 28,
+ create_sql => 'GRANT ALL ON SEQUENCE dump_test_second_schema.test_third_table_col1_seq TO dump_test;',
+ regexp => qr/^GRANT ALL ON SEQUENCE test_third_table_col1_seq TO dump_test;$/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_owner => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ },
+ unlike => {
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ test_schema_plus_blobs => 1,
+ },
+ },
+ 'GRANT INSERT(col1) ON TABLE test_second_table' => {
+ create_order => 8,
+ create_sql => 'GRANT INSERT (col1) ON TABLE dump_test.test_second_table TO dump_test;',
+ regexp => qr/^GRANT INSERT\(col1\) ON TABLE test_second_table TO dump_test;$/m,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ },
+ },
+ 'GRANT EXECUTE ON FUNCTION pg_sleep() TO dump_test' => {
+ create_order => 16,
+ create_sql => 'GRANT EXECUTE ON FUNCTION pg_sleep(float8) TO dump_test;',
+ regexp => qr/^GRANT ALL ON FUNCTION pg_sleep\(double precision\) TO dump_test;$/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_owner => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ },
+ unlike => {
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ test_schema_plus_blobs => 1,
+ },
+ },
+ 'GRANT commands' => { # catch-all for GRANT commands
+ regexp => qr/^GRANT /m,
+ like => { }, # use more-specific options above
+ unlike => {
+ column_inserts => 1,
+ data_only => 1,
+ no_privs => 1,
+ section_data => 1,
+ },
+ },
+ 'REFRESH MATERIALIZED VIEW matview' => {
+ regexp => qr/^REFRESH MATERIALIZED VIEW matview;$/m,
+ like => {
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ test_schema_plus_blobs => 1,
+ section_post_data => 1,
+ },
+ unlike => {
+ binary_upgrade => 1,
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ },
+ },
+ 'REFRESH MATERIALIZED VIEW matview_second' => {
+ regexp => qr/^REFRESH MATERIALIZED VIEW matview;\n.*REFRESH MATERIALIZED VIEW matview_second;$/ms,
+ like => {
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ test_schema_plus_blobs => 1,
+ section_post_data => 1,
+ },
+ unlike => {
+ binary_upgrade => 1,
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ },
+ },
+ 'REVOKE EXECUTE ON FUNCTION pg_sleep() FROM public' => {
+ create_order => 15,
+ create_sql => 'REVOKE EXECUTE ON FUNCTION pg_sleep(float8) FROM public;',
+ regexp => qr/^REVOKE ALL ON FUNCTION pg_sleep\(double precision\) FROM PUBLIC;$/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_owner => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ },
+ unlike => {
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ test_schema_plus_blobs => 1,
+ },
+ },
+ 'REVOKE CREATE ON SCHEMA public FROM public' => {
+ create_order => 16,
+ create_sql => 'REVOKE CREATE ON SCHEMA public FROM public;',
+ regexp => qr/^REVOKE ALL ON SCHEMA public FROM PUBLIC;\nGRANT USAGE ON SCHEMA public TO PUBLIC;$/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_owner => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ },
+ unlike => {
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ test_schema_plus_blobs => 1,
+ },
+ },
+ 'REVOKE USAGE ON LANGUAGE plpgsql FROM public' => {
+ create_order => 16,
+ create_sql => 'REVOKE USAGE ON LANGUAGE plpgsql FROM public;',
+ regexp => qr/^REVOKE ALL ON LANGUAGE plpgsql FROM PUBLIC;$/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_owner => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ },
+ unlike => {
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ test_schema_plus_blobs => 1,
+ },
+ },
+ 'REVOKE commands' => { # catch-all for REVOKE commands
+ regexp => qr/^REVOKE /m,
+ like => { }, # use more-specific options above
+ unlike => {
+ column_inserts => 1,
+ data_only => 1,
+ no_privs => 1,
+ pg_dumpall_globals => 1,
+ },
+ },
+);
+
+#########################################
+# Create a PG instance to test actually dumping from
+
+my $node = get_new_node('main');
+$node->init;
+$node->start;
+
+my $port = $node->port;
+
+# Start with 2 because of command_exit_is() tests below
+my $num_tests = 2;
+
+foreach my $run (sort keys %pgdump_runs) {
+ my $test_key = $run;
+
+ # Each run of pg_dump is a test itself
+ $num_tests++;
+
+ # If there is a restore cmd, that's another test
+ if ($pgdump_runs{$run}->{restore_cmd}) {
+ $num_tests++;
+ }
+
+ if ($pgdump_runs{$run}->{test_key}) {
+ $test_key = $pgdump_runs{$run}->{test_key};
+ }
+
+ # Then count all the tests run against each run
+ foreach my $test (sort keys %tests) {
+ if ($tests{$test}->{like}->{$test_key}) {
+ $num_tests++;
+ }
+
+ if ($tests{$test}->{unlike}->{$test_key}) {
+ $num_tests++;
+ }
+ }
+}
+plan tests => $num_tests;
+
+#########################################
+# Set up schemas, tables, etc, to be dumped.
+
+# Build up the create statements
+my $create_sql = '';
+
+foreach my $test (
+ sort {
+ if ($tests{$a}->{create_order} and $tests{$b}->{create_order}) {
+ $tests{$a}->{create_order} <=> $tests{$b}->{create_order};
+ } elsif ($tests{$a}->{create_order}) {
+ -1;
+ } elsif ($tests{$b}->{create_order}) {
+ 1;
+ } else {
+ 0;
+ }
+ } keys %tests) {
+ if ($tests{$test}->{create_sql}) {
+ $create_sql .= $tests{$test}->{create_sql};
+ }
+}
+
+# Send the combined set of commands to psql
+$node->safe_psql('postgres', $create_sql);
+
+#########################################
+# Test connecting to a non-existent database
+
+command_exit_is([ 'pg_dump', '-p', "$port", 'qqq' ],
+ 1, 'pg_dump: [archiver (db)] connection to database "qqq" failed: FATAL: database "qqq" does not exist');
+
+command_exit_is([ 'pg_dump', '-p', "$port", '--role=dump_test' ],
+ 1, 'pg_dump: [archiver (db)] query failed: ERROR: permission denied for');
+
+#########################################
+# Run all runs
+
+foreach my $run (sort keys %pgdump_runs) {
+
+ my $test_key = $run;
+
+ $node->command_ok(\@{ $pgdump_runs{$run}->{dump_cmd} }, "$run: pg_dump runs");
+
+ if ($pgdump_runs{$run}->{restore_cmd}) {
+ $node->command_ok(\@{ $pgdump_runs{$run}->{restore_cmd} }, "$run: pg_restore runs");
+ }
+
+ if ($pgdump_runs{$run}->{test_key}) {
+ $test_key = $pgdump_runs{$run}->{test_key};
+ }
+
+ my $output_file = slurp_file("$tempdir/${run}.sql");
+
+ #########################################
+ # Run all tests where this run is included
+ # as either a 'like' or 'unlike' test.
+
+ foreach my $test (sort keys %tests) {
+ if ($tests{$test}->{like}->{$test_key}) {
+ like($output_file, $tests{$test}->{regexp}, "$run: dumps $test");
+ }
+
+ if ($tests{$test}->{unlike}->{$test_key}) {
+ unlike($output_file, $tests{$test}->{regexp}, "$run: does not dump $test");
+ }
+ }
+}
+
+#########################################
+# Stop the database instance, which will be removed at the end of the tests.
+
+$node->stop('fast');
--
2.5.0
signature.asc
Description: Digital signature
