[Andreas Karlsson <andreas@proxel.se>]

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:

  [text/x-patch] 0001-Make-pg_dump-always-write-rules-in-a-stable-order.patch (1.4K, ../b4e468d8-0cd6-42e6-ac8a-1d6afa6e0cf1@proxel.se/2-0001-Make-pg_dump-always-write-rules-in-a-stable-order.patch)
  download | inline diff:
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