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

Поиск
Список
Период
Сортировка
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

Вложения

В списке pgsql-general по дате отправления:

Предыдущее
От: Christopher Browne
Дата:
Сообщение: Re: Scheduler in Postgres
Следующее
От: Ciprian Popovici
Дата:
Сообщение: Trigger: what rows were deleted?