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

On 12/05/2014 11:14 AM, Nathanael Terrien wrote:
> 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 ?

Maybe. Or just changed behavior between 8.4 and 9.0.

Could you write a self-contained test case to demonstrate this?
Something with the CREATE TABLE and other statements required to create
the database, and a small test program. It's pretty hard to debug otherwise.

- Heikki