weird state after aborted transaction in pgsql 6.4

My database is in weird state after some operation. Here is an example:

[david@kerberos david]$ psql
.....
david=> begin;
BEGIN
david=> create table tbl ( i int );
CREATE
david=> insert into tbl values(1);
INSERT 104137 1
EOFid=> [<ctrl-d> here]
[david@kerberos david]$ psql
....
david=> create table tbl ( a text );
ERROR: cannot create tbl
david=> \d
Couldn't find any tables, sequences or indices!
====================== cut here ======================
Simple question: why 'ERROR: cannot create tbl' ?
Platform: linux i586, glibc, regression tests seems ok (except minor
problem with floating point. The some problem have my colleague.

The problem is, that the CREATE TABLE is inside the
transaction. So the catalog updates for the new table get
rolled back, but the file is still there. On the next CREATE
TABLE the file creation (made with O_EXCL) fails.

You can safely remove the file .../data/base/david/tbl and
you should execute CREATE/DROP statements outside of
transactions.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

Jan Wieck wrote:

The problem is, that the CREATE TABLE is inside the
transaction. So the catalog updates for the new table get
rolled back, but the file is still there. On the next CREATE
TABLE the file creation (made with O_EXCL) fails.

You can safely remove the file .../data/base/david/tbl and
you should execute CREATE/DROP statements outside of
transactions.

Is there any plans (in TODO) to fix this ?

Either by making the CREATE/DROP totally ignore transactions (probably
hard),
ot then update the transaction machanism to be able to delete files
created
inside the transaction.

--------------
Hannu

My database is in weird state after some operation. Here is an example:

[david@kerberos david]$ psql
.....
david=> begin;
BEGIN
david=> create table tbl ( i int );
CREATE
david=> insert into tbl values(1);
INSERT 104137 1
EOFid=> [<ctrl-d> here]
[david@kerberos david]$ psql
....
david=> create table tbl ( a text );
ERROR: cannot create tbl
david=> \d
Couldn't find any tables, sequences or indices!
====================== cut here ======================
Simple question: why 'ERROR: cannot create tbl' ?
Platform: linux i586, glibc, regression tests seems ok (except minor
problem with floating point. The some problem have my colleague.

The problem is, that the CREATE TABLE is inside the
transaction. So the catalog updates for the new table get
rolled back, but the file is still there. On the next CREATE
TABLE the file creation (made with O_EXCL) fails.

You can safely remove the file .../data/base/david/tbl and
you should execute CREATE/DROP statements outside of
transactions.

Added to TODO:

* CREATE TABLE inside aborted transaction causes stray table file

-- 
  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