Thread: 6.5.2 BUG: Rolling back after dropping a table causes dangling relationships.
6.5.2 BUG: Rolling back after dropping a table causes dangling relationships.
From
Lincoln Yeoh
Date:
Your name : Lincoln Yeoh Your email address : lylyeoh@mecomb.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.
Re: [BUGS] 6.5.2 BUG: Rolling back after dropping a table causes dangling relationships.
From
Tom Lane
Date:
Lincoln Yeoh <lylyeoh@mecomb.com> writes:
> 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.
Yes, this is a known problem that's rather difficult to fix.
You can't undo a file removal :-(
(Postponing the physical delete of the database's file until commit
wouldn't be good enough, since that would interfere with an attempt to
create another table by the same name later in the same transaction.)
Current sources issue a notice message like "DROP TABLE can't be rolled
back, so don't abort now" if you execute DROP TABLE within a transaction
block. Kind of a half-baked answer, but fixing it really right will
require more effort than anyone seems to want to put into the issue
at the moment. A lot of other DBMSes don't support rolling back this
kind of command either, so we don't feel too horrible about it.
> Are there other similar problems for drop sequence/index and stuff?
Yes, drop index, sequence, or view are the same as drop table in that
they remove an associated file. Renaming a table or truncating it (new
for 6.6) also have non-undoable side effects. Not all of these issue
warning messages right at the moment, but I'm working on it. VACUUM
mustn't be executed inside a transaction block at all (6.5.* doesn't
enforce that, but future releases will), and I think the same probably
goes for CREATE DATABASE and DROP DATABASE.
regards, tom lane
Re: [BUGS] 6.5.2 BUG: Rolling back after dropping a table causes dangling relationships.
From
Bruce Momjian
Date:
This will be fixed in 6.6, due out in a few months. > Your name : Lincoln Yeoh > Your email address : lylyeoh@mecomb.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. > > > > ************ > > -- 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
Re: [BUGS] 6.5.2 BUG: Rolling back after dropping a table causes dangling relationships.
From
Lincoln Yeoh
Date:
At 10:04 AM 08-10-1999 -0400, Tom Lane wrote: >Lincoln Yeoh <lylyeoh@mecomb.com> writes: >> 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. > >Yes, this is a known problem that's rather difficult to fix. >You can't undo a file removal :-( I'm not really concerned about undoing/rolling back of a drop table. I'm not surprised if "drop table" can't be rolled back. The problem is that it is NOT _completely_ dropped, and you cannot recreate a new table with the same name nor use the old one. It's in a zombie state. Cheerio, Link.