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

Commit05649b8

Browse files
committed
Rewrite pg_dump's comment-dumping code to pull over all the comments
in one query, rather than making a separate query for each object thatcould have a comment. This costs relatively little space (a few tens ofK typically) and saves substantial time in databases with many objects.I find it reduces the runtime of 'pg_dump -s regression' by about athird.
1 parentc8aaa5c commit05649b8

File tree

1 file changed

+215
-93
lines changed

1 file changed

+215
-93
lines changed

‎src/bin/pg_dump/pg_dump.c

Lines changed: 215 additions & 93 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.367 2004/03/03 21:28:54 tgl Exp $
15+
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.368 2004/03/20 20:09:45 tgl Exp $
1616
*
1717
*-------------------------------------------------------------------------
1818
*/
@@ -67,6 +67,15 @@ extern intoptind,
6767
opterr;
6868

6969

70+
typedefstruct
71+
{
72+
constchar*descr;/* comment for an object */
73+
Oidclassoid;/* object class (catalog OID) */
74+
Oidobjoid;/* object OID */
75+
intobjsubid;/* subobject (table column #) */
76+
}CommentItem;
77+
78+
7079
/* global decls */
7180
boolg_verbose;/* User wants verbose narration of our
7281
* activities. */
@@ -105,6 +114,9 @@ static void dumpTableData(Archive *fout, TableDataInfo *tdinfo);
105114
staticvoiddumpComment(Archive*fout,constchar*target,
106115
constchar*namespace,constchar*owner,
107116
CatalogIdcatalogId,intsubid,DumpIddumpId);
117+
staticintfindComments(Archive*fout,Oidclassoid,Oidobjoid,
118+
CommentItem**items);
119+
staticintcollectComments(Archive*fout,CommentItem**items);
108120
staticvoiddumpDumpableObject(Archive*fout,DumpableObject*dobj);
109121
staticvoiddumpNamespace(Archive*fout,NamespaceInfo*nspinfo);
110122
staticvoiddumpType(Archive*fout,TypeInfo*tinfo);
@@ -3880,141 +3892,81 @@ dumpComment(Archive *fout, const char *target,
38803892
constchar*namespace,constchar*owner,
38813893
CatalogIdcatalogId,intsubid,DumpIddumpId)
38823894
{
3883-
PGresult*res;
3884-
PQExpBufferquery;
3885-
inti_description;
3895+
CommentItem*comments;
3896+
intncomments;
38863897

38873898
/* Comments are SCHEMA not data */
38883899
if (dataOnly)
38893900
return;
38903901

3891-
/*
3892-
* Note we do NOT change source schema here; preserve the caller's
3893-
* setting, instead.
3894-
*/
3902+
/* Search for comments associated with catalogId, using table */
3903+
ncomments=findComments(fout,catalogId.tableoid,catalogId.oid,
3904+
&comments);
38953905

3896-
/* Build query to find comment */
3897-
3898-
query=createPQExpBuffer();
3899-
3900-
if (fout->remoteVersion >=70300)
3901-
{
3902-
appendPQExpBuffer(query,
3903-
"SELECT description FROM pg_catalog.pg_description "
3904-
"WHERE classoid = '%u'::pg_catalog.oid and "
3905-
"objoid = '%u'::pg_catalog.oid and objsubid = %d",
3906-
catalogId.tableoid,catalogId.oid,subid);
3907-
}
3908-
elseif (fout->remoteVersion >=70200)
3909-
{
3910-
appendPQExpBuffer(query,
3911-
"SELECT description FROM pg_description "
3912-
"WHERE classoid = '%u'::oid and "
3913-
"objoid = '%u'::oid and objsubid = %d",
3914-
catalogId.tableoid,catalogId.oid,subid);
3915-
}
3916-
else
3906+
/* Is there one matching the subid? */
3907+
while (ncomments>0)
39173908
{
3918-
/* Note: this will fail to find attribute comments in pre-7.2... */
3919-
appendPQExpBuffer(query,"SELECT description FROM pg_description WHERE objoid = '%u'::oid",catalogId.oid);
3909+
if (comments->objsubid==subid)
3910+
break;
3911+
comments++;
3912+
ncomments--;
39203913
}
39213914

3922-
/* Execute query */
3923-
3924-
res=PQexec(g_conn,query->data);
3925-
check_sql_result(res,g_conn,query->data,PGRES_TUPLES_OK);
3926-
39273915
/* If a comment exists, build COMMENT ON statement */
3928-
3929-
if (PQntuples(res)==1)
3916+
if (ncomments>0)
39303917
{
3931-
i_description=PQfnumber(res,"description");
3932-
resetPQExpBuffer(query);
3918+
PQExpBufferquery=createPQExpBuffer();
3919+
39333920
appendPQExpBuffer(query,"COMMENT ON %s IS ",target);
3934-
appendStringLiteral(query,PQgetvalue(res,0,i_description), false);
3921+
appendStringLiteral(query,comments->descr, false);
39353922
appendPQExpBuffer(query,";\n");
39363923

39373924
ArchiveEntry(fout,nilCatalogId,createDumpId(),
39383925
target,namespace,owner,
39393926
"COMMENT",query->data,"",NULL,
39403927
&(dumpId),1,
39413928
NULL,NULL);
3942-
}
39433929

3944-
PQclear(res);
3945-
destroyPQExpBuffer(query);
3930+
destroyPQExpBuffer(query);
3931+
}
39463932
}
39473933

