Thread: XMIN/xid vs UNION

XMIN/xid vs UNION

From
Karsten Hilbert
Date:
Dear all,

some of my views are created with help of the UNION operator.
Now, I also need to include the base table XMIN system column
into those views. Which works fine (as long as I alias them)
except that UNION does an internal sort. My PG version (7.4)
complains about not being able to find a sort operator for
data type XID (of which XMIN is).

Searching the web unearthes a solution for loading those
missing operators for as early as 6.4.1 (1998) !

 http://archives.postgresql.org/pgsql-general/1998-11/msg00096.php

Strangely enough I see those operators listed in the release
notes for 7.2 ...

 http://www.postgresql.org/docs/7.2/interactive/release-7-2.html

Are those operators included in PG > 7.4 ? Should they be ? Is
it my version that's compiled without them, perhaps ?

Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: XMIN/xid vs UNION

From
Martijn van Oosterhout
Date:
Try a cast, or just use UNION ALL

On Fri, Oct 29, 2004 at 04:44:37PM +0200, Karsten Hilbert wrote:
> Dear all,
>
> some of my views are created with help of the UNION operator.
> Now, I also need to include the base table XMIN system column
> into those views. Which works fine (as long as I alias them)
> except that UNION does an internal sort. My PG version (7.4)
> complains about not being able to find a sort operator for
> data type XID (of which XMIN is).
>
> Searching the web unearthes a solution for loading those
> missing operators for as early as 6.4.1 (1998) !
>
>  http://archives.postgresql.org/pgsql-general/1998-11/msg00096.php
>
> Strangely enough I see those operators listed in the release
> notes for 7.2 ...
>
>  http://www.postgresql.org/docs/7.2/interactive/release-7-2.html
>
> Are those operators included in PG > 7.4 ? Should they be ? Is
> it my version that's compiled without them, perhaps ?
>
> Thanks,
> Karsten
> --
> GPG key ID E4071346 @ wwwkeys.pgp.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Attachment

Re: XMIN/xid vs UNION

From
Karsten Hilbert
Date:
> Try a cast, or just use UNION ALL
Thanks.

Casting didn't work (it was missing the proper cast function
from xid to int4) but using UNION ALL worked. This was also
possible to use in my case since both parts of the UNION do
indeed return distinct sets of rows so UNION ALL does not
produce duplicates.

However, the question still holds true: Is there any
particular reason those operators aren't found in my PG
installation despite being listed as added since 7.2 ?
IOW why does
 select 1::xid::int2/int4/int8
fail (on 7.4) despite 7.2 docs suggesting it should work ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: XMIN/xid vs UNION

From
Tom Lane
Date:
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> However, the question still holds true: Is there any
> particular reason those operators aren't found in my PG
> installation despite being listed as added since 7.2 ?

The only thing that was added in 7.2 was xid equality.

There was some talk recently of adding the other comparison operations,
but I'm not sure it's a good idea.  For many purposes you'd want such
comparison operators to use the same semantics as TransactionIdPrecedes
(ie, compare mod 2^31) but that would mean that some of the usual
arithmetic laws break down (no transitive law, no triangle inequality)
and in particular you could not sort or build a btree index with such
operators.  Which in turn means that you still wouldn't get the behavior
you wanted of having UNION or ORDER BY "just work".

            regards, tom lane