Re: implicit abort harmful?

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