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

I reconfigured the ODBC connection setting (through the driver's windows interface) and set the protocol to "7.4-1" and it solved the slowdown problem.
(w00t!)
Which is fine by us as it was the default behavior before (I guess), and that's how our application is designed to handle transaction.

Thank you, guys :)

The test case is pretty simple :
--------------------------------------------------------
-- create and populate table
CREATE TABLE _slowlocks
(
lib character varying(50),
numbers integer
);
INSERT INTO _slowlocks VALUES ('one',0),('two',0),('three',0);
----------------------------------------------------------
--- In any language/program, run something like this :
---------------
ExecuteSQL("BEGIN;")
FOR(i=0,i++,i<1000)
ExecuteSQL("SELECT numbers FROM _slowlocks WHERE lib='two' FOR UPDATE;")
ExecuteSQL("UPDATE _slowlocks SET numbers= numbers +1 WHERE lib='two';")
NEXT i
ExecuteSQL("COMMIT;")
----------------------------------------------------------

With protocol set to "7.4-2", it took 1 minute and 11 seconds
With protocol set to "7.4-1", it took 1.2 second ...

-----Message d'origine-----
De : Heikki Linnakangas [mailto:hlinnakangas(at)vmware(dot)com]
Envoyé : vendredi 5 décembre 2014 13:06
À : Alvaro Herrera; Nathanael Terrien
Cc : 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 01:59 PM, Alvaro Herrera wrote:
> Now that I think about this, maybe the cache in your case is not being
> useful for some reason or other, and it's causing more of a slowdown.
> Is this plpgsql? If so, do you have EXCEPTION blocks in plpgsql code?
> Maybe SAVEPOINTs somewhere? (Does the ODBC driver create SAVEPOINTs
> automatically?)

Ah, good point. psqlodbc does create SAVEPOINTs automatically, if you run in the "statement" "rollback on error" mode. That is, Protocol=*-2 mode. It is the default.

You could try setting Protocol=7.4-0 in the config file (or set the corresponding option in the ODBC configuration GUI) and see if it helps.
That changes the behaviour on errors, so that when an error happens the whole transaction is rolled back, rather than just the statement. I don't know if that's acceptable for your application, but if it is, you should change that setting, because that gets rid of the extra SAVEPOINT commands, and improves performance. Even if it turns out not to be the cause of the exponential slowdown.

- Heikki