Skip to content

Commit d6f549d

Browse files
tglsfdctglsfdc
committed
Fix pg_dump's failure to honor dependencies of SQL functions.
A new-style SQL function can contain a parse-time dependency on a unique index, much as views and matviews can (such cases arise from GROUP BY and ON CONFLICT clauses, for example). To dump and restore such a function successfully, pg_dump must postpone the function until after the unique index is created, which will happen in the post-data part of the dump. Therefore we have to remove the normal constraint that functions are dumped in pre-data. Add code similar to the existing logic that handles this for matviews. I added test cases for both as well, since code coverage tests showed that we weren't testing the matview logic. Per report from Sami Imseih. Back-patch to v14 where new-style SQL functions came in. Discussion: https://postgr.es/m/2C1933AB-C2F8-499B-9D18-4AC1882256A0@amazon.com
1 parent 3d8aefe commit d6f549d

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+
bool postponed_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+
static void
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)