-----BEGIN PGP SIGNED MESSAGE-----


    select min(oid) from pg_class;
    ERROR:  function min(oid) does not exist

I got this surprise while trying to optimize a hack for the PostgreSQL
functions in Keystone (http://www.stonekeep.com/keystone).

roland

-----BEGIN PGP SIGNATURE-----
Version: 2.6.2
Comment: Processed by Mailcrypt 3.4, an Emacs/PGP interface

iQCVAwUBNfCGxuoW38lmvDvNAQGA5AP9FkTlbxQlr1jWUASnLPIG7A2/mk0U3eRa
9mSGts7EorIfqK3swSiU8DoTQ4TkfA7JKqe6SCG0MbLpkD9a8ZhsSO50LzbJuBdb
gURS3VFUH8ETuB1aKj67TgngNkO9gJkNOpv3HwD1BgdMBj2sb5aCMHsI5wkjKEC2
GIiOlcvk0Go=
=Et3z
-----END PGP SIGNATURE-----
-- 
Roland B. Roberts, PhD                  Custom Software Solutions
roberts@panix.com                           101 West 15th St #4NN
                                               New York, NY 10011


04 Sep 1998 20:33:12 -0400
Roland Roberts <roberts@panix.com>
MIN not defined for OID types
-- Start of PGP signed section.
> 
>     select min(oid) from pg_class;
>     ERROR:  function min(oid) does not exist
> 
> I got this surprise while trying to optimize a hack for the PostgreSQL
> functions in Keystone (http://www.stonekeep.com/keystone).
> 

Is this in 6.4 or 6.3.2?  I think 6.4 may be able to do this, and if
not, perhaps Thomas can add it.  Sounds like a good use for
binary-compatable operators.

Thomas, can we delete the old operators now that the binary compatable
stuff works?  Just an idea, not sure if it is do-able.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Sat, 5 Sep 1998 00:37:23 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] MIN not defined for OID types
> >     select min(oid) from pg_class;
> >     ERROR:  function min(oid) does not exist
> > I got this surprise while trying to optimize a hack for the 
> > PostgreSQL functions in Keystone 
> > (http://www.stonekeep.com/keystone).
> Is this in 6.4 or 6.3.2?  I think 6.4 may be able to do this, and if
> not, perhaps Thomas can add it.  Sounds like a good use for
> binary-compatable operators.
> Thomas, can we delete the old operators now that the binary compatable
> stuff works?  Just an idea, not sure if it is do-able.

It is do-able, but I'm not sure it is desirable. If there is an exact
function or operator match, then that would be preferred, right? The
"binary-compatible" is a cheat to help performance and to allow us to
not have to define _every_ possible combination of functions and
operators. So it's helped on some things, but probably shouldn't become
the only mechanism. For one thing, if you change the underlying
implementation of a type, then it would not longer be binary compatible
with a second one, and you would need the explicit functions and
operators anyway.

I didn't change any of the type matching behavior of the aggregate
functions that I can recall. Maybe I should have; if I have time I'll
look at it. That would probably be better than bothering to implement
something explicitly for oids:

  postgres=> select min(oid) from foo;
  ERROR:  No such function 'min' with the specified attributes

Also, it's a bit hard to get too excited about a missing feature to
allow someone to "optimize a hack" Isn't that like "patching a patch" or
"fixing a repair"? :)

                    - Tom

Sat, 05 Sep 1998 05:19:03 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] MIN not defined for OID types
> It is do-able, but I'm not sure it is desirable. If there is an exact
> function or operator match, then that would be preferred, right? The
> "binary-compatible" is a cheat to help performance and to allow us to
> not have to define _every_ possible combination of functions and
> operators. So it's helped on some things, but probably shouldn't become
> the only mechanism. For one thing, if you change the underlying
> implementation of a type, then it would not longer be binary compatible
> with a second one, and you would need the explicit functions and
> operators anyway.
> 
> I didn't change any of the type matching behavior of the aggregate
> functions that I can recall. Maybe I should have; if I have time I'll
> look at it. That would probably be better than bothering to implement
> something explicitly for oids:
> 
>   postgres=> select min(oid) from foo;
>   ERROR:  No such function 'min' with the specified attributes

