Thread: using xmin in a query?

using xmin in a query?

From

Michael Nolan

Date:

28 July 2011, 16:40:14

Why does this query succeed:

select count(*) from tablename where xmin = 2

while this query fails:

select count(*) from tablename where xmin != 2

The latter will generate an error message (using 9.0.4, but it does not seem to be version specific):

ERROR: operator does not exist: xid <> integer
LINE 1: select count(*) from tablename where xmin != 2;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

What cast or comparison operator would work? You cannot cast an xid to an integer, nor can you cast an integer to an xid.

The only way I can get this to work is:

select count(*) from tablename where not xmin = 2

That seems pretty obscure.
--
Mike Nolan
nolan@tssi.com

Re: using xmin in a query?

From

Andy Colson

Date:

28 July 2011, 17:33:06

On 7/28/2011 11:40 AM, Michael Nolan wrote:
> Why does this query succeed:
>
> select count(*) from tablename where xmin = 2
>
> while this query fails:
>
> select count(*) from tablename where xmin != 2
>

You probably want <>.

select count(*) from tablename where xmin <> 2

-Andy

Re: using xmin in a query?

From

Michael Nolan

Date:

28 July 2011, 18:09:51

On Thu, Jul 28, 2011 at 12:23 PM, Andy Colson <andy@squeakycode.net> wrote:

On 7/28/2011 11:40 AM, Michael Nolan wrote:
Why does this query succeed:

select count(*) from tablename where xmin = 2

while this query fails:

select count(*) from tablename where xmin != 2

You probably want <>.

That doesn't work either.
--
Mike Nolan

select count(*) from tablename where xmin <> 2

-Andy

Re: using xmin in a query?

From

Rodrigo Gonzalez

Date:

28 July 2011, 18:46:56

On 07/28/2011 03:09 PM, Michael Nolan wrote:

On Thu, Jul 28, 2011 at 12:23 PM, Andy Colson <andy@squeakycode.net> wrote:
On 7/28/2011 11:40 AM, Michael Nolan wrote:
Why does this query succeed:

select count(*) from tablename where xmin = 2

while this query fails:

select count(*) from tablename where xmin != 2

You probably want <>.

That doesn't work either.

What about select count(*) from tablename where xmin::text::integer != 2;

Regards

Rodrigo

Re: using xmin in a query?

From

Tom Lane

Date:

28 July 2011, 19:28:01

Michael Nolan <htfoot@gmail.com> writes:
> Why does this query succeed:
> select count(*) from tablename where xmin = 2

> while this query fails:

> select count(*) from tablename where xmin != 2

It told you why not:

> ERROR:  operator does not exist: xid <> integer

You could do "where not (xmin = 2)", I suppose.

            regards, tom lane

Re: using xmin in a query?

From

Michael Nolan

Date:

28 July 2011, 21:55:44

On Thu, Jul 28, 2011 at 2:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Michael Nolan <htfoot@gmail.com> writes:
> Why does this query succeed:
> select count(*) from tablename where xmin = 2

> while this query fails:

> select count(*) from tablename where xmin != 2

It told you why not:

> ERROR: operator does not exist: xid <> integer

You could do "where not (xmin = 2)", I suppose.

I understand that, Tom, and my original posted did cite 'not xmin = 2' as working.

The parentheses appear to be optional, though in a more complex query they would probably be necessary to make sure it parses properly.

It appears to me that it is doing an implicit cast of the integer '2' into an xid in the first query.

It seems like we're being inconsistent here in allowing 'where xid = integer'
but not allowing 'where xid != integer'.

Is there no explicit 'cast to xid' available?
--
Mike Nolan

Re: using xmin in a query?

From

Tom Lane

Date:

28 July 2011, 22:09:17

Michael Nolan <htfoot@gmail.com> writes:
> It seems like we're being inconsistent here in allowing 'where xid =
> integer' but not allowing 'where xid != integer'.

Well, if you look into pg_operator you'll soon find that there are
exactly two built-in operators that accept type xid: "=(xid,xid)" and
"=(xid,integer)" (where I'd say the latter is just a kluge).
There hasn't previously been any demand to flesh it out more than that.
Do you have an actual use-case where <> would be helpful, or is this
just experimentation?

            regards, tom lane

Re: using xmin in a query?

From

Michael Nolan

Date:

28 July 2011, 22:20:31

On Thu, Jul 28, 2011 at 5:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Michael Nolan <htfoot@gmail.com> writes:
> It seems like we're being inconsistent here in allowing 'where xid =
> integer' but not allowing 'where xid != integer'.

Well, if you look into pg_operator you'll soon find that there are
exactly two built-in operators that accept type xid: "=(xid,xid)" and
"=(xid,integer)" (where I'd say the latter is just a kluge).
There hasn't previously been any demand to flesh it out more than that.
Do you have an actual use-case where <> would be helpful, or is this
just experimentation?

I'm not sure yet. I was doing some thinking about ways to do incremental backups
(at least for inserted/updated rows, deleted rows present a different challenge),
and was just doing some simple queries to see what worked and what didn't..

It also appears you cannot group on a column of type xid.

Would adding a <> operator enable that?
--
Mike Nolan

Re: using xmin in a query?

From

Tom Lane

Date:

28 July 2011, 22:36:50

Michael Nolan <htfoot@gmail.com> writes:
> It also appears you cannot group on a column of type xid.

You can in 8.4 and up.  Previous versions only know how to GROUP BY
sortable columns, which requires a btree opclass, which xid doesn't
have and really can't have because it doesn't have a linear ordering.
There is a hash opclass for it, though, so in versions that know how to
GROUP BY using hashing, it'll work.

> Would adding a <> operator enable that?

No, it's pretty irrelevant ...

            regards, tom lane

Re: using xmin in a query?

From

Michael Nolan

Date:

28 July 2011, 23:27:14

On Thu, Jul 28, 2011 at 5:36 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Michael Nolan <htfoot@gmail.com> writes:
> It also appears you cannot group on a column of type xid.

You can in 8.4 and up. Previous versions only know how to GROUP BY
sortable columns, which requires a btree opclass, which xid doesn't
have and really can't have because it doesn't have a linear ordering.
There is a hash opclass for it, though, so in versions that know how to
GROUP BY using hashing, it'll work.

:sigh: I thought I had done all the tests on my 9.0.4 testbed server too. One of
these days I hope to get the production and development servers off 8.2.

I more or less understand why xid types don't have a linear ordering from Robert Hass's tutorial at PGCON11.

So, a <> operator (either xid,xid or xid,integer) would need to be implemented using the hash opclass, correct?

(I don't have a use case for it yet, though.)

> Would adding a <> operator enable that?

No, it's pretty irrelevant ...

OK, thanks for putting up with my noobie questions.
--
Mike Nolan

Re: using xmin in a query?

From

Tom Lane

Date:

28 July 2011, 23:53:55

Michael Nolan <htfoot@gmail.com> writes:
> So, a <> operator (either xid,xid or xid,integer) would need to be
> implemented using the hash opclass, correct?

No, it's unrelated to the opclass.  It'd be worth marking it as the
negator of the equality operator, but otherwise it'd really be
unconnected to anything else.

            regards, tom lane