Обсуждение: the XID question

От:
"Charles.Hou"
Дата:

after i backdb->dropdb->restoredb and then vacuum analy+full -> vacuum
freeze

the XID had been increased by 4 billion in two weeks...is it noraml?

what's the definetion of XID?

" select * from mybook" SQL command also increase the XID ?

reference:
http://www.postgresql.org/docs/9.0/static/routine-vacuuming.html

От:
"Charles.Hou"
Дата:

On 1月19日, 下午5時19分, "Charles.Hou" <giveme...@gmail.com> wrote:
> after i backdb->dropdb->restoredb and then vacuum analy+full -> vacuum
> freeze
>
> the XID had been increased by 4 billion in two weeks...is it noraml?
>
> what's the definetion of XID?
>
> " select * from mybook" SQL command also increase the XID ?
>
> reference:http://www.postgresql.org/docs/9.0/static/routine-vacuuming.html

sorry... not 4 billion , is 4 hundred million


От:
Filip Rembiałkowski
Дата:

2011/1/19 Charles.Hou <>:
> what's the definetion of XID?

XID == "Transaction ID".

> " select * from mybook" SQL command also increase the XID ?

Yes. Single SELECT is a transaction. Hence, it needs a transaction ID.


greets,
Filip

От:
"Kevin Grittner"
Дата:

Filip Rembia*kowski<> wrote:
> 2011/1/19 Charles.Hou <>:

>> " select * from mybook" SQL command also increase the XID ?
>
> Yes. Single SELECT is a transaction. Hence, it needs a transaction
> ID.

No, not in recent versions of PostgreSQL.  There's virtual
transaction ID, too; which is all that's needed unless the
transaction writes something.

Also, as a fine point, if you use explicit database transactions
(with BEGIN or START TRANSACTION) then you normally get one XID for
the entire transaction, unless you use SAVEPOINTs.

-Kevin

От:
Chris Browne
Дата:

 ("Kevin Grittner") writes:
> Filip Rembia*kowski<> wrote:
>> 2011/1/19 Charles.Hou <>:
>
>>> " select * from mybook" SQL command also increase the XID ?
>>
>> Yes. Single SELECT is a transaction. Hence, it needs a transaction
>> ID.
>
> No, not in recent versions of PostgreSQL.  There's virtual
> transaction ID, too; which is all that's needed unless the
> transaction writes something.
>
> Also, as a fine point, if you use explicit database transactions
> (with BEGIN or START TRANSACTION) then you normally get one XID for
> the entire transaction, unless you use SAVEPOINTs.

Erm, "not *necessarily* in recent versions of PostgreSQL."

A read-only transaction won't consume XIDs, but if you don't expressly
declare it read-only, they're still liable to get eaten...
--
(format nil "~S@~S" "cbbrowne" "gmail.com")
http://www3.sympatico.ca/cbbrowne/lisp.html
Parenthesize to avoid ambiguity.

От:
Andres Freund
Дата:

On Wednesday, January 19, 2011 07:06:58 PM Chris Browne wrote:
>  ("Kevin Grittner") writes:
> > Filip Rembia*kowski<> wrote:
> >> 2011/1/19 Charles.Hou <>:
> >>> " select * from mybook" SQL command also increase the XID ?
> >>
> >> Yes. Single SELECT is a transaction. Hence, it needs a transaction
> >> ID.
> >
> > No, not in recent versions of PostgreSQL.  There's virtual
> > transaction ID, too; which is all that's needed unless the
> > transaction writes something.
> >
> > Also, as a fine point, if you use explicit database transactions
> > (with BEGIN or START TRANSACTION) then you normally get one XID for
> > the entire transaction, unless you use SAVEPOINTs.
>
> Erm, "not *necessarily* in recent versions of PostgreSQL."
>
> A read-only transaction won't consume XIDs, but if you don't expressly
> declare it read-only, they're still liable to get eaten...
No. The Xid is generally only allocated at the first place a real xid is
needed. See GetCurrentTransactionId, AssignTransactionId in xact.c and the
caller of the former.

Andres

От:
"Kevin Grittner"
Дата:

Andres Freund <> wrote:
> On Wednesday, January 19, 2011 07:06:58 PM Chris Browne wrote:

>> A read-only transaction won't consume XIDs, but if you don't
>> expressly declare it read-only, they're still liable to get
>> eaten...
> No. The Xid is generally only allocated at the first place a real
> xid is needed. See GetCurrentTransactionId, AssignTransactionId in
> xact.c and the caller of the former.

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....

-Kevin

От:
Greg Smith
Дата:

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       Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books


От:
"Charles.Hou"
Дата:

On 1月19日, 下午10時39分, Kevin.Gritt...@wicourts.gov ("Kevin Grittner")
wrote:
> Filip Rembia*kowski<plk.zu...@gmail.com> wrote:
> > 2011/1/19 Charles.Hou <giveme...@gmail.com>:
> >> " select * from mybook" SQL command also increase the XID ?
>
> > Yes. Single SELECT is a transaction. Hence, it needs a transaction
> > ID.
>
> No, not in recent versions of PostgreSQL.  There's virtual
> transaction ID, too; which is all that's needed unless the
> transaction writes something.
>
my postgresql version is 8.1.3
you means the newer version has a virtual transaction ID. and what's
the maxmium of this virtual id,  also 4 billion ?
should i also vacuum freeze the virtual id in the new version when it
reached the 4 billion?

> Also, as a fine point, if you use explicit database transactions
> (with BEGIN or START TRANSACTION) then you normally get one XID for
> the entire transaction, unless you use SAVEPOINTs.
>
> -Kevin
>
> --
> Sent via pgsql-performance mailing list (pgsql-performa...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-performance



От:
"Charles.Hou"
Дата:

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???

От:
"Kevin Grittner"
Дата:

"Charles.Hou" <> wrote:

> my postgresql version is 8.1.3

Ouch!  That's getting pretty old; I hope it's not on Windows.

http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy

http://www.postgresql.org/about/news.865

> you means the newer version has a virtual transaction ID. and
> what's the maxmium of this virtual id,  also 4 billion ?
> should i also vacuum freeze the virtual id in the new version when
> it reached the 4 billion?

The point is to reduce maintenance, not increase it -- you don't
need to worry about cleaning these up.

-Kevin

От:
Robert Haas
Дата:

On Thu, Jan 20, 2011 at 12:04 PM, Kevin Grittner
<> wrote:
> "Charles.Hou" <> wrote:
>
>> my postgresql version is 8.1.3
>
> Ouch!  That's getting pretty old; I hope it's not on Windows.
>
> http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy
>
> http://www.postgresql.org/about/news.865
>
>> you means the newer version has a virtual transaction ID. and
>> what's the maxmium of this virtual id,  also 4 billion ?
>> should i also vacuum freeze the virtual id in the new version when
>> it reached the 4 billion?
>
> The point is to reduce maintenance, not increase it -- you don't
> need to worry about cleaning these up.

And in fact, in more recent releases - particularly 8.4 and 9.0, the
need to worry about vacuum in general is much less.  There are many
improvements to both vacuum generally and autovacuum in particular
that make things much better, including enabling autovacuum by
default, multiple autovacuum worker threads, the visibility map, and
so on.  It's fairly likely that everything that the OP is struggling
with on 8.1 would Just Work on 8.4 or 9.0.

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