pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCreate table doesn't always respect atomicity of transactions.
6+ messages / 4 participants
[nested] [flat]
* Create table doesn't always respect atomicity of transactions.
@ 1999-06-22 05:21 Zalman Stern <zalman@netcom.com>
1999-06-22 14:45 ` Re: [SQL] Create table doesn't always respect atomicity of transactions. Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Zalman Stern @ 1999-06-22 05:21 UTC (permalink / raw)
To: pgsql-sql
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...
Glancing at the code, I'm wondering if it is possible for a database to get
stuck in "bootstrap mode." It looks like that would cause problems with
transactions not properly unlink'ing files. Though I haven't scoped out the
details of that...
-Z-
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Create table doesn't always respect atomicity of transactions.
1999-06-22 05:21 Create table doesn't always respect atomicity of transactions. Zalman Stern <zalman@netcom.com>
@ 1999-06-22 14:45 ` Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 1999-06-22 14:45 UTC (permalink / raw)
To: Zalman Stern <zalman@netcom.com>; +Cc: pgsql-sql
> 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...
We fixed this problem in 6.4 or 6.5. Not sure why it goes away.
--
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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re:Create table doesn't always respect atomicity of transactions
@ 1999-06-23 16:35 Mark Dalphin <mdalphin@amgen.com>
1999-06-24 01:15 ` Re: [SQL] Re:Create table doesn't always respect atomicity of transactions Zalman Stern <zalman@netcom.com>
1999-06-24 13:06 ` Re: [SQL] Re:Create table doesn't always respect atomicity of transactions José Soares <jose@sferacarta.com>
0 siblings, 2 replies; 6+ messages in thread
From: Mark Dalphin @ 1999-06-23 16:35 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Re:Create table doesn't always respect atomicity of transactions
1999-06-23 16:35 Re:Create table doesn't always respect atomicity of transactions Mark Dalphin <mdalphin@amgen.com>
@ 1999-06-24 01:15 ` Zalman Stern <zalman@netcom.com>
1 sibling, 0 replies; 6+ messages in thread
From: Zalman Stern @ 1999-06-24 01:15 UTC (permalink / raw)
To: mdalphin@amgen.com; +Cc: pgsql-sql
> 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-
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Re:Create table doesn't always respect atomicity of transactions
1999-06-23 16:35 Re:Create table doesn't always respect atomicity of transactions Mark Dalphin <mdalphin@amgen.com>
@ 1999-06-24 13:06 ` José Soares <jose@sferacarta.com>
1999-09-21 21:17 ` Re: [SQL] Re:Create table doesn't always respect atomicity of transactions Bruce Momjian <maillist@candle.pha.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: José Soares @ 1999-06-24 13:06 UTC (permalink / raw)
To: Mark Dalphin <mdalphin@amgen.com>; +Cc: pgsql-sql
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'
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Re:Create table doesn't always respect atomicity of transactions
1999-06-23 16:35 Re:Create table doesn't always respect atomicity of transactions Mark Dalphin <mdalphin@amgen.com>
1999-06-24 13:06 ` Re: [SQL] Re:Create table doesn't always respect atomicity of transactions José Soares <jose@sferacarta.com>
@ 1999-09-21 21:17 ` Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 1999-09-21 21:17 UTC (permalink / raw)
To: [Jos_] Soares <jose@sferacarta.com>; +Cc: Mark Dalphin <mdalphin@amgen.com>; pgsql-sql
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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~1999-09-21 21:17 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-22 05:21 Create table doesn't always respect atomicity of transactions. Zalman Stern <zalman@netcom.com>
1999-06-22 14:45 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-06-23 16:35 Re:Create table doesn't always respect atomicity of transactions Mark Dalphin <mdalphin@amgen.com>
1999-06-24 01:15 ` Re: [SQL] Re:Create table doesn't always respect atomicity of transactions Zalman Stern <zalman@netcom.com>
1999-06-24 13:06 ` Re: [SQL] Re:Create table doesn't always respect atomicity of transactions José Soares <jose@sferacarta.com>
1999-09-21 21:17 ` Re: [SQL] Re:Create table doesn't always respect atomicity of transactions Bruce Momjian <maillist@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox