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

Commitd8c05af

Browse files
committed
Fix pg_dump's handling of circular dependencies in views.
pg_dump's traditional solution for breaking a circular dependency involvinga view was to create the view with CREATE TABLE and then later issue CREATERULE "_RETURN" ... to convert the table to a view, relying on the backend'svery very ancient code that supports making views that way. We've wantedto get rid of that kluge for a long time, but the thing that finallymotivates doing something about it is the recognition that this methodfails with the --clean option, because it leads to issuing DROP RULE"_RETURN" followed by DROP TABLE --- and the backend won't let you drop aview's _RETURN rule.Instead, let's break circular dependencies by initially creating the viewusing CREATE VIEW AS SELECT NULL::columntype AS columnname, ... (so thatit has the right column names and types to support external references,but no dependencies beyond the column data types), and then later dumpingthe ON SELECT rule using the spelling CREATE OR REPLACE VIEW. This methodwasn't available when this code was originally written, but it's beenpossible since PG 7.3, so it seems fine to start relying on it now.To solve the --clean problem, make the dropStmt for an ON SELECT rulebe CREATE OR REPLACE VIEW with the same dummy target list as above.In this way, during the DROP phase, we first reduce the view to haveno extra dependencies, and then we can drop it entirely when we'vegotten rid of whatever had a circular dependency on it.(Note: this should work adequately well with the --if-exists option, sincethe CREATE OR REPLACE VIEW will go through whether the view exists or not.It could fail if the view exists with a conflicting column set, but wedon't really support --clean against a non-matching database anyway.)This allows cleaning up some other kluges inside pg_dump, notably thatwe don't need a notion of reloptions attached to a rule anymore.Although this is a bug fix, commit to HEAD only for now. The problem'sexisted for a long time and we've had relatively few complaints, so itdoesn't really seem worth taking risks to fix it in the back branches.We might revisit that choice if no problems emerge.Discussion: <19092.1479325184@sss.pgh.pa.us>
1 parentac88898 commitd8c05af

File tree

3 files changed

+140
-59
lines changed

3 files changed

+140
-59
lines changed

‎src/bin/pg_dump/pg_dump.c

Lines changed: 136 additions & 45 deletions
Original file line numberDiff line numberDiff line change
@@ -5486,7 +5486,7 @@ getTables(Archive *fout, int *numTables)
54865486
tblinfo[i].dobj.dump &= ~DUMP_COMPONENT_ACL;
54875487

54885488
tblinfo[i].interesting=tblinfo[i].dobj.dump ? true : false;
5489-
5489+
tblinfo[i].dummy_view= false;/* might get set during sort */
54905490
tblinfo[i].postponed_def= false;/* might get set during sort */
54915491

54925492
/*
@@ -6207,16 +6207,6 @@ getRules(Archive *fout, int *numRules)
62076207
}
62086208
else
62096209
ruleinfo[i].separate= true;
6210-
6211-
/*
6212-
* If we're forced to break a dependency loop by dumping a view as a
6213-
* table and separate _RETURN rule, we'll move the view's reloptions
6214-
* to the rule. (This is necessary because tables and views have
6215-
* different valid reloptions, so we can't apply the options until the
6216-
* backend knows it's a view.) Otherwise the rule's reloptions stay
6217-
* NULL.
6218-
*/
6219-
ruleinfo[i].reloptions=NULL;
62206210
}
62216211

62226212
PQclear(res);
@@ -13977,6 +13967,54 @@ createViewAsClause(Archive *fout, TableInfo *tbinfo)
1397713967
returnresult;
1397813968
}
1397913969

