unsupported types in 8.0.1
I'm attempting to upgrade to 8.0.1, and have hit my latest hurdle: an
unsupported type when I try to compare ints. Example:
gr-test=> \d invitecodes
Table "public.invitecodes"
Column | Type | Modifiers
------------+---------
+-----------------------------------------------------------------
invite | integer | not null default
nextval('public.invitecodes_invite_seq'::text)
sponsor | bigint | not null
generated | integer | not null default ((now())::abstime)::integer
expires | integer | not null
acceptedby | bigint |
acceptedon | integer |
expiredon | integer |gr-test=> select expires from invitecodes;
expires
------------
1111611373
1111551093
1112139900
1112169368
(4 rows)
gr-test=> select expires from invitecodes where expires <
((now())::abstime)::int4;
ERROR: unsupported type: 23
gr-test=> select expires from invitecodes where expires > 1;
expires
------------
1111611373
1111551093
1112139900
1112169368
(4 rows)
gr-test=> select expires from invitecodes where 1 <
((now())::abstime)::int4;
expires
------------
1111611373
1111551093
1112139900
1112169368
(4 rows)
I haven't a clue how to go about debugging this. Any pointers?
On Sat, Mar 26, 2005 at 08:25:24AM -0800, Ben wrote:
gr-test=> select expires from invitecodes where expires <
((now())::abstime)::int4;
ERROR: unsupported type: 23
Hmmm...
CREATE TABLE foo (x integer);
INSERT INTO foo (x) VALUES (1000000000);
INSERT INTO foo (x) VALUES (2000000000);
SELECT x FROM foo WHERE x < now()::abstime::integer;
x
------------
1000000000
(1 row)
ANALYZE foo;
SELECT x FROM foo WHERE x < now()::abstime::integer;
ERROR: unsupported type: 23
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
On Sat, Mar 26, 2005 at 10:24:06AM -0700, Michael Fuhr wrote:
SELECT x FROM foo WHERE x < now()::abstime::integer;
ERROR: unsupported type: 23
\set VERBOSITY verbose
SELECT x FROM foo WHERE x < now()::abstime::integer;
ERROR: XX000: unsupported type: 23
LOCATION: convert_timevalue_to_scalar, selfuncs.c:2831
The example I posted fails in REL8_0_STABLE and HEAD but works in
earlier versions.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Michael Fuhr <mike@fuhr.org> writes:
SELECT x FROM foo WHERE x < now()::abstime::integer;
ERROR: unsupported type: 23
It looks like examine_variable shouldn't be throwing away the
RelabelType on the now() call ... this code is all new in 8.0
IIRC, which is why you don't see the failure in prior versions
(you also do not get a good estimate ...)
regards, tom lane
Is there a workaround I could use, or should I stick with 7.4 for now?
On Mar 26, 2005, at 11:14 AM, Tom Lane wrote:
Show quoted text
Michael Fuhr <mike@fuhr.org> writes:
SELECT x FROM foo WHERE x < now()::abstime::integer;
ERROR: unsupported type: 23It looks like examine_variable shouldn't be throwing away the
RelabelType on the now() call ... this code is all new in 8.0
IIRC, which is why you don't see the failure in prior versions
(you also do not get a good estimate ...)regards, tom lane
On Sat, Mar 26, 2005 at 12:22:51PM -0800, Ben wrote:
Is there a workaround I could use, or should I stick with 7.4 for now?
The documentation discourages using abstime -- is there a reason
you're using it instead of extract(epoch from now())? That should
work in the query.
Is there a reason you're using integer instead of timestamp or
timestamp with time zone?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Ben <bench@silentmedia.com> writes:
Is there a workaround I could use,
Make the column abstime instead of int, perhaps. Or better yet
timestamp. Have you considered what will happen in 2038?
regards, tom lane
The way our app is structured, storing unixtime is the most efficient
way to get what we want.
I can't recall why I started using abstime... I started it with some
other projects years ago and was under the impression that it was the
preferred way. Maybe it was then, but isn't anymore? Maybe I'm just
wrong? Anyway, extract() works great, so I'll use that from now on.
Thanks!
On Mar 26, 2005, at 12:41 PM, Michael Fuhr wrote:
Show quoted text
On Sat, Mar 26, 2005 at 12:22:51PM -0800, Ben wrote:
Is there a workaround I could use, or should I stick with 7.4 for now?
The documentation discourages using abstime -- is there a reason
you're using it instead of extract(epoch from now())? That should
work in the query.Is there a reason you're using integer instead of timestamp or
timestamp with time zone?--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Well, 33 years from now, it's a pretty safe bet that this project won't
exist. :) Or, if it does exist, that it will have been rewritten from
scratch for numerous other reasons.
(I know, I know, and 640KB ought to be enough memory for everybody.....
but this time I'm right.)
On Mar 26, 2005, at 12:57 PM, Tom Lane wrote:
Show quoted text
Ben <bench@silentmedia.com> writes:
Is there a workaround I could use,
Make the column abstime instead of int, perhaps. Or better yet
timestamp. Have you considered what will happen in 2038?regards, tom lane