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

Commitd6f549d

Browse files
committed
Fix pg_dump's failure to honor dependencies of SQL functions.
A new-style SQL function can contain a parse-time dependencyon a unique index, much as views and matviews can (such casesarise from GROUP BY and ON CONFLICT clauses, for example).To dump and restore such a function successfully, pg_dump mustpostpone the function until after the unique index is created,which will happen in the post-data part of the dump. Thereforewe have to remove the normal constraint that functions aredumped in pre-data. Add code similar to the existing logicthat handles this for matviews. I added test cases for bothas well, since code coverage tests showed that we weren'ttesting the matview logic.Per report from Sami Imseih. Back-patch to v14 wherenew-style SQL functions came in.Discussion:https://postgr.es/m/2C1933AB-C2F8-499B-9D18-4AC1882256A0@amazon.com
1 parent3d8aefe commitd6f549d

File tree

4 files changed

+89
-1
lines changed

4 files changed

+89
-1
lines changed

‎src/bin/pg_dump/pg_dump.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6073,6 +6073,7 @@ getAggregates(Archive *fout, int *numAggs)
60736073
agginfo[i].aggfn.argtypes,
60746074
agginfo[i].aggfn.nargs);
60756075
}
6076+
agginfo[i].aggfn.postponed_def = false; /* might get set during sort */
60766077

60776078
/* Decide whether we want to dump it */
60786079
selectDumpableObject(&(agginfo[i].aggfn.dobj), fout);
@@ -6303,6 +6304,7 @@ getFuncs(Archive *fout, int *numFuncs)
63036304
parseOidArray(PQgetvalue(res, i, i_proargtypes),
63046305
finfo[i].argtypes, finfo[i].nargs);
63056306
}
6307+
finfo[i].postponed_def = false; /* might get set during sort */
63066308

63076309
/* Decide whether we want to dump it */
63086310
selectDumpableObject(&(finfo[i].dobj), fout);
@@ -12691,7 +12693,8 @@ dumpFunc(Archive *fout, const FuncInfo *finfo)
1269112693
.namespace = finfo->dobj.namespace->dobj.name,
1269212694
.owner = finfo->rolname,
1269312695
.description = keyword,
12694-
.section = SECTION_PRE_DATA,
12696+
.section = finfo->postponed_def ?
12697+
SECTION_POST_DATA : SECTION_PRE_DATA,
1269512698
.createStmt = q->data,
1269612699
.dropStmt = delqry->data));
1269712700

‎src/bin/pg_dump/pg_dump.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -207,6 +207,7 @@ typedef struct _funcInfo
207207
char*rproacl;
208208
char*initproacl;
209209
char*initrproacl;
210+
boolpostponed_def;/* function must be postponed into post-data */
210211
}FuncInfo;
211212

212213
/* AggInfo is a superset of FuncInfo */

‎src/bin/pg_dump/pg_dump_sort.c

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -866,6 +866,28 @@ repairMatViewBoundaryMultiLoop(DumpableObject *boundaryobj,
866866
}
867867
}
868868

