Re: implicit abort harmful? - Mailing list pgsql-general
From | Nigel J. Andrews |
---|---|
Subject | Re: implicit abort harmful? |
Date | |
Msg-id | Pine.LNX.4.21.0305250200020.4741-100000@ponder.fairway2k.co.uk Whole thread Raw |
In response to | implicit abort harmful? ("Wayne Armstrong" <wdarmst@bacchus.com.au>) |
List | pgsql-general |
On Sun, 25 May 2003, Wayne Armstrong wrote: > Hi, > I have been using postgresql for about 3 months now (after about 15 years of > using various other dbms). > > I love it :) but - (and you just knew that but was coming :) there is one thing > that really causes me grief. > . > It is the tendency for postgres to rollback automatically on some errors. > > ... > > It actually makes more of a mess than leaving the decision to rollback under > application control. > If in this example the three "bad" records are scattered throughout the > dataset, I could end up (if I ignore the error as i would do for most other > dbms), with a random number of records in the table. etc etc. I'm confused on this. You're complaining that Postgresql lets you insert and commit a known number of records or rollback and insert none and yet you seem here to be saying in support of not applying that level of data integrity checks that it is messy to not apply that level of checking and ending up with a random number of inserted tuples. > Of course also, to do robust imports of the insert ifError update style > avaiilable in most other dbms I have worked with, it is also nescessary to > issue a commit before each insert/update attempt. Ok. So you're faced with a client wanting to do an bulk import of data. 3 out of a million records fail the import so you suggest the client determines the business reasons for why those records fail the import and are stopping the entire import or you import the rest and then spend the next six months alternately trying to a) get the client to determine the business reasons for the failure of those records because the lack of them isn't causing a problem and b) explaining to the client exactly why what they are trying to do won't work because of the lack of those business records? Ok, so after a couple of months you do get used to it and it becomes water off a ducks back but it still gets seen as a failing on your part by the client. BTW, I do believe in giving a client what they want in case you're wonder, just that sometimes you have to try and educate them in what they _really_ want. > > Example2 - ODBC driver rollbacks :- > > Here is a snippet from a postgres log :- > 2003-05-24 23:09:14 [1525] LOG: duration: 0.134503 sec > 2003-05-24 23:09:14 [1525] LOG: query: select nspname from pg_namespace n, p > _class c where c.relnamespace=n.oid and c.oid='select'::regclass > 2003-05-24 23:09:14 [1525] ERROR: Relation "select" does not exist > 2003-05-24 23:09:14 [1525] LOG: statement: select nspname from pg_namespace > , pg_class c where c.relnamespace=n.oid and c.oid='select'::regclass > 2003-05-24 23:09:14 [1525] LOG: query: ROLLBACK > 2003-05-24 23:09:14 [1525] LOG: duration: 0.000538 sec > > The rollback here is generated by the odbc driver to clear an error created by > the odbc driver incorrectly parsing the select from a subselect statement as a > table name. > The application is totally unaware an error has occured and a rollback has been > issued. > This is most likely to lead to data loss, unless, basically, an application > using odbc is in autocommit mode or commits after every sql statement issued. Ok. I can see that would be a pain but that is an odbc issue. If the odbc layer is misparsing something and issuing a rollback then that is nothing to do with the server, you'll hit that whether or not a single error server side forces a transaction abort or not. > This concerns me to the piont where I would really recommend not using the > parse statement option in the odbc driver (even though that reduces the odbc > drivers ability to mimic a prepare which is also problematic :) unless you are > using autocommit. > > For any application complex enough to be working with autocommit turned off in > the odbc driver, the auto rollback is a real problem. > > Please consider making the automatic abort/rollback feature an option ? I've got to disagree with this strongly. Like pain, errors are there to tell you something is wrong not to be ignored. Although, may be you're only on about the odbc thing. Even in that case I would suggest that the error is in odbc not flagging the offending query as being 'in error' when it generated the error instead of the backend. ...Actually, rereading the log above I see what you mean. ODBC generates a query which generates an error thus aborting the current transaction and making odbc issue a rollback becuase it noticed the error raised...however, my comment still stands, odbc should show that error to the client application. Indeed the standard behaviour of postgresql is correct in this situation and odbc trying to be clever has broken it. Until the client app. acknowledges an error occured, by causing the issuance of the rollback, then all queries should raise additional errors stopping the partial data commit you are seeing. If odbc was not trying to be clever and hide that fact from the application then any partial data commit would be the applications fault. Of course, I could have completely misunderstood your post considering the hour and everything. -- Nigel J. Andrews
pgsql-general by date: