Message-ID: <4AF2E0FF.5050702@2ndquadrant.com>
Date: Thu, 05 Nov 2009 09:28:15 -0500
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Craig Ringer <craig@postnewspapers.com.au>
CC: Dave Crooke <dcrooke@gmail.com>,
	pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Followup: vacuum'ing toast
References: <ca24673e0911041552k70b4af34k6f689ab116fc8752@mail.gmail.com>
	<4AF229F5.201@2ndquadrant.com>
	<4AF24006.1010509@postnewspapers.com.au>
In-Reply-To: <4AF24006.1010509@postnewspapers.com.au>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Craig Ringer wrote:
> Hibernate is pretty well behaved with transaction management. In fact,
> it's downright nuts about keeping transactions open for as short a
> period of time as possible. It even implements its own row-versioning
> based optimistic locking scheme (oplock) rather than relying on holding
> a transaction open with row locks in the database.
>   
It's probably more nuts than it needs to be with PostgreSQL as the 
backing store, since MVCC prevents some of the common sources of row 
locks from being needed.  But since Hibernate is database-agnostic and 
it worried about locally cached copies of things too, it ends up needing 
to do this extra work regardless.

> 3) Autocommit being disabled. At least when using Hibernate via JPA,
> that'll cause a major mess and would easily explain the issues you're
> seeing. Hibernate manages transactions explicitly when required, and
> expects autocommit to be off.
>   
Downthread it suggests there's still some confusion here, but everyone 
should be clear about one thing:  turning autocommit on is the first 
step down a road that usually leads to bad batch performance.  If your 
problems go away by enabling it, which they sometimes do, that is a sign 
there's a problem to be investigated, not a true solution.  One day 
you're going to find yourself wanting transactions to be explicitly 
committed only when required, both for atomicity and performance 
reasons, and you won't be able to rely on autocommit as a crutch at that 
point.  Better to never get used to be there in the first place.

--
Greg Smith    greg@2ndquadrant.com    Baltimore, MD