Wednesday, August 01, 2007

Postgres Xid Type Confusion

This morning I was trying to write a query against pg_locks but received this gem of a message:

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

It even gave me a hint, and I still have no idea what the heck I'm supposed to do to fix it.

A Google search turned up this promising discussion. Unfortunately, it turned out to be a dead end, as the poster resolved his problem but I couldn't extract a solution from it.

I ended up looking for an alternate approach. From checking the postgres source code, I learned that an xid is output as a cstring. From Googling around, I learned that it's possible to convert cstrings to text by using the very handy function:

create or replace function any2text(anyelement) returns text as '
  return $1;
' language plpgsql;

As the name suggests, the function above will convert any argument to text - not just xids.

With any2text I am now able to say any2text(transactionid) to convert this type as text. This means that, among other things, the group by clause can now work and my error above went away.

Warning: the above may in fact be a terrible idea. I'm not quite sure. I just know that it seems to work as advertised, and for my ad hoc queries was what I needed. I'd do a wee bit more research before I used this in a production system.

1 comment:

  1. Anonymous2:05 PM

    As mentioned in the promising discussion you linked to, transactions IDs don't allow being ordered by default because of that ordering isn't transitive. For grouping purposes, casting them as text is a prefectly acceptable thing to do--you just need to understand that if you used that text version for ORDER BY, you would not get what you expected.

    Your function isn't needed though, the two built-in ways to handle this are: