Thread: RE: [HACKERS] drop table inside transactions
Is this really a bug? I haven't seen any (commercial) system supporting this kind of transaction recovery. Once you drop a table the data is lost, no matter if you rollback or not. Michael -- Dr. Michael Meskes, Project-Manager | topsystem Systemhaus GmbH meskes@topsystem.de | Europark A2, Adenauerstr. 20 meskes@debian.org | 52146 Wuerselen Go SF49ers! Go Rhein Fire! | Tel: (+49) 2405/4670-44 Use Debian GNU/Linux! | Fax: (+49) 2405/4670-10 > -----Original Message----- > From: Jose' Soares Da Silva [SMTP:sferac@proxy.bazzanese.com] > Sent: Friday, April 17, 1998 4:30 PM > To: pgsql-hackers@postgreSQL.org; pgsq-bugs@postgreSQL.org > Cc: sferac@bo.nettuno.it > Subject: [HACKERS] drop table inside transactions > > ====================================================================== > ====== > POSTGRESQL BUG REPORT TEMPLATE > ====================================================================== > ====== > > Your name : Jose' Soares > Your email address : sferac@bo.nettuno.it > > > System Configuration > --------------------- > Architecture (example: Intel Pentium) : Intel Pentium > > Operating System (example: Linux 2.0.26 ELF) : Linux 2.0.31 > Elf > > PostgreSQL version (example: PostgreSQL-6.1) : PostgreSQL-snapshot > april 6, 1998 > > Compiler used (example: gcc 2.7.2) : gcc 2.7.2.1 > > > Please enter a FULL description of your problem: > ------------------------------------------------ > > There's another bug on transactions. If one drop a table inside a > transaction > and then change his mind and rollback work, the table structure is > restored > but data are lost. > Take a look... > > prova=> begin work; > BEGIN > prova=> lock table a; > DELETE 0 > prova=> select * from a; > a > --- > 1 > 13 > 134 > (3 rows) > > prova=> drop table a; > DROP > prova=> select * from a; > ERROR: a: Table does not exist. > prova=> rollback; > ABORT > prova=> select * from a; > a > - > (0 rows) > Jose' >
> > Is this really a bug? I haven't seen any (commercial) system supporting > this kind of transaction recovery. Once you drop a table the data is > lost, no matter if you rollback or not. > > Michael > Meta-data changes, like drop table, are not roll-back-able. I knew this was a reported problem, but I don't think it is required by standard, so it is not on the TODO list. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
On Fri, 17 Apr 1998, Meskes, Michael wrote: > Is this really a bug? I haven't seen any (commercial) system supporting > this kind of transaction recovery. Once you drop a table the data is > lost, no matter if you rollback or not. > > Michael Maybe you are right Michael, but there's another point; the table wasn't removed, it is still there, only data are cancelled. It's more, like a DELETE FROM ... not a DROP TABLE... and, if another user inserts data into this dropped table, the table returns with all data. (Refer to my first bug-report on this matter), and more; some times ROLLBACK restores both data and table structure. ;-) > > > prova=> drop table a; > > DROP > > prova=> select * from a; > > ERROR: a: Table does not exist. > > prova=> rollback; > > ABORT > > prova=> select * from a; > > a > > - > > (0 rows) > > Jose'
Meskes, Michael wrote: > > Is this really a bug? I haven't seen any (commercial) system supporting > this kind of transaction recovery. Once you drop a table the data is > lost, no matter if you rollback or not. > > Michael I tend to agree. Sybase will not even honor a drop table request inside a transaction: 1> begin tran 2> go 1> drop table foo 2> go Msg 2762, Level 16, State 4: Line 1: The 'DROP TABLE' command is not allowed within a multi-statement transaction in the 'ociedb' database. 1> We _could_ do something like check a "deleted" flag in the relation and postpone the actual delete until the transaction is committed, but at least in my experience, changing table structure is usually best left to human admins as opposed to applications. Rows change but the basic table structure stays the same until the application and schema are changed. Ocie
On Fri, 17 Apr 1998, Meskes, Michael wrote:
> Is this really a bug? I haven't seen any (commercial) system supporting
> this kind of transaction recovery. Once you drop a table the data is
> lost, no matter if you rollback or not.
>
SOLID does it, take a look:
SOLID SQL Editor (teletype) v.02.20.0007
(C) Copyright Solid Information Technology Ltd 1993-1997
Execute SQL statements terminated by a semicolon.
Exit by giving command: exit;
Connected to default server.
select * from cities;
CODE CITY
---- ----
SFO SAN FRANCISCO
STL ST. LOUIS
SJC SAN JOSE
3 rows fetched.
drop table cities;
Command completed succesfully, 0 rows affected.
drop table cities;
SOLID Table Error 13011: Table CITIES does not exist
rollback work;
Command completed succesfully, 0 rows affected.
select * from cities;
CODE CITY
---- ----
SFO SAN FRANCISCO
STL ST. LOUIS
SJC SAN JOSE
3 rows fetched.
Jose'
On Fri, 17 Apr 1998 ocie@paracel.com wrote:
> Meskes, Michael wrote:
> >
> > Is this really a bug? I haven't seen any (commercial) system supporting
> > this kind of transaction recovery. Once you drop a table the data is
> > lost, no matter if you rollback or not.
SOLID restore a dropped table inside a transaction.
> >
> > Michael
>
> I tend to agree. Sybase will not even honor a drop table request
> inside a transaction:
>
> 1> begin tran
> 2> go
> 1> drop table foo
> 2> go
> Msg 2762, Level 16, State 4:
> Line 1:
> The 'DROP TABLE' command is not allowed within a multi-statement transaction in
> the 'ociedb' database.
> 1>
>
> We _could_ do something like check a "deleted" flag in the relation
> and postpone the actual delete until the transaction is committed, but
> at least in my experience, changing table structure is usually best
> left to human admins as opposed to applications. Rows change but the
> basic table structure stays the same until the application and schema
> are changed.
>
What about temporary tables ?
We don't have CREATE TEMPORARY TABLE statement
thus users need to create
and drop tmp tables inside transactions.
Jose'
>
> On Fri, 17 Apr 1998, Meskes, Michael wrote:
>
> > Is this really a bug? I haven't seen any (commercial) system supporting
> > this kind of transaction recovery. Once you drop a table the data is
> > lost, no matter if you rollback or not.
> >
> > Michael
> Maybe you are right Michael, but there's another point; the table wasn't
> removed, it is still there, only data are cancelled.
> It's more, like a DELETE FROM ... not a DROP TABLE...
> and, if another user inserts data into this dropped table,
> the table returns with all data.
> (Refer to my first bug-report on this matter),
> and more; some times ROLLBACK restores both data and table structure. ;-)
Partially right. The tables data file was removed at DROP
TABLE. On the ROLLBACK, the pg_class and pg_type entries got
restored and the storage manager created a new (empty) data
file on the SELECT command after the ROLLBACK.
Maybe we could setup an internal list of files to be deleted
on the next transaction commit, so the files remain intact
after ROLLBACK.
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) #
On Thu, 30 Apr 1998, Jan Wieck wrote:
> >
> > On Fri, 17 Apr 1998, Meskes, Michael wrote:
> >
> > > Is this really a bug? I haven't seen any (commercial) system supporting
> > > this kind of transaction recovery. Once you drop a table the data is
> > > lost, no matter if you rollback or not.
> > >
> > > Michael
> > Maybe you are right Michael, but there's another point; the table wasn't
> > removed, it is still there, only data are cancelled.
> > It's more, like a DELETE FROM ... not a DROP TABLE...
> > and, if another user inserts data into this dropped table,
> > the table returns with all data.
> > (Refer to my first bug-report on this matter),
> > and more; some times ROLLBACK restores both data and table structure. ;-)
>
> Partially right. The tables data file was removed at DROP
> TABLE. On the ROLLBACK, the pg_class and pg_type entries got
> restored and the storage manager created a new (empty) data
> file on the SELECT command after the ROLLBACK.
>
> Maybe we could setup an internal list of files to be deleted
> on the next transaction commit, so the files remain intact
> after ROLLBACK.
Great!
Remember that we have the same problem with CREATE DATABASE
in case of ROLLBACK will be removed references from "pg_database"
but directory $PGDATA/databasename will not be removed.
Jose'