sorting operators in pg_dump

Started by Peter Eisentrautabout 14 years ago3 messages
#1Peter Eisentraut
peter_e@gmx.net
2 attachment(s)

Currently, pg_dump sorts operators by name, but operators with the same
name come out in random order. A few releases ago we adjusted this for
functions, so that they are in increasing number of arguments order.
I'd like to do this for operators as well, so that they come out in
order, say, prefix, postfix, infix.

(It might be surprising that something like this is necessary, but it
happens. ip4r for example contains operators with different fixnesses
(fixities?).)

Patch attached, and a little test case.

Attachments:

pg_dump-operator-sort.patchtext/x-patch; charset=UTF-8; name=pg_dump-operator-sort.patchDownload
diff --git i/src/bin/pg_dump/pg_dump.c w/src/bin/pg_dump/pg_dump.c
index 89a8a23..486cf64 100644
--- i/src/bin/pg_dump/pg_dump.c
+++ w/src/bin/pg_dump/pg_dump.c
@@ -3137,6 +3137,7 @@ getOperators(int *numOprs)
 	int			i_oprname;
 	int			i_oprnamespace;
 	int			i_rolname;
+	int			i_oprkind;
 	int			i_oprcode;
 
 	/*
@@ -3152,6 +3153,7 @@ getOperators(int *numOprs)
 		appendPQExpBuffer(query, "SELECT tableoid, oid, oprname, "
 						  "oprnamespace, "
 						  "(%s oprowner) AS rolname, "
+						  "oprkind, "
 						  "oprcode::oid AS oprcode "
 						  "FROM pg_operator",
 						  username_subquery);
@@ -3161,6 +3163,7 @@ getOperators(int *numOprs)
 		appendPQExpBuffer(query, "SELECT tableoid, oid, oprname, "
 						  "0::oid AS oprnamespace, "
 						  "(%s oprowner) AS rolname, "
+						  "oprkind, "
 						  "oprcode::oid AS oprcode "
 						  "FROM pg_operator",
 						  username_subquery);
@@ -3172,6 +3175,7 @@ getOperators(int *numOprs)
 						  "oid, oprname, "
 						  "0::oid AS oprnamespace, "
 						  "(%s oprowner) AS rolname, "
+						  "oprkind, "
 						  "oprcode::oid AS oprcode "
 						  "FROM pg_operator",
 						  username_subquery);
@@ -3190,6 +3194,7 @@ getOperators(int *numOprs)
 	i_oprname = PQfnumber(res, "oprname");
 	i_oprnamespace = PQfnumber(res, "oprnamespace");
 	i_rolname = PQfnumber(res, "rolname");
+	i_oprkind = PQfnumber(res, "oprkind");
 	i_oprcode = PQfnumber(res, "oprcode");
 
 	for (i = 0; i < ntups; i++)
@@ -3202,6 +3207,7 @@ getOperators(int *numOprs)
 		oprinfo[i].dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_oprnamespace)),
 												  oprinfo[i].dobj.catId.oid);
 		oprinfo[i].rolname = pg_strdup(PQgetvalue(res, i, i_rolname));
+		oprinfo[i].oprkind = (PQgetvalue(res, i, i_oprkind))[0];
 		oprinfo[i].oprcode = atooid(PQgetvalue(res, i, i_oprcode));
 
 		/* Decide whether we want to dump it */
diff --git i/src/bin/pg_dump/pg_dump.h w/src/bin/pg_dump/pg_dump.h
index 3bfeb31..1c6dd26 100644
--- i/src/bin/pg_dump/pg_dump.h
+++ w/src/bin/pg_dump/pg_dump.h
@@ -204,6 +204,7 @@ typedef struct _oprInfo
 {
 	DumpableObject dobj;
 	char	   *rolname;
+	char		oprkind;
 	Oid			oprcode;
 } OprInfo;
 
diff --git i/src/bin/pg_dump/pg_dump_sort.c w/src/bin/pg_dump/pg_dump_sort.c
index 368208d..64f38ff 100644
--- i/src/bin/pg_dump/pg_dump_sort.c
+++ w/src/bin/pg_dump/pg_dump_sort.c
@@ -178,6 +178,16 @@ DOTypeNameCompare(const void *p1, const void *p2)
 		if (cmpval != 0)
 			return cmpval;
 	}
+	else if (obj1->objType == DO_OPERATOR)
+	{
+		OprInfo	*oobj1 = *(OprInfo * const *) p1;
+		OprInfo *oobj2 = *(OprInfo * const *) p2;
+
+		/* oprkind is 'l', 'r', or 'b'; this sorts prefix, postfix, infix */
+		cmpval = (oobj2->oprkind - oobj1->oprkind);
+		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);
pg_dump-operator-sort-test.sqltext/x-sql; charset=UTF-8; name=pg_dump-operator-sort-test.sqlDownload
#2Dimitri Fontaine
dimitri@2ndQuadrant.fr
In reply to: Peter Eisentraut (#1)
Re: sorting operators in pg_dump

Peter Eisentraut <peter_e@gmx.net> writes:

order, say, prefix, postfix, infix.

(It might be surprising that something like this is necessary, but it
happens. ip4r for example contains operators with different fixnesses
(fixities?).)

I think it's called “arity” :)

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

#3Robert Haas
robertmhaas@gmail.com
In reply to: Peter Eisentraut (#1)
Re: sorting operators in pg_dump

On Sun, Jan 1, 2012 at 11:50 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

Currently, pg_dump sorts operators by name, but operators with the same
name come out in random order.  A few releases ago we adjusted this for
functions, so that they are in increasing number of arguments order.
I'd like to do this for operators as well, so that they come out in
order, say, prefix, postfix, infix.

(It might be surprising that something like this is necessary, but it
happens.  ip4r for example contains operators with different fixnesses
(fixities?).)

Patch attached, and a little test case.

Seems like a good idea. The patch looks good, too.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company