Обсуждение: DROP TABLE inside transaction block
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
> (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
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
>
> 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
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
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.
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
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
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.
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.
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
> -----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 ----
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
> > 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, Pennsylvania19026
"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
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
>> >> 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 |/
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
respectto recovery. These oper- ations are performed by one or more compilation units and <module>s or by
thedirect invocation of SQL.
It is implementation-defined whether or not the non-dynamic or ^^^^^^^^^^^^^^^^^^^^^^
dynamicexecution of an SQL-data statement or the execution of an <SQL dynamic data statement> is permitted to
occurwithin the same SQL-transaction as the non-dynamic or dynamic execution of an SQL-schema statement.
Ifit does occur, then the effect on any ^^^^^^^^^^^^^^^^^^^^ open cursor, prepared dynamic statement,
ordeferred constraint is implementation-defined. There may be additional implementation- defined
restrictions,requirements, and conditions. If any such restrictions, requirements, or conditions are violated,
thenan implementation-defined exception condition or a completion con- dition warning with an
implementation-definedsubclass code is raised.
Vadim
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
backendis unpredictable. - CREATE DATABASE -- in case of ROLLBACK will be removed references
from"pg_database" but directory
$PGDATA/databasename will not be removed.
José
José 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
> -----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 > > > José 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
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, Pennsylvania19026
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, Pennsylvania19026
Seems like good comments on these items. Anything for TODO list here? > José 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, Pennsylvania19026
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
>> José 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...)
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, Pennsylvania19026