The following bug has been logged online:

Bug reference: 2821
Logged by: Edwin Groothuis
Email address: mavetju (AT) gmail (DOT) com
PostgreSQL version: 8.0 / 8.1. / 8.
Operating system: FreeBSD
Description: xid cannot be casted to a different type
Details:

This statement is part of a longer one, which we used on 8.0
to determine the status of the locks on the database:

select relation,transaction::bigint,count(*) as waiting from
pg_locks where not granted group by relation,transaction;

This worked fine on 8.0.x. Now I'm preparing to move to 8.1
and/or 8.2, but that above statement now gives me:

ERROR: could not identify an ordering operator for type xid
HINT: Use an explicit ordering operator or modify the query.

According to a discussion on IRC with neilc and davidfetter:

<neilc> davidfetter, Mavvie: we need an ordering operator to do GROUP
BY
<neilc> for some reason that's not clear to me atm
<neilc> perhaps because originally we only implemented grouping / aggs
via sorting?
<davidfetter> neilc, makes sense, in a way
<davidfetter> yeah
<neilc> anyway, so the problem is just there aren't ordering operators
for the xid type
<Mavvie> neilc: I've tried to cast the transaction field (which is the
xid) to a text, bigint or something else but it keeps coming back as "cannot
cast type xid to ..."
<neilc> there probably should be, like we added for tid i think in 8.2
<davidfetter> would this be an initdb-forcing thing? :f
<neilc> well, you could package the changes as sql
<davidfetter> cool
<neilc> i wouldn't personally bother backporting it tho, it's not a bug
per se
<davidfetter> hrm. i'd say anything that causes you not to be able to do
"expected" operations like aggregation is a bug

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

"Edwin Groothuis" <mavetju (AT) gmail (DOT) com> writes:
Really?

regression=# select version();
version
------------------------------------------------------------------
PostgreSQL 8.0.9 on hppa2.0-hp-hpux10.20, compiled by GCC 2.95.3
(1 row)

regression=# select relation,transaction::bigint,count(*) as waiting from
regression-# pg_locks where not granted group by relation,transaction;
ERROR: cannot cast type xid to bigint
regression=# select relation,transaction,count(*) as waiting from
regression-# pg_locks where not granted group by relation,transaction;
ERROR: could not identify an ordering operator for type xid
HINT: Use an explicit ordering operator or modify the query.

which is exactly the same behavior as 8.2. I suppose you might have had
a custom cast in your 8.0 database.

The reason there are no ordering operators for xid is that the internal
comparison semantics for xid violate the law of transitivity; there can
be XIDs for which A < B, B < C, but C < A. This is OK for the system's
internal purposes but would confuse btree terribly.

For the purposes you're showing us, there seems no particular harm in
sorting in integer order without worrying about the xid ordering, so
I'd suggest you stick with the custom cast.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Edwin Groothuis <edwin (AT) mavetju (DOT) org> writes:
There was no change between 8.0 and 8.0.9 on such a point, because
adding or removing a cast would have required an initdb which we don't
do in minor releases. Just to prove it, I checked out 8.0.6 and
rebuilt it:

template1=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 8.0.6 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 20060525 (Red Hat 4.1.1-1)
(1 row)

template1=# select relation,transaction::bigint,count(*) as waiting from
template1-# pg_locks where not granted group by relation,transaction;
ERROR: cannot cast type xid to bigint
template1=# select relation,transaction,count(*) as waiting from
template1-# pg_locks where not granted group by relation,transaction;
ERROR: could not identify an ordering operator for type xid
HINT: Use an explicit ordering operator or modify the query.


Better look harder; there's *something* nonstandard about your 8.0
database.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane wrote:
if I had to guess I would say that there is slony installed in the old
database(slony installs operators for comparing XID) ...


Stefan

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Sun, Dec 10, 2006 at 05:47:16PM -0500, Tom Lane wrote:
Thanks for the push, I found it: It is part of slony which isn't
on the new databases:

mail=> select oid,* from pg_catalog.pg_type where typname='xid';
oid | typname | typnamespace | typowner | typlen | typbyval | typtype |
28 | xid | 11 | 1 | 4 | t | b |

mail=> select * from pg_catalog.pg_cast where castsource=28;
castsource | casttarget | castfunc | castcontext
------------+------------+----------+-------------
28 | 21800 | 0 | i

mail=> select oid,* from pg_catalog.pg_type where oid=21800;
oid | typname | typnamespace | typowner | typlen | typbyval | typtype | typisdefined | typdelim | typrelid | typelem | typinput | typoutput | typreceive | typsend | typanalyze | typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typdefaultbin | typdefault
-------+---------+--------------+----------+--------+----------+---------+--------------+----------+----------+---------+------------------------+-------------------------+------------+---------+------------+----------+------------+------------+-------------+-----------+----------+---------------+------------
21800 | xxid | 21799 | 1 | 4 | t | b | t | , | 0 | 0 | _upsreplication.xxidin | _upsreplication.xxidout | - | - | - | i | p | f | 0 | -1 | 0 | |

When running it on a test database with 8.1.5 with replication
enabled it works fine there too.

Sorry about the noise,

Edwin

--
Edwin Groothuis | Personal website: http://www.mavetju.org
edwin (AT) mavetju (DOT) org | Weblog: http://weblog.barnet.com.au/edwin/

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Sun, Dec 10, 2006 at 03:20:50PM -0500, Tom Lane wrote:
Hmm... We used this script on 8.0.0 to 8.0.6 (which is the current
database version we're running), so when I tested it on 8.0.6, I
assumed it was fine on all 8.0.>6 too:

mail=> select version();
version
-------------------------------------------------------------------------------------------------
PostgreSQL 8.0.6 on i386-unknown-freebsd6.0, compiled by GCC gcc (GCC) 3.4.4 [FreeBSD] 20050518
(1 row)

mail=> select relation,transaction,count(*) as waiting from pg_locks where not granted group by relation,transaction;
relation | transaction | waiting
----------+-------------+---------
(0 rows)

Except that there isn't a custom cast...

Edwin
--
Edwin Groothuis | Personal website: http://www.mavetju.org
edwin (AT) mavetju (DOT) org | Weblog: http://weblog.barnet.com.au/edwin/

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend