Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitbc01b45

Browse files
committed
Make pg_dump --data-only try to order the table dumps so that foreign keys'
referenced tables are dumped before the referencing tables. This avoidsfailures when the data is loaded with the FK constraints already active.If no such ordering is possible because of circular or self-referentialconstraints, print a NOTICE to warn the user about it.
1 parenta0b76dc commitbc01b45

File tree

3 files changed

+98
-8
lines changed

3 files changed

+98
-8
lines changed

‎src/bin/pg_dump/pg_dump.c‎

Lines changed: 65 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@
1212
*by PostgreSQL
1313
*
1414
* IDENTIFICATION
15-
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.499 2008/07/30 19:35:13 tgl Exp $
15+
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.500 2008/09/08 15:26:23 tgl Exp $
1616
*
1717
*-------------------------------------------------------------------------
1818
*/
@@ -166,6 +166,7 @@ static void dumpACL(Archive *fout, CatalogId objCatId, DumpId objDumpId,
166166
staticvoidgetDependencies(void);
167167
staticvoidgetDomainConstraints(TypeInfo*tinfo);
168168
staticvoidgetTableData(TableInfo*tblinfo,intnumTables,booloids);
169+
staticvoidgetTableDataFKConstraints(void);
169170
staticchar*format_function_arguments(FuncInfo*finfo,char*funcargs);
170171
staticchar*format_function_arguments_old(FuncInfo*finfo,intnallargs,
171172
char**allargtypes,
@@ -659,7 +660,11 @@ main(int argc, char **argv)
659660
guessConstraintInheritance(tblinfo,numTables);
660661

661662
if (!schemaOnly)
663+
{
662664
getTableData(tblinfo,numTables,oids);
665+
if (dataOnly)
666+
getTableDataFKConstraints();
667+
}
663668

664669
if (outputBlobs&&hasBlobs(g_fout))
665670
{
@@ -1392,10 +1397,59 @@ getTableData(TableInfo *tblinfo, int numTables, bool oids)
13921397
tdinfo->tdtable=&(tblinfo[i]);
13931398
tdinfo->oids=oids;
13941399
addObjectDependency(&tdinfo->dobj,tblinfo[i].dobj.dumpId);
1400+
1401+
tblinfo[i].dataObj=tdinfo;
13951402
}
13961403
}
13971404
}
13981405

1406+
/*
1407+
* getTableDataFKConstraints -
1408+
* add dump-order dependencies reflecting foreign key constraints
1409+
*
1410+
* This code is executed only in a data-only dump --- in schema+data dumps
1411+
* we handle foreign key issues by not creating the FK constraints until
1412+
* after the data is loaded. In a data-only dump, however, we want to
1413+
* order the table data objects in such a way that a table's referenced
1414+
* tables are restored first. (In the presence of circular references or
1415+
* self-references this may be impossible; we'll detect and complain about
1416+
* that during the dependency sorting step.)
1417+
*/
1418+
staticvoid
1419+
getTableDataFKConstraints(void)
1420+
{
1421+
DumpableObject**dobjs;
1422+
intnumObjs;
1423+
inti;
1424+
1425+
/* Search through all the dumpable objects for FK constraints */
1426+
getDumpableObjects(&dobjs,&numObjs);
1427+
for (i=0;i<numObjs;i++)
1428+
{
1429+
if (dobjs[i]->objType==DO_FK_CONSTRAINT)
1430+
{
1431+
ConstraintInfo*cinfo= (ConstraintInfo*)dobjs[i];
1432+
TableInfo*ftable;
1433+
1434+
/* Not interesting unless both tables are to be dumped */
1435+
if (cinfo->contable==NULL||
1436+
cinfo->contable->dataObj==NULL)
1437+
continue;
1438+
ftable=findTableByOid(cinfo->confrelid);
1439+
if (ftable==NULL||
1440+
ftable->dataObj==NULL)
1441+
continue;
1442+
/*
1443+
* Okay, make referencing table's TABLE_DATA object depend on
1444+
* the referenced table's TABLE_DATA object.
1445+
*/
1446+
addObjectDependency(&cinfo->contable->dataObj->dobj,
1447+
ftable->dataObj->dobj.dumpId);
1448+
}
1449+
}
1450+
free(dobjs);
1451+
}
1452+
13991453

14001454
/*
14011455
* guessConstraintInheritance:
@@ -3626,6 +3680,7 @@ getIndexes(TableInfo tblinfo[], int numTables)
36263680
constrinfo[j].condomain=NULL;
36273681
constrinfo[j].contype=contype;
36283682
constrinfo[j].condef=NULL;
3683+
constrinfo[j].confrelid=InvalidOid;
36293684
constrinfo[j].conindex=indxinfo[j].dobj.dumpId;
36303685
constrinfo[j].conislocal= true;
36313686
constrinfo[j].separate= true;
@@ -3666,10 +3721,11 @@ getConstraints(TableInfo tblinfo[], int numTables)
36663721
ConstraintInfo*constrinfo;
36673722
PQExpBufferquery;
36683723
PGresult*res;
3669-
inti_condef,
3670-
i_contableoid,
3724+
inti_contableoid,
36713725
i_conoid,
3672-
i_conname;
3726+
i_conname,
3727+
i_confrelid,
3728+
i_condef;
36733729
intntups;
36743730

36753731
/* pg_constraint was created in 7.3, so nothing to do if older */
@@ -3697,7 +3753,7 @@ getConstraints(TableInfo tblinfo[], int numTables)
36973753

