Pursuant to a phone conversation I had with Bruce, I added code this
morning to reject DROP TABLE or DROP INDEX inside a transaction block;
that is, you can't do BEGIN; DROP TABLE foo; END anymore.  The reason
for rejecting this case is that we do the wrong thing if the transaction
is later aborted.  Following BEGIN; DROP TABLE foo; ABORT, the system
tables will claim that foo is still valid (since the changes to them
were never committed) but we've already unlinked foo's physical file,
and we can't get it back.  Solution: only allow DROP TABLE outside
BEGIN, so that the user can't try to change his mind later.

However, on second thought I wonder if this cure is worse than the
disease.  Will it be unreasonably hard to drop tables using client
interfaces that like to wrap everything in BEGIN/END?  Plugging an
obscure hole might not be worth that.

A possible compromise is not to error out, but just to issue a NOTICE
along the lines of "DROP TABLE is not undoable, so don't even think of
trying to abort now..."

(Of course, what would be really nice is if it just worked, but I don't
see any way to make that happen without major changes.  Simply
postponing the unlink to end of transaction isn't workable; consider
BEGIN; DROP TABLE foo; CREATE TABLE foo; ...)

Any thoughts?  Will there indeed be a problem with JDBC or ODBC if we
leave this error check in place?

			regards, tom lane

Sun, 05 Sep 1999 18:17:55 -0400
Tom Lane <tgl@sss.pgh.pa.us>
DROP TABLE inside transaction block
> (Of course, what would be really nice is if it just worked, but I don't
> see any way to make that happen without major changes.  Simply
> postponing the unlink to end of transaction isn't workable; consider
> BEGIN; DROP TABLE foo; CREATE TABLE foo; ...)

Cant you just rename to a unique name, maybee in another directory,
suchas:

~pgsql/data/base/template1/sometable

moves to

~pgsql/data/base/template1/pg_removals/postmasterpid/sometable

And if there is an abort, move back, if there is an end, delete it.

Possible?

					Michael Simms

Sun, 5 Sep 1999 23:47:37 +0100 (BST)
Michael Simms <grim@argh.demon.co.uk>
Re: [HACKERS] DROP TABLE inside transaction block
Michael Simms <grim@argh.demon.co.uk> writes:
>> (Of course, what would be really nice is if it just worked, but I don't
>> see any way to make that happen without major changes.  Simply
>> postponing the unlink to end of transaction isn't workable; consider
>> BEGIN; DROP TABLE foo; CREATE TABLE foo; ...)

> Cant you just rename to a unique name, maybee in another directory,

Not if other backends are also accessing the table.  Remember that to
make this really work, the DROP would have to be invisible to other
backends until commit.

I think that to make this work correctly, we'd have to give up naming
table datafiles after the tables, and use a table's OID or some such
as its file name.  Ugly, and a pain in the neck for debugging and
maintenance.  And we'd still need to postpone the unlink till commit.

The amount of work needed seems vastly more than the feature is worth...

			regards, tom lane

Sun, 05 Sep 1999 18:54:08 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] DROP TABLE inside transaction block 
> 
> Michael Simms <grim@argh.demon.co.uk> writes:
> >> (Of course, what would be really nice is if it just worked, but I don't
> >> see any way to make that happen without major changes.  Simply
> >> postponing the unlink to end of transaction isn't workable; consider
> >> BEGIN; DROP TABLE foo; CREATE TABLE foo; ...)
> 
> > Cant you just rename to a unique name, maybee in another directory,
> 
> Not if other backends are also accessing the table.  Remember that to
> make this really work, the DROP would have to be invisible to other
> backends until commit.

Could you not then:

send a notification to all other backends

Put something into the table header that any new backend that tries to use it
is informed that the correct table is stored elsewhere.

I dont know, Im just throwing ideas here {:-)

						Michael Simms

Mon, 6 Sep 1999 00:11:36 +0100 (BST)
Michael Simms <grim@argh.demon.co.uk>
Re: [HACKERS] DROP TABLE inside transaction block
Tom Lane wrote:
> 
> Pursuant to a phone conversation I had with Bruce, I added code this
> morning to reject DROP TABLE or DROP INDEX inside a transaction block;
> that is, you can't do BEGIN; DROP TABLE foo; END anymore.  The reason
> for rejecting this case is that we do the wrong thing if the transaction
> is later aborted.  Following BEGIN; DROP TABLE foo; ABORT, the system
> tables will claim that foo is still valid (since the changes to them
> were never committed) but we've already unlinked foo's physical file,
> and we can't get it back.  Solution: only allow DROP TABLE outside
> BEGIN, so that the user can't try to change his mind later.