869+
/*
870+
* If a function is involved in a multi-object loop, we can't currently fix
871+
* that by splitting it into two DumpableObjects. As a stopgap, we try to fix
872+
* it by dropping the constraint that the function be dumped in the pre-data
873+
* section. This is sufficient to handle cases where a function depends on
874+
* some unique index, as can happen if it has a GROUP BY for example.
875+
*/
876+
staticvoid
877+
repairFunctionBoundaryMultiLoop(DumpableObject*boundaryobj,
878+
DumpableObject*nextobj)
879+
{
880+
/* remove boundary's dependency on object after it in loop */
881+
removeObjectDependency(boundaryobj,nextobj->dumpId);
882+
/* if that object is a function, mark it as postponed into post-data */
883+
if (nextobj->objType==DO_FUNC)
884+
{
885+
FuncInfo*nextinfo= (FuncInfo*)nextobj;
886+
887+
nextinfo->postponed_def= true;
888+
}
889+
}
890+
869891
/*
870892
* Because we make tables depend on their CHECK constraints, while there
871893
* will be an automatic dependency in the other direction, we need to break
@@ -1060,6 +1082,28 @@ repairDependencyLoop(DumpableObject **loop,
10601082
}
10611083
}
10621084

1085+
/* Indirect loop involving function and data boundary */
1086+
if (nLoop>2)
1087+
{
1088+
for (i=0;i<nLoop;i++)
1089+
{
1090+
if (loop[i]->objType==DO_FUNC)
1091+
{
1092+
for (j=0;j<nLoop;j++)
1093+
{
1094+
if (loop[j]->objType==DO_PRE_DATA_BOUNDARY)
1095+
{
1096+
DumpableObject*nextobj;
1097+
1098+
nextobj= (j<nLoop-1) ?loop[j+1] :loop[0];
1099+
repairFunctionBoundaryMultiLoop(loop[j],nextobj);
1100+
return;
1101+
}
1102+
}
1103+
}
1104+
}
1105+
}
1106+
10631107
/* Table and CHECK constraint */
10641108
if (nLoop==2&&
10651109
loop[0]->objType==DO_TABLE&&

‎src/bin/pg_dump/t/002_pg_dump.pl

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1957,6 +1957,27 @@
19571957
unlike => { exclude_dump_test_schema => 1, },
19581958
},
19591959
1960+
'Check ordering of a function that depends on a primary key' => {
1961+
create_order => 41,
1962+
create_sql => '
1963+
CREATE TABLE dump_test.ordering_table (id int primary key, data int);
1964+
CREATE FUNCTION dump_test.ordering_func ()
1965+
RETURNS SETOF dump_test.ordering_table
1966+
LANGUAGE sql BEGIN ATOMIC
1967+
SELECT * FROM dump_test.ordering_table GROUP BY id; END;',
1968+
regexp => qr/^
1969+
\QALTER TABLE ONLY dump_test.ordering_table\E
1970+
\n\s+\QADD CONSTRAINT ordering_table_pkey PRIMARY KEY (id);\E
1971+
.*^
1972+
\QCREATE FUNCTION dump_test.ordering_func\E/xms,
1973+
like =>
1974+
{%full_runs,%dump_test_schema_runs, section_post_data => 1, },
1975+
unlike => {
1976+
exclude_dump_test_schema => 1,
1977+
only_dump_measurement => 1,
1978+
},
1979+
},
1980+
19601981
'CREATE PROCEDURE dump_test.ptest1' => {
19611982
create_order => 41,
19621983
create_sql => 'CREATE PROCEDURE dump_test.ptest1(a int)
@@ -2169,6 +2190,25 @@
21692190
{ exclude_dump_test_schema => 1, no_toast_compression => 1, },
21702191
},
21712192
2193+
'Check ordering of a matview that depends on a primary key' => {
2194+
create_order => 42,
2195+
create_sql => '
2196+
CREATE MATERIALIZED VIEW dump_test.ordering_view AS
2197+
SELECT * FROM dump_test.ordering_table GROUP BY id;',
2198+
regexp => qr/^
2199+
\QALTER TABLE ONLY dump_test.ordering_table\E
2200+
\n\s+\QADD CONSTRAINT ordering_table_pkey PRIMARY KEY (id);\E
2201+
.*^
2202+
\QCREATE MATERIALIZED VIEW dump_test.ordering_view AS\E
2203+
\n\s+\QSELECT ordering_table.id,\E/xms,
2204+
like =>
2205+
{%full_runs,%dump_test_schema_runs, section_post_data => 1, },
2206+
unlike => {
2207+
exclude_dump_test_schema => 1,
2208+
only_dump_measurement => 1,
2209+
},
2210+
},
2211+
21722212
'CREATE POLICY p1 ON test_table' => {
21732213
create_order => 22,
21742214
create_sql => 'CREATE POLICY p1 ON dump_test.test_table

0 commit comments

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