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
functioncalled 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@vmware.com]
Envoyé : vendredi 5 décembre 2014 11:48
À : Nathanael Terrien; pgsql-odbc@postgresql.org
Objet : Re: [ODBC] Exponential processing time for multiple SELECT FOR UPDATE / UPDATE in a single transaction with
PostgreSQL9.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
downexponentially, 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
prettyhard to debug otherwise. 

- Heikki