DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=TpxC6sozf4HzDKVOeKAMQ5VLMF/GNBcA+bRTPC1BXHQf/y3qUAHTMinH5dNJ3+TsU3
	U6SuNNTjVF7c/0wg3d5wMLXadJKhgn20GLlt8djTUMaDBWCpBdEQVXIclHuF0zObCvi5
	GsPRo0EFw4BX9efV1Hh63QVh4re8mMN8PNuzI=
MIME-Version: 1.0
In-Reply-To: <4D70AF32.7020706@icritical.com>
References: <4D6FA208.1040800@icritical.com>
	<AANLkTikbvc9KHd98=3oeenGmmGq8Qi+ng4eRBGHffpNh@mail.gmail.com>
	<4D70AF32.7020706@icritical.com>
Date: Fri, 4 Mar 2011 09:20:46 -0500
Message-ID: <AANLkTikCadoAC64-R8NH+RMGtJCvDPtk59ejFLm=_d+W@mail.gmail.com>
Subject: Re: Slowing UPDATEs inside a transaction
From: Robert Haas <robertmhaas@gmail.com>
To: Matt Burke <mattblists@icritical.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1

On Fri, Mar 4, 2011 at 4:21 AM, Matt Burke <mattblists@icritical.com> wrote:
> Robert Haas wrote:
>> Old row versions have to be kept around until they're no longer of
>> interest to any still-running transaction.
>
> Thanks for the explanation.
>
> Regarding the snippet above, why would the intermediate history of
> multiply-modified uncommitted rows be of interest to anything, or is the
> current behaviour simply "cheaper" overall in terms of cpu/developer time?

Because in theory you could have a cursor open.  You could open a
cursor, start to read from it, then make an update.  Now the cursor
needs to see things as they were before the update.

We might be able to do some optimization here if we had some
infrastructure to detect when a backend has no registered snapshots
with a command-ID less than the command-ID of the currently active
snapshot, but nobody's put in the effort to figure out exactly what's
involved and whether it makes sense.  It's a problem I'm interested
in, but #(needed round-tuits) > #(actual round-tuits).

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company