Обсуждение: Re:Create table doesn't always respect atomicity of transactions

Поиск
Список
Период
Сортировка

Re:Create table doesn't always respect atomicity of transactions

От
Mark Dalphin
Дата:
Hi,

Running version 6.4.2 of Postgresql on IRIX, I see exactly this same behavior,
that is, when I create a table within a transaction and some later action in
that transaction generates an error, leaving the transaction in an abort state,
leaves me stuck with a ghost table. I can create a new table of the same name
because "Table already exists" and I can't delete the table because that table
"doesn't exist".

I can, however, get around the problem by becoming the Postgres super-user and
going into the data/base/...
directory; there I find the file with the name of the ghost table (it is of size
zero) and I delete it.  The problem then goes away. I assume this bug arises
from the order in which Postgres creates entries in its system tables and
creates the file on the disk.  As I am not creating tables on the fly, the bug
only bites me when I am creating a new DB.

In short, I can confirm the behavior you are seeing and I believe it occurs on
multiple platforms. I can send in code to re-create the problem is people wish.

Mark

----------------------
Zalman Stern wrote:

> Date: Mon, 21 Jun 1999 22:21:48 -0700 (PDT)
> From: Zalman Stern
> Subject: Create table doesn't always respect atomicity of transactions.
>
> Off and on, I've been seeing a situation where the following code:
>         begin;
>         create table foo (name text);
>         abort; leaves a file called "foo" in the database directory and
> further attempts to create a relation called foo or to select anything from
> it all fail.  The database has been left in an inconsistent state.
>
> I filed a bug report on this earlier today as it seemed dead on repeatable.
> But then I recompiled with debug symbols to have a go at figuring out what
> was up and the problem went away. So I recompiled with full optimization
> again and the problem still doesn't occur now. I've been starting over each
> time with a fresh database so if it was some property of the database
> itself, then that state is lost. But this is not the first time I've seen
> this. Has any one else seen such a thing? Its rather troublesome 'cause when
> it does happen, the database is somewhat unuseable until I remove the file
> in question and I hate going in and removing files that are supposed to be
> under Postgres' control...
>
>
--
Mark Dalphin                          email: mdalphin@amgen.com
Mail Stop: 29-2-A                     phone: +1-805-447-4951 (work)
One Amgen Center Drive                       +1-805-375-0680 (home)
Thousand Oaks, CA 91320                 fax: +1-805-499-9955 (work)





Re: [SQL] Re:Create table doesn't always respect atomicity of transactions

От
Zalman Stern
Дата:
> In short, I can confirm the behavior you are seeing and I believe it occurs on
> multiple platforms. I can send in code to re-create the problem is people wish.

I have not been able to reproduce this with 6.5 . I was fairly certain when
I filed the bug report that I was using 6.5, but I did have a 6.4.2 install
still around and maybe I messed up and was using 6.4.2 unintentionally. Or
perhaps the 6.5 snapshot from June 9th or so. I really don't think either
of these were the case, but its a more likely explanation than that there's
a really subtle nondeterministic bug in this code. (As in I looked through
the code in question and don't see how it would be doing anything subtlely
tricky.)

I'm sending this to the list 'cause I can't figure out how to update a bug
report from the Web site...

-Z-


Re: [SQL] Re:Create table doesn't always respect atomicity of transactions

От
José Soares
Дата:
There's the same problem in v6.5 using temporary tables:

prova=> begin;
BEGIN
prova=> create temporary table prova(a text);
CREATE
prova=> insert into prova values('aa');
INSERT 225134 1
prova=> rollback;
ABORT
prova=> create table prova(a text);
ERROR:  Relation 'prova' already exists
prova=> drop table prova;
ERROR:  cannot find attribute 1 of relation pg_temp.17407.0
prova=>


Mark Dalphin ha scritto:

> Hi,
>
> Running version 6.4.2 of Postgresql on IRIX, I see exactly this same behavior,
> that is, when I create a table within a transaction and some later action in
> that transaction generates an error, leaving the transaction in an abort state,
> leaves me stuck with a ghost table. I can create a new table of the same name
> because "Table already exists" and I can't delete the table because that table
> "doesn't exist".
>
> I can, however, get around the problem by becoming the Postgres super-user and
> going into the data/base/...
> directory; there I find the file with the name of the ghost table (it is of size
> zero) and I delete it.  The problem then goes away. I assume this bug arises
> from the order in which Postgres creates entries in its system tables and
> creates the file on the disk.  As I am not creating tables on the fly, the bug
> only bites me when I am creating a new DB.
>
> In short, I can confirm the behavior you are seeing and I believe it occurs on
> multiple platforms. I can send in code to re-create the problem is people wish.
>
> Mark
>
> ----------------------
> Zalman Stern wrote:
>
> > Date: Mon, 21 Jun 1999 22:21:48 -0700 (PDT)
> > From: Zalman Stern
> > Subject: Create table doesn't always respect atomicity of transactions.
> >
> > Off and on, I've been seeing a situation where the following code:
> >         begin;
> >         create table foo (name text);
> >         abort; leaves a file called "foo" in the database directory and
> > further attempts to create a relation called foo or to select anything from
> > it all fail.  The database has been left in an inconsistent state.
> >
> > I filed a bug report on this earlier today as it seemed dead on repeatable.
> > But then I recompiled with debug symbols to have a go at figuring out what
> > was up and the problem went away. So I recompiled with full optimization
> > again and the problem still doesn't occur now. I've been starting over each
> > time with a fresh database so if it was some property of the database
> > itself, then that state is lost. But this is not the first time I've seen
> > this. Has any one else seen such a thing? Its rather troublesome 'cause when
> > it does happen, the database is somewhat unuseable until I remove the file
> > in question and I hate going in and removing files that are supposed to be
> > under Postgres' control...
> >
> >
> --
> Mark Dalphin                          email: mdalphin@amgen.com
> Mail Stop: 29-2-A                     phone: +1-805-447-4951 (work)
> One Amgen Center Drive                       +1-805-375-0680 (home)
> Thousand Oaks, CA 91320                 fax: +1-805-499-9955 (work)

--
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'




Re: [SQL] Re:Create table doesn't always respect atomicity of transactions

От
Bruce Momjian
Дата:
This is fixed this in 6.5.2.

> There's the same problem in v6.5 using temporary tables:
> 
> prova=> begin;
> BEGIN
> prova=> create temporary table prova(a text);
> CREATE
> prova=> insert into prova values('aa');
> INSERT 225134 1
> prova=> rollback;
> ABORT
> prova=> create table prova(a text);
> ERROR:  Relation 'prova' already exists
> prova=> drop table prova;
> ERROR:  cannot find attribute 1 of relation pg_temp.17407.0
> prova=>
> 
> 
> Mark Dalphin ha scritto:
> 
> > Hi,
> >
> > Running version 6.4.2 of Postgresql on IRIX, I see exactly this same behavior,
> > that is, when I create a table within a transaction and some later action in
> > that transaction generates an error, leaving the transaction in an abort state,
> > leaves me stuck with a ghost table. I can create a new table of the same name
> > because "Table already exists" and I can't delete the table because that table
> > "doesn't exist".
> >
> > I can, however, get around the problem by becoming the Postgres super-user and
> > going into the data/base/...
> > directory; there I find the file with the name of the ghost table (it is of size
> > zero) and I delete it.  The problem then goes away. I assume this bug arises
> > from the order in which Postgres creates entries in its system tables and
> > creates the file on the disk.  As I am not creating tables on the fly, the bug
> > only bites me when I am creating a new DB.
> >
> > In short, I can confirm the behavior you are seeing and I believe it occurs on
> > multiple platforms. I can send in code to re-create the problem is people wish.
> >
> > Mark
> >
> > ----------------------
> > Zalman Stern wrote:
> >
> > > Date: Mon, 21 Jun 1999 22:21:48 -0700 (PDT)
> > > From: Zalman Stern
> > > Subject: Create table doesn't always respect atomicity of transactions.
> > >
> > > Off and on, I've been seeing a situation where the following code:
> > >         begin;
> > >         create table foo (name text);
> > >         abort; leaves a file called "foo" in the database directory and
> > > further attempts to create a relation called foo or to select anything from
> > > it all fail.  The database has been left in an inconsistent state.
> > >
> > > I filed a bug report on this earlier today as it seemed dead on repeatable.
> > > But then I recompiled with debug symbols to have a go at figuring out what
> > > was up and the problem went away. So I recompiled with full optimization
> > > again and the problem still doesn't occur now. I've been starting over each
> > > time with a fresh database so if it was some property of the database
> > > itself, then that state is lost. But this is not the first time I've seen
> > > this. Has any one else seen such a thing? Its rather troublesome 'cause when
> > > it does happen, the database is somewhat unuseable until I remove the file
> > > in question and I hate going in and removing files that are supposed to be
> > > under Postgres' control...
> > >
> > >
> > --
> > Mark Dalphin                          email: mdalphin@amgen.com
> > Mail Stop: 29-2-A                     phone: +1-805-447-4951 (work)
> > One Amgen Center Drive                       +1-805-375-0680 (home)
> > Thousand Oaks, CA 91320                 fax: +1-805-499-9955 (work)
> 
> --
> ______________________________________________________________
> PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> Jose'
> 
> 
> 
> 


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026