What if table was created inside BEGIN/END?
Any reason to disallow DROP of local tables?

Vadim

Mon, 06 Sep 1999 09:09:47 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] DROP TABLE inside transaction block
Tom Lane wrote:

> 
> > Cant you just rename to a unique name, maybee in another directory,
> 
> Not if other backends are also accessing the table.  Remember that to
> make this really work, the DROP would have to be invisible to other
> backends until commit.
> 

Is that really needed? Remember that table's creation is not transparent
to other users - when someone attempts to create a table, others,
though can't see that table, cannot create a table with the same name.
So you can simply issue a draconian-level lock on a table being deleted.
But in any case it would need postponing real killing until transaction
commit.

> The amount of work needed seems vastly more than the feature is worth...

I personally have a project in development which extensively uses
that feature. It is meant to be database restructuring 'on the fly'.
If you break that, it would be a big drawback to me. And I assume, not
only to me, because it would break an idea of trasaction itself. 
Database restructuring by software, not by hand, will be seriously
damaged.

-- 
Leon.
-------
He knows he'll never have to answer for any of his theories actually 
being put to test. If they were, they would be contaminated by reality.


Mon, 06 Sep 1999 16:51:46 +0500
Leon <leon@udmnet.ru>
Re: [HACKERS] DROP TABLE inside transaction block
Vadim Mikheev <vadim@krs.ru> writes:
>> and we can't get it back.  Solution: only allow DROP TABLE outside
>> BEGIN, so that the user can't try to change his mind later.

> What if table was created inside BEGIN/END?
> Any reason to disallow DROP of local tables?

None, and in fact the code does allow that case, but I forgot to
mention it.

			regards, tom lane

Mon, 06 Sep 1999 10:14:12 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] DROP TABLE inside transaction block 
Leon <leon@udmnet.ru> writes:
> Tom Lane wrote:
>>>> Cant you just rename to a unique name, maybee in another directory,
>> 
>> Not if other backends are also accessing the table.  Remember that to
>> make this really work, the DROP would have to be invisible to other
>> backends until commit.

> Is that really needed? Remember that table's creation is not transparent
> to other users - when someone attempts to create a table, others,
> though can't see that table, cannot create a table with the same name.
> So you can simply issue a draconian-level lock on a table being deleted.

That's a good point --- we acquire exclusive lock anyway on a table
about to be deleted, so just holding that lock till end of transaction
should prevent other backends from trying to touch the table.

So someone could probably cobble together a real solution consisting of
locking the table and renaming the files to unique temp names at DROP
time, then either completing the drop and unlinking the files at commit
time, or re-renaming them at abort.

There are a bunch of subtleties to be dealt with though.  A couple of
gotchas I can think of offhand: better flush dirty buffers for the
target rel before doing the rename, else another backend might try to
do it between DROP and COMMIT, and write to the wrong file name.  The
renaming at abort time has to be done in the right order relative to
dropping tables created during the xact, or else BEGIN; DROP TABLE foo;
CREATE TABLE foo; ABORT won't work right.  Currently, an attempt to
lock a table always involves making a relcache entry first, and the
relcache will try to open the underlying files as soon as you do that,
so other backends trying to touch the dying table for the first time
would get unexpected error messages.  Probably a few other things.

In short, a lot of work for a very marginal feature.  How many other
DBMSes permit DROP TABLE to be rolled back?  How many users care?

> I personally have a project in development which extensively uses
> that feature. It is meant to be database restructuring 'on the fly'.

What do you mean by "that feature"?  The ability to abort a DROP TABLE?
We have no such feature, and never have.  If you just mean that you
want to issue DROP TABLE inside BEGIN/END, and you don't care about
problems that ensue if the transaction is aborted, then we could
consider downgrading the error report to a notice as I suggested
yesterday.

			regards, tom lane

Mon, 06 Sep 1999 10:44:00 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] DROP TABLE inside transaction block 
Tom Lane wrote:

> 
> In short, a lot of work for a very marginal feature.  How many other
> DBMSes permit DROP TABLE to be rolled back?  How many users care?
> 

Don't know. But here is the idea: drop table rollback is needed in
automation of DB restructuring. There is no need of that in web or
'custom' applications for that feature. It is only needed in complex,
two-stage applications, when first stage manages the underlying DB
structure for the second. In other words, in big projects. If you
are not very ambitious, you can get rid of that complication. I 
personally can live without it, though with some redesign of my
project, and there will be no restructuring 'on the fly'.

