Обсуждение: 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