Re: the XID question - Mailing list pgsql-performance

On 1月20日, 上午6時46分, g...@2ndquadrant.com (Greg Smith) wrote:
> 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    g...@2ndQuadrant.com   Baltimore, MD
> PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
> "PostgreSQL 9.0 High Performance":http://www.2ndQuadrant.com/books
>
> --
> Sent via pgsql-performance mailing list (pgsql-performa...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-performance

every time, i execute this query string  "SELECT datname,
age(datfrozenxid), FROM pg_database;"  in the sql query of
pgAdminIII , the age will be increased by 5 , not 1. why???