> > I personally have a project in development which extensively uses
> > that feature. It is meant to be database restructuring 'on the fly'.
> 
> What do you mean by "that feature"?  The ability to abort a DROP TABLE?
> We have no such feature, and never have.  

Sadly I always supposed that rollback can work wonders and resurrect
a table killed in transaction. I was so sure it was so that no testing
had been done. It isn't mentioned in docs.

-- 
Leon.
-------
He knows he'll never have to answer for any of his theories actually 
being put to test. If they were, they would be contaminated by reality.


Mon, 06 Sep 1999 21:02:07 +0500
Leon <leon@udmnet.ru>
Re: [HACKERS] DROP TABLE inside transaction block
Here's some comments from a naive viewpoint (moving from InterBase to
PostgreSQL):

Tom Lane wrote:

> So someone could probably cobble together a real solution consisting of
> locking the table and renaming the files to unique temp names at DROP
> time, then either completing the drop and unlinking the files at commit
> time, or re-renaming them at abort.

Why should all of this renaming stuff be necessary?  I would expect all
entities CREATEd in a transaction to live entirely in cache (or a temp file),
and all DROPped entities to remain where they are until COMMIT time, at which
point DROPs should unlink and then CREATEs should create.  Is this too hard?

> In short, a lot of work for a very marginal feature.  How many other
> DBMSes permit DROP TABLE to be rolled back?  How many users care?

Sybase documentation explicitly allows most forms of CREATE and DROP in
transactions.  InterBase (which uses a versioning system much like
PostgreSQL) definitely handles CREATE/DROP in transactions correctly, but you
can't access a newly created table until after COMMIT.

What happens, in the current system if you want to make metadata changes in a
transaction and you make a typo that requires ABORTing the changes?  If you
have to make all metadata changes outside of transactions, you lose safety at
the most fragile and critical level.


Mon, 06 Sep 1999 12:10:15 -0500
Evan Simpson <evan@4-am.com>
Re: [HACKERS] DROP TABLE inside transaction block
Leon wrote:
> 
> Tom Lane wrote:
> 
> >
> > In short, a lot of work for a very marginal feature.  How many other
> > DBMSes permit DROP TABLE to be rolled back?  How many users care?
> >
> 
> Don't know. But here is the idea: drop table rollback is needed in
> automation of DB restructuring.

Actually the underlying mechanics could be used for other things too,
like:

ALTER TABLE DROP COLUMN colname, or even changing the type of column,
say 
from int4 -> int8 -> float -> char -> varchar -> text ?

I know that Oracle at least allows the latter but I'm not sure how 
it does that

-------------
Hannu

Mon, 06 Sep 1999 21:29:20 +0300
Hannu Krosing <hannu@tm.ee>
Re: [HACKERS] DROP TABLE inside transaction block
> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Tom Lane
> Sent: Monday, September 06, 1999 11:44 PM
> To: Leon
> Cc: Michael Simms; pgsql-hackers@postgreSQL.org
> Subject: Re: [HACKERS] DROP TABLE inside transaction block
>
>
> Leon <leon@udmnet.ru> writes:
> > Tom Lane wrote:
> >>>> Cant you just rename to a unique name, maybee in another directory,
> >>
> >> Not if other backends are also accessing the table.  Remember that to
> >> make this really work, the DROP would have to be invisible to other
> >> backends until commit.
>
> > Is that really needed? Remember that table's creation is not transparent
> > to other users - when someone attempts to create a table, others,
> > though can't see that table, cannot create a table with the same name.
> > So you can simply issue a draconian-level lock on a table being deleted.
>
> That's a good point --- we acquire exclusive lock anyway on a table
> about to be deleted, so just holding that lock till end of transaction
> should prevent other backends from trying to touch the table.
>

That reminds me.
DROP TABLE doesn't hold exlusive lock till end of transaction.
UnlockRelation() seems too early.
Here is a patch.

Seems ALTER TABLE doesn't acquire any lock for the target
relation. It's OK ?

regards.

Hiroshi Inoue
Inoue@tpf.co.jp

*** catalog/heap.c.orig	Tue Sep  7 08:52:04 1999
--- catalog/heap.c	Tue Sep  7 08:58:16 1999
***************
*** 1330,1336 ****

  	rel->rd_nonameunlinked = TRUE;

- 	UnlockRelation(rel, AccessExclusiveLock);

  	heap_close(rel);

--- 1330,1335 ----


Tue, 7 Sep 1999 10:13:05 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] DROP TABLE inside transaction block 
Tom Lane wrote:
> 
> There are a bunch of subtleties to be dealt with though.  A couple of
> gotchas I can think of offhand: better flush dirty buffers for the
> target rel before doing the rename, else another backend might try to
> do it between DROP and COMMIT, and write to the wrong file name.  The

