analyze-error: "cannot compare arrays of different element types" revisited

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

Responses

Browse pgsql-general by date

  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