39483934
/*
39493935
* dumpTableComment --
39503936
*
39513937
* As above, but dump comments for both the specified table (or view)
3952-
* and its columns. For speed, we want to do this with only one query.
3938+
* and its columns.
39533939
*/
39543940
staticvoid
39553941
dumpTableComment(Archive*fout,TableInfo*tbinfo,
39563942
constchar*reltypename)
39573943
{
3958-
PGresult*res;
3944+
CommentItem*comments;
3945+
intncomments;
39593946
PQExpBufferquery;
39603947
PQExpBuffertarget;
3961-
inti_description;
3962-
inti_objsubid;
3963-
intntups;
3964-
inti;
39653948

39663949
/* Comments are SCHEMA not data */
39673950
if (dataOnly)
39683951
return;
39693952

3970-
/*
3971-
* Note we do NOT change source schema here; preserve the caller's
3972-
* setting, instead.
3973-
*/
3953+
/* Search for comments associated with relation, using table */
3954+
ncomments=findComments(fout,
3955+
tbinfo->dobj.catId.tableoid,
3956+
tbinfo->dobj.catId.oid,
3957+
&comments);
39743958

3975-
/* Build query to find comments */
3959+
/* If comments exist, build COMMENT ON statements */
3960+
if (ncomments <=0)
3961+
return;
39763962

39773963
query=createPQExpBuffer();
39783964
target=createPQExpBuffer();
39793965

3980-
if (fout->remoteVersion >=70300)
3981-
{
3982-
appendPQExpBuffer(query,"SELECT description, objsubid FROM pg_catalog.pg_description "
3983-
"WHERE classoid = '%u'::pg_catalog.oid and "
3984-
"objoid = '%u'::pg_catalog.oid "
3985-
"ORDER BY objoid, classoid, objsubid",
3986-
tbinfo->dobj.catId.tableoid,tbinfo->dobj.catId.oid);
3987-
}
3988-
elseif (fout->remoteVersion >=70200)
3989-
{
3990-
appendPQExpBuffer(query,"SELECT description, objsubid FROM pg_description "
3991-
"WHERE classoid = '%u'::oid and "
3992-
"objoid = '%u'::oid "
3993-
"ORDER BY objoid, classoid, objsubid",
3994-
tbinfo->dobj.catId.tableoid,tbinfo->dobj.catId.oid);
3995-
}
3996-
else
3997-
{
3998-
/* Note: this will fail to find attribute comments in pre-7.2... */
3999-
appendPQExpBuffer(query,"SELECT description, 0 as objsubid FROM pg_description WHERE objoid = '%u'::oid",
4000-
tbinfo->dobj.catId.oid);
4001-
}
4002-
4003-
/* Execute query */
4004-
4005-
res=PQexec(g_conn,query->data);
4006-
check_sql_result(res,g_conn,query->data,PGRES_TUPLES_OK);
4007-
4008-
i_description=PQfnumber(res,"description");
4009-
i_objsubid=PQfnumber(res,"objsubid");
4010-
4011-
/* If comments exist, build COMMENT ON statements */
4012-
4013-
ntups=PQntuples(res);
4014-
for (i=0;i<ntups;i++)
3966+
while (ncomments>0)
40153967
{
4016-
constchar*descr=PQgetvalue(res,i,i_description);
4017-
intobjsubid=atoi(PQgetvalue(res,i,i_objsubid));
3968+
constchar*descr=comments->descr;
3969+
intobjsubid=comments->objsubid;
40183970

40193971
if (objsubid==0)
40203972
{
@@ -4054,13 +4006,183 @@ dumpTableComment(Archive *fout, TableInfo *tbinfo,
40544006
&(tbinfo->dobj.dumpId),1,
40554007
NULL,NULL);
40564008
}
4009+
4010+
comments++;
4011+
ncomments--;
40574012
}
40584013

4059-
PQclear(res);
40604014
destroyPQExpBuffer(query);
40614015
destroyPQExpBuffer(target);
40624016
}
40634017

