Обсуждение: Re: [GENERAL] drop/rename table and transactions

Mike Mascari wrote:
>
> This is one of the few areas that I disagree with the development trend in
> PostgreSQL. Every release contains different bugs related to DDL statements in
> transactions. The developers appear to want to make them work (i.e., have the
> ability to rollback a DROP TABLE, ALTER TABLE ADD COLUMN, etc.). This, in my
> opinion, goes far above and beyond the call of duty for a RDBMS. Oracle issues
> an implicit COMMIT whenever a DDL statement is found. In fact, one could argue
> that those who are porting Oracle apps to PostgreSQL would assume,
> incorrectly, than a DROP TABLE in a transaction committed any work done
> previously.
>
> I personally believe that PostgreSQL should do the same as Oracle and greatly
> simplify the implementation of DDL statements in the backed by issuing an
> implicit COMMIT....
>
> Just my opinion, though

And I agreed with this.
But I would like to preserve ability to CREATE TABLE, mostly
because I think that SELECT ... INTO TABLE ... is very usefull
thing.

Vadim

Mike Mascari wrote:
>> This is one of the few areas that I disagree with the development
>> trend in PostgreSQL. Every release contains different bugs related to
>> DDL statements in transactions. The developers appear to want to make
>> them work (i.e., have the ability to rollback a DROP TABLE, ALTER
>> TABLE ADD COLUMN, etc.). This, in my opinion, goes far above and
>> beyond the call of duty for a RDBMS. Oracle issues an implicit COMMIT
>> whenever a DDL statement is found.

So, the limits of our ambition should be to be as good as Oracle?
(Only one-half :-) here.)

I've seen quite a few discussions on the mailing lists about
applications that could really use rollback-able DDL commands.

Personally, I certainly wouldn't give up any reliability for this,
and darn little performance; but within those constraints I think
we should do what we can.

            regards, tom lane

Tom Lane wrote:

> Mike Mascari wrote:
> >> This is one of the few areas that I disagree with the development
> >> trend in PostgreSQL. Every release contains different bugs related to
> >> DDL statements in transactions. The developers appear to want to make
> >> them work (i.e., have the ability to rollback a DROP TABLE, ALTER
> >> TABLE ADD COLUMN, etc.). This, in my opinion, goes far above and
> >> beyond the call of duty for a RDBMS. Oracle issues an implicit COMMIT
> >> whenever a DDL statement is found.
>
> So, the limits of our ambition should be to be as good as Oracle?
> (Only one-half :-) here.)
>
> I've seen quite a few discussions on the mailing lists about
> applications that could really use rollback-able DDL commands.
>
> Personally, I certainly wouldn't give up any reliability for this,
> and darn little performance; but within those constraints I think
> we should do what we can.
>
>                         regards, tom lane
>

Well, I agree that it would be GREAT to be able to rollback DDL
statements.  However, at the moment, failures during a transaction while
DDL statements occur usually require direct intervention by the user (in
the case of having to drop/recreate indexes) and often require the services
of the DBA, if filesystem intervention is necessary (i.e., getting rid of
partially dropped/created tables and their associated fileystem files). I
guess I'm worried by the current state of ambiguity with respect to which
DDL statements can be safely rolled back and which can't.  I know you added
NOTICEs in current, but it seems less than robust to ask the user not to
trigger a bug. And of course, something like the following can always
happen:

test=# CREATE TABLE example(value text);
CREATE
test=# BEGIN;
BEGIN
test=# DROP TABLE example;
NOTICE:  Caution: DROP TABLE cannot be rolled back, so don't abort now
DROP

-- someone just yanked the RJ45 cable from the hub in the T-COM closet --

(which, ludicrous as it might seem, happens)

From an otherwise EXTREMELY happy user :-) (full smile...),  I see 3
scenarios:

(1) Disallow DDL statements in transactions
(2) Send NOTICE's asking for the user to not trigger the bug until the bugs
can be fixed -or-
(3) Have all DDL statements implicity commit any running transactions.

1, of course, stinks. 2 is the current state and would probably take
several releases before all DDL statement rollback bugs could be crushed
(look how many times it took to get segmented files right -- and are we
REALLY sure it is?). 3, it seems to me, could be implemented in a day's
time, would prevent the various forms of data corruption people often post
to this list (GENERAL) about, and still allows 2 to happen in the future as
a configure-time or run-time option.

Just some ramblings,

Mike Mascari

Mike Mascari <mascarm@mascari.com> writes:
> Well, I agree that it would be GREAT to be able to rollback DDL
> statements.  However, at the moment, failures during a transaction while
> DDL statements occur usually require direct intervention by the user (in
> the case of having to drop/recreate indexes) and often require the services
> of the DBA, if filesystem intervention is necessary (i.e., getting rid of
> partially dropped/created tables and their associated fileystem
> files).

And forced commit after the DDL statement completes will improve that
how?

> I see 3 scenarios:

> (1) Disallow DDL statements in transactions
> (2) Send NOTICE's asking for the user to not trigger the bug until the bugs
> can be fixed -or-
> (3) Have all DDL statements implicity commit any running transactions.

> 1, of course, stinks. 2 is the current state and would probably take
> several releases before all DDL statement rollback bugs could be crushed

It's not an overnight project, for sure.

> 3, it seems to me, could be implemented in a day's
> time, would prevent the various forms of data corruption people often post
> to this list (GENERAL) about,

I don't believe either of those assumptions.  We've had problems with
VACUUM's internal commit, and I don't think it'd be either quick or
inherently more reliable to apply the same model to all DDL commands.


A more significant point is that implicit commit is not a transparent
change; it will break applications.  People use transaction blocks for
two reasons: (1) to define where to roll back to after an error, (2) to
ensure that the results of logically related updates become visible to
other backends atomically.  Implicit commit destroys both of those
guarantees, even though only the first one is really related to the
implementation problem we are trying to solve.

As a user I'd be pretty unhappy if "SELECT ... INTO" suddenly became
"COMMIT; SELECT; BEGIN".  Not only would that mean that updates made
by my transaction would become visible prematurely, but it might also
mean that the SELECT retrieves results it should not (ie, results from
xacts that were not committed when my xact started).  Both of these
things could make my application logic fail in hard-to-find, hard-to-
reproduce-except-under-load ways.

So, although implicit commit might look like a convenient workaround at
the level of Postgres itself, it'd be a horrible loss of reliability
at the application level.  I'd rather go with #1 (hard error) than
risk introducing transactional bugs into applications that use Postgres.


> Since ORACLE has 70% of the RDBMS market, it is the de facto standard

Yes, and Windows is the de facto standard operating system.  I don't use
Windows, and I'm not willing to follow Oracle's lead when they make a
bad decision...

            regards, tom lane