Skip site navigation (1) Skip section navigation (2)

6.5.2 BUG: Rolling back after dropping a table causes dangling relationships.

From: Lincoln Yeoh <lylyeoh(at)mecomb(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: 6.5.2 BUG: Rolling back after dropping a table causes dangling relationships.
Date: 1999-10-08 07:02:23
Message-ID: 3.0.5.32.19991008150223.00856e80@pop.mecomb.po.my (view raw or flat)
Thread:
Lists: pgsql-bugs
Your name               : Lincoln Yeoh
Your email address      : lylyeoh(at)mecomb(dot)com

System Configuration
---------------------
Architecture (example: Intel Pentium)   :   Intel Pentium II (64MB)
Operating System (example: Linux 2.0.26 ELF)    :  Redhat 6.0 Linux
2.2.5-15 ELF
PostgreSQL version (example: PostgreSQL-6.5.1):   PostgreSQL-6.5.2
Compiler used (example:  gcc 2.8.0)             :   egcs-2.91.66  

Please enter a FULL description of your problem:
------------------------------------------------
If you drop a table in a transaction and do a rollback, the table is "half
dropped", the table is gone but you cannot create a new table with the same
name, nor can you drop it again.

You have to manually go to the data/base/database directory and recreate
any necessary referenced files (just empty files will do). Only then can
you drop the table properly.

------------------------------------------------------------------
Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

CREATE TABLE SESSION (
SES_ID SERIAL,
SES_UID INT4,
SES_STR CHAR(40),
SES_STAT CHAR,
SES_SDATE DATETIME,
SES_EDATE DATETIME,
SES_ADATE DATETIME
);
begin transaction;
drop table session;
rollback; 
(if you do commit it's ok)

select * from session;
              
ERROR:  mdopen: couldn't open session: No such file or directory

create table session (a int4);
ERROR:  Relation 'session' already exists
(Uh Oh)
lylyeoh=> drop table session;
                        
ERROR:  mdopen: couldn't open session: No such file or directory 
(Oh no!)

So create empty tablefile in data/base/lylyeoh directory
[postgres bash shell]$ > session
                         
lylyeoh=> drop table session;
ERROR:  mdopen: couldn't open session_ses_id_key: No such file or directory 

So create empty indexfile in data/base/lylyeoh directory
[postgres bash shell]$ > session_ses_id_key

lylyeoh=> drop table session;
                        
DROP
(YEEHA! DIEDIEDIE!)
lylyeoh=> create table session (a int4);
CREATE

Phew :).

Not a big deal since I won't be doing drop tables in transactions. I only
encountered this after just installing 6.5.2 and doing some tests on
PostgreSQL transaction handling.

                       
If you know how this problem might be fixed, list the solution below:
--------------------------------------------------------------------- 
Only know how to fix the damage. Not the bug. I suspect the rollback
doesn't check that there's a "drop table" and does something that it
shouldn't for that case.

Are there other similar problems for drop sequence/index and stuff? 

Cheerio,

Link.
    


Responses

pgsql-bugs by date

Next:From: Daniel BoydDate: 1999-10-08 11:59:09
Subject: Mac address lobits macro error
Previous:From: Daniel P├ęderDate: 1999-10-07 16:03:55
Subject: RE: [BUGS] Pg.so bug

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group