OK.  A question.  Aren't oid's unsigned int, while int4 is signed.  How
does binary compatable indexes handle this.  Do oid's grater than 2gig
work with the int4 indexes?  Do negative integers work with oid indexes?


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Sat, 5 Sep 1998 10:20:39 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] MIN not defined for OID types
> Aren't oid's unsigned int, while int4 is signed.  How
> does binary compatable indexes handle this.  Do oid's grater than 2gig
> work with the int4 indexes?  Do negative integers work with oid indexes?

Good point. I have specified that OIDs are binary compatible with ints,
and in cases where the types mix and there are not explicit
operators/functions then the OIDs and ints are evaluated as signed
quantities.

At the moment, I don't see this as a _big_ problem, in that formerly the
_only_ way of specifying OIDs above 2^^31 in size was to use the
"stringified" syntax of
  OID '4000000000'
or
  '4000000000'::OID
and if one does that now one gets the right thing. All the platforms we
run on use twos-complement integers, so signed and unsigned quantities
below 2^^32-1 are binary equivalent.

But at the least it is sloppy. So perhaps in the future we should have
more explicit functions and operators on OIDs to avoid having to choose
between signed and unsigned evaluation. At that point, we should
probably no longer consider it equivalent to an integer, but rather have
explicit conversion.

When we have indices available for constant functions then the automatic
conversion will work better; I'd made OIDs and int4s equivalent to solve
the problem that
  SELECT * FROM foo WHERE oid = 100;
did not use an index on oid even if it were available, but that
  SELECT * FROM foo WHERE oid = '100'::oid
did.

                      - Tom

Sat, 05 Sep 1998 14:49:33 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] MIN not defined for OID types
-----BEGIN PGP SIGNED MESSAGE-----

>>>>> "Bruce" == Bruce Momjian <maillist@candle.pha.pa.us> writes:

    >> select min(oid) from pg_class;
    >> ERROR: function min(oid) does not exist

    Bruce> Is this in 6.4 or 6.3.2?  I think 6.4 may be able to do
    Bruce> this, and if not, perhaps Thomas can add it.  Sounds like a
    Bruce> good use for binary-compatable operators.

This is in 6.3.2 (or rather, min(oid) is not in 6.3.2 :-)

roland
-----BEGIN PGP SIGNATURE-----
Version: 2.6.2
Comment: Processed by Mailcrypt 3.4, an Emacs/PGP interface

iQCVAwUBNfFaO+oW38lmvDvNAQEj0QP/XO4fM0ph1xJ8bMLOS5OpbqQ+CM1g8aua
Sd6QRjRNH2nPQ0lC3EQ+mFK9NoCa/+cA61ZACnhacPCMIwhHM22rxjb91bZstOZC
fhqh0eTrcZ+xcwVL73k7070gOBNQ3CTZHu0Pf74P+sFW3HMMPhUpCAPJrt7WZUbP
xAib5eRh94U=
=aOoU
-----END PGP SIGNATURE-----
-- 
Roland B. Roberts, PhD                  Custom Software Solutions
roberts@panix.com                           101 West 15th St #4NN
                                               New York, NY 10011


05 Sep 1998 11:35:31 -0400
Roland Roberts <roberts@panix.com>
Re: [HACKERS] MIN not defined for OID types
-----BEGIN PGP SIGNED MESSAGE-----

>>>>> "Thomas" == Thomas G Lockhart <lockhart@alumni.caltech.edu> writes:

    Thomas> Also, it's a bit hard to get too excited about a missing
    Thomas> feature to allow someone to "optimize a hack" Isn't that
    Thomas> like "patching a patch" or "fixing a repair"? :)