BTW, I'm going to use relation oid as relation file name for WAL:
it would be bad to store relname in log records for each updated
tuple and it would be hard to scan pg_class to get relname from
reloid in recovery.

> renaming at abort time has to be done in the right order relative to
> dropping tables created during the xact, or else BEGIN; DROP TABLE foo;
> CREATE TABLE foo; ABORT won't work right.  Currently, an attempt to
> lock a table always involves making a relcache entry first, and the
> relcache will try to open the underlying files as soon as you do that,
> so other backends trying to touch the dying table for the first time
> would get unexpected error messages.  Probably a few other things.
> 
> In short, a lot of work for a very marginal feature.  How many other
> DBMSes permit DROP TABLE to be rolled back?  How many users care?

Oracle auto-commits current in-progress transaction before
execution of any DDL statement and executes such statements in
separate transaction. 

Vadim

Tue, 07 Sep 1999 09:44:19 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] DROP TABLE inside transaction block
> > renaming at abort time has to be done in the right order relative to
> > dropping tables created during the xact, or else BEGIN; DROP TABLE foo;
> > CREATE TABLE foo; ABORT won't work right.  Currently, an attempt to
> > lock a table always involves making a relcache entry first, and the
> > relcache will try to open the underlying files as soon as you do that,
> > so other backends trying to touch the dying table for the first time
> > would get unexpected error messages.  Probably a few other things.
> > 
> > In short, a lot of work for a very marginal feature.  How many other
> > DBMSes permit DROP TABLE to be rolled back?  How many users care?
> 
> Oracle auto-commits current in-progress transaction before
> execution of any DDL statement and executes such statements in
> separate transaction. 

That's cheating!

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

Mon, 6 Sep 1999 22:53:29 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] DROP TABLE inside transaction block
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
>> That's a good point --- we acquire exclusive lock anyway on a table
>> about to be deleted, so just holding that lock till end of transaction
>> should prevent other backends from trying to touch the table.

> That reminds me.
> DROP TABLE doesn't hold exlusive lock till end of transaction.
> UnlockRelation() seems too early.

I wondered about that too --- but I didn't change it because I wasn't
sure it was wrong.  Vadim, what do you think?

> Seems ALTER TABLE doesn't acquire any lock for the target
> relation. It's OK ?

None?  Yipes.  Seems to me it should *definitely* be grabbing
AccessExclusiveLock.

			regards, tom lane

Mon, 06 Sep 1999 23:00:55 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] DROP TABLE inside transaction block 
Tom Lane wrote:
> 
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> >> That's a good point --- we acquire exclusive lock anyway on a table
> >> about to be deleted, so just holding that lock till end of transaction
> >> should prevent other backends from trying to touch the table.
> 
> > That reminds me.
> > DROP TABLE doesn't hold exlusive lock till end of transaction.
> > UnlockRelation() seems too early.
> 
> I wondered about that too --- but I didn't change it because I wasn't
> sure it was wrong.  Vadim, what do you think?

I remember that Hiroshi reported about this already and
seems we decided to remove UnlockRelation from heap_destroy_with_catalog(),
but forgot to do it?

> 
> > Seems ALTER TABLE doesn't acquire any lock for the target
> > relation. It's OK ?
> 
> None?  Yipes.  Seems to me it should *definitely* be grabbing
> AccessExclusiveLock.

Yes.

Vadim

Tue, 07 Sep 1999 11:09:40 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] DROP TABLE inside transaction block
>> 
>> Oracle auto-commits current in-progress transaction before
>> execution of any DDL statement and executes such statements in
>> separate transaction. 
>
>That's cheating!
>

Dec (Oracle) Rdb cheats by locking a tables meta-data as soon as any user
accesses it, so that 'alter/drop table' will not run while that user is
attached. But is does support meta-data changes inside transactions
(assuming no-else who is currently connected has ever read that particular
meta-data). It is nice being able to rollback 'alter table' statements,
even under these strong restrictions.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: +61-03-5367 7422            |                 _________  \
Fax: +61-03-5367 7430            |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Tue, 07 Sep 1999 13:19:24 +1000
Philip Warner <pjw@rhyme.com.au>
Re: [HACKERS] DROP TABLE inside transaction block
Bruce Momjian wrote:
> 
> > > renaming at abort time has to be done in the right order relative to
> > > dropping tables created during the xact, or else BEGIN; DROP TABLE foo;
> > > CREATE TABLE foo; ABORT won't work right.  Currently, an attempt to
> > > lock a table always involves making a relcache entry first, and the
> > > relcache will try to open the underlying files as soon as you do that,
> > > so other backends trying to touch the dying table for the first time
> > > would get unexpected error messages.  Probably a few other things.
> > >
> > > In short, a lot of work for a very marginal feature.  How many other
> > > DBMSes permit DROP TABLE to be rolled back?  How many users care?
> >
> > Oracle auto-commits current in-progress transaction before
> > execution of any DDL statement and executes such statements in
> > separate transaction.
> 
> That's cheating!