13970+
/*
13971+
* Create a dummy AS clause for a view. This is used when the real view
13972+
* definition has to be postponed because of circular dependencies.
13973+
* We must duplicate the view's external properties -- column names and types
13974+
* (including collation) -- so that it works for subsequent references.
13975+
*
13976+
* This returns a new buffer which must be freed by the caller.
13977+
*/
13978+
staticPQExpBuffer
13979+
createDummyViewAsClause(Archive*fout,TableInfo*tbinfo)
13980+
{
13981+
PQExpBufferresult=createPQExpBuffer();
13982+
intj;
13983+
13984+
appendPQExpBufferStr(result,"SELECT");
13985+
13986+
for (j=0;j<tbinfo->numatts;j++)
13987+
{
13988+
if (j>0)
13989+
appendPQExpBufferChar(result,',');
13990+
appendPQExpBufferStr(result,"\n ");
13991+
13992+
appendPQExpBuffer(result,"NULL::%s",tbinfo->atttypnames[j]);
13993+
13994+
/*
13995+
* Must add collation if not default for the type, because CREATE OR
13996+
* REPLACE VIEW won't change it
13997+
*/
13998+
if (OidIsValid(tbinfo->attcollation[j]))
13999+
{
14000+
CollInfo*coll;
14001+
14002+
coll=findCollationByOid(tbinfo->attcollation[j]);
14003+
if (coll)
14004+
{
14005+
/* always schema-qualify, don't try to be smart */
14006+
appendPQExpBuffer(result," COLLATE %s.",
14007+
fmtId(coll->dobj.namespace->dobj.name));
14008+
appendPQExpBufferStr(result,fmtId(coll->dobj.name));
14009+
}
14010+
}
14011+
14012+
appendPQExpBuffer(result," AS %s",fmtId(tbinfo->attnames[j]));
14013+
}
14014+
14015+
returnresult;
14016+
}
14017+
1398014018
/*
1398114019
* dumpTableSchema
1398214020
* write the declaration (not data) of one user-defined table or view
@@ -14010,6 +14048,10 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
1401014048
{
1401114049
PQExpBufferresult;
1401214050

14051+
/*
14052+
* Note: keep this code in sync with the is_view case in dumpRule()
14053+
*/
14054+
1401314055
reltypename="VIEW";
1401414056

1401514057
/*
@@ -14026,17 +14068,22 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
1402614068
tbinfo->dobj.catId.oid, false);
1402714069

1402814070
appendPQExpBuffer(q,"CREATE VIEW %s",fmtId(tbinfo->dobj.name));
14029-
if (nonemptyReloptions(tbinfo->reloptions))
14071+
if (tbinfo->dummy_view)
14072+
result=createDummyViewAsClause(fout,tbinfo);
14073+
else
1403014074
{
14031-
appendPQExpBufferStr(q," WITH (");
14032-
appendReloptionsArrayAH(q,tbinfo->reloptions,"",fout);
14033-
appendPQExpBufferChar(q,')');
14075+
if (nonemptyReloptions(tbinfo->reloptions))
14076+
{
14077+
appendPQExpBufferStr(q," WITH (");
14078+
appendReloptionsArrayAH(q,tbinfo->reloptions,"",fout);
14079+
appendPQExpBufferChar(q,')');
14080+
}
14081+
result=createViewAsClause(fout,tbinfo);
1403414082
}
14035-
result=createViewAsClause(fout,tbinfo);
1403614083
appendPQExpBuffer(q," AS\n%s",result->data);
1403714084
destroyPQExpBuffer(result);
1403814085

14039-
if (tbinfo->checkoption!=NULL)
14086+
if (tbinfo->checkoption!=NULL&& !tbinfo->dummy_view)
1404014087
appendPQExpBuffer(q,"\n WITH %s CHECK OPTION",tbinfo->checkoption);
1404114088
appendPQExpBufferStr(q,";\n");
1404214089

@@ -15648,6 +15695,7 @@ dumpRule(Archive *fout, RuleInfo *rinfo)
1564815695
{
1564915696
DumpOptions*dopt=fout->dopt;
1565015697
TableInfo*tbinfo=rinfo->ruletable;
15698+
boolis_view;
1565115699
PQExpBufferquery;
1565215700
PQExpBuffercmd;
1565315701
PQExpBufferdelcmd;
@@ -15666,6 +15714,12 @@ dumpRule(Archive *fout, RuleInfo *rinfo)
1566615714
if (!rinfo->separate)
1566715715
return;
1566815716

15717+
/*
15718+
* If it's an ON SELECT rule, we want to print it as a view definition,
15719+
* instead of a rule.
15720+
*/
15721+
is_view= (rinfo->ev_type=='1'&&rinfo->is_instead);
15722+
1566915723
/*
1567015724
* Make sure we are in proper schema.
1567115725
*/
@@ -15676,20 +15730,50 @@ dumpRule(Archive *fout, RuleInfo *rinfo)
1567615730
delcmd=createPQExpBuffer();
1567715731
labelq=createPQExpBuffer();
1567815732

