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

> Maybe. Or just changed behavior between 8.4 and 9.0.

The tests we performed with other connection methods don't slow down (Omnis Studio + PGDAM, C# + npgsql, or just a function called multiple times in the same query window from pgAdmin).

> Could you write a self-contained test case to demonstrate this?

Yes.
Do you mind if it's a DotNet EXE ?

-----Message d'origine-----
De : Heikki Linnakangas [mailto:hlinnakangas(at)vmware(dot)com]
Envoyé : vendredi 5 décembre 2014 11:48
À : Nathanael Terrien; pgsql-odbc(at)postgresql(dot)org
Objet : Re: [ODBC] 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