Maybe :))
But sql3-12aug93 says:

         4.41  SQL-transactions


         An SQL-transaction (transaction) is a sequence of executions of   
         SQL-statements that is atomic with respect to recovery. These oper-
         ations are performed by one or more compilation units and <module>s
         or by the direct invocation of SQL.

         It is implementation-defined whether or not the non-dynamic or   
               ^^^^^^^^^^^^^^^^^^^^^^
         dynamic execution of an SQL-data statement or the execution of
         an <SQL dynamic data statement> is permitted to occur within the
         same SQL-transaction as the non-dynamic or dynamic execution of
         an SQL-schema statement. If it does occur, then the effect on any
            ^^^^^^^^^^^^^^^^^^^^
         open cursor, prepared dynamic statement, or deferred constraint
         is implementation-defined. There may be additional implementation-
         defined restrictions, requirements, and conditions. If any such
         restrictions, requirements, or conditions are violated, then an
         implementation-defined exception condition or a completion con-
         dition warning with an implementation-defined subclass code is
         raised.

Vadim

Tue, 07 Sep 1999 12:13:39 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] DROP TABLE inside transaction block


Tom Lane ha scritto:

> Pursuant to a phone conversation I had with Bruce, I added code this
> morning to reject DROP TABLE or DROP INDEX inside a transaction block;
> that is, you can't do BEGIN; DROP TABLE foo; END anymore.  The reason
> for rejecting this case is that we do the wrong thing if the transaction
> is later aborted.  Following BEGIN; DROP TABLE foo; ABORT, the system
> tables will claim that foo is still valid (since the changes to them
> were never committed) but we've already unlinked foo's physical file,
> and we can't get it back.  Solution: only allow DROP TABLE outside
> BEGIN, so that the user can't try to change his mind later.
>
> However, on second thought I wonder if this cure is worse than the
> disease.  Will it be unreasonably hard to drop tables using client
> interfaces that like to wrap everything in BEGIN/END?  Plugging an
> obscure hole might not be worth that.
>
> A possible compromise is not to error out, but just to issue a NOTICE
> along the lines of "DROP TABLE is not undoable, so don't even think of
> trying to abort now..."
>
> (Of course, what would be really nice is if it just worked, but I don't
> see any way to make that happen without major changes.  Simply
> postponing the unlink to end of transaction isn't workable; consider
> BEGIN; DROP TABLE foo; CREATE TABLE foo; ...)
>
> Any thoughts?  Will there indeed be a problem with JDBC or ODBC if we
> leave this error check in place?
>
>                         regards, tom lane
>
> ************
>
> ************

Seems a good solution. I have an old note about this problem.
What about to reject also the following commands inside transactions?


* BUGS: There are some commands that doesn't work properly
               inside transactions. Users should NOT use the following
              statements inside transactions:

     - DROP TABLE      -- in case of ROLLBACK only table structure
                                         will be recovered, data will be
lost.
     - CREATE VIEWS    -- the behavior of the backend is unpredictable.
     - ALTER TABLE     -- the behavior of the backend is unpredictable.
     - CREATE DATABASE -- in case of ROLLBACK will be removed references
                          from "pg_database" but directory
$PGDATA/databasename will not be removed.

Josï¿½




Tue, 07 Sep 1999 14:55:56 +0200
=?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com>
Re: [HACKERS] DROP TABLE inside transaction block
=?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com> writes:
> Seems a good solution. I have an old note about this problem.
> What about to reject also the following commands inside transactions?

> * BUGS: There are some commands that doesn't work properly
>                inside transactions. Users should NOT use the following
>               statements inside transactions:

>      - DROP TABLE      -- in case of ROLLBACK only table structure
>                                          will be recovered, data will be
> lost.
>      - CREATE VIEWS    -- the behavior of the backend is unpredictable.
>      - ALTER TABLE     -- the behavior of the backend is unpredictable.
>      - CREATE DATABASE -- in case of ROLLBACK will be removed references
>                           from "pg_database" but directory
> $PGDATA/databasename will not be removed.

