> 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