Thread: MIN not defined for OID types
-----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
-- 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)
> > 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
> 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)
> 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
-----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
-----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
> 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
-----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