Hi Álvaro,
A foreign key that references a table with N partitions makes N(N+1)/2
index probes in ChooseConstraintName(), because the name of each
partition's row is searched from suffix 1 again. With 10,000
partitions, ALTER TABLE ... ADD FOREIGN KEY takes 43 s on master and
2 s with the attached patch. pg_restore pays the same in post-data:
765 billion instructions there on master, 65 billion patched.
The patch keeps the last suffix chosen in addFkRecurseReferenced() and
starts the next search after it, so the names do not change: an oracle
over 3,880 cases gives the same names as master, and a new test pins a
taken name in the middle. DETACH of a referencing partition had the
same cost and is covered; creating or attaching one referenced
partition is still linear per command. The new extern sits next to
the one in Marko's CF 6842, so one of the two will need a small rebase.
Manu
From 63bb93fb784d841ed77e9dc7f28d22e1656e9371 Mon Sep 17 00:00:00 2001
From: Manuel Reyes Bravo <[email protected]>
Date: Mon, 16 Nov 2026 19:41:27 -0300
Subject: [PATCH v1] Avoid quadratic name search for FKs to partitioned tables
When a foreign key references a partitioned table,
addFkRecurseReferenced() creates one pg_constraint row per partition,
all on the referencing relation and named after the constraint, and
ChooseConstraintName() looked for a free name starting from suffix 1
each time: N partitions took N(N+1)/2 index probes. pg_restore and
pg_upgrade pay this once per such foreign key. With 10,000
partitions, ALTER TABLE ... ADD FOREIGN KEY took 43 s instead of 2 s.
Keep the last suffix chosen in the recursion and start the next search
after it. Every lower suffix is known to be taken, so the names are the
same as before; they could only differ if another session dropped a
constraint with one of those names in the same schema meanwhile, the
race ChooseConstraintName() already accepts. This covers ADD FOREIGN
KEY, CREATE TABLE ... REFERENCES and DETACH PARTITION of a referencing
partition. Attaching or creating a single referenced partition still
searches from 1, which is linear per command.
Add ChooseConstraintNameExtended() to carry the suffix, and a test that
pins the names when one in the middle of the sequence is taken.
Discussion:
---
src/backend/catalog/pg_constraint.c | 32 ++++++++++--
src/backend/commands/tablecmds.c | 60 ++++++++++++++++-------
src/include/catalog/pg_constraint.h | 3 ++
src/test/regress/expected/foreign_key.out | 37 ++++++++++++++
src/test/regress/sql/foreign_key.sql | 22 +++++++++
5 files changed, 132 insertions(+), 22 deletions(-)
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index 3a8dbbe..edd8742 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -517,7 +517,30 @@ ChooseConstraintName(const char *name1, const char *name2,
const char *label, Oid namespaceid,
List *others)
{
- int pass = 0;
+ return ChooseConstraintNameExtended(name1, name2, label, namespaceid,
+ others, NULL);
+}
+
+/*
+ * ChooseConstraintNameExtended
+ * As ChooseConstraintName, for callers that choose several names with
+ * the same name1, name2, label and namespace in a row.
+ *
+ * If passp is not NULL, the caller promises that the names numbered up to
+ * *passp (and the unmodified label, if *passp > 0) are taken, so the search
+ * starts at *passp + 1. On return, *passp is set to the number used in the
+ * chosen name (0 for the unmodified label), so that passing it back for the
+ * next name of the series continues where this one stopped. If the promise
+ * is wrong, the result is still a free name, just not the lowest one; that
+ * includes the case of a name being dropped concurrently, which is the same
+ * race described for ChooseConstraintName.
+ */
+char *
+ChooseConstraintNameExtended(const char *name1, const char *name2,
+ const char *label, Oid namespaceid,
+ List *others, int *passp)
+{
+ int pass = passp ? *passp : 0;
char *conname = NULL;
char modlabel[NAMEDATALEN];
Relation conDesc;
@@ -528,8 +551,8 @@ ChooseConstraintName(const char *name1, const char *name2,
conDesc = table_open(ConstraintRelationId, AccessShareLock);
- /* try the unmodified label first, unless it's empty */
- if (label[0] != '\0')
+ /* try the unmodified label first, unless it's empty or known taken */
+ if (label[0] != '\0' && pass == 0)
strlcpy(modlabel, label, sizeof(modlabel));
else
snprintf(modlabel, sizeof(modlabel), "%s%d", label, ++pass);
@@ -579,6 +602,9 @@ ChooseConstraintName(const char *name1, const char *name2,
table_close(conDesc, AccessShareLock);
+ if (passp)
+ *passp = pass;
+
return conname;
}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index b670092..a2cfa3b 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -604,7 +604,7 @@ static ObjectAddress addFkConstraint(addFkConstraintSides fkside,
Oid *pfeqoperators, Oid *ppeqoperators,
Oid *ffeqoperators, int numfkdelsetcols,
int16 *fkdelsetcols, bool is_internal,
- bool with_period);
+ bool with_period, int *conname_pass);
static void addFkRecurseReferenced(Constraint *fkconstraint,
Relation rel, Relation pkrel, Oid indexOid, Oid parentConstr,
int numfks, int16 *pkattnum, int16 *fkattnum,
@@ -612,7 +612,7 @@ static void addFkRecurseReferenced(Constraint *fkconstraint,
int numfkdelsetcols, int16 *fkdelsetcols,
bool old_check_ok,
Oid parentDelTrigger, Oid parentUpdTrigger,
- bool with_period);
+ bool with_period, int *conname_pass);
static void addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint,
Relation rel, Relation pkrel, Oid indexOid, Oid parentConstr,
int numfks, int16 *pkattnum, int16 *fkattnum,
@@ -10788,6 +10788,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
Oid indexOid;
bool old_check_ok;
ObjectAddress address;
+ int conname_pass = 0;
ListCell *old_pfeqop_item = list_head(fkconstraint->old_conpfeqop);
/*
@@ -11288,7 +11289,10 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
&intersectoperoid);
}
- /* First, create the constraint catalog entry itself. */
+ /*
+ * First, create the constraint catalog entry itself. Its name is not
+ * part of the series of names for the partitions, hence no conname_pass.
+ */
address = addFkConstraint(addFkBothSides,
fkconstraint->conname, fkconstraint, rel, pkrel,
indexOid,
@@ -11302,7 +11306,8 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
numfkdelsetcols,
fkdelsetcols,
false,
- with_period);
+ with_period,
+ NULL);
/* Next process the action triggers at the referenced side and recurse */
addFkRecurseReferenced(fkconstraint, rel, pkrel,
@@ -11318,7 +11323,8 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
fkdelsetcols,
old_check_ok,
InvalidOid, InvalidOid,
- with_period);
+ with_period,
+ &conname_pass);
/* Lastly create the check triggers at the referencing side and recurse */
addFkRecurseReferencing(wqueue, fkconstraint, rel, pkrel,
@@ -11428,6 +11434,9 @@ validateFkOnDeleteSetColumns(int numfks, const int16 *fkattnums,
* fkdelsetcols: the attnum array of the columns in the ON DELETE SET
* NULL/DEFAULT clause
* with_period: true if this is a temporal FK
+ * conname_pass: if not NULL, the suffix number of the last name chosen for
+ * the constraint rows created so far for this same rel and
+ * constraintname (see addFkRecurseReferenced); updated on return
*/
static ObjectAddress
addFkConstraint(addFkConstraintSides fkside,
@@ -11436,7 +11445,7 @@ addFkConstraint(addFkConstraintSides fkside,
int numfks, int16 *pkattnum,
int16 *fkattnum, Oid *pfeqoperators, Oid *ppeqoperators,
Oid *ffeqoperators, int numfkdelsetcols, int16 *fkdelsetcols,
- bool is_internal, bool with_period)
+ bool is_internal, bool with_period, int *conname_pass)
{
ObjectAddress address;
Oid constrOid;
@@ -11460,15 +11469,17 @@ addFkConstraint(addFkConstraintSides fkside,
* Caller supplies us with a constraint name; however, it may be used in
* this partition, so come up with a different one in that case. Unless
* truncation to NAMEDATALEN dictates otherwise, the new name will be the
- * supplied name with an underscore and digit(s) appended.
+ * supplied name with an underscore and digit(s) appended. conname_pass,
+ * if given, lets a series of such names skip the suffixes already used.
*/
if (ConstraintNameIsUsed(CONSTRAINT_RELATION,
RelationGetRelid(rel),
constraintname))
- conname = ChooseConstraintName(constraintname,
- NULL,
- "",
- RelationGetNamespace(rel), NIL);
+ conname = ChooseConstraintNameExtended(constraintname,
+ NULL,
+ "",
+ RelationGetNamespace(rel), NIL,
+ conname_pass);
else
conname = constraintname;
@@ -11606,6 +11617,12 @@ addFkConstraint(addFkConstraintSides fkside,
* partition, the OIDs of the parent action triggers for DELETE and
* UPDATE respectively.
* with_period: true if this is a temporal FK
+ * conname_pass: points to the suffix number of the last name chosen in this
+ * recursion (0 at the top), and is updated as names are chosen. Every
+ * row created here is on rel and named after fkconstraint->conname, and
+ * each gets the lowest free suffix, so the lower ones are all taken and
+ * the search for the next name can start after the last one. See
+ * ChooseConstraintNameExtended.
*/
static void
addFkRecurseReferenced(Constraint *fkconstraint, Relation rel,
@@ -11616,7 +11633,7 @@ addFkRecurseReferenced(Constraint *fkconstraint, Relation rel,
int numfkdelsetcols, int16 *fkdelsetcols,
bool old_check_ok,
Oid parentDelTrigger, Oid parentUpdTrigger,
- bool with_period)
+ bool with_period, int *conname_pass)
{
Oid deleteTriggerOid = InvalidOid,
updateTriggerOid = InvalidOid;
@@ -11685,7 +11702,8 @@ addFkRecurseReferenced(Constraint *fkconstraint, Relation rel,
numfks, mapped_pkattnum,
fkattnum, pfeqoperators, ppeqoperators,
ffeqoperators, numfkdelsetcols,
- fkdelsetcols, true, with_period);
+ fkdelsetcols, true, with_period,
+ conname_pass);
/* ... and recurse to our children */
addFkRecurseReferenced(fkconstraint, rel, partRel,
partIndexId, address.objectId, numfks,
@@ -11694,7 +11712,7 @@ addFkRecurseReferenced(Constraint *fkconstraint, Relation rel,
numfkdelsetcols, fkdelsetcols,
old_check_ok,
deleteTriggerOid, updateTriggerOid,
- with_period);
+ with_period, conname_pass);
/* Done -- clean up (but keep the lock) */
table_close(partRel, NoLock);
@@ -11884,7 +11902,7 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel,
mapped_fkattnum, pfeqoperators,
ppeqoperators, ffeqoperators,
numfkdelsetcols, fkdelsetcols, true,
- with_period);
+ with_period, NULL);
/* call ourselves to finalize the creation and we're done */
addFkRecurseReferencing(wqueue, fkconstraint, partition, pkrel,
@@ -12028,6 +12046,7 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel)
ObjectAddress address;
Oid deleteTriggerOid = InvalidOid,
updateTriggerOid = InvalidOid;
+ int conname_pass = 0;
tuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(constrOid));
if (!HeapTupleIsValid(tuple))
@@ -12118,7 +12137,7 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel)
numfks, mapped_confkey,
conkey, conpfeqop, conppeqop, conffeqop,
numfkdelsetcols, confdelsetcols, false,
- constrForm->conperiod);
+ constrForm->conperiod, &conname_pass);
/* ... and recurse */
addFkRecurseReferenced(fkconstraint,
fkRel,
@@ -12136,7 +12155,8 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel)
true,
deleteTriggerOid,
updateTriggerOid,
- constrForm->conperiod);
+ constrForm->conperiod,
+ &conname_pass);
table_close(fkRel, NoLock);
ReleaseSysCache(tuple);
@@ -12363,7 +12383,7 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
mapped_conkey, conpfeqop,
conppeqop, conffeqop,
numfkdelsetcols, confdelsetcols,
- false, with_period);
+ false, with_period, NULL);
/* Done with the cloned constraint's tuple */
ReleaseSysCache(tuple);
@@ -22650,6 +22670,7 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
int numfkdelsetcols;
AttrNumber confdelsetcols[INDEX_MAX_KEYS];
Relation refdRel;
+ int conname_pass = 0;
DeconstructFkConstraintRow(contup,
&numfks,
@@ -22710,7 +22731,8 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
confdelsetcols,
true,
InvalidOid, InvalidOid,
- conform->conperiod);
+ conform->conperiod,
+ &conname_pass);
table_close(refdRel, NoLock); /* keep lock till end of xact */
}
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index e0e55b0..58894be 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -263,6 +263,9 @@ extern bool ConstraintNameExists(const char *conname, Oid namespaceid);
extern char *ChooseConstraintName(const char *name1, const char *name2,
const char *label, Oid namespaceid,
List *others);
+extern char *ChooseConstraintNameExtended(const char *name1, const char *name2,
+ const char *label, Oid namespaceid,
+ List *others, int *passp);
extern HeapTuple findNotNullConstraintAttnum(Oid relid, AttrNumber attnum);
extern HeapTuple findNotNullConstraint(Oid relid, const char *colname);
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 95a0531..701d097 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -3699,6 +3699,43 @@ DETAIL: drop cascades to table fkpart13_t1
drop cascades to table fkpart13_t2
drop cascades to table fkpart13_t3
RESET search_path;
+-- The constraint rows created for each partition of a referenced partitioned
+-- table get the lowest suffix not taken in the schema, even when a name in the
+-- middle of the sequence is already taken, across levels of partitioning, and
+-- for a partition attached later.
+CREATE SCHEMA fkpart14;
+SET search_path TO fkpart14;
+CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
+CREATE TABLE pk1 PARTITION OF pk FOR VALUES FROM (1) TO (2);
+CREATE TABLE pk2 PARTITION OF pk FOR VALUES FROM (2) TO (3);
+CREATE TABLE pk3 PARTITION OF pk FOR VALUES FROM (3) TO (5) PARTITION BY RANGE (a);
+CREATE TABLE pk31 PARTITION OF pk3 FOR VALUES FROM (3) TO (4);
+CREATE TABLE pk32 PARTITION OF pk3 FOR VALUES FROM (4) TO (5);
+CREATE TABLE pk4 PARTITION OF pk FOR VALUES FROM (5) TO (6);
+CREATE TABLE other (a int CONSTRAINT fk_a_fkey_3 CHECK (a > 0));
+CREATE TABLE fk (a int REFERENCES pk);
+CREATE TABLE pk5 (a int PRIMARY KEY);
+ALTER TABLE pk ATTACH PARTITION pk5 FOR VALUES FROM (6) TO (7);
+SELECT conname, confrelid::regclass FROM pg_constraint
+WHERE conrelid = 'fk'::regclass ORDER BY conname COLLATE "C";
+ conname | confrelid
+-------------+-----------
+ fk_a_fkey | pk
+ fk_a_fkey_1 | pk1
+ fk_a_fkey_2 | pk2
+ fk_a_fkey_4 | pk3
+ fk_a_fkey_5 | pk31
+ fk_a_fkey_6 | pk32
+ fk_a_fkey_7 | pk4
+ fk_a_fkey_8 | pk5
+(8 rows)
+
+DROP SCHEMA fkpart14 CASCADE;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table pk
+drop cascades to table other
+drop cascades to table fk
+RESET search_path;
-- Test the fast-path ALTER TABLE validation path. RLS on the referenced
-- table makes RI_Initial_Check() decline the set-based check, so validation
-- invokes the RI trigger once per referencing row.
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 65b68f4..1b7e35a 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -2672,6 +2672,28 @@ WITH cte AS (
DROP SCHEMA fkpart13 CASCADE;
RESET search_path;
+-- The constraint rows created for each partition of a referenced partitioned
+-- table get the lowest suffix not taken in the schema, even when a name in the
+-- middle of the sequence is already taken, across levels of partitioning, and
+-- for a partition attached later.
+CREATE SCHEMA fkpart14;
+SET search_path TO fkpart14;
+CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
+CREATE TABLE pk1 PARTITION OF pk FOR VALUES FROM (1) TO (2);
+CREATE TABLE pk2 PARTITION OF pk FOR VALUES FROM (2) TO (3);
+CREATE TABLE pk3 PARTITION OF pk FOR VALUES FROM (3) TO (5) PARTITION BY RANGE (a);
+CREATE TABLE pk31 PARTITION OF pk3 FOR VALUES FROM (3) TO (4);
+CREATE TABLE pk32 PARTITION OF pk3 FOR VALUES FROM (4) TO (5);
+CREATE TABLE pk4 PARTITION OF pk FOR VALUES FROM (5) TO (6);
+CREATE TABLE other (a int CONSTRAINT fk_a_fkey_3 CHECK (a > 0));
+CREATE TABLE fk (a int REFERENCES pk);
+CREATE TABLE pk5 (a int PRIMARY KEY);
+ALTER TABLE pk ATTACH PARTITION pk5 FOR VALUES FROM (6) TO (7);
+SELECT conname, confrelid::regclass FROM pg_constraint
+WHERE conrelid = 'fk'::regclass ORDER BY conname COLLATE "C";
+DROP SCHEMA fkpart14 CASCADE;
+RESET search_path;
+
-- Test the fast-path ALTER TABLE validation path. RLS on the referenced
-- table makes RI_Initial_Check() decline the set-based check, so validation
-- invokes the RI trigger once per referencing row.
--
2.56.1
Foreign keys to a partitioned table: constraint naming, measurements
=====================================================================
Base: master 7fc2f0423320e02beb8c44b863f1827604c47c75, with and without
v1-0001. Builds: -O2 -g, no assertions, same configure for both.
Host: Ryzen AI 9 HX 370 (Zen 5 cores 0-3,12-15 pinned), Linux 7.3.
Method: both builds run on byte-identical clones of the same cluster,
alternating per round (m p, p m, ...). Instructions are
instructions:u of the backend (perf stat -p) for the measured
statements only; ms is the client's wall clock. "change" is the
median of patched against master with a bootstrap 95% CI.
Script: fk-names-measure.py.txt. p = table with N range partitions and
a primary key, f = table with one int column.
addfk ALTER TABLE f ADD FOREIGN KEY (a) REFERENCES p, N partitions.
partof with that FK in place, CREATE TABLE ... PARTITION OF p for 50
more partitions, per statement (this path is not changed by
the patch: it searches from suffix 1, linear per command).
restore pg_dump -Fc of p (N partitions x ROWS rows) and f with the FK,
restored into an empty cluster section by section: pre-data,
data, then post-data (the PKs of every partition and the FK).
postdata N = 10000: pre-data and data restored once into a template;
each round clones it and runs only post-data.
op size metric master patch change [95% CI] n
addfk 100 instr (M) 152 89.8 -40.8% [-40.8..-40.8] 6/6
addfk 100 ms 93.8 95.1 +1.4% [-25.8..+22.9] 6/6
addfk 1000 instr (M) 7,382 917 -87.6% [-87.6..-87.6] 6/6
addfk 1000 ms 688 280 -59.3% [-82.5..-16.0] 6/6
addfk 5000 instr (M) 172,581 4,717 -97.3% [-97.3..-97.3] 6/6
addfk 5000 ms 11,184 1,097 -90.2% [-91.0..-85.9] 6/6
addfk 8192 instr (M) 463,267 7,886 -98.3% [-98.3..-98.3] 6/6
addfk 8192 ms 28,720 1,478 -94.9% [-94.9..-94.5] 6/6
addfk 10000 instr (M) 691,002 9,699 -98.6% [-98.6..-98.6] 6/6
addfk 10000 ms 42,800 2,053 -95.2% [-95.2..-95.1] 6/6
partof 1000 instr/op (M) 32.8 32.8 +0.0% [+0.0..+0.0] 6/6
partof 1000 ms/op 8.495 8.345 -1.8% [-11.9..+6.5] 6/6
partof 10000 instr/op (M) 303 303 +0.0% [+0.0..+0.0] 6/6
partof 10000 ms/op 26.7 26.1 -2.5% [-15.9..+2.3] 6/6
postdata 10000x10 post instr (M) 764,938 64,721 -91.5% [-91.6..-91.5] 5/5
postdata 10000x10 post ms 172,995 124,714 -27.9% [-74.5..+66.3] 5/5
restore 1000x10 data ms 1,555 1,449 -6.8% [-69.6..+20.5] 6/6
restore 1000x10 post instr (M) 9,439 2,846 -69.8% [-69.9..-69.8] 6/6
restore 1000x10 post ms 3,048 2,645 -13.2% [-70.8..+4.1] 6/6
restore 1000x10 pre ms 5,095 4,417 -13.3% [-66.6..+2.9] 6/6
restore 1000x10 total ms 10,606 8,588 -19.0% [-66.7..+1.2] 6/6
restore 1000x1000 data ms 8,396 8,156 -2.9% [-22.8..+4.0] 9/9
restore 1000x1000 post instr (M) 12,927 6,332 -51.0% [-51.0..-51.0] 9/9
restore 1000x1000 post ms 14,180 13,484 -4.9% [-18.0..+3.7] 9/9
restore 1000x1000 pre ms 20,182 20,382 +1.0% [-13.7..+18.0] 9/9
restore 1000x1000 total ms 43,236 41,750 -3.4% [-13.5..+7.2] 9/9
Reading:
- addfk: the cost moves from quadratic to linear; N = 1000 is about the
x8 the naming share predicted. At N = 100 the wall clock does not
change within noise.
- restore, N = 1000: the patch saves the same ~6,590M instructions in
post-data with 10 and with 1000 rows per partition; that is the naming.
What grows with the data is index builds and validation. The wall
clock of pg_restore on this machine is dominated by the commit of
every object (fsync), so the totals are within noise.
- postdata, N = 10000: 765 billion instructions on master, 65 billion
patched. The wall clock is bimodal on both builds (master 173, 173,
75, 174, 81 s; patched 134, 133, 44, 114, 125 s) because post-data also
builds 10000 primary key indexes and the disk dominates; I do not
draw a time from it. The instruction difference (~700 billion) is
the same as in addfk N = 10000, which costs ~41 s here.
- restore 1000x1000 has 9 rounds: a run cut by a time limit kept going
and a second run repeated rounds 4-6; all 9 are kept.
Single full restore, N = 10000, 10 rows per partition (1 round each, for
scale only): master 604 s (pre-data 342, data 80, post-data 182); patched
513 s (296, 85, 132).
Not measured cleanly: pg_upgrade with N = 10000 (one patched run took
268 s while the machine was loaded by other jobs; the noise sentinel was
27-29% over its baseline, so it is not a number). Other platforms.
#!/usr/bin/env python3
"""Requires production builds (configure without --enable-cassert): instruction
counts
are not meaningful with assertions. Needs perf (instructions:u) and the
builds' bin
directories. Example, 6 rounds alternating master (m) and patched (p):
python3 fk-names-measure.py.txt addfk 10000 6 m=/path/master/bin
p=/path/patched/bin
"""
"""Real-operation measurements for foreign keys that reference a partitioned
table
with N partitions. Production builds only (no cassert).
Builds are given as NAME=BINDIR (e.g. m=/x/i-m/bin p=/x/i-p/bin). Every
measurement
alternates the builds by round (m p, p m, ...) on reflink clones of one
template, so
both builds start from byte-identical clusters. The templates are made with the
first build given (the patch does not change the catalog format).
b2.py addfk N ROUNDS B=BIN ... ALTER TABLE f ADD FOREIGN KEY (a)
REFERENCES p:
wall clock + instructions:u of the
backend
b2.py restore N ROWS ROUNDS B=BIN ... pg_dump -Fc of p (N partitions, ROWS
rows each) and
f (FK to p); restore into an empty
cluster section by
section: pre-data and data with
pg_restore, post-data
(the FK) as SQL in a counted session;
total = the sum
b2.py postdata N ROWS ROUNDS B=BIN .. as restore, but pre-data and data are
restored once
into a template; each round clones it
and runs only
post-data (for sizes where pre-data
takes minutes)
b2.py upgrade N ROUNDS B=BIN ... pg_upgrade of a cluster holding p and f
(no data)
into a fresh cluster of the same build:
wall clock
b2.py partof N NEW ROUNDS B=BIN ... with the FK in place, CREATE TABLE ...
PARTITION OF
p for NEW more partitions: ms +
instructions per op
Output: one TSV line per measurement on stdout: op build N round metric value
B2_ROUND0=R starts at round R+1 (keeps the m/p alternation across split runs).
Work dir: $B2_WORK (default ./fk-names-work).
"""
import os, re, shutil, signal, subprocess, sys, time
W = os.environ.get('B2_WORK', os.path.abspath('fk-names-work'))
SOCK = os.path.join(W, 'sock')
PORT = 55451
def sh(cmd, **kw):
r = subprocess.run(cmd, text=True, capture_output=True, **kw)
if r.returncode:
sys.exit(f'{cmd[0]}: {(r.stdout + r.stderr)[-1500:]}')
return r.stdout
def builds(args):
return [tuple(a.split('=', 1)) for a in args]
def order(bs, r):
return bs if r % 2 == 0 else list(reversed(bs))
def start(bindir, d, port=PORT):
os.makedirs(SOCK, exist_ok=True)
sh([f'{bindir}/pg_ctl', '-D', d, '-o', f'-p {port} -k {SOCK} -c
listen_addresses=',
'-l', d + '.log', '-w', 'start'])
def stop(bindir, d):
sh([f'{bindir}/pg_ctl', '-D', d, '-m', 'fast', '-w', 'stop'])
def psql(bindir, sql, db='postgres', port=PORT):
r = subprocess.run([f'{bindir}/psql', '-X', '-q', '-At', '-v',
'ON_ERROR_STOP=1', '-h', SOCK,
'-p', str(port), '-d', db], input=sql, text=True,
capture_output=True)
if r.returncode:
sys.exit(f'psql: {r.stderr[:800]}')
return r.stdout
def initdb(bindir, d):
shutil.rmtree(d, ignore_errors=True)
sh([f'{bindir}/initdb', '-D', d, '-A', 'trust', '--no-sync'])
with open(os.path.join(d, 'postgresql.conf'), 'a') as f:
f.write('max_wal_size = 8GB\nmax_locks_per_transaction = 1024\n')
def clone(src, tag, bindir):
d = os.path.join(W, f'cell-{tag}')
if os.path.exists(os.path.join(d, 'postmaster.pid')):
subprocess.run([f'{bindir}/pg_ctl', '-D', d, '-m', 'immediate', '-w',
'stop'],
capture_output=True)
shutil.rmtree(d, ignore_errors=True)
sh(['cp', '-a', '--reflink=auto', src, d])
return d
def schema_sql(n, rows=0, fk=False):
st = ['CREATE TABLE p (id int PRIMARY KEY) PARTITION BY RANGE (id);']
st += [f'CREATE TABLE p{i} PARTITION OF p FOR VALUES FROM ({i * 1000}) TO
({(i + 1) * 1000});'
for i in range(n)]
st.append('CREATE TABLE f (a int);')
if rows:
st.append(f'INSERT INTO p SELECT i * 1000 + j FROM generate_series(0,
{n - 1}) i, '
f'generate_series(0, {rows - 1}) j;')
st.append('INSERT INTO f SELECT id FROM p;')
if fk:
st.append('ALTER TABLE f ADD FOREIGN KEY (a) REFERENCES p;')
return '\n'.join(st) + '\n'
def template(name, bindir, sql):
d = os.path.join(W, f'tpl-{name}')
if os.path.exists(os.path.join(d, 'PG_VERSION')):
return d
os.makedirs(W, exist_ok=True)
initdb(bindir, d)
if sql:
start(bindir, d)
psql(bindir, sql)
psql(bindir, 'VACUUM ANALYZE; CHECKPOINT;')
stop(bindir, d)
return d
class Session:
"""An interactive psql whose backend can be counted with perf stat -p."""
def __init__(self, bindir, db='postgres'):
self.p = subprocess.Popen([f'{bindir}/psql', '-X', '-q', '-At', '-v',
'ON_ERROR_STOP=1', '-h', SOCK,
'-p', str(PORT), '-d', db],
stdin=subprocess.PIPE,
stdout=subprocess.PIPE,
stderr=subprocess.STDOUT, text=True, bufsize=1)
self.pid = int(self.run('SELECT pg_backend_pid();')[-1])
def run(self, sql):
self.p.stdin.write(sql + '\n\\echo __done__\n')
self.p.stdin.flush()
out = []
for line in self.p.stdout:
line = line.rstrip('\n')
if line == '__done__':
return out
if 'ERROR' in line:
sys.exit(f'session: {line}')
out.append(line)
sys.exit('session: psql exited')
def close(self):
self.p.stdin.close()
self.p.wait()
def counted(bindir, sql, db='postgres'):
"""Run sql in a fresh session; return (ms, instructions:u of its
backend)."""
s = Session(bindir, db)
perf = subprocess.Popen(['perf', 'stat', '-x,', '-e', 'instructions:u',
'-p', str(s.pid)],
stderr=subprocess.PIPE, text=True)
time.sleep(0.5)
t0 = time.monotonic()
s.run(sql)
ms = (time.monotonic() - t0) * 1000
perf.send_signal(signal.SIGINT)
err = perf.communicate()[1]
s.close()
ins = [int(l.split(',')[0]) for l in err.splitlines() if 'instructions' in
l and l[0].isdigit()]
if not ins:
sys.exit(f'perf: {err[:400]}')
return ms, ins[0]
def fk_rows(bindir, db='postgres'):
return int(psql(bindir, "SELECT count(*) FROM pg_constraint WHERE contype =
'f';", db).split()[-1])
def addfk(n, rounds, bs):
tpl = template(f'addfk-{n}', bs[0][1], schema_sql(n))
r0 = int(os.environ.get('B2_ROUND0', '0')) # first round index (to
split long runs)
for r in range(r0, r0 + rounds):
for name, bindir in order(bs, r):
d = clone(tpl, name, bindir)
start(bindir, d)
ms, ins = counted(bindir, 'ALTER TABLE f ADD FOREIGN KEY (a)
REFERENCES p;')
if fk_rows(bindir) != n + 1:
sys.exit('addfk: wrong number of FK rows')
print(f'addfk\t{name}\t{n}\t{r + 1}\tms\t{ms:.1f}', flush=True)
print(f'addfk\t{name}\t{n}\t{r + 1}\tinstructions_M\t{ins /
1e6:.1f}', flush=True)
stop(bindir, d)
def restore(n, rows, rounds, bs):
src = template(f'dumpsrc-{n}-{rows}', bs[0][1], schema_sql(n, rows,
fk=True))
empty = template('empty', bs[0][1], '')
dump = os.path.join(W, f'dump-{n}-{rows}.custom')
post = os.path.join(W, f'dump-{n}-{rows}.post.sql')
if not os.path.exists(post):
d = clone(src, 'dumper', bs[0][1])
start(bs[0][1], d)
sh([f'{bs[0][1]}/pg_dump', '-Fc', '-h', SOCK, '-p', str(PORT), '-f',
dump, 'postgres'])
stop(bs[0][1], d)
sh([f'{bs[0][1]}/pg_restore', '--section=post-data', '-f', post, dump])
r0 = int(os.environ.get('B2_ROUND0', '0')) # first round index (to
split long runs)
for r in range(r0, r0 + rounds):
for name, bindir in order(bs, r):
d = clone(empty, name, bindir)
start(bindir, d)
psql(bindir, 'CREATE DATABASE r;')
t = {}
for sec in ('pre-data', 'data'):
t0 = time.monotonic()
sh([f'{bindir}/pg_restore', '-h', SOCK, '-p', str(PORT), '-d',
'r', '--exit-on-error',
f'--section={sec}', dump])
t[sec] = (time.monotonic() - t0) * 1000
with open(post) as fh:
ms, ins = counted(bindir, fh.read(), db='r')
if fk_rows(bindir, 'r') != n + 1:
sys.exit('restore: wrong number of FK rows')
total = t['pre-data'] + t['data'] + ms
print(f'restore\t{name}\t{n}x{rows}\t{r +
1}\tpredata_ms\t{t["pre-data"]:.1f}', flush=True)
print(f'restore\t{name}\t{n}x{rows}\t{r +
1}\tdata_ms\t{t["data"]:.1f}', flush=True)
print(f'restore\t{name}\t{n}x{rows}\t{r +
1}\tpostdata_ms\t{ms:.1f}', flush=True)
print(f'restore\t{name}\t{n}x{rows}\t{r +
1}\tpostdata_instructions_M\t{ins / 1e6:.1f}',
flush=True)
print(f'restore\t{name}\t{n}x{rows}\t{r +
1}\ttotal_ms\t{total:.1f}', flush=True)
stop(bindir, d)
def postdata(n, rows, rounds, bs):
restore(n, rows, 0, bs) # make the dump
and post.sql
dump = os.path.join(W, f'dump-{n}-{rows}.custom')
post = os.path.join(W, f'dump-{n}-{rows}.post.sql')
tpl = os.path.join(W, f'tpl-predata-{n}-{rows}')
if not os.path.exists(os.path.join(tpl, 'PG_VERSION')):
empty = template('empty', bs[0][1], '')
d = clone(empty, 'predata', bs[0][1])
start(bs[0][1], d)
psql(bs[0][1], 'CREATE DATABASE r;')
for sec in ('pre-data', 'data'):
sh([f'{bs[0][1]}/pg_restore', '-h', SOCK, '-p', str(PORT), '-d',
'r', '--exit-on-error',
f'--section={sec}', dump])
psql(bs[0][1], 'VACUUM ANALYZE; CHECKPOINT;', db='r')
stop(bs[0][1], d)
shutil.rmtree(tpl, ignore_errors=True)
os.rename(d, tpl)
r0 = int(os.environ.get('B2_ROUND0', '0'))
for r in range(r0, r0 + rounds):
for name, bindir in order(bs, r):
d = clone(tpl, name, bindir)
start(bindir, d)
with open(post) as fh:
ms, ins = counted(bindir, fh.read(), db='r')
if fk_rows(bindir, 'r') != n + 1:
sys.exit('postdata: wrong number of FK rows')
print(f'postdata\t{name}\t{n}x{rows}\t{r +
1}\tpostdata_ms\t{ms:.1f}', flush=True)
print(f'postdata\t{name}\t{n}x{rows}\t{r +
1}\tpostdata_instructions_M\t{ins / 1e6:.1f}',
flush=True)
stop(bindir, d)
def upgrade(n, rounds, bs):
old = template(f'upgsrc-{n}', bs[0][1], schema_sql(n, 0, fk=True))
empty = template('empty', bs[0][1], '')
r0 = int(os.environ.get('B2_ROUND0', '0')) # first round index (to
split long runs)
for r in range(r0, r0 + rounds):
for name, bindir in order(bs, r):
o = clone(old, f'{name}-old', bindir)
nw = clone(empty, f'{name}-new', bindir)
wd = os.path.join(W, f'upg-{name}')
shutil.rmtree(wd, ignore_errors=True)
os.makedirs(wd)
t0 = time.monotonic()
sh([f'{bindir}/pg_upgrade', '-b', bindir, '-B', bindir, '-d', o,
'-D', nw,
'-p', str(PORT), '-P', str(PORT + 1), '-s', SOCK, '--no-sync',
'--link'], cwd=wd)
ms = (time.monotonic() - t0) * 1000
start(bindir, nw)
if fk_rows(bindir) != n + 1:
sys.exit('upgrade: wrong number of FK rows')
stop(bindir, nw)
print(f'upgrade\t{name}\t{n}\t{r + 1}\tms\t{ms:.1f}', flush=True)
def partof(n, new, rounds, bs):
tpl = template(f'partof-{n}', bs[0][1], schema_sql(n, 0, fk=True))
sql = '\n'.join(f'CREATE TABLE q{i} PARTITION OF p FOR VALUES FROM ({i *
1000}) TO ({(i + 1) * 1000});'
for i in range(n, n + new))
r0 = int(os.environ.get('B2_ROUND0', '0')) # first round index (to
split long runs)
for r in range(r0, r0 + rounds):
for name, bindir in order(bs, r):
d = clone(tpl, name, bindir)
start(bindir, d)
ms, ins = counted(bindir, sql)
if fk_rows(bindir) != n + new + 1:
sys.exit('partof: wrong number of FK rows')
print(f'partof\t{name}\t{n}\t{r + 1}\tms_per_op\t{ms / new:.2f}',
flush=True)
print(f'partof\t{name}\t{n}\t{r + 1}\tinstructions_M_per_op\t{ins /
1e6 / new:.2f}', flush=True)
stop(bindir, d)
def main():
a = sys.argv[1:]
if not a:
sys.exit(__doc__)
op, n = a[0], int(a[1])
if op == 'addfk':
addfk(n, int(a[2]), builds(a[3:]))
elif op == 'restore':
restore(n, int(a[2]), int(a[3]), builds(a[4:]))
elif op == 'postdata':
postdata(n, int(a[2]), int(a[3]), builds(a[4:]))
elif op == 'upgrade':
upgrade(n, int(a[2]), builds(a[3:]))
elif op == 'partof':
partof(n, int(a[2]), int(a[3]), builds(a[4:]))
else:
sys.exit(__doc__)
if __name__ == '__main__':
main()
#!/usr/bin/env python3
"""Run it against a master server and a patched server (any build), then diff
the two
output files; they must be identical. Use autovacuum = off on both: autovacuum
can change
the physical order in which CloneFkReferenced() visits constraints, which
changes names on
master itself.
"""
"""Name-equivalence oracle for foreign keys that reference a partitioned table.
For every case it builds a fresh schema on a running server, creates the foreign
key through one of several paths, and prints the sorted list
(conrelid::regclass, confrelid::regclass, conname) of every constraint in that
schema. Run it once against master and once against the patch; the outputs
must be identical (diff empty).
oracle.py run --port P --host H --bindir B --out FILE [--only-n LIST]
[--sample K]
oracle.py list # print the case matrix and its size
Axes:
n partitions of the referenced table: 1 2 9 10 11 99 100 101 1000
depth 1 (leaves under p) or 2 (p -> groups of up to 10 -> leaves)
name short (default name f_a_fkey), len61, len62, len63, len63_1 (63 bytes
ending in "_1")
occ names already taken in the schema: none, s1 (_1), s3 (hole at _3), blk
(_1.._10)
fks 1, or 2 foreign keys from the same table with the same columns
path addfk (ALTER TABLE ADD FOREIGN KEY), inline (CREATE TABLE ...
REFERENCES),
attach (ATTACH PARTITION after the FK exists), partof (CREATE TABLE ...
PARTITION OF after the FK exists), restore (pg_dump of the schema,
drop,
restore with psql), detach (the referencing table is a partition of fp
and is detached after the FK exists: the detached table gets its own
referenced-side rows, one per partition of p)
Exhaustive for n <= 101; n = 1000 runs a deterministic sample (--sample,
default 40).
"""
import argparse, itertools, os, random, subprocess, sys
NS = [1, 2, 9, 10, 11, 99, 100, 101, 1000]
DEPTHS = [1, 2]
NAMES = ['short', 'len61', 'len62', 'len63', 'len63_1']
OCCS = ['none', 's1', 's3', 'blk']
FKS = [1, 2]
PATHS = ['addfk', 'inline', 'attach', 'partof', 'restore', 'detach']
NAMEDATALEN = 64
def make_object_name(name1, label):
"""makeObjectName(name1, NULL, label) for ASCII names
(src/backend/commands/indexcmds.c)."""
avail = NAMEDATALEN - 1 - (len(label) + 1)
return name1[:avail] + '_' + label
def base_name(kind):
if kind == 'short':
return None # let the server choose f_a_fkey
if kind == 'len63_1':
return ('k' * 61 + '_1')
return 'n' * int(kind[3:])
def cases():
return list(itertools.product(NS, DEPTHS, NAMES, OCCS, FKS, PATHS))
def select(all_cases, sample, only_n, only_path=None):
small = [c for c in all_cases if c[0] <= 101]
big = [c for c in all_cases if c[0] > 101]
rnd = random.Random(20261116)
big = sorted(rnd.sample(big, min(sample, len(big))))
sel = small + big
if only_n:
sel = [c for c in sel if c[0] in only_n]
if only_path:
sel = [c for c in sel if c[5] in only_path]
return sel
def ident(c):
return 'c_%d_d%d_%s_%s_f%d_%s' % c
def partition_ddl(sch, n, depth, skip_last=False):
"""DDL for p and its partitions. If skip_last, the last leaf (depth 1) or
the
last group (depth 2) is left out and returned as (standalone create, attach,
create-as-partition-of)."""
st = [f'CREATE TABLE {sch}.p (id int PRIMARY KEY) PARTITION BY RANGE (id)']
later = None
if depth == 1:
for i in range(n):
q = f'CREATE TABLE {sch}.p{i} PARTITION OF {sch}.p FOR VALUES FROM
({i}) TO ({i + 1})'
if skip_last and i == n - 1:
later = (f'CREATE TABLE {sch}.p{i} (id int PRIMARY KEY)',
f'ALTER TABLE {sch}.p ATTACH PARTITION {sch}.p{i} FOR
VALUES FROM ({i}) TO ({i + 1})',
q)
else:
st.append(q)
else:
groups = [list(range(g, min(g + 10, n))) for g in range(0, n, 10)]
for gi, leaves in enumerate(groups):
lo, hi = leaves[0], leaves[-1] + 1
g = [f'CREATE TABLE {sch}.g{gi} PARTITION OF {sch}.p FOR VALUES
FROM ({lo}) TO ({hi}) '
f'PARTITION BY RANGE (id)']
g += [f'CREATE TABLE {sch}.p{i} PARTITION OF {sch}.g{gi} FOR VALUES
FROM ({i}) TO ({i + 1})'
for i in leaves]
if skip_last and gi == len(groups) - 1:
standalone = [f'CREATE TABLE {sch}.g{gi} (id int PRIMARY KEY)
PARTITION BY RANGE (id)']
standalone += [f'CREATE TABLE {sch}.p{i} PARTITION OF
{sch}.g{gi} '
f'FOR VALUES FROM ({i}) TO ({i + 1})' for i in
leaves]
later = (';\n'.join(standalone),
f'ALTER TABLE {sch}.p ATTACH PARTITION {sch}.g{gi} FOR
VALUES FROM ({lo}) TO ({hi})',
';\n'.join(g))
else:
st += g
return st, later
def occupy(sch, base, occ):
if occ == 'none':
return []
ks = {'s1': [1], 's3': [3], 'blk': list(range(1, 11))}[occ]
st = [f'CREATE TABLE {sch}.occ (x int)']
for k in ks:
st.append(f'ALTER TABLE {sch}.occ ADD CONSTRAINT
"{make_object_name(base, str(k))}" CHECK (x > {k})')
return st
def fk_clause(nm, j, sch):
b = base_name(nm)
if b is None:
return f'FOREIGN KEY (a) REFERENCES {sch}.p'
# the second FK gets its own explicit name: same base minus the last byte,
plus "x"
name = b if j == 0 else b[:-1] + 'x'
return f'CONSTRAINT "{name}" FOREIGN KEY (a) REFERENCES {sch}.p'
def case_sql(c):
n, depth, nm, occ, fks, path = c
sch = ident(c)
base = base_name(nm) or ('fp_a_fkey' if path == 'detach' else 'f_a_fkey')
st = [f'DROP SCHEMA IF EXISTS {sch} CASCADE', f'CREATE SCHEMA {sch}']
st += occupy(sch, base, occ)
pst, later = partition_ddl(sch, n, depth, skip_last=path in ('attach',
'partof'))
st += pst
fkcl = [fk_clause(nm, j, sch) for j in range(fks)]
if path == 'detach':
st.append(f'CREATE TABLE {sch}.fp (a int) PARTITION BY LIST (a)')
st.append(f'CREATE TABLE {sch}.f PARTITION OF {sch}.fp DEFAULT')
st += [f'ALTER TABLE {sch}.fp ADD {x}' for x in fkcl]
st.append(f'ALTER TABLE {sch}.fp DETACH PARTITION {sch}.f')
return sch, st
if path == 'inline':
st.append(f'CREATE TABLE {sch}.f (a int, {", ".join(fkcl)})')
else:
st.append(f'CREATE TABLE {sch}.f (a int)')
st += [f'ALTER TABLE {sch}.f ADD {x}' for x in fkcl]
if path == 'attach':
st += [later[0], later[1]]
elif path == 'partof':
st += [later[2]]
return sch, st
def listing(sch):
return (f"SELECT conrelid::regclass::text || ' ' ||
confrelid::regclass::text || ' ' || conname "
f"FROM pg_constraint WHERE connamespace = '{sch}'::regnamespace
ORDER BY 1")
def psql(a, sql):
r = subprocess.run([os.path.join(a.bindir, 'psql'), '-X', '-q', '-At',
'-v', 'ON_ERROR_STOP=1',
'-h', a.host, '-p', str(a.port), '-d', a.db],
input=sql, capture_output=True, text=True)
if r.returncode:
sys.exit(f'psql failed: {r.stderr[:500]}')
return r.stdout
def run(a):
sel = select(cases(), a.sample, a.only_n, a.only_path)
with open(a.out, 'w') as f:
for i, c in enumerate(sel):
sch, st = case_sql(c)
psql(a, ';\n'.join(st) + ';\n')
if c[5] == 'restore':
dump = subprocess.run([os.path.join(a.bindir, 'pg_dump'), '-h',
a.host, '-p', str(a.port),
'-n', sch, a.db], capture_output=True,
text=True, check=True).stdout
psql(a, f'DROP SCHEMA {sch} CASCADE;\n')
psql(a, dump)
rows = psql(a, listing(sch) + ';\n')
f.write(f'== {sch}\n{rows}')
psql(a, f'DROP SCHEMA {sch} CASCADE;\n')
if i % 50 == 0:
print(f'{i + 1}/{len(sel)} {sch}', flush=True)
with open(a.out, 'a') as f:
f.write(f'== done: {len(sel)} cases\n')
print(f'done: {len(sel)} cases -> {a.out}')
def main():
ap = argparse.ArgumentParser()
sp = ap.add_subparsers(dest='cmd', required=True)
r = sp.add_parser('run')
r.add_argument('--host', required=True)
r.add_argument('--port', type=int, required=True)
r.add_argument('--bindir', required=True)
r.add_argument('--db', default='postgres')
r.add_argument('--out', required=True)
r.add_argument('--sample', type=int, default=40)
r.add_argument('--only-n', type=lambda s: [int(x) for x in s.split(',')])
r.add_argument('--only-path', type=lambda s: s.split(','))
sp.add_parser('list')
a = ap.parse_args()
if a.cmd == 'list':
sel = select(cases(), 40, None)
print(f'full product: {len(cases())}; selected: {len(sel)} '
f'({sum(1 for c in sel if c[0] <= 101)} exhaustive n<=101, '
f'{sum(1 for c in sel if c[0] > 101)} sampled n=1000)')
else:
run(a)
if __name__ == '__main__':
main()