CREATE DATABASE (and presumably also DROP DATABASE) probably should
refuse to run inside a transaction.

I see no good reason that CREATE VIEW or ALTER TABLE should not work
cleanly in a transaction.  It may be that they have bugs interfering
with that (for example, Hiroshi just pointed out that ALTER TABLE
seems not to be locking the table, which is surely bogus).

The main reason that DROP TABLE is an issue is that it alters the
underlying Unix file structure, which means we can't just rely on the
normal transaction mechanisms of committed/uncommitted tuples to handle
rollback.  ALTER TABLE doesn't do anything except change tuples.
CREATE VIEW is a CREATE TABLE plus tuple changes (and while CREATE TABLE
does alter the file structure by making a new file, we have extra code
in there to handle rolling it back).  So it seems like they oughta work.

RENAME TABLE is another thing that can't currently be rolled back,
because it renames the underlying Unix files and there's no mechanism
to undo that.  (RENAME TABLE is missing a lock too...)

			regards, tom lane

Tue, 07 Sep 1999 09:53:54 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] DROP TABLE inside transaction block 


> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Tom Lane
> Sent: Tuesday, September 07, 1999 10:54 PM
> To: Josï¿½Soares
> Cc: pgsql-hackers@postgreSQL.org
> Subject: Re: [HACKERS] DROP TABLE inside transaction block
>
>
> =?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com> writes:
> > Seems a good solution. I have an old note about this problem.
> > What about to reject also the following commands inside transactions?
>
> > * BUGS: There are some commands that doesn't work properly
> >                inside transactions. Users should NOT use the following
> >               statements inside transactions:
>
> >      - DROP TABLE      -- in case of ROLLBACK only table structure
> >                                          will be recovered, data will be
> > lost.
> >      - CREATE VIEWS    -- the behavior of the backend is unpredictable.
> >      - ALTER TABLE     -- the behavior of the backend is unpredictable.
> >      - CREATE DATABASE -- in case of ROLLBACK will be removed references
> >                           from "pg_database" but directory
> > $PGDATA/databasename will not be removed.
>
> CREATE DATABASE (and presumably also DROP DATABASE) probably should
> refuse to run inside a transaction.
>

Probably VACUUM should also refuse to run inside transactions.
VACUUM has a phase like commit in the middle of execution.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp


Wed, 8 Sep 1999 09:29:44 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] DROP TABLE inside transaction block 

My guess is that your new open routines with locking have fixed this.


> Pursuant to a phone conversation I had with Bruce, I added code this
> morning to reject DROP TABLE or DROP INDEX inside a transaction block;
> that is, you can't do BEGIN; DROP TABLE foo; END anymore.  The reason
> for rejecting this case is that we do the wrong thing if the transaction
> is later aborted.  Following BEGIN; DROP TABLE foo; ABORT, the system
> tables will claim that foo is still valid (since the changes to them
> were never committed) but we've already unlinked foo's physical file,
> and we can't get it back.  Solution: only allow DROP TABLE outside
> BEGIN, so that the user can't try to change his mind later.
> 
> However, on second thought I wonder if this cure is worse than the
> disease.  Will it be unreasonably hard to drop tables using client
> interfaces that like to wrap everything in BEGIN/END?  Plugging an
> obscure hole might not be worth that.
> 
> A possible compromise is not to error out, but just to issue a NOTICE
> along the lines of "DROP TABLE is not undoable, so don't even think of
> trying to abort now..."
> 
> (Of course, what would be really nice is if it just worked, but I don't
> see any way to make that happen without major changes.  Simply
> postponing the unlink to end of transaction isn't workable; consider
> BEGIN; DROP TABLE foo; CREATE TABLE foo; ...)
> 
> Any thoughts?  Will there indeed be a problem with JDBC or ODBC if we
> leave this error check in place?
> 
> 			regards, tom lane
> 
> ************
> 
> 


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

Tue, 28 Sep 1999 00:08:12 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] DROP TABLE inside transaction block

