http://www.postgresql.org/docs/8.1/i...FOR-WRAPAROUND

XID is transcation ID?
so, "select * from mydb" is a transcation?
if i executed "select * from mydb" twice, the XID wil be increased by 2

or ,just the "update","delete" command can be counted
--
View this message in context: http://www.nabble.com/What%27s-the-X...p14508639.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Wed, Dec 26, 2007 at 05:48:14PM -0800, ivan.hou wrote:
>
> XID is transcation ID?

Yes.

> so, "select * from mydb" is a transcation?

Yes.

> if i executed "select * from mydb" twice, the XID wil be increased by 2

Yep. Whereas if you did

BEGIN;
SELECT 1;
SELECT 1;
COMMIT;

the xid would be increased by 1.

A

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Andrew Sullivan <ajs@crankycanuck.ca> writes:
> On Wed, Dec 26, 2007 at 05:48:14PM -0800, ivan.hou wrote:
>> if i executed "select * from mydb" twice, the XID wil be increased by 2

> Yep. Whereas if you did

> BEGIN;
> SELECT 1;
> SELECT 1;
> COMMIT;

> the xid would be increased by 1.

But note that as of 8.3, SELECT-only transactions won't acquire an
XID and hence won't advance the counter. So if you're thinking of
writing code that depends on that behavior, don't.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Thu, 27 Dec 2007, Tom Lane wrote:

> Andrew Sullivan <ajs@crankycanuck.ca> writes:
>> On Wed, Dec 26, 2007 at 05:48:14PM -0800, ivan.hou wrote:
>>> if i executed "select * from mydb" twice, the XID wil be increased by 2
>
>> Yep. Whereas if you did
>
>> BEGIN;
>> SELECT 1;
>> SELECT 1;
>> COMMIT;
>
>> the xid would be increased by 1.
>
> But note that as of 8.3, SELECT-only transactions won't acquire an
> XID and hence won't advance the counter. So if you're thinking of
> writing code that depends on that behavior, don't.

>
Tom,

So, the new XID counter won't advance unless there's at least one
INSERT/UPDATE/DELETE in the transaction? Does it also update for SELECTs that
call a function which does some write activity?

Is there a new counter (or old one that I don't know about) that keeps track
of the SELECT-only transactions?


--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Jeff Frost <jeff@frostconsultingllc.com> writes:
> On Thu, 27 Dec 2007, Tom Lane wrote:
>> But note that as of 8.3, SELECT-only transactions won't acquire an
>> XID and hence won't advance the counter. So if you're thinking of
>> writing code that depends on that behavior, don't.

> So, the new XID counter won't advance unless there's at least one
> INSERT/UPDATE/DELETE in the transaction? Does it also update for SELECTs that
> call a function which does some write activity?

Any "write" activity causes an XID to be acquired.

> Is there a new counter (or old one that I don't know about) that keeps track
> of the SELECT-only transactions?

There's no global counter. There's a backend-local "virtual transaction
id" counter.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Thu, 27 Dec 2007, Tom Lane wrote:

> Jeff Frost <jeff@frostconsultingllc.com> writes:
>> On Thu, 27 Dec 2007, Tom Lane wrote:
>>> But note that as of 8.3, SELECT-only transactions won't acquire an
>>> XID and hence won't advance the counter. So if you're thinking of
>>> writing code that depends on that behavior, don't.
>
>> So, the new XID counter won't advance unless there's at least one
>> INSERT/UPDATE/DELETE in the transaction? Does it also update for SELECTs that
>> call a function which does some write activity?
>
> Any "write" activity causes an XID to be acquired.
>
>> Is there a new counter (or old one that I don't know about) that keeps track
>> of the SELECT-only transactions?
>
> There's no global counter. There's a backend-local "virtual transaction
> id" counter.
>

That's a drag as I have quite a few clients who graph the xacts/sec with MRTG.
Most of these clients have read heavy workloads and it would be great to be
able to graph read vs write xacts, but a drag if you have no visibility into
the read xacts.


--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Jeff Frost wrote:

> That's a drag as I have quite a few clients who graph the xacts/sec with
> MRTG. Most of these clients have read heavy workloads and it would be great
> to be able to graph read vs write xacts, but a drag if you have no
> visibility into the read xacts.

You can still get the transaction commit counter from pgstats, right?
In fact I would be surprised if you did something different.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Jeff Frost <jeff@frostconsultingllc.com> writes:
> On Thu, 27 Dec 2007, Tom Lane wrote:
>> There's no global counter. There's a backend-local "virtual transaction
>> id" counter.

> That's a drag as I have quite a few clients who graph the xacts/sec with MRTG.
> Most of these clients have read heavy workloads and it would be great to be
> able to graph read vs write xacts, but a drag if you have no visibility into
> the read xacts.

Why aren't you using the pgstats xact counters for that?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Thu, 27 Dec 2007, Alvaro Herrera wrote:

> Jeff Frost wrote:
>
>> That's a drag as I have quite a few clients who graph the xacts/sec with
>> MRTG. Most of these clients have read heavy workloads and it would be great
>> to be able to graph read vs write xacts, but a drag if you have no
>> visibility into the read xacts.
>
> You can still get the transaction commit counter from pgstats, right?
> In fact I would be surprised if you did something different.
>

Maybe I already am. This is what we've been doing:

select sum(xact_commit) as transactions from pg_stat_database;

Is that still the same in 8.3 (i.e. it still puts lumps in reads + writes)?
If so, pretend I said nothing before because I must have misunderstood what
Tom was saying before. :-)

--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

ivan.hou wrote:

>>> the read xacts.
>> Why aren't you using the pgstats xact counters for that?
>>
>> regards, tom lane
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>>
>>
> if no begin or commit , then the xid would also be increased ??

Yes. If you don't do a begin or commit, then it is a transaction per
statement.

Joshua D. Drake


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match