Date: Thu, 11 May 2006 14:57:10 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Martijn van Oosterhout <kleptog@svana.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, PFC <lists@peufeu.com>,
	Mitchell Skinner <mitch@arctur.us>,
	pgsql-performance@postgresql.org, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Big IN() clauses etc : feature proposal
Message-ID: <20060511195710.GL99570@pervasive.com>
References: <op.s893vzcpcigqcu@apollo13> <20060509103632.GD29652@svana.org>
	<op.s897j60qcigqcu@apollo13> <1147190285.32304.43.camel@firebolt>
	<op.s9alu1vhcigqcu@apollo13> <16680.1147201981@sss.pgh.pa.us>
	<20060510190011.GO99570@pervasive.com>
	<22558.1147307514@sss.pgh.pa.us>
	<20060511171806.GJ99570@pervasive.com>
	<20060511180319.GK30113@svana.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20060511180319.GK30113@svana.org>
User-Agent: Mutt/1.5.11

On Thu, May 11, 2006 at 08:03:19PM +0200, Martijn van Oosterhout wrote:
> On Thu, May 11, 2006 at 12:18:06PM -0500, Jim C. Nasby wrote:
> > > Yes, because there can be more than one active snapshot within a single
> > > transaction (think about volatile functions in particular).
> > 
> > Any documentation on how snapshot's work? They're a big mystery to me.
> > :(
> 
> A snapshot is a particular view on a database. In particular, you have
> to be able to view a version of the database that doesn't have you own
> changes, otherwise an UPDATE would keep updating the same tuple. Also,
> for example, a cursor might see an older version of the database than
> queries being run. I don't know of any particular information about it
> though. Google wasn't that helpful.

Ahh, I'd forgotten that commands sometimes needed to see prior data. But
that's done with cmin/max, right?

In any case, going back to the original thought/question... my point was
that in a single-session table, it should be possible to maintain a
row counter. Worst case, you might have to keep a seperate count for
each CID or XID, but that doesn't seem that unreasonable for a single
backend to do, unless you end up running a heck of a lot of commands.
More importantnly, it seems a lot more feasable to at least know how
many rows there are every time you COMMIT, which means you can
potentially avoid having to ANALYZE.

> > > No; you forgot about subtransactions.
> > 
> > Oh, I thought those were done with cmin and cmax... if that's not what
> > cmin/cmax are for, then what is?
> 
> cmin/cmax are command counters. So in the sequence:
> 
> BEGIN;
> SELECT 1;
> SELECT 2;
> 
> The second query runs as the same transaction ID but a higher command
> ID so it can see the result of the previous query. Subtransactions are
> (AIUI anyway) done by having transactions depend on other transactions.
> When you start a savepoint you start a new transaction ID whose status
> is tied to its top-level transaction ID but can also be individually
> rolledback.

Hmmm, interesting. I would have thought it was tied to CID, but I guess
XID has more of that machinery around to support rollback.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461