Skip to content

Commit e90710f

Browse files
alvherrealvherre
committed
Dump an unvalidated constraint separately from its table
This allows possibly violating data to be imported before the constraint is installed. Bug reported by Thom Brown
1 parent f21fc7f commit e90710f

File tree

1 file changed

+33
-11
lines changed

1 file changed

+33
-11
lines changed

‎src/bin/pg_dump/pg_dump.c

Lines changed: 33 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -5953,11 +5953,22 @@ getTableAttrs(TableInfo *tblinfo, int numTables)
59535953
tbinfo->dobj.name);
59545954

59555955
resetPQExpBuffer(q);
5956-
if (g_fout->remoteVersion >= 80400)
5956+
if (g_fout->remoteVersion >= 90100)
59575957
{
59585958
appendPQExpBuffer(q, "SELECT tableoid, oid, conname, "
59595959
"pg_catalog.pg_get_constraintdef(oid) AS consrc, "
5960-
"conislocal "
5960+
"conislocal, convalidated "
5961+
"FROM pg_catalog.pg_constraint "
5962+
"WHERE conrelid = '%u'::pg_catalog.oid "
5963+
" AND contype = 'c' "
5964+
"ORDER BY conname",
5965+
tbinfo->dobj.catId.oid);
5966+
}
5967+
else if (g_fout->remoteVersion >= 80400)
5968+
{
5969+
appendPQExpBuffer(q, "SELECT tableoid, oid, conname, "
5970+
"pg_catalog.pg_get_constraintdef(oid) AS consrc, "
5971+
"conislocal, true AS convalidated "
59615972
"FROM pg_catalog.pg_constraint "
59625973
"WHERE conrelid = '%u'::pg_catalog.oid "
59635974
" AND contype = 'c' "
@@ -5968,7 +5979,7 @@ getTableAttrs(TableInfo *tblinfo, int numTables)
59685979
{
59695980
appendPQExpBuffer(q, "SELECT tableoid, oid, conname, "
59705981
"pg_catalog.pg_get_constraintdef(oid) AS consrc, "
5971-
"true AS conislocal "
5982+
"true AS conislocal, true AS convalidated "
59725983
"FROM pg_catalog.pg_constraint "
59735984
"WHERE conrelid = '%u'::pg_catalog.oid "
59745985
" AND contype = 'c' "
@@ -5980,7 +5991,7 @@ getTableAttrs(TableInfo *tblinfo, int numTables)
59805991
/* no pg_get_constraintdef, must use consrc */
59815992
appendPQExpBuffer(q, "SELECT tableoid, oid, conname, "
59825993
"'CHECK (' || consrc || ')' AS consrc, "
5983-
"true AS conislocal "
5994+
"true AS conislocal, true AS convalidated "
59845995
"FROM pg_catalog.pg_constraint "
59855996
"WHERE conrelid = '%u'::pg_catalog.oid "
59865997
" AND contype = 'c' "
@@ -5993,7 +6004,7 @@ getTableAttrs(TableInfo *tblinfo, int numTables)
59936004
appendPQExpBuffer(q, "SELECT tableoid, 0 AS oid, "
59946005
"rcname AS conname, "
59956006
"'CHECK (' || rcsrc || ')' AS consrc, "
5996-
"true AS conislocal "
6007+
"true AS conislocal, true AS convalidated "
59976008
"FROM pg_relcheck "
59986009
"WHERE rcrelid = '%u'::oid "
59996010
"ORDER BY rcname",
@@ -6004,7 +6015,7 @@ getTableAttrs(TableInfo *tblinfo, int numTables)
60046015
appendPQExpBuffer(q, "SELECT tableoid, oid, "
60056016
"rcname AS conname, "
60066017
"'CHECK (' || rcsrc || ')' AS consrc, "
6007-
"true AS conislocal "
6018+
"true AS conislocal, true AS convalidated "
60086019
"FROM pg_relcheck "
60096020
"WHERE rcrelid = '%u'::oid "
60106021
"ORDER BY rcname",
@@ -6017,7 +6028,7 @@ getTableAttrs(TableInfo *tblinfo, int numTables)
60176028
"(SELECT oid FROM pg_class WHERE relname = 'pg_relcheck') AS tableoid, "
60186029
"oid, rcname AS conname, "
60196030
"'CHECK (' || rcsrc || ')' AS consrc, "
6020-
"true AS conislocal "
6031+
"true AS conislocal, true AS convalidated "
60216032
"FROM pg_relcheck "
60226033
"WHERE rcrelid = '%u'::oid "
60236034
"ORDER BY rcname",
@@ -6042,6 +6053,8 @@ getTableAttrs(TableInfo *tblinfo, int numTables)
60426053

60436054
for (j = 0; j < numConstrs; j++)
60446055
{
6056+
bool validated = PQgetvalue(res, j, 5)[0] == 't';
6057+
60456058
constrs[j].dobj.objType = DO_CONSTRAINT;
60466059
constrs[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, 0));
60476060
constrs[j].dobj.catId.oid = atooid(PQgetvalue(res, j, 1));
@@ -6057,18 +6070,27 @@ getTableAttrs(TableInfo *tblinfo, int numTables)
60576070
constrs[j].condeferrable = false;
60586071
constrs[j].condeferred = false;
60596072
constrs[j].conislocal = (PQgetvalue(res, j, 4)[0] == 't');
6060-
constrs[j].separate = false;
6073+
/*
6074+
* An unvalidated constraint needs to be dumped separately, so
6075+
* that potentially-violating existing data is loaded before
6076+
* the constraint.
6077+
*/
6078+
constrs[j].separate = !validated;
60616079

60626080
constrs[j].dobj.dump = tbinfo->dobj.dump;
60636081

60646082
/*
60656083
* Mark the constraint as needing to appear before the table
60666084
* --- this is so that any other dependencies of the
60676085
* constraint will be emitted before we try to create the
6068-
* table.
6086+
* table. If the constraint is not validated, it will be
6087+
* dumped after data is loaded anyway, so don't do it. (There's
6088+
* an automatic dependency in the opposite direction anyway, so
6089+
* don't need to add one manually here.)
60696090
*/
6070-
addObjectDependency(&tbinfo->dobj,
6071-
constrs[j].dobj.dumpId);
6091+
if (validated)
6092+
addObjectDependency(&tbinfo->dobj,
6093+
constrs[j].dobj.dumpId);
60726094

60736095
/*
60746096
* If the constraint is inherited, this will be detected later

0 commit comments

Comments
 (0)