4018+
/*
4019+
* findComments --
4020+
*
4021+
* Find the comment(s), if any, associated with the given object. All the
4022+
* objsubid values associated with the given classoid/objoid are found with
4023+
* one search.
4024+
*/
4025+
staticint
4026+
findComments(Archive*fout,Oidclassoid,Oidobjoid,
4027+
CommentItem**items)
4028+
{
4029+
/* static storage for table of comments */
4030+
staticCommentItem*comments=NULL;
4031+
staticintncomments=-1;
4032+
4033+
CommentItem*middle=NULL;
4034+
CommentItem*low;
4035+
CommentItem*high;
4036+
intnmatch;
4037+
4038+
/* Get comments if we didn't already */
4039+
if (ncomments<0)
4040+
ncomments=collectComments(fout,&comments);
4041+
4042+
/*
4043+
* Pre-7.2, pg_description does not contain classoid, so collectComments
4044+
* just stores a zero. If there's a collision on object OID, well, you
4045+
* get duplicate comments.
4046+
*/
4047+
if (fout->remoteVersion<70200)
4048+
classoid=0;
4049+
4050+
/*
4051+
* Do binary search to find some item matching the object.
4052+
*/
4053+
low=&comments[0];
4054+
high=&comments[ncomments-1];
4055+
while (low <=high)
4056+
{
4057+
middle=low+ (high-low) /2;
4058+
4059+
if (classoid<middle->classoid)
4060+
high=middle-1;
4061+
elseif (classoid>middle->classoid)
4062+
low=middle+1;
4063+
elseif (objoid<middle->objoid)
4064+
high=middle-1;
4065+
elseif (objoid>middle->objoid)
4066+
low=middle+1;
4067+
else
4068+
break;/* found a match */
4069+
}
4070+
4071+
if (low>high)/* no matches */
4072+
{
4073+
*items=NULL;
4074+
return0;
4075+
}
4076+
4077+
/*
4078+
* Now determine how many items match the object. The search loop
4079+
* invariant still holds: only items between low and high inclusive
4080+
* could match.
4081+
*/
4082+
nmatch=1;
4083+
while (middle>low)
4084+
{
4085+
if (classoid!=middle[-1].classoid||
4086+
objoid!=middle[-1].objoid)
4087+
break;
4088+
middle--;
4089+
nmatch++;
4090+
}
4091+
4092+
*items=middle;
4093+
4094+
middle+=nmatch;
4095+
while (middle <=high)
4096+
{
4097+
if (classoid!=middle->classoid||
4098+
objoid!=middle->objoid)
4099+
break;
4100+
middle++;
4101+
nmatch++;
4102+
}
4103+
4104+
returnnmatch;
4105+
}
4106+
4107+
/*
4108+
* collectComments --
4109+
*
4110+
* Construct a table of all comments available for database objects.
4111+
* We used to do per-object queries for the comments, but it's much faster
4112+
* to pull them all over at once, and on most databases the memory cost
4113+
* isn't high.
4114+
*
4115+
* The table is sorted by classoid/objid/objsubid for speed in lookup.
4116+
*/
4117+
staticint
4118+
collectComments(Archive*fout,CommentItem**items)
4119+
{
4120+
PGresult*res;
4121+
PQExpBufferquery;
4122+
inti_description;
4123+
inti_classoid;
4124+
inti_objoid;
4125+
inti_objsubid;
4126+
intntups;
4127+
inti;
4128+
CommentItem*comments;
4129+
4130+
/*
4131+
* Note we do NOT change source schema here; preserve the caller's
4132+
* setting, instead.
4133+
*/
4134+
4135+
query=createPQExpBuffer();
4136+
4137+
if (fout->remoteVersion >=70300)
4138+
{
4139+
appendPQExpBuffer(query,"SELECT description, classoid, objoid, objsubid "
4140+
"FROM pg_catalog.pg_description "
4141+
"ORDER BY classoid, objoid, objsubid");
4142+
}
4143+
elseif (fout->remoteVersion >=70200)
4144+
{
4145+
appendPQExpBuffer(query,"SELECT description, classoid, objoid, objsubid "
4146+
"FROM pg_description "
4147+
"ORDER BY classoid, objoid, objsubid");
4148+
}
4149+
else
4150+
{
4151+
/* Note: this will fail to find attribute comments in pre-7.2... */
4152+
appendPQExpBuffer(query,"SELECT description, 0 as classoid, objoid, 0 as objsubid "
4153+
"FROM pg_description "
4154+
"ORDER BY objoid");
4155+
}
4156+
4157+
res=PQexec(g_conn,query->data);
4158+
check_sql_result(res,g_conn,query->data,PGRES_TUPLES_OK);
4159+
4160+
/* Construct lookup table containing OIDs in numeric form */
4161+
4162+
i_description=PQfnumber(res,"description");
4163+
i_classoid=PQfnumber(res,"classoid");
4164+
i_objoid=PQfnumber(res,"objoid");
4165+
i_objsubid=PQfnumber(res,"objsubid");
4166+
4167+
ntups=PQntuples(res);
4168+
4169+
comments= (CommentItem*)malloc(ntups*sizeof(CommentItem));
4170+
4171+
for (i=0;i<ntups;i++)
4172+
{
4173+
comments[i].descr=PQgetvalue(res,i,i_description);
4174+
comments[i].classoid=atooid(PQgetvalue(res,i,i_classoid));
4175+
comments[i].objoid=atooid(PQgetvalue(res,i,i_objoid));
4176+
comments[i].objsubid=atoi(PQgetvalue(res,i,i_objsubid));
4177+
}
4178+
4179+
/* Do NOT free the PGresult since we are keeping pointers into it */
4180+
destroyPQExpBuffer(query);
4181+
4182+
*items=comments;
4183+
returnntups;
4184+
}
4185+
40644186
/*
40654187
* dumpDumpableObject
40664188
*

0 commit comments

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