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'
> 

Fri, 17 Apr 1998 14:45:00 +0200
"Meskes, Michael" <meskes@topsystem.de>
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
> 

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)

Fri, 17 Apr 1998 10:48:08 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] drop table inside transactions
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'


Fri, 17 Apr 1998 17:05:08 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@proxy.bazzanese.com>
RE: [HACKERS] drop table inside transactions
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

Fri, 17 Apr 1998 10:44:43 -0700 (PDT)
ocie@paracel.com
Re: [HACKERS] drop table inside transactions
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'


Mon, 20 Apr 1998 11:18:06 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@proxy.bazzanese.com>
RE: [HACKERS] drop table inside transactions
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'


Mon, 20 Apr 1998 12:00:08 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@proxy.bazzanese.com>
Re: [HACKERS] drop table inside transactions
>
> 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) #



Thu, 30 Apr 1998 11:44:56 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] drop table inside transactions
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'


Thu, 30 Apr 1998 15:25:37 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: [HACKERS] drop table inside transactions