Thread: error after dropping column

error after dropping column

From
Filip Rembiałkowski
Date:
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 /> 

Re: error after dropping column

From
Craig Ringer
Date:
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

Re: error after dropping column

From
Tom Lane
Date:
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