[GENERAL] 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
611373
551093
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
611373
551093
1112139900
1112169368
(4 rows)
gr-test= select expires from invitecodes where 1
((now())::abstime)::int4;
expires
611373
551093
1112139900
1112169368
(4 rows)
I haven't a clue how to go about debugging this. Any pointers?
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] unsupported types in 8.0.1
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 (10); INSERT INTO foo (x) VALUES (20); SELECT x FROM foo WHERE x now()::abstime::integer; x 10 (1 row) ANALYZE foo; SELECT x FROM foo WHERE x now()::abstime::integer; ERROR: unsupported type: 23 -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] unsupported types in 8.0.1
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/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] unsupported types in 8.0.1
Michael Fuhr [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] unsupported types in 8.0.1
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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] unsupported types in 8.0.1
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/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] unsupported types in 8.0.1
Ben [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] unsupported types in 8.0.1
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/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] unsupported types in 8.0.1
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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