36983754
resetPQExpBuffer(query);
36993755
appendPQExpBuffer(query,
3700-
"SELECT tableoid, oid, conname, "
3756+
"SELECT tableoid, oid, conname,confrelid,"
37013757
"pg_catalog.pg_get_constraintdef(oid) as condef "
37023758
"FROM pg_catalog.pg_constraint "
37033759
"WHERE conrelid = '%u'::pg_catalog.oid "
@@ -3711,6 +3767,7 @@ getConstraints(TableInfo tblinfo[], int numTables)
37113767
i_contableoid=PQfnumber(res,"tableoid");
37123768
i_conoid=PQfnumber(res,"oid");
37133769
i_conname=PQfnumber(res,"conname");
3770+
i_confrelid=PQfnumber(res,"confrelid");
37143771
i_condef=PQfnumber(res,"condef");
37153772

37163773
constrinfo= (ConstraintInfo*)malloc(ntups*sizeof(ConstraintInfo));
@@ -3727,6 +3784,7 @@ getConstraints(TableInfo tblinfo[], int numTables)
37273784
constrinfo[j].condomain=NULL;
37283785
constrinfo[j].contype='f';
37293786
constrinfo[j].condef=strdup(PQgetvalue(res,j,i_condef));
3787+
constrinfo[j].confrelid=atooid(PQgetvalue(res,j,i_confrelid));
37303788
constrinfo[j].conindex=0;
37313789
constrinfo[j].conislocal= true;
37323790
constrinfo[j].separate= true;
@@ -3810,6 +3868,7 @@ getDomainConstraints(TypeInfo *tinfo)
38103868
constrinfo[i].condomain=tinfo;
38113869
constrinfo[i].contype='c';
38123870
constrinfo[i].condef=strdup(PQgetvalue(res,i,i_consrc));
3871+
constrinfo[i].confrelid=InvalidOid;
38133872
constrinfo[i].conindex=0;
38143873
constrinfo[i].conislocal= true;
38153874
constrinfo[i].separate= false;
@@ -4788,6 +4847,7 @@ getTableAttrs(TableInfo *tblinfo, int numTables)
47884847
constrs[j].condomain=NULL;
47894848
constrs[j].contype='c';
47904849
constrs[j].condef=strdup(PQgetvalue(res,j,3));
4850+
constrs[j].confrelid=InvalidOid;
47914851
constrs[j].conindex=0;
47924852
constrs[j].conislocal= (PQgetvalue(res,j,4)[0]=='t');
47934853
constrs[j].separate= false;

‎src/bin/pg_dump/pg_dump.h‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
9-
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.h,v 1.140 2008/05/09 23:32:04 tgl Exp $
9+
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.h,v 1.141 2008/09/08 15:26:23 tgl Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -280,6 +280,7 @@ typedef struct _tableInfo
280280
*/
281281
intnumParents;/* number of (immediate) parent tables */
282282
struct_tableInfo**parents;/* TableInfos of immediate parents */
283+
struct_tableDataInfo*dataObj;/* TableDataInfo, if dumping its data */
283284
}TableInfo;
284285

285286
typedefstruct_attrDefInfo
@@ -352,6 +353,7 @@ typedef struct _constraintInfo
352353
TypeInfo*condomain;/* NULL if table constraint */
353354
charcontype;
354355
char*condef;/* definition, if CHECK or FOREIGN KEY */
356+
Oidconfrelid;/* referenced table, if FOREIGN KEY */
355357
DumpIdconindex;/* identifies associated index if any */
356358
boolconislocal;/* TRUE if constraint has local definition */
357359
boolseparate;/* TRUE if must dump as separate item */

‎src/bin/pg_dump/pg_dump_sort.c‎

Lines changed: 30 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump_sort.c,v 1.20 2008/01/01 19:45:55 momjian Exp $
12+
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump_sort.c,v 1.21 2008/09/08 15:26:23 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -946,6 +946,30 @@ repairDependencyLoop(DumpableObject **loop,
946946
}
947947
}
948948

949+
/*
950+
* If all the objects are TABLE_DATA items, what we must have is a
951+
* circular set of foreign key constraints (or a single self-referential
952+
* table). Print an appropriate complaint and break the loop arbitrarily.
953+
*/
954+
for (i=0;i<nLoop;i++)
955+
{
956+
if (loop[i]->objType!=DO_TABLE_DATA)
957+
break;
958+
}
959+
if (i >=nLoop)
960+
{
961+
write_msg(NULL,"NOTICE: there are circular foreign-key constraints among these table(s):\n");
962+
for (i=0;i<nLoop;i++)
963+
write_msg(NULL," %s\n",loop[i]->name);
964+
write_msg(NULL,"You may not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.\n");
965+
write_msg(NULL,"Consider using a full dump instead of a --data-only dump to avoid this problem.\n");
966+
if (nLoop>1)
967+
removeObjectDependency(loop[0],loop[1]->dumpId);
968+
else/* must be a self-dependency */
969+
removeObjectDependency(loop[0],loop[0]->dumpId);
970+
return;
971+
}
972+
949973
/*
950974
* If we can't find a principled way to break the loop, complain and break
951975
* it in an arbitrary fashion.
@@ -958,7 +982,11 @@ repairDependencyLoop(DumpableObject **loop,
958982
describeDumpableObject(loop[i],buf,sizeof(buf));
959983
write_msg(modulename," %s\n",buf);
960984
}
961-
removeObjectDependency(loop[0],loop[1]->dumpId);
985+
986+
if (nLoop>1)
987+
removeObjectDependency(loop[0],loop[1]->dumpId);
988+
else/* must be a self-dependency */
989+
removeObjectDependency(loop[0],loop[0]->dumpId);
962990
}
963991

964992
/*

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp