I reconfigured the ODBC connection setting (through the driver's windows interface) and set the protocol to "7.4-1" and
itsolved 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
handletransaction.
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@vmware.com]
Envoyé : vendredi 5 décembre 2014 13:06
À : Alvaro Herrera; Nathanael Terrien
Cc : 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 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.
Thatis, 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)
andsee if it helps.
That changes the behaviour on errors, so that when an error happens the whole transaction is rolled back, rather than
justthe 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
thecause of the exponential slowdown.
- Heikki