15679-
appendPQExpBuffer(query,
15680-
"SELECT pg_catalog.pg_get_ruledef('%u'::pg_catalog.oid) AS definition",
15681-
rinfo->dobj.catId.oid);
15682-
15683-
res=ExecuteSqlQuery(fout,query->data,PGRES_TUPLES_OK);
15684-
15685-
if (PQntuples(res)!=1)
15733+
if (is_view)
1568615734
{
15687-
write_msg(NULL,"query to get rule \"%s\" for table \"%s\" failed: wrong number of rows returned\n",
15688-
rinfo->dobj.name,tbinfo->dobj.name);
15689-
exit_nicely(1);
15735+
PQExpBufferresult;
15736+
15737+
/*
15738+
* We need OR REPLACE here because we'll be replacing a dummy view.
15739+
* Otherwise this should look largely like the regular view dump code.
15740+
*/
15741+
appendPQExpBuffer(cmd,"CREATE OR REPLACE VIEW %s",
15742+
fmtId(tbinfo->dobj.name));
15743+
if (nonemptyReloptions(tbinfo->reloptions))
15744+
{
15745+
appendPQExpBufferStr(cmd," WITH (");
15746+
appendReloptionsArrayAH(cmd,tbinfo->reloptions,"",fout);
15747+
appendPQExpBufferChar(cmd,')');
15748+
}
15749+
result=createViewAsClause(fout,tbinfo);
15750+
appendPQExpBuffer(cmd," AS\n%s",result->data);
15751+
destroyPQExpBuffer(result);
15752+
if (tbinfo->checkoption!=NULL)
15753+
appendPQExpBuffer(cmd,"\n WITH %s CHECK OPTION",
15754+
tbinfo->checkoption);
15755+
appendPQExpBufferStr(cmd,";\n");
1569015756
}
15757+
else
15758+
{
15759+
/* In the rule case, just print pg_get_ruledef's result verbatim */
15760+
appendPQExpBuffer(query,
15761+
"SELECT pg_catalog.pg_get_ruledef('%u'::pg_catalog.oid)",
15762+
rinfo->dobj.catId.oid);
15763+
15764+
res=ExecuteSqlQuery(fout,query->data,PGRES_TUPLES_OK);
15765+
15766+
if (PQntuples(res)!=1)
15767+
{
15768+
write_msg(NULL,"query to get rule \"%s\" for table \"%s\" failed: wrong number of rows returned\n",
15769+
rinfo->dobj.name,tbinfo->dobj.name);
15770+
exit_nicely(1);
15771+
}
1569115772

15692-
printfPQExpBuffer(cmd,"%s\n",PQgetvalue(res,0,0));
15773+
printfPQExpBuffer(cmd,"%s\n",PQgetvalue(res,0,0));
15774+
15775+
PQclear(res);
15776+
}
1569315777

1569415778
/*
1569515779
* Add the command to alter the rules replication firing semantics if it
@@ -15716,25 +15800,34 @@ dumpRule(Archive *fout, RuleInfo *rinfo)
1571615800
}
1571715801

1571815802
/*
15719-
*Apply view's reloptions when its ON SELECT rule is separate.
15803+
*DROP must be fully qualified in case same name appears in pg_catalog
1572015804
*/
15721-
if (nonemptyReloptions(rinfo->reloptions))
15805+
if (is_view)
1572215806
{
15723-
appendPQExpBuffer(cmd,"ALTER VIEW %s SET (",
15807+
/*
15808+
* We can't DROP a view's ON SELECT rule. Instead, use CREATE OR
15809+
* REPLACE VIEW to replace the rule with something with minimal
15810+
* dependencies.
15811+
*/
15812+
PQExpBufferresult;
15813+
15814+
appendPQExpBuffer(delcmd,"CREATE OR REPLACE VIEW %s.",
15815+
fmtId(tbinfo->dobj.namespace->dobj.name));
15816+
appendPQExpBuffer(delcmd,"%s",
15817+
fmtId(tbinfo->dobj.name));
15818+
result=createDummyViewAsClause(fout,tbinfo);
15819+
appendPQExpBuffer(delcmd," AS\n%s;\n",result->data);
15820+
destroyPQExpBuffer(result);
15821+
}
15822+
else
15823+
{
15824+
appendPQExpBuffer(delcmd,"DROP RULE %s ",
15825+
fmtId(rinfo->dobj.name));
15826+
appendPQExpBuffer(delcmd,"ON %s.",
15827+
fmtId(tbinfo->dobj.namespace->dobj.name));
15828+
appendPQExpBuffer(delcmd,"%s;\n",
1572415829
fmtId(tbinfo->dobj.name));
15725-
appendReloptionsArrayAH(cmd,rinfo->reloptions,"",fout);
15726-
appendPQExpBufferStr(cmd,");\n");
1572715830
}
15728-
15729-
/*
15730-
* DROP must be fully qualified in case same name appears in pg_catalog
15731-
*/
15732-
appendPQExpBuffer(delcmd,"DROP RULE %s ",
15733-
fmtId(rinfo->dobj.name));
15734-
appendPQExpBuffer(delcmd,"ON %s.",
15735-
fmtId(tbinfo->dobj.namespace->dobj.name));
15736-
appendPQExpBuffer(delcmd,"%s;\n",
15737-
fmtId(tbinfo->dobj.name));
1573815831

1573915832
appendPQExpBuffer(labelq,"RULE %s",
1574015833
fmtId(rinfo->dobj.name));
@@ -15761,8 +15854,6 @@ dumpRule(Archive *fout, RuleInfo *rinfo)
1576115854
tbinfo->rolname,
1576215855
rinfo->dobj.catId,0,rinfo->dobj.dumpId);
1576315856

