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:
------------------------------------------------
I found a lock error on transactions using drop table.
Take a look...
--first user:------------------------------------------------------
BEGIN WORK;
prova=> SELECT * FROM tmp;
a
-----
first
last
(2 rows)
prova=> DROP TABLE tmp;
DROP
prova=> SELECT * FROM tmp;
ERROR: tmp: Table does not exist.
--second user:---------------------------------------------------
prova=> select * from tmp;
a
-
(0 rows)
prova=> insert into tmp values ('new');
INSERT 178789 1
prova=> select * from tmp;
a
-----
first
last
new
(3 rows)
--again first user:--------------------------------------------------
prova=> select * from tmp;
ERROR: tmp: Table does not exist.
prova=> commit;
END
prova=> select * from tmp;
a
-----
first
last
new
(3 rows)
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
Show quoted text
-----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.itSystem Configuration
---------------------
Architecture (example: Intel Pentium) : Intel PentiumOperating System (example: Linux 2.0.26 ELF) : Linux 2.0.31
ElfPostgreSQL version (example: PostgreSQL-6.1) : PostgreSQL-snapshot
april 6, 1998Compiler 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'
Import Notes
Resolved by subject fallback
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. ;-)
Show quoted text
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
I wonder why it sometimes restores the data. Maybe because it's still in
the same area of the disk/file?
Michael
--
Dr. Michael Meskes, Projekt-Manager | topystem Systemhaus GmbH
meskes@topsystem.de | Europark A2, Adenauerstr. 20
meskes@debian.org | 52146 Wuerselen
Go SF49ers! Use Debian GNU/Linux! | Tel: (+49) 2405/4670-44
Show quoted text
----------
From: Jose' Soares Da Silva[SMTP:sferac@proxy.bazzanese.com]
Sent: Freitag, 17. April 1998 19:05
To: Meskes, Michael
Cc: pgsql-hackers@postgreSQL.org; pgsq-bugs@postgreSQL.org;
sferac@bo.nettuno.it
Subject: RE: [HACKERS] drop table inside transactionsOn 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.
;-)
Import Notes
Resolved by subject fallback
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'
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 restores a dropped table with rollback.
Same with Informix.
Import Notes
Resolved by subject fallback
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 restores a dropped table with rollback.
Same with Informix.
Added to TODO list:
* Allow table creation/destruction to be rolled back
--
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 Mon, 20 Apr 1998, Bruce Momjian 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 restores a dropped table with rollback.
Same with Informix.
Added to TODO list:
* Allow table creation/destruction to be rolled back
Table creation already works. We have some problems with drop table and alter
table also.
Jose'
Import Notes
Reply to msg id not found: Pine.LNX.3.96.980421144919.586C-100000@rosso.bazzanese.com | Resolved by subject fallback
Added to TODO list:
* Allow table creation/destruction to be rolled back
Table creation already works. We have some problems with drop table and alter
table also.
TODO updated.
--
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. ;-)
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'