Re: insert after table modify bug

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jan Urbanek" <JURB6630(at)Barbora(dot)ms(dot)mff(dot)cuni(dot)cz>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: insert after table modify bug
Date: 2000-04-08 23:25:55
Message-ID: 26688.955236355@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"Jan Urbanek" <JURB6630(at)Barbora(dot)ms(dot)mff(dot)cuni(dot)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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message JawjB 2000-04-09 07:21:15 REMOVE
Previous Message Jan Urbanek 2000-04-08 22:37:22 insert after table modify bug