Yes, well, in this case, it comes about as a result of trying to make
something database independent.  Keystone has functions for both
PostgreSQL and MySQL, and the MySQL way of doing things seems to be
limitation.  As a result, the DB-independent code does a "select *
from <table>" then queries to find what the column names are.
Obviously, we don't need to do it that way with PostgreSQL.  My "hack"
was to change the query so it wouldn't pull back the whole table just
to find the column names, so I did a "select * from <table> where
oid=min(oid)".

I have no idea how useful the function is in general, but it still
seems like an obvious hole to not be able to ask what the min of a
numeric column is.  I can ask what the min(rowid) is in Oracle (but
don't get me started on Oracle's abysmal "support"...ack!).

roland
-----BEGIN PGP SIGNATURE-----
Version: 2.6.2
Comment: Processed by Mailcrypt 3.4, an Emacs/PGP interface

iQCVAwUBNfFfI+oW38lmvDvNAQFI4gP/cCV6JCVmyBiQQqB4GI4lRAMCdbfTI7Wi
4gtMrHqvy9ct6S2UKbXZFREfVduRzfM9kwxaEw6W5BbcXdaSKQCgoAQrhohMLRtq
IhcTT0CAqbYuF8oy0cxeHq7EI/nJumXhbxk11ZHFYwsxBFKoxjKqGuzP9GExiAQ5
P5y78KFJhzQ=
=NB6u
-----END PGP SIGNATURE-----
-- 
Roland B. Roberts, PhD                  Custom Software Solutions
roberts@panix.com                           101 West 15th St #4NN
                                               New York, NY 10011


05 Sep 1998 11:56:21 -0400
Roland Roberts <roberts@panix.com>
Re: [HACKERS] MIN not defined for OID types
> Yes, well, in this case, it comes about as a result of trying to make
> something database independent.  Keystone has functions for both
> PostgreSQL and MySQL, and the MySQL way of doing things seems to be
> limitation.  As a result, the DB-independent code does a "select *
> from <table>" then queries to find what the column names are.
> Obviously, we don't need to do it that way with PostgreSQL.  My "hack"
> was to change the query so it wouldn't pull back the whole table just
> to find the column names, so I did a "select * from <table> where
> oid=min(oid)".

Ah. Try another hack:

  SELECT * from <table> WHERE FALSE;

That will return zero rows but tell you about the columns. Even better,
it won't traverse the table or an index as a min() operation would since
the optimizer can tell apriori that there will be no matching rows.

I haven't done it myself, but psql seems to get the information it needs
about the columns with a query as above. And if that doesn't work then
there will be another trick which does work for you; someone on the list
will tell us :)

                      - Tom

Sat, 05 Sep 1998 18:50:00 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] MIN not defined for OID types
-----BEGIN PGP SIGNED MESSAGE-----

>>>>> "Thomas" == Thomas G Lockhart <lockhart@alumni.caltech.edu> writes:

    Thomas> Ah. Try another hack:

    Thomas> SELECT * from <table> WHERE FALSE;

Cool, it works interactively, now to stuff in into keystone....

Thanks,

roland

-----BEGIN PGP SIGNATURE-----
Version: 2.6.2
Comment: Processed by Mailcrypt 3.4, an Emacs/PGP interface

iQCVAwUBNfGujuoW38lmvDvNAQHpuwQAuQeB4ixP+UNxSHfF6wlpPi07a3kU6CRR
L/G2gF3ic9ByGWdfYnfJeCg8+IFrz6jswdLmiFuvjJArE96N+WPdvHeaIF81elaL
uBuT5mcIAvGwqp29YuzvNqRyweDI7JDKLUujpFfTgD0LKvNks8VjtE6Ep1WvuD5s
luoNuuz3h7E=
=eK2y
-----END PGP SIGNATURE-----
--
Roland B. Roberts, PhD                  Custom Software Solutions
roberts@panix.com                           101 West 15th St #4NN
                                               New York, NY 10011


05 Sep 1998 17:35:12 -0400
Roland Roberts <roberts@panix.com>
Re: [HACKERS] MIN not defined for OID types