Re: What's the XID?

http://www.postgresql.org/docs/8.1/interactive/maintenance.html#VACUUM-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-XID--tp14508639p14508639.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

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

Andrew Sullivan <ajs(at)crankycanuck(dot)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

On Thu, 27 Dec 2007, Tom Lane wrote:

> Andrew Sullivan <ajs(at)crankycanuck(dot)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(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

Jeff Frost <jeff(at)frostconsultingllc(dot)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

On Thu, 27 Dec 2007, Tom Lane wrote:

> Jeff Frost <jeff(at)frostconsultingllc(dot)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(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

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.

Jeff Frost <jeff(at)frostconsultingllc(dot)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

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(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

Tom Lane-2 wrote:
>
> Jeff Frost <jeff(at)frostconsultingllc(dot)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
>
>
if no begin or commit , then the xid would also be increased ??
--
View this message in context: http://www.nabble.com/What%27s-the-XID--tp14508639p14553816.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

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

how can i prove that the xid had been increased by 1?

i do a test,

step 1. SELECT datname, age(datfrozenxid) FROM pg_database;
--> mydb age:33183

step 2. SELECT datname, age(datfrozenxid) FROM pg_database;
--> mydb age:33188

step 3. SELECT datname, age(datfrozenxid) FROM pg_database;
--> mydb age:33193

why always increased by 5?

Postgresql Ver:8.1.3
using pgAdminIII Query window

Andrew Sullivan wrote:
>
> 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
>
>

--
View this message in context: http://www.nabble.com/What%27s-the-XID--tp14508639p14573789.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

"ivan.hou" <ivan(dot)hou(at)msa(dot)hinet(dot)net> writes:
> step 1. SELECT datname, age(datfrozenxid) FROM pg_database;
> --> mydb age:33183
> step 2. SELECT datname, age(datfrozenxid) FROM pg_database;
> --> mydb age:33188

> why always increased by 5?

If you do it directly in psql, it increases by 1.

> Postgresql Ver:8.1.3
> using pgAdminIII Query window

Perhaps you should ask the pgAdmin people what they're doing behind your
back ...

regards, tom lane

Tom Lane wrote:
> "ivan.hou" <ivan(dot)hou(at)msa(dot)hinet(dot)net> writes:
>> step 1. SELECT datname, age(datfrozenxid) FROM pg_database;
>> --> mydb age:33183
>> step 2. SELECT datname, age(datfrozenxid) FROM pg_database;
>> --> mydb age:33188
>
>> why always increased by 5?
>
> If you do it directly in psql, it increases by 1.
>
>> Postgresql Ver:8.1.3
>> using pgAdminIII Query window
>
> Perhaps you should ask the pgAdmin people what they're doing behind your
> back ...

We first send the query to the database. For each returned column, we
send two queries. The first one gives us the type's name of the column
and the second one gives us the OID of the base type. So, each query
executed with pgAdmin's query tool should increase age(datfrozenxid) by :
1 + (2 * number of columns in the SELECT)

Regards.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

Guillaume Lelarge wrote:

> We first send the query to the database. For each returned column, we
> send two queries. The first one gives us the type's name of the column
> and the second one gives us the OID of the base type. So, each query
> executed with pgAdmin's query tool should increase age(datfrozenxid) by :
> 1 + (2 * number of columns in the SELECT)

Ever thought of doing it all in a single transaction?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera wrote:
> Guillaume Lelarge wrote:
>
>> We first send the query to the database. For each returned column, we
>> send two queries. The first one gives us the type's name of the column
>> and the second one gives us the OID of the base type. So, each query
>> executed with pgAdmin's query tool should increase age(datfrozenxid) by :
>> 1 + (2 * number of columns in the SELECT)
>
> Ever thought of doing it all in a single transaction?
>

Well, I'm not sure of the consequences of this. I'll get a look at this.
What I first thought on my previous mail was to keep in memory a list
of all types available, but this was stupid : it needs refresh when
another user adds a new type and I'm sure there are others corner cases.
I much prefer your idea, thanks.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

how do u know this theory ? 1 + (2 * number of columns in the SELECT)
just try & test? or have any document?

Guillaume Lelarge-3 wrote:
>
> Tom Lane wrote:
>> "ivan.hou" <ivan(dot)hou(at)msa(dot)hinet(dot)net> writes:
>>> step 1. SELECT datname, age(datfrozenxid) FROM pg_database;
>>> --> mydb age:33183
>>> step 2. SELECT datname, age(datfrozenxid) FROM pg_database;
>>> --> mydb age:33188
>>
>>> why always increased by 5?
>>
>> If you do it directly in psql, it increases by 1.
>>
>>> Postgresql Ver:8.1.3
>>> using pgAdminIII Query window
>>
>> Perhaps you should ask the pgAdmin people what they're doing behind your
>> back ...
>
> We first send the query to the database. For each returned column, we
> send two queries. The first one gives us the type's name of the column
> and the second one gives us the OID of the base type. So, each query
> executed with pgAdmin's query tool should increase age(datfrozenxid) by :
> 1 + (2 * number of columns in the SELECT)
>
> Regards.
>
>
> --
> Guillaume.
> http://www.postgresqlfr.org
> http://dalibo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>

--
View this message in context: http://www.nabble.com/What%27s-the-XID--tp14508639p14590661.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

ivan.hou wrote:
> how do u know this theory ? 1 + (2 * number of columns in the SELECT)
> just try & test?

I did this at first, yes. I changed log_statement to all, reload the
configuration, and voila. I've seen that pgAdmin's query tool executes
this number of queries to display the result.

> or have any document?

After I did a few tests, I get a look at the source. There's no other
documents, as far as I know, about this.

Regards.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com