Hello all,
Currently pg_dump sorts most dumpable objects by priority, namespace, name
and then object ID. Since triggers and RLS policies belong to tables, there
may be more than one with the same name within the same namespace, leading to
potential sorting discrepancies between databases that only differ by object
IDs.
The attached draft patch (made against `pg_dump_sort.c` on master) breaks
ties for trigger and policy objects by using the table name, increasing the
sort order stability. I have compiled it and executed it against a number of
local databases and it behaves as desired.
I am new to PostgreSQL contribution and my C-skills are rusty, so please let
me know if I can improve the patch, or if there are areas of PostgreSQL that
I have overlooked.
Kind regards,
Benjie Gillam
From 55b3845b746498a1544bc7ced16a369f4da3905f Mon Sep 17 00:00:00 2001
From: Benjie Gillam <ben...@jemjie.com>
Date: Mon, 23 Sep 2019 21:18:24 +0100
Subject: [PATCH] Sort policies and triggers by table name in pg_dump.
To: pgsql-hack...@postgresql.org
---
src/bin/pg_dump/pg_dump_sort.c | 22 ++++++++++++++++++++++
1 file changed, 22 insertions(+)
diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c
index 31fc06a255..5ff7359dff 100644
--- a/src/bin/pg_dump/pg_dump_sort.c
+++ b/src/bin/pg_dump/pg_dump_sort.c
@@ -207,6 +207,28 @@ DOTypeNameCompare(const void *p1, const void *p2)
if (cmpval != 0)
return cmpval;
}
+ else if (obj1->objType == DO_POLICY)
+ {
+ PolicyInfo *pobj1 = *(PolicyInfo *const *) p1;
+ PolicyInfo *pobj2 = *(PolicyInfo *const *) p2;
+
+ /* Sort by table name */
+ cmpval = strcmp(pobj1->poltable->dobj.name,
+ pobj2->poltable->dobj.name);
+ if (cmpval != 0)
+ return cmpval;
+ }
+ else if (obj1->objType == DO_TRIGGER)
+ {
+ TriggerInfo *tobj1 = *(PolicyInfo *const *) p1;
+ TriggerInfo *tobj2 = *(PolicyInfo *const *) p2;
+
+ /* Sort by table name */
+ cmpval = strcmp(tobj1->tgtable->dobj.name,
+ tobj2->tgtable->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.17.1
