"Edwin Groothuis" <mavetju@gmail.com> writes:
> 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.
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