From: | "Florian G(dot) Pflug" <fgp(at)phlo(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | analyze-error: "cannot compare arrays of different element types" revisited |
Date: | 2004-12-17 13:20:36 |
Message-ID: | 41C2DD24.7050407@phlo.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi
A few weeks ago, I reported that ANALYZE gives the error
"cannot compare arrays of different element types"
in one of my databases.
I now constructed a small testcase that is able to reproduce the problem:
---------------------------------------------------------------------------
-- The database was created as:
-- create database array_testcase with owner fgp encoding 'utf-8' ;
-- We use the "-" - operator from contrib/intarray
-- For testing, we just import the "-" operator,
-- not everything from contrib/intarray
create function intarray_del_elem(int4[], int4)
returns int[]
as '$libdir/_int' language 'c' with (isStrict, isCachable)
;
create operator - (
leftarg = int4[],
rightarg = int4,
procedure = intarray_del_elem
);
-- Create two domains and a table.
create domain myint as int8 ;
create domain mylist as int8[] ;
create table mytable (value myint, list mylist) ;
-- Insert testdata
insert into mytable values (1::myint, array[1]::mylist) ;
insert into mytable values (2::myint, array[2]::mylist) ;
select * from mytable ;
-- Generate empty array
update mytable set list = list::int4[] - 1::myint::int4 where value =
1::myint ;
select * from mytable ;
-- Reinsert element
update mytable set list = (list::int8[] || 1::myint::int8) where value =
1::myint;
-- It's broken now (Strange large numbers)
select * from mytable ;
-- This gives "ERROR: cannot compare arrays of different element types"
analyze mytable ;
-- Drop everything
drop table mytable ;
drop domain mylist ;
drop domain myint ;
drop operator - (int4[], int4) ;
drop function intarray_del_elem(int4[], int4) ;
---------------------------------------------------------------------------
Note that this is quite hackish, and I realize it will fail if the
entries of mylist grow beyong 2^31-1, but I guess it still shouldn't
make anaylze fail, or produce random numbers ;-)
Since postgres disallows empty array literals (array[] gives an syntax
error), I guess creating empty array by removing the last element is
quite hackish too... Will empty arrays be eventually supported, or will
they be forbidden entirely (e.g, making "array[1] - 1" return null)?
greetings, Florian Pflug
From | Date | Subject | |
---|---|---|---|
Next Message | Ciprian Popovici | 2004-12-17 13:53:48 | Trigger: what rows were deleted? |
Previous Message | Christopher Browne | 2004-12-17 13:01:05 | Re: Scheduler in Postgres |