Message-ID: <4D3769DB.4090804@2ndquadrant.com>
Date: Wed, 19 Jan 2011 17:46:51 -0500
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: Chris Browne <cbbrowne@acm.org>, Andres Freund <andres@anarazel.de>,
	pgsql-performance@postgresql.org
Subject: Re: the XID question
References: 
 <ca4f167a-0c04-4c59-9dec-a10d8f43437b@i25g2000prd.googlegroups.com>
	<4D36A35302000025000397CC@gw.wicourts.gov>
	<878vyg9399.fsf@cbbrowne.afilias-int.info>
	<201101191931.52841.andres@anarazel.de>
	<4D36DBE202000025000397F8@gw.wicourts.gov>
In-Reply-To: <4D36DBE202000025000397F8@gw.wicourts.gov>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Kevin Grittner wrote:
> Or just test it in psql.  BEGIN, run your query, look at pg_locks. 
> If an xid has been assigned, you'll see it there in the
> transactionid column.  You can easily satisfy yourself which
> statements grab an xid...

That's a good way to double-check exactly what's happening, but it's not 
even that hard:

gsmith=# select txid_current();
txid_current | 696

gsmith=# select 1;
?column? | 1

gsmith=# select 1;
?column? | 1

gsmith=# select txid_current();
txid_current | 697

Calling txid_current bumps the number up, but if you account for that 
you can see whether the thing(s) in the middle grabbed a real txid by 
whether the count increased by 1 or more than that.  So here's what one 
that did get a real xid looks like:

gsmith=# select txid_current();
txid_current | 702

gsmith=# insert into t(i) values(1);
INSERT 0 1
gsmith=# select txid_current();
txid_current | 704

That proves the INSERT in the middle was assigned one.

The commit message that added this feature to 8.3 has a good quick intro 
to what changed from earlier revs: 
http://archives.postgresql.org/pgsql-committers/2007-09/msg00026.php

Don't have to actually read the source to learn a bit more, because it's 
actually documented!  Mechanics are described at 
pgsql/src/backend/access/transam/README ; you need to know a bit more 
about subtransactions to follow all of it, but it gets the general idea 
across regardless:

= Transaction and Subtransaction Numbering =

Transactions and subtransactions are assigned permanent XIDs only when/if
they first do something that requires one --- typically, 
insert/update/delete
a tuple, though there are a few other places that need an XID assigned.
If a subtransaction requires an XID, we always first assign one to its
parent.  This maintains the invariant that child transactions have XIDs 
later
than their parents, which is assumed in a number of places.

The subsidiary actions of obtaining a lock on the XID and and entering 
it into
pg_subtrans and PG_PROC are done at the time it is assigned.

A transaction that has no XID still needs to be identified for various
purposes, notably holding locks.  For this purpose we assign a "virtual
transaction ID" or VXID to each top-level transaction.  VXIDs are formed 
from
two fields, the backendID and a backend-local counter; this arrangement 
allows
assignment of a new VXID at transaction start without any contention for
shared memory.  To ensure that a VXID isn't re-used too soon after backend
exit, we store the last local counter value into shared memory at backend
exit, and initialize it from the previous value for the same backendID slot
at backend start.  All these counters go back to zero at shared memory
re-initialization, but that's OK because VXIDs never appear anywhere 
on-disk.

Internally, a backend needs a way to identify subtransactions whether or not
they have XIDs; but this need only lasts as long as the parent top 
transaction
endures.  Therefore, we have SubTransactionId, which is somewhat like
CommandId in that it's generated from a counter that we reset at the 
start of
each top transaction.  The top-level transaction itself has 
SubTransactionId 1,
and subtransactions have IDs 2 and up.  (Zero is reserved for
InvalidSubTransactionId.)  Note that subtransactions do not have their
own VXIDs; they use the parent top transaction's VXID.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books