Обсуждение: 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/
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.
2010/1/7 Tom Lane <tgl@sss.pgh.pa.us>
Filip Rembiałkowski <plk.zuber@gmail.com> writes:If you want any help with this you need to show a *complete* example
> INSERT INTO thetable ( ... ) VALUES ( ... );
> ERROR: table row type and query-specified row type do not match
of how to produce this failure.
regards, tom lane
--
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