> From: Tim Perdue <tperdue@valinux.com>
>
> Here's some fun new problems in Pgsql 7.0.2.
> 
> My nightly job failed last night because supposedly the tables already
> existed. If you do a \d, you can see the tables. If you issue drop
> table, look at the output below.
> 

Unfortunately, PostgreSQL cannot rollback transactions with
DDL statements in them. I suspect that what happened was
that the underlying file was unlinked, but the entry from pg_class
wasn't marked deleted, because the backend performing the
DROP TABLE crashed before the pg_class delete could be
committed.

Unlike eveything everyone else has told you about transactions,
as of 7.0.2, I wouldn't run DDL statements in them, 
only DML statements. Rolling back DDL statements properly
in a MVCC transaction environment is very difficult, as 
you can imagine. IIRC Oracle cheats, Informix and DEC Rdb
lock the DDL target until transaction commit, etc. If 
the PostgreSQL team implements their stated goal in this
area, it will be far superior to its commercial counterparts.

Hope that helps, 

Mike Mascari


Wed, 6 Sep 2000 01:47:05 -0400
"Mike Mascari" <mascarm@mascari.com>
Re: Fragged State in 7.0.2
"Mike Mascari" <mascarm@mascari.com> writes:
> Rolling back DDL statements properly
> in a MVCC transaction environment is very difficult, as 
> you can imagine. IIRC Oracle cheats, Informix and DEC Rdb
> lock the DDL target until transaction commit, etc. If 
> the PostgreSQL team implements their stated goal in this
> area, it will be far superior to its commercial counterparts.

AFAIK we intend to keep the current behavior of exclusively
locking any table you try to drop or modify.  So it'll be
pretty much like the Informix/RDB behavior.

But yes, at the moment DROP or RENAME inside a transaction is
pretty risky (and 7.0 tells you so, with an annoying NOTICE).

			regards, tom lane

Wed, 06 Sep 2000 10:52:28 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Fragged State in 7.0.2 
It doesn't suprise me that it doesn't work, but I am surprised to get
into a half-baked state if an abort does happen for some reason.

NOTICE:  Caution: DROP TABLE cannot be rolled back, so don't abort now

Probably what should happen is it should error out if you try to do
things like this in a transaction, or autocommit for you no matter what.
Whatever it takes to not be in a half-baked state.

Tim


Tom Lane wrote:
> 
> "Mike Mascari" <mascarm@mascari.com> writes:
> > Rolling back DDL statements properly
> > in a MVCC transaction environment is very difficult, as
> > you can imagine. IIRC Oracle cheats, Informix and DEC Rdb
> > lock the DDL target until transaction commit, etc. If
> > the PostgreSQL team implements their stated goal in this
> > area, it will be far superior to its commercial counterparts.
> 
> AFAIK we intend to keep the current behavior of exclusively
> locking any table you try to drop or modify.  So it'll be
> pretty much like the Informix/RDB behavior.
> 
> But yes, at the moment DROP or RENAME inside a transaction is
> pretty risky (and 7.0 tells you so, with an annoying NOTICE).
> 
>                         regards, tom lane

-- 
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
408-542-5723

Wed, 06 Sep 2000 08:14:42 -0700
Tim Perdue <tim@sourceforge.net>
Re: Fragged State in 7.0.2