Re: [bug #27823] SQLClient drops connections without sending notifications

[Richard Frith-Macdonald]

On 28 Oct 2009, at 08:33, Nicola Pero wrote:

> > > At work, one part of our application is used to 'scan' data  
> > > gathered from various devices, normalizes it, and inserts it into  
> > > the database as measurements.  This is very performance-critical,  
> > > and for a very large percentage of the cases, we are only ever  
> > > inserting rows (new measurements).  However, on occasion we need  
> > > to 'reprocess' the data that was originally gathered (for  
> > > instance, if the model used to normalize the data has been  
> > > modified to correct an error).  In this case, the same measurement  
> > > rows will be inserted, albeit they may contain slightly different  
> > > information.  This component of the application has no knowledge  
> > > of whether it is processing data for the first time or  
> > > reprocessing it (and no need to know, either).  It first attempts  
> > > to insert a row and if that fails due to a duplicate key  
> > > constraint violation, it will do an update instead.  The overhead  
> > > of querying the database first to see if the record is already  
> > > there would in most cases be completely wasted, and in the rarer  
> > > case would not save anything over simply attempting the insert in  
> > > the first place.  This of course uses straight C and ODBC, but the  
> > > principle is the same…if the ODBC drivers forced a disconnect on  
> > > every constraint violation, we would have significantly worse  
> > > performance, and would have to opt for the generally slower  
> > > approach of querying first, since we can only commit a group of  
> > > measurements for an interval on success of the entire scan (it  
> > > either all goes in or none of it does).
> >
> > As I said I don't mind accepting a patch to allow things to not  
> > disconnect on error, but your example really just re-enforces my  
> > assertion that it should not be an issue.
>
> What about simply having an option to choose between disconnect (or  
> not) on error ?

That's what I meant by the line above about accepting a patch ...  
something to permit you to choose not to disconnect.

> I agree that disconnect on error is safer and should be the default,  
> on the other hand it should be
> trivial to have an option not to disconnect on error (and just do a  
> quick cleanup of the connection). :-)

I would say fairly easy rather than trivial ... it would need to be  
implemented for all backends, and it would probably need to  
differentiate between fatal errors and recoverable ones (it's no good  
keeping the connection 'open' if the server won't talk to us).