Any comment on this?

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> 
> 
> Tom Lane ha scritto:
> 
> > Pursuant to a phone conversation I had with Bruce, I added code this
> > morning to reject DROP TABLE or DROP INDEX inside a transaction block;
> > that is, you can't do BEGIN; DROP TABLE foo; END anymore.  The reason
> > for rejecting this case is that we do the wrong thing if the transaction
> > is later aborted.  Following BEGIN; DROP TABLE foo; ABORT, the system
> > tables will claim that foo is still valid (since the changes to them
> > were never committed) but we've already unlinked foo's physical file,
> > and we can't get it back.  Solution: only allow DROP TABLE outside
> > BEGIN, so that the user can't try to change his mind later.
> >
> > However, on second thought I wonder if this cure is worse than the
> > disease.  Will it be unreasonably hard to drop tables using client
> > interfaces that like to wrap everything in BEGIN/END?  Plugging an
> > obscure hole might not be worth that.
> >
> > A possible compromise is not to error out, but just to issue a NOTICE
> > along the lines of "DROP TABLE is not undoable, so don't even think of
> > trying to abort now..."
> >
> > (Of course, what would be really nice is if it just worked, but I don't
> > see any way to make that happen without major changes.  Simply
> > postponing the unlink to end of transaction isn't workable; consider
> > BEGIN; DROP TABLE foo; CREATE TABLE foo; ...)
> >
> > Any thoughts?  Will there indeed be a problem with JDBC or ODBC if we
> > leave this error check in place?
> >
> >                         regards, tom lane
> >
> > ************
> >
> > ************
> 
> Seems a good solution. I have an old note about this problem.
> What about to reject also the following commands inside transactions?
> 
> 
> * BUGS: There are some commands that doesn't work properly
>                inside transactions. Users should NOT use the following
>               statements inside transactions:
> 
>      - DROP TABLE      -- in case of ROLLBACK only table structure
>                                          will be recovered, data will be
> lost.
>      - CREATE VIEWS    -- the behavior of the backend is unpredictable.
>      - ALTER TABLE     -- the behavior of the backend is unpredictable.
>      - CREATE DATABASE -- in case of ROLLBACK will be removed references
>                           from "pg_database" but directory
> $PGDATA/databasename will not be removed.
> 
> Jos_
> 
> 
> 
> 
> ************
> 
> 


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

Tue, 28 Sep 1999 00:08:39 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] DROP TABLE inside transaction block

Seems like good comments on these items.  Anything for TODO list here?


> =?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com> writes:
> > Seems a good solution. I have an old note about this problem.
> > What about to reject also the following commands inside transactions?
> 
> > * BUGS: There are some commands that doesn't work properly
> >                inside transactions. Users should NOT use the following
> >               statements inside transactions:
> 
> >      - DROP TABLE      -- in case of ROLLBACK only table structure
> >                                          will be recovered, data will be
> > lost.
> >      - CREATE VIEWS    -- the behavior of the backend is unpredictable.
> >      - ALTER TABLE     -- the behavior of the backend is unpredictable.
> >      - CREATE DATABASE -- in case of ROLLBACK will be removed references
> >                           from "pg_database" but directory
> > $PGDATA/databasename will not be removed.
> 
> CREATE DATABASE (and presumably also DROP DATABASE) probably should
> refuse to run inside a transaction.
> 
> I see no good reason that CREATE VIEW or ALTER TABLE should not work
> cleanly in a transaction.  It may be that they have bugs interfering
> with that (for example, Hiroshi just pointed out that ALTER TABLE
> seems not to be locking the table, which is surely bogus).
> 
> The main reason that DROP TABLE is an issue is that it alters the
> underlying Unix file structure, which means we can't just rely on the
> normal transaction mechanisms of committed/uncommitted tuples to handle
> rollback.  ALTER TABLE doesn't do anything except change tuples.
> CREATE VIEW is a CREATE TABLE plus tuple changes (and while CREATE TABLE
> does alter the file structure by making a new file, we have extra code
> in there to handle rolling it back).  So it seems like they oughta work.
> 
> RENAME TABLE is another thing that can't currently be rolled back,
> because it renames the underlying Unix files and there's no mechanism
> to undo that.  (RENAME TABLE is missing a lock too...)
> 
> 			regards, tom lane
> 
> ************
> 
> 


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

Tue, 28 Sep 1999 00:09:10 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] DROP TABLE inside transaction block
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> Seems like good comments on these items.  Anything for TODO list here?

