On 1æ20æ, äå6æ46å, g...@xxxxxxxxxxxxxxx (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...@xxxxxxxxxxxxxxx  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...@xxxxxxxxxxxxxx) > 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??? -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
