Re: 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