Thread

1. insert after table modify bug

   Jan Urbanek <jurb6630@barbora.ms.mff.cuni.cz> — 2000-04-08T22:37:22Z

   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
   
   

2. Re: insert after table modify bug

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-04-08T23:25:55Z

   "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