Actually, the current state of play is that I reduced the ERROR messages
to NOTICEs in DROP TABLE and DROP INDEX ("NOTICE: DROP TABLE cannot be
rolled back, so don't abort now"), since there seemed to be some
unhappiness about making them hard errors.  I also put similar messages
into RENAME TABLE and TRUNCATE TABLE.

I have a personal TODO item to go and insert some more checks: per the
discussions so far, CREATE/DROP DATABASE probably need similar messages,
and I think we need to make VACUUM refuse to run inside a transaction
block at all (since its internal commits will not do the intended thing
if you do BEGIN; VACUUM).  Also on my list is to investigate these
reports that CREATE VIEW and ALTER TABLE don't roll back cleanly ---
there may be bugs lurking there.  If you want to add those to the
public list, go ahead.

			regards, tom lane


>> =?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com> writes:
>>>> Seems a good solution. I have an old note about this problem.
>>>> What about to reject also the following commands inside transactions?
>> 
>>>> * BUGS: There are some commands that doesn't work properly
>>>> inside transactions. Users should NOT use the following
>>>> statements inside transactions:
>> 
>>>> - DROP TABLE      -- in case of ROLLBACK only table structure
>>>> will be recovered, data will be
>>>> lost.
>>>> - CREATE VIEWS    -- the behavior of the backend is unpredictable.
>>>> - ALTER TABLE     -- the behavior of the backend is unpredictable.
>>>> - CREATE DATABASE -- in case of ROLLBACK will be removed references
>>>> from "pg_database" but directory
>>>> $PGDATA/databasename will not be removed.
>> 
>> CREATE DATABASE (and presumably also DROP DATABASE) probably should
>> refuse to run inside a transaction.
>> 
>> I see no good reason that CREATE VIEW or ALTER TABLE should not work
>> cleanly in a transaction.  It may be that they have bugs interfering
>> with that (for example, Hiroshi just pointed out that ALTER TABLE
>> seems not to be locking the table, which is surely bogus).
>> 
>> The main reason that DROP TABLE is an issue is that it alters the
>> underlying Unix file structure, which means we can't just rely on the
>> normal transaction mechanisms of committed/uncommitted tuples to handle
>> rollback.  ALTER TABLE doesn't do anything except change tuples.
>> CREATE VIEW is a CREATE TABLE plus tuple changes (and while CREATE TABLE
>> does alter the file structure by making a new file, we have extra code
>> in there to handle rolling it back).  So it seems like they oughta work.
>> 
>> RENAME TABLE is another thing that can't currently be rolled back,
>> because it renames the underlying Unix files and there's no mechanism
>> to undo that.  (RENAME TABLE is missing a lock too...)

Tue, 28 Sep 1999 09:40:17 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] DROP TABLE inside transaction block 
Tom Lane is working on this, and it should be improved for 6.6.


[Charset iso-8859-1 unsupported, filtering to ASCII...]
> 
> 
> Tom Lane ha scritto:
> 
> > Pursuant to a phone conversation I had with Bruce, I added code this
> > morning to reject DROP TABLE or DROP INDEX inside a transaction block;
> > that is, you can't do BEGIN; DROP TABLE foo; END anymore.  The reason
> > for rejecting this case is that we do the wrong thing if the transaction
> > is later aborted.  Following BEGIN; DROP TABLE foo; ABORT, the system
> > tables will claim that foo is still valid (since the changes to them
> > were never committed) but we've already unlinked foo's physical file,
> > and we can't get it back.  Solution: only allow DROP TABLE outside
> > BEGIN, so that the user can't try to change his mind later.
> >
> > However, on second thought I wonder if this cure is worse than the
> > disease.  Will it be unreasonably hard to drop tables using client
> > interfaces that like to wrap everything in BEGIN/END?  Plugging an
> > obscure hole might not be worth that.
> >
> > A possible compromise is not to error out, but just to issue a NOTICE
> > along the lines of "DROP TABLE is not undoable, so don't even think of
> > trying to abort now..."
> >
> > (Of course, what would be really nice is if it just worked, but I don't
> > see any way to make that happen without major changes.  Simply
> > postponing the unlink to end of transaction isn't workable; consider
> > BEGIN; DROP TABLE foo; CREATE TABLE foo; ...)
> >
> > Any thoughts?  Will there indeed be a problem with JDBC or ODBC if we
> > leave this error check in place?
> >
> >                         regards, tom lane
> >
> > ************
> >
> > ************
> 
> Seems a good solution. I have an old note about this problem.
> What about to reject also the following commands inside transactions?
> 
> 
> * BUGS: There are some commands that doesn't work properly
>                inside transactions. Users should NOT use the following
>               statements inside transactions:
> 
>      - DROP TABLE      -- in case of ROLLBACK only table structure
>                                          will be recovered, data will be
> lost.
>      - CREATE VIEWS    -- the behavior of the backend is unpredictable.
>      - ALTER TABLE     -- the behavior of the backend is unpredictable.
>      - CREATE DATABASE -- in case of ROLLBACK will be removed references
>                           from "pg_database" but directory
> $PGDATA/databasename will not be removed.
> 
> Jos_
> 
> 
> 
> 
> ************
> 
> 


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

Tue, 28 Sep 1999 11:26:34 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] DROP TABLE inside transaction block