Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions

Mike Mascari <mascarm(at)mascari(dot)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