Thread: 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: > 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
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: > 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: > 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