pg.dropped

=?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>

=?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
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/

(continued from -general)

W dniu 7 stycznia 2010 22:31 użytkownik Greg Smith
<greg@2ndquadrant.com>napisał:

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.

When should server delete this row? In my case it looks like it's never
deleted (it lasts even server restart).

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.

Thanks, I get it.

--
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