Exponential processing time for multiple SELECT FOR UPDATE / UPDATE in a single transaction with PostgreSQL 9.x ?

From: Nathanael Terrien <nte(at)mustinformatique(dot)fr>
To: "pgsql-odbc(at)postgresql(dot)org" <pgsql-odbc(at)postgresql(dot)org>
Subject: Exponential processing time for multiple SELECT FOR UPDATE / UPDATE in a single transaction with PostgreSQL 9.x ?
Date: 2014-12-05 09:14:38
Message-ID: 52dd07051f6b42b09328c339c7b9c5d4@EXCH2013.mustinformatique.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

Hi List.

Our application does something like this, through psqlodbc :
------------------------------------------------------------------------------
Open transaction (« BEGIN »)
FOR x TO y STEP 1
Do Stuff
« SELECT col1 FROM table1 WHERE condition1 FOR UPDATE ; »
Do Stuff
« UPDATE table1 SET col1=z WHERE condition1 ; »
Do Stuff
NEXT x
End transaction (« COMMIT »)
------------------------------------------------------------------------------

Against PostgreSQL 8.4 : no problem.
Against PostgreSQL 9.x : starting at about a few hundred loops (locks), the process slows down, and continues to slow down exponentially, until the COMMIT happens.

We tried with different languages (C#, Omnis Studio, plain plpgsql ...), different versions of psqlodbc (8.4.2 to 9.3.x), different versions of PostgreSQL (8.4, 9.3, 9.4 RC1) , without psqlODBC ... and it all comes down to this :
The slow-down only happens with psqlodbc and PostgreSQL 9.x

So we guess it's abug ?

Regards,
Nathanael TERRIEN
Must  Informatique

Responses

Browse pgsql-odbc by date

  From Date Subject
Next Message Heikki Linnakangas 2014-12-05 10:48:12 Re: Exponential processing time for multiple SELECT FOR UPDATE / UPDATE in a single transaction with PostgreSQL 9.x ?
Previous Message Heikki Linnakangas 2014-12-03 10:57:43 Re: Pgodbc is thread safe?