15764-
PQclear(res);
15765-
1576615857
free(tag);
1576715858
destroyPQExpBuffer(query);
1576815859
destroyPQExpBuffer(cmd);

‎src/bin/pg_dump/pg_dump.h

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -287,6 +287,7 @@ typedef struct _tableInfo
287287
intrelpages;/* table's size in pages (from pg_class) */
288288

289289
boolinteresting;/* true if need to collect more data */
290+
booldummy_view;/* view's real definition must be postponed */
290291
boolpostponed_def;/* matview must be postponed into post-data */
291292

292293
/*
@@ -364,8 +365,6 @@ typedef struct _ruleInfo
364365
charev_enabled;
365366
boolseparate;/* TRUE if must dump as separate item */
366367
/* separate is always true for non-ON SELECT rules */
367-
char*reloptions;/* options specified by WITH (...) */
368-
/* reloptions is only set if we need to dump the options with the rule */
369368
}RuleInfo;
370369

371370
typedefstruct_triggerInfo

‎src/bin/pg_dump/pg_dump_sort.c

Lines changed: 3 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -786,6 +786,7 @@ repairViewRuleLoop(DumpableObject *viewobj,
786786
{
787787
/* remove rule's dependency on view */
788788
removeObjectDependency(ruleobj,viewobj->dumpId);
789+
/* flags on the two objects are already set correctly for this case */
789790
}
790791

791792
/*
@@ -805,27 +806,17 @@ repairViewRuleMultiLoop(DumpableObject *viewobj,
805806
{
806807
TableInfo*viewinfo= (TableInfo*)viewobj;
807808
RuleInfo*ruleinfo= (RuleInfo*)ruleobj;
808-
inti;
809809

810810
/* remove view's dependency on rule */
811811
removeObjectDependency(viewobj,ruleobj->dumpId);
812-
/*pretend viewis a plain table and dump it that way */
813-
viewinfo->relkind='r';/* RELKIND_RELATION */
812+
/*mark viewto be printed with a dummy definition */
813+
viewinfo->dummy_view=true;
814814
/* mark rule as needing its own dump */
815815
ruleinfo->separate= true;
816-
/* move any reloptions from view to rule */
817-
if (viewinfo->reloptions)
818-
{
819-
ruleinfo->reloptions=viewinfo->reloptions;
820-
viewinfo->reloptions=NULL;
821-
}
822816
/* put back rule's dependency on view */
823817
addObjectDependency(ruleobj,viewobj->dumpId);
824818
/* now that rule is separate, it must be post-data */
825819
addObjectDependency(ruleobj,postDataBoundId);
826-
/* also, any triggers on the view must be dumped after the rule */
827-
for (i=0;i<viewinfo->numTriggers;i++)
828-
addObjectDependency(&(viewinfo->triggers[i].dobj),ruleobj->dumpId);
829820
}
830821

831822
/*

0 commit comments

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