Thread: pg.dropped

Hi all,

I have a deja vu or I had this very problem before.

Now I use 8.4.2 and it happened again.

After dropping a column from table, there is still entry in pg_attribute

filip@la_dev=# select * from pg_attribute where attrelid = (select oid from pg_class where relname='thetable') order by attnum desc limit 1;
-[ RECORD 1 ]-+------------------------------
attrelid      | 4753849
attname       | ........pg.dropped.69........
atttypid      | 0
attstattarget | 0
attlen        | 1
attnum        | 69
attndims      | 0
attcacheoff   | -1
atttypmod     | -1
attbyval      | t
attstorage    | p
attalign      | c
attnotnull    | f
atthasdef     | f
attisdropped  | t
attislocal    | t
attinhcount   | 0
attacl        | <NULL>


And of course this makes my INSERT not working...

INSERT INTO thetable ( ... ) VALUES ( ... );
ERROR:  table row type and query-specified row type do not match
DETAIL:  Physical storage mismatch on dropped attribute at ordinal position 69.

Any clues / hint how to NEVER get into this again?


TIA.


--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/

=?UTF-8?Q?Filip_Rembia=C5=82kowski?= <plk.zuber@gmail.com> writes:
> INSERT INTO thetable ( ... ) VALUES ( ... );
> ERROR:  table row type and query-specified row type do not match

If you want any help with this you need to show a *complete* example
of how to produce this failure.

            regards, tom lane

Filip Rembiałkowski wrote:
> After dropping a column from table, there is still entry in pg_attribute
>
> filip@la_dev=# select * from pg_attribute where attrelid = (select oid
> from pg_class where relname='thetable') order by attnum desc limit 1;
> -[ RECORD 1 ]-+------------------------------
> attrelid      | 4753849
> attname       | ........pg.dropped.69........
> ...
> attisdropped  | t

See that last part?  That's what happens when you drop a
table--"attisdropped" is set to true.  The server can't just delete the
pg_attribute entry altogether for various internal reasons, this is what
it does instead.

> And of course this makes my INSERT not working...

There's obviously something wrong here, but the fact that the
pg_attribute entry is still there (but marked dropped) is a not a direct
cause of your problem.

--
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com  www.2ndQuadrant.com

Full test case, reproduced in 8.4.2 on two different hosts

create table test (id serial primary key, t1 text, t2 text);
create function myhash(test) returns text as 'select md5($1::text)' language sql immutable;
create index myhash on test( myhash(test) );
alter table test add t3 text;
alter table test drop t3;
insert into test(t1,t2) select 'foo', 'bar';

PS. I realise that marking of CAST (rowtype as text) as immutable may be not safe.
But this behaviour is probably a bug anyway.







--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/

=?UTF-8?Q?Filip_Rembia=C5=82kowski?= <plk.zuber@gmail.com> writes:
> create table test (id serial primary key, t1 text, t2 text);
> create function myhash(test) returns text as 'select md5($1::text)' language
> sql immutable;
> create index myhash on test( myhash(test) );
> alter table test add t3 text;
> alter table test drop t3;
> insert into test(t1,t2) select 'foo', 'bar';

Mph.  That seems to be an unhandled case that we ought to handle.

            regards, tom lane

=?UTF-8?Q?Filip_Rembia=C5=82kowski?= <plk.zuber@gmail.com> writes:
> Full test case, reproduced in 8.4.2 on two different hosts

> create table test (id serial primary key, t1 text, t2 text);
> create function myhash(test) returns text as 'select md5($1::text)' language
> sql immutable;
> create index myhash on test( myhash(test) );
> alter table test add t3 text;
> alter table test drop t3;
> insert into test(t1,t2) select 'foo', 'bar';

I've applied a patch for this in HEAD and 8.4.

            regards, tom lane