Обсуждение: 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.
            thanks,
-- 
* David Sauer, student of Czech Technical University
* electronic mail: davids@iol.cz (mime compatible)
* boycott czech telecom >>>> http://www.bojkot.cz/ (czech)
*                       >>>> http://www.bojkot.cz/english/ (english)

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