Thread: insert after table modify bug

Hello!

I have found a transaction problem in 6.5.3. This is an example:

Begin;
create table a (a1 int4,a2 int4);
create table b (b1 int4);
alter table b add column b2 int4;
insert into b select * from a ;
ERROR:  INSERT has more expressions than target columns

The db sees new table b, but thinks it has only one column: if I try
"select * from b;" after the alter table command, I receive:
b1
--
(0 rows)

Moreover, I found another bug, but it seems it's the same as reported
Radhesh Mohandas at Feb, 4:

Begin;
Drop table a;
Drop table b;
-- now the second drop failed, e.g. table b doesn't exist - and I
-- want to roll back the whole transaction
rollback;

Table a "does" and "doesn't" exist: it cannot be created but every
select from a failes.

I found the only way how to fix the problem: delete these tables from
system tables, this means "delete from pg_class where relname='a'"
and then "delete from pg_type where typname='a'". Then execute vacuum,
which should, I think, synchronize indices. I tried it and it worked.

Don't you have anybody better solution?

Jan Urbanek

"Jan Urbanek" <JURB6630@Barbora.ms.mff.cuni.cz> writes:
> I have found a transaction problem in 6.5.3. This is an example:

> Begin;
> create table a (a1 int4,a2 int4);
> create table b (b1 int4);
> alter table b add column b2 int4;
> insert into b select * from a ;
> ERROR:  INSERT has more expressions than target columns

Fixed in current sources.

> Moreover, I found another bug, but it seems it's the same as reported
> Radhesh Mohandas at Feb, 4:

> Begin;
> Drop table a;
> Drop table b;
> -- now the second drop failed, e.g. table b doesn't exist - and I
> -- want to roll back the whole transaction
> rollback;

> Table a "does" and "doesn't" exist: it cannot be created but every
> select from a failes.

DROP inside a transaction block is still pretty dangerous, but at least
you can clean up afterwards by dropping again:

regression=# create table a(f1 int);
CREATE
regression=# begin;
BEGIN
regression=# drop table a;
NOTICE:  Caution: DROP TABLE cannot be rolled back, so don't abort now
DROP
regression=# rollback;
ROLLBACK
regression=# select * from a;
NOTICE:  mdopen: couldn't open a: No such file or directory
NOTICE:  RelationIdBuildRelation: smgropen(a): No such file or directory
NOTICE:  mdopen: couldn't open a: No such file or directory
NOTICE:  mdopen: couldn't open a: No such file or directory
NOTICE:  mdopen: couldn't open a: No such file or directory
NOTICE:  mdopen: couldn't open a: No such file or directory
ERROR:  cannot open relation a
regression=# drop table a;
NOTICE:  mdopen: couldn't open a: No such file or directory
NOTICE:  mdopen: couldn't open a: No such file or directory
DROP
regression=# create table a(f1 int, f2 int);
CREATE

Most of the implementation problem here comes from wanting to use
relation names as file names.  If we use OIDs as file names then
it would be easy to postpone the physical delete of the relation's
file until commit.  It'll probably happen in another release or two.

            regards, tom lane