Gregory Stark <stark@enterprisedb.com> writes:
> Uhm, yeah, I somehow didn't write was I was thinking. I didn't mean to say we
> would be taking a new snapshot for each INSERT but that we would be resetting
> xmin for each INSERT. Whereas currently we only set xmin once when we set the
> serializable snapshot.

Right, but setting xmin within GetSnapshotData is essentially free.
What I'm envisioning is that we lose the notion of "this is a
serializable snapshot" that that function currently has, and just
give it the rule "if MyProc->xmin is currently zero, then set it".
Then the only additional mechanism needed is for the snapshot
manager to detect when all snapshots are gone and zero out
MyProc->xmin --- that would happen sometime during command shutdown,
and per current discussion it shouldn't need a lock.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Le mercredi 26 mars 2008, Tom Lane a écritÂ*:
> Dimitri Fontaine <dfontaine@hi-media.com> writes:
> > Le mercredi 26 mars 2008, Tom Lane a écritÂ*:
> >> whenever the number of active snapshots goes to zero
> > Does this ever happen?
> Certainly: between any two commands of a non-serializable transaction.

Oh, it's a transaction scope snapshot when I though about cluster global
snapshots. Thanks a lot for explaining, and sorry for disturbing!

--
dim

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQBH6nRQlBXRlnbh1bkRArk9AJ4mN2iuPzVtuSEr+YxKcL dVaewnwgCgpCa1
344GJFAjPciv2sIajHuZ5nc=
=BT/l
-----END PGP SIGNATURE-----

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> Gregory Stark <stark@enterprisedb.com> writes:
>> Uhm, yeah, I somehow didn't write was I was thinking. I didn't mean to say we
>> would be taking a new snapshot for each INSERT but that we would be resetting
>> xmin for each INSERT. Whereas currently we only set xmin once when we set the
>> serializable snapshot.
>
> Right, but setting xmin within GetSnapshotData is essentially free.
> What I'm envisioning is that we lose the notion of "this is a
> serializable snapshot" that that function currently has, and just
> give it the rule "if MyProc->xmin is currently zero, then set it".
> Then the only additional mechanism needed is for the snapshot
> manager to detect when all snapshots are gone and zero out
> MyProc->xmin --- that would happen sometime during command shutdown,
> and per current discussion it shouldn't need a lock.

It would be nice if there was some way to notice that no other transactions
have committed since last we calculated a snapshot and just reuse that
snapshot.

I would say ideally before we throw out our xmin but I suspect the point of
synchronization needed to notice this condition would be tantamount to that
same lock anyways.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Tom Lane wrote:

> What I'm envisioning is that we lose the notion of "this is a
> serializable snapshot" that that function currently has, and just
> give it the rule "if MyProc->xmin is currently zero, then set it".
> Then the only additional mechanism needed is for the snapshot
> manager to detect when all snapshots are gone and zero out
> MyProc->xmin --- that would happen sometime during command shutdown,
> and per current discussion it shouldn't need a lock.

This is all easily done -- it's just a couple of extra lines.

However I am now having a definitional problem. Perhaps it is so
obvious to everyone else that nobody bothered mentioning it. I know I
wasn't aware until I tried a simple test and found that the Xmin wasn't
advancing as I was expecting.

The problem is that we always consider every transaction's PGPROC->xid
in calculating MyProc->xmin. So if you have a long running transaction,
it doesn't matter how far beyond the snapshots are -- the value returned
by GetOldestXmin will always be at most the old transaction's Xid. Even
if that transaction cannot see the old rows because all of its snapshots
are way in the future.

As far as I can see, for the purposes of VACUUM we can remove any tuple
that was deleted after the old transaction's Xid but before that
transaction's Xmin (i.e. all of its live snapshots). This means we get
to ignore Xid in GetOldestXmin and in the TransactionXmin calculations
in GetSnapshotData. It would not surprise me, however, to find out that
I am overlooking something and this is incorrect.

Am I blind?

It is quite possible that for the other purposes that we're using Xmins
for, this is not so. If that's the case, I would argue that we would
need to introduce a separate TransactionId to keep track of, which would
retain the current semantics of Xmin, and let VACUUM use what I am
proposing. I haven't examined those other uses though.

