error after dropping column

Hi all,

not sure if I should send it here; if it was reported before in
pgsql-general. you can just ignore it :-)

with postgres 8.2.4:

create table test (t1 text);
create function row2text(test) returns text as 'select $1::text' language
sql immutable;
create index test_row2text_idx on test( row2text(test) );
alter table test add t2 text;
alter table test drop t2;
insert into test(t1) select 'foo';

ERROR: table row type and query-specified row type do not match
DETAIL: Physical storage mismatch on dropped attribute at ordinal position
2.

--
Filip Rembiałkowski
JID,mailto:filip(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/

On 11/01/2010 10:27 PM, Filip Rembiałkowski wrote:
> Hi all,
>
> not sure if I should send it here; if it was reported before in
> pgsql-general. you can just ignore it :-)
>
> with postgres 8.2.4:
>
> create table test (t1 text);
> create function row2text(test) returns text as 'select $1::text'
> language sql immutable;
> create index test_row2text_idx on test( row2text(test) );
> alter table test add t2 text;
> alter table test drop t2;
> insert into test(t1) select 'foo';
>
> ERROR: table row type and query-specified row type do not match
> DETAIL: Physical storage mismatch on dropped attribute at ordinal
> position 2.

I'm pretty certain that's not a bug.

It's certainly a feature limitation of PostgreSQL, but it's working as
(currently) designed. You're expected to reload any functions after
dropping columns of tables.

I've seen this discusssed recently, but 8.4 still behaves the same way.

--
Craig Ringer

Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> writes:
> On 11/01/2010 10:27 PM, Filip Rembiakowski wrote:
>> ERROR: table row type and query-specified row type do not match
>> DETAIL: Physical storage mismatch on dropped attribute at ordinal
>> position 2.

> I'm pretty certain that's not a bug.

No, it is a bug, or at least an overlooked case. There might be some
variants of this that aren't readily fixable, but that one is/was:
http://archives.postgresql.org/pgsql-committers/2010-01/msg00168.php

regards, tom lane