Always have pg_dump write rules in a consistent order
Hi,
When working on a new feature for PostgreSQL I noticed that the
pg_upgrade tests became flaky due to rules being dumped in a different
order between the original cluster and the upgraded cluster. (For
context: my regress scripts left a bunch of views with dependency
circles which I had forgot to clean up.)
So I propose that we sort rules on (schema, name, relation_name) instead
of (schema, name, oid) like it currently does. The patch itself is small
enough and makes it easier to test that we upgrade rules and views
correctly with pg_upgrade.
Andreas
Attachments:
0001-Make-pg_dump-always-write-rules-in-a-stable-order.patchtext/x-patch; charset=UTF-8; name=0001-Make-pg_dump-always-write-rules-in-a-stable-order.patchDownload
From 18b39f0e79306082481c7ee304de578f5b32a6b7 Mon Sep 17 00:00:00 2001
From: Andreas Karlsson <andreas@proxel.se>
Date: Tue, 29 Oct 2024 14:59:04 +0100
Subject: [PATCH] Make pg_dump always write rules in a stable order
Instead of sorting rules by schema and name and then finally oid in
pg_dump we sort them by schema, name and relation name. This way we make
sure that the order rules are dumped in consistent across dump plus
restore.
This is especially a big problem if we have to break depednency many
cycles involving views and we get a lot of views named _RETURN.
---
src/bin/pg_dump/pg_dump_sort.c | 11 +++++++++++
1 file changed, 11 insertions(+)
diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c
index 4cb754caa55..e0f70abdcc2 100644
--- a/src/bin/pg_dump/pg_dump_sort.c
+++ b/src/bin/pg_dump/pg_dump_sort.c
@@ -305,6 +305,17 @@ DOTypeNameCompare(const void *p1, const void *p2)
if (cmpval != 0)
return cmpval;
}
+ else if (obj1->objType == DO_RULE)
+ {
+ RuleInfo *robj1 = *(RuleInfo *const *) p1;
+ RuleInfo *robj2 = *(RuleInfo *const *) p2;
+
+ /* Sort by the rule's table name (namespace and name were considered already) */
+ cmpval = strcmp(robj1->ruletable->dobj.name,
+ robj2->ruletable->dobj.name);
+ if (cmpval != 0)
+ return cmpval;
+ }
/* Usually shouldn't get here, but if we do, sort by OID */
return oidcmp(obj1->catId.oid, obj2->catId.oid);
--
2.45.2
Andreas Karlsson <andreas@proxel.se> writes:
When working on a new feature for PostgreSQL I noticed that the
pg_upgrade tests became flaky due to rules being dumped in a different
order between the original cluster and the upgraded cluster. (For
context: my regress scripts left a bunch of views with dependency
circles which I had forgot to clean up.)
So I propose that we sort rules on (schema, name, relation_name) instead
of (schema, name, oid) like it currently does. The patch itself is small
enough and makes it easier to test that we upgrade rules and views
correctly with pg_upgrade.
Seems reasonable. Pushed with some trivial cosmetic adjustments
to make it look more like the identical adjacent cases for policies
and triggers.
regards, tom lane