Thread: error after dropping column

Hi all,<br /><br />not sure if I should send it here; if it was reported before in pgsql-general. you can just ignore
it:-)<br /><br />with postgres 8.2.4:<br /><br />create table test (t1 text);<br />create function row2text(test)
returnstext as 'select $1::text' language sql immutable;<br /> create index test_row2text_idx on test( row2text(test)
);<br/> alter table test add t2 text;<br />alter table test drop t2;<br />insert into test(t1) select 'foo';<br /><br
/>ERROR: table row type and query-specified row type do not match<br />DETAIL:  Physical storage mismatch on dropped
attributeat ordinal position 2.<br /><br /><br clear="all" /><br />-- <br />Filip Rembiałkowski<br />JID,mailto:<a
href="mailto:filip.rembialkowski@gmail.com"target="_blank">filip.rembialkowski@gmail.com</a><br /><a
href="http://filip.rembialkowski.net/"target="_blank">http://filip.rembialkowski.net/</a><br /> 

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@postnewspapers.com.au> writes:
> On 11/01/2010 10:27 PM, Filip Rembiałkowski 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