Re: BUG #2821: xid cannot be casted to a different type

On Sun, Dec 10, 2006 at 03:20:50PM -0500, Tom Lane wrote:
> "Edwin Groothuis" <mavetju(at)gmail(dot)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.

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)

> 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.

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/