Обсуждение: ROLLBACK of DROP TABLE leaves database in inconsistent state

Поиск
Список
Период
Сортировка

ROLLBACK of DROP TABLE leaves database in inconsistent state

От
"Oliver Elphick"
Дата:
Of course, no one should try to rollback a DROP TABLE, but if it happens
the system tables are left in a mess, because the disk file has gone but
the system records are restored by the rollback:

[version 7.0RC2]
junk=# create table aaa (a int4);
CREATE
junk=# begin
junk-# ;
BEGIN
junk=# drop table aaa;
NOTICE:  Caution: DROP TABLE cannot be rolled back, so don't abort now
DROP
junk=# rollback;
ROLLBACK
junk=#  select * from aaa;
NOTICE:  mdopen: couldn't open aaa: No such file or directory
NOTICE:  RelationIdBuildRelation: smgropen(aaa): No such file or directory
NOTICE:  mdopen: couldn't open aaa: No such file or directory
NOTICE:  mdopen: couldn't open aaa: No such file or directory
NOTICE:  mdopen: couldn't open aaa: No such file or directory
NOTICE:  mdopen: couldn't open aaa: No such file or directory
ERROR:  cannot open relation aaa
junk=# create table aaa (a int4);
ERROR:  Relation 'aaa' already exists

The solution is either to exclude the system records from the rollback, or
to delay deleting the disk file until the transaction is committed.

Which is preferable?

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver              PGP key from public servers; key
ID32B8FAA1                ========================================    "If it is possible, as much as it depends on you,
live     peaceably with all men."        Romans 12:18 
 




Re: ROLLBACK of DROP TABLE leaves database in inconsistent state

От
Tom Lane
Дата:
This is news?

> junk=# drop table aaa;
> NOTICE:  Caution: DROP TABLE cannot be rolled back, so don't abort now
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> DROP
> junk=# rollback;
> ROLLBACK

What did you think the NOTICE was trying to tell you?  (I wanted to
make it a hard ERROR, but was shot down.)

This will be fixed eventually, but until something is done to decouple
filenames from relation names it can't be fixed properly.  Consider
BEGIN; DROP TABLE foo; CREATE TABLE foo ...; ABORT.  There are plans
in the pipeline to do that, and we'll fix it when that happens.
        regards, tom lane