Thoughs?

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, 2008-03-28 at 10:35 -0300, Alvaro Herrera wrote:

> The problem is that we always consider every transaction's PGPROC->xid
> in calculating MyProc->xmin. So if you have a long running
> transaction, it doesn't matter how far beyond the snapshots are -- the
> value returned by GetOldestXmin will always be at most the old
> transaction's Xid. Even if that transaction cannot see the old rows
> because all of its snapshots are way in the future.

It may not have a TransactionId yet.

So we should have the capability to prevent long running read-only
transactions from causing a build up of dead row versions. But long
running write transactions would still be a problem.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Simon Riggs wrote:
> On Fri, 2008-03-28 at 10:35 -0300, Alvaro Herrera wrote:
>
> > The problem is that we always consider every transaction's PGPROC->xid
> > in calculating MyProc->xmin. So if you have a long running
> > transaction, it doesn't matter how far beyond the snapshots are -- the
> > value returned by GetOldestXmin will always be at most the old
> > transaction's Xid. Even if that transaction cannot see the old rows
> > because all of its snapshots are way in the future.
>
> It may not have a TransactionId yet.

How is this a problen? If it ever gets one, it will be in the future.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, 2008-03-28 at 11:26 -0300, Alvaro Herrera wrote:
> Simon Riggs wrote:
> > On Fri, 2008-03-28 at 10:35 -0300, Alvaro Herrera wrote:
> >
> > > The problem is that we always consider every transaction's PGPROC->xid
> > > in calculating MyProc->xmin. So if you have a long running
> > > transaction, it doesn't matter how far beyond the snapshots are -- the
> > > value returned by GetOldestXmin will always be at most the old
> > > transaction's Xid. Even if that transaction cannot see the old rows
> > > because all of its snapshots are way in the future.
> >
> > It may not have a TransactionId yet.
>
> How is this a problen? If it ever gets one, it will be in the future.

Yeh, that was my point. So the problem you mention mostly goes away.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Alvaro Herrera <alvherre@commandprompt.com> writes:
> As far as I can see, for the purposes of VACUUM we can remove any tuple
> that was deleted after the old transaction's Xid but before that
> transaction's Xmin (i.e. all of its live snapshots). This means we get
> to ignore Xid in GetOldestXmin and in the TransactionXmin calculations
> in GetSnapshotData. It would not surprise me, however, to find out that
> I am overlooking something and this is incorrect.

This seems entirely off-base to me. In particular, if a transaction
has an XID then its XMIN will never be greater than that, so I don't
even see how you figure the case will arise.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > As far as I can see, for the purposes of VACUUM we can remove any tuple
> > that was deleted after the old transaction's Xid but before that
> > transaction's Xmin (i.e. all of its live snapshots). This means we get
> > to ignore Xid in GetOldestXmin and in the TransactionXmin calculations
> > in GetSnapshotData. It would not surprise me, however, to find out that
> > I am overlooking something and this is incorrect.
>
> This seems entirely off-base to me. In particular, if a transaction
> has an XID then its XMIN will never be greater than that, so I don't
> even see how you figure the case will arise.

My point exactly -- can we let the Xmin go past its Xid? You imply we
can't, but why?

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Alvaro Herrera wrote:
> Tom Lane wrote:
>> Alvaro Herrera <alvherre@commandprompt.com> writes:
>>> As far as I can see, for the purposes of VACUUM we can remove any tuple
>>> that was deleted after the old transaction's Xid but before that
>>> transaction's Xmin (i.e. all of its live snapshots). This means we get
>>> to ignore Xid in GetOldestXmin and in the TransactionXmin calculations
>>> in GetSnapshotData. It would not surprise me, however, to find out that
>>> I am overlooking something and this is incorrect.
>> This seems entirely off-base to me. In particular, if a transaction
>> has an XID then its XMIN will never be greater than that, so I don't
>> even see how you figure the case will arise.
>
> My point exactly -- can we let the Xmin go past its Xid? You imply we
> can't, but why?

Everything < xmin is considered to be not running anymore. Other
transactions would consider the still-alive transaction as aborted, and
start setting hint bits etc.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers