Horton

Thread

  1. Multiple Xids in PGPROC?

    Alvaro Herrera <alvherre@dcc.uchile.cl> — 2004-05-05T02:32:09Z

    Hackers,

I've whacked the subtrans patch enough so that the simple tests (i.e.
non concurrent) for tuple visibility work.  I can create a table and
populate it in subtransactions, rollback or commit them selectively and
get the desired effect at the end.  Naturally, catalog entries also
behave [somewhat] sanely.  Oh, I made pg_subtrans work too.  (Though
whether it's relatively bug-free is yet to be proven.)

I'm now looking at changing the concurrent visibility rules, i.e.
utils/time/tqual.c.  It seems to me one of the most important parts is
making TransactionIdIsInProgress() behave, that is, yield true for every
committed and in-progress subtransaction of a current transaction tree.
(Not only the topmost Xid, which is what it currently does.)

So, the big question is, how do we do this?  The most obvious way (to
me) is to keep the whole array inside the PGPROC struct.  This would be
nice because it would only need little modification to
access/transam/varsup.c.  The main downside is that it potentially
requires a lot of shared memory.  Can we afford that?

I am already keeping the list of committed Xids in a backend-local list,
but of course this is not visible to other backends.

Does anyone have a better idea?  This is crucial.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Voy a acabar con todos los humanos / con los humanos yo acabaré
voy a acabar con todos / con todos los humanos acabaré (Bender)

  2. Re: Multiple Xids in PGPROC?

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-05-05T03:21:07Z

    Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> So, the big question is, how do we do this?  The most obvious way (to
> me) is to keep the whole array inside the PGPROC struct.
> ...
> The main downside is that it potentially
> requires a lot of shared memory.  Can we afford that?

No.  Shared memory is fixed size, therefore the above is guaranteed to
fail.

I thought we had devised a solution that did not require expansible
shared memory for this.  Bruce, Manfred, do you recall how that went?

			regards, tom lane

  3. Re: Multiple Xids in PGPROC?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2004-05-05T03:21:18Z

    Alvaro Herrera wrote:
> Hackers,
> 
> I've whacked the subtrans patch enough so that the simple tests (i.e.
> non concurrent) for tuple visibility work.  I can create a table and
> populate it in subtransactions, rollback or commit them selectively and
> get the desired effect at the end.  Naturally, catalog entries also
> behave [somewhat] sanely.  Oh, I made pg_subtrans work too.  (Though
> whether it's relatively bug-free is yet to be proven.)
> 
> I'm now looking at changing the concurrent visibility rules, i.e.
> utils/time/tqual.c.  It seems to me one of the most important parts is
> making TransactionIdIsInProgress() behave, that is, yield true for every
> committed and in-progress subtransaction of a current transaction tree.
> (Not only the topmost Xid, which is what it currently does.)
> 
> So, the big question is, how do we do this?  The most obvious way (to
> me) is to keep the whole array inside the PGPROC struct.  This would be
> nice because it would only need little modification to
> access/transam/varsup.c.  The main downside is that it potentially
> requires a lot of shared memory.  Can we afford that?
> 
> I am already keeping the list of committed Xids in a backend-local list,
> but of course this is not visible to other backends.

I remember going through this.  Other backends will use pg_subtrans to
know what transactions are in progress. They have to do the standard
lookups to find the status of the parent transaction.  The backend-local
list of xids is needed so the commit can clean up those subtransaction
xids so that later transactions don't have to use pg_subtrans.

Does this help?

Sorry I haven't gotten your patches in yet.  Tom is working on some
other back patches.  Also, do you have a plan to handle some of the more
complex issues like locking in subtransactions?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  4. Re: Multiple Xids in PGPROC?

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2004-05-05T04:00:12Z

    > I remember going through this.  Other backends will use pg_subtrans to
> know what transactions are in progress. They have to do the standard
> lookups to find the status of the parent transaction.  The backend-local
> list of xids is needed so the commit can clean up those subtransaction
> xids so that later transactions don't have to use pg_subtrans.

Is there some solution whereby the common case (99.999% of transactions 
won't be subtransactoins) is fast, and the uncommon case of being in a 
subtransaction is slower?

Chris

  5. Re: Multiple Xids in PGPROC?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2004-05-05T04:02:14Z

    Christopher Kings-Lynne wrote:
> > I remember going through this.  Other backends will use pg_subtrans to
> > know what transactions are in progress. They have to do the standard
> > lookups to find the status of the parent transaction.  The backend-local
> > list of xids is needed so the commit can clean up those subtransaction
> > xids so that later transactions don't have to use pg_subtrans.
> 
> Is there some solution whereby the common case (99.999% of transactions 
> won't be subtransactoins) is fast, and the uncommon case of being in a 
> subtransaction is slower?

Yes, we use an unreserved clog status to indicate a pg_subtrans lookup
is required.  In non-subtrans cases, no pg_subtrans lookup is required.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  6. Re: Multiple Xids in PGPROC?

    Rod Taylor <rbt@rbt.ca> — 2004-05-05T04:03:45Z

    > Is there some solution whereby the common case (99.999% of transactions 
> won't be subtransactoins) is fast, and the uncommon case of being in a 
> subtransaction is slower?

I hope not, because for many of us there will be as many (if not more)
subtransactions than standard transactions.

-- 
Rod Taylor <rbt [at] rbt [dot] ca>

Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/signature.asc

  7. Re: Multiple Xids in PGPROC?

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2004-05-05T04:22:28Z

    > I hope not, because for many of us there will be as many (if not more)
> subtransactions than standard transactions.

How can that possibly be true?  Every statement executed in postgres is 
a "transaction"  how many subtransactions are really needed and how can 
they be as common as normal transactions?

Chris

  8. Re: Multiple Xids in PGPROC?

    Rod Taylor <pg@rbt.ca> — 2004-05-05T04:30:23Z

    On Wed, 2004-05-05 at 00:22, Christopher Kings-Lynne wrote:
> > I hope not, because for many of us there will be as many (if not more)
> > subtransactions than standard transactions.
> 
> How can that possibly be true?  Every statement executed in postgres is 
> a "transaction"  how many subtransactions are really needed and how can 
> they be as common as normal transactions?

Yup.. And some of us intend on wrapping every single statement in a
subtransaction so we can rollback on an error without aborting the main
transaction.

In fact, I would be surprised if tools like psql went very long without
doing the same thing so users can recover from spelling mistakes.

  9. Re: Multiple Xids in PGPROC?

    Rod Taylor <pg@rbt.ca> — 2004-05-05T04:41:14Z

    On Wed, 2004-05-05 at 00:45, Christopher Kings-Lynne wrote:
> > Yup.. And some of us intend on wrapping every single statement in a
> > subtransaction so we can rollback on an error without aborting the main
> > transaction.
> 
> Point there being "main transaction".  What i'm saying is that the vast 
> majority of your "transactions" will be single statements.  eg. single 
> selects, single updates, etc.

Actually, they're not. A vast majority of my transactions are over 5
statements -- each of which is eagerly anticipating being wrapped in a
subtransaction.

> > In fact, I would be surprised if tools like psql went very long without
> > doing the same thing so users can recover from spelling mistakes.
> 
> If the user does an explicit BEGIN, then perhaps we might, but how often 
> does the user do an explicit BEGIN?

What user? Users aren't allowed in production. Strictly code.

  10. Re: Multiple Xids in PGPROC?

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2004-05-05T04:45:45Z

    > Yup.. And some of us intend on wrapping every single statement in a
> subtransaction so we can rollback on an error without aborting the main
> transaction.

Point there being "main transaction".  What i'm saying is that the vast 
majority of your "transactions" will be single statements.  eg. single 
selects, single updates, etc.

> In fact, I would be surprised if tools like psql went very long without
> doing the same thing so users can recover from spelling mistakes.

If the user does an explicit BEGIN, then perhaps we might, but how often 
does the user do an explicit BEGIN?

Chris

  11. Re: Multiple Xids in PGPROC?

    Greg Stark <gsstark@mit.edu> — 2004-05-05T04:49:10Z

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

> > I hope not, because for many of us there will be as many (if not more)
> > subtransactions than standard transactions.
> 
> How can that possibly be true?  Every statement executed in postgres is a
> "transaction"  how many subtransactions are really needed and how can they be
> as common as normal transactions?

Well consider that one thing discussed on this list previously was using
subtransactions to handle being able to continue after an error in a query.

Then any situation where autocommit was off would have every single query
being executed in a subtransaction within the main transaction. So a psql
script would likely be a single big transaction but every statement in it a
subtransaction. Or a web application could treat every page request as a
single atomic transaction but every individual query would automatically be a
subtransaction.

This would let a user C-c a large query and try a different way of writing it
without having to restart the whole sequence of commands in the transaction.
Or even simply correct a typo which is the big annoyance everyone's always
complaining about.

-- 
greg

  12. Re: Multiple Xids in PGPROC?

    Alvaro Herrera <alvherre@dcc.uchile.cl> — 2004-05-05T12:20:11Z

    On Tue, May 04, 2004 at 11:21:18PM -0400, Bruce Momjian wrote:
> Alvaro Herrera wrote:

> > I've whacked the subtrans patch enough so that the simple tests (i.e.
> > non concurrent) for tuple visibility work.  I can create a table and
> > populate it in subtransactions, rollback or commit them selectively and
> > get the desired effect at the end.  Naturally, catalog entries also
> > behave [somewhat] sanely.  Oh, I made pg_subtrans work too.  (Though
> > whether it's relatively bug-free is yet to be proven.)
> 
> I remember going through this.  Other backends will use pg_subtrans to
> know what transactions are in progress. They have to do the standard
> lookups to find the status of the parent transaction.  The backend-local
> list of xids is needed so the commit can clean up those subtransaction
> xids so that later transactions don't have to use pg_subtrans.

Ok, this can be done with what I have so far.  I'm not sure how slow
will it be compared to checking the PGPROC struct, because it may
involve getting a pg_subtrans page from disk.  Currently I have 8
pg_subtrans buffers on shared memory, the same as pg_clog; maybe we want
more to reduce that probability.  8 kB each, 2k xacts each, 16k xacts
total.

I'll test this and will probably be submitting a patch shortly.

> Sorry I haven't gotten your patches in yet.  Tom is working on some
> other back patches.

I've been sloppy lately with #ifdef, because it takes some time to get
right and testing it takes even more time.  I don't know if it's worth
it -- do you still have the idea of incremental, non disturbing patches?

> Also, do you have a plan to handle some of the more complex issues
> like locking in subtransactions?

Certainly.  As soon as I have a concurrent scenario working, I'll pursue
the cleanup of all modules at subxact abort.  (I have some working, some
which I know don't work, and some which I haven't tried yet.)

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Cada quien es cada cual y baja las escaleras como quiere" (JMSerrat)

  13. Re: Multiple Xids in PGPROC?

    Greg Stark <gsstark@mit.edu> — 2004-05-05T15:05:28Z

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

> > Yup.. And some of us intend on wrapping every single statement in a
> > subtransaction so we can rollback on an error without aborting the main
> > transaction.
> 
> Point there being "main transaction".  What i'm saying is that the vast
> majority of your "transactions" will be single statements.  eg. single selects,
> single updates, etc.

And if autocommit mode is off, which is how I would strongly urge people to
work, these single statements will be a subtransaction within a main
transaction.

> > In fact, I would be surprised if tools like psql went very long without
> > doing the same thing so users can recover from spelling mistakes.
> 
> If the user does an explicit BEGIN, then perhaps we might, but how often does
> the user do an explicit BEGIN?

Well currently very rare since it's so infuriating to have to start all over
when you make a spelling error. As long as autocommit mode is on the same
thing would happen.

But in Oracle autocommit mode is OFF in the command line tool. You have to
type commit to commit any changes. At first this is surprising and annoying,
but after a while you find it's terribly useful and a much safer way to work.
You can do an update, then double-check that you did the right thing before
committing it.

-- 
greg

  14. Re: Multiple Xids in PGPROC?

    Alvaro Herrera <alvherre@dcc.uchile.cl> — 2004-05-05T17:14:52Z

    On Tue, May 04, 2004 at 11:21:07PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> > So, the big question is, how do we do this?  The most obvious way (to
> > me) is to keep the whole array inside the PGPROC struct.
> > ...
> > The main downside is that it potentially
> > requires a lot of shared memory.  Can we afford that?
> 
> No.  Shared memory is fixed size, therefore the above is guaranteed to
> fail.
> 
> I thought we had devised a solution that did not require expansible
> shared memory for this.  Bruce, Manfred, do you recall how that went?

All right, here is how I think it should work.

Consider the following scenario:

create table foo (a int);
BEGIN;					-- Xid = 100
	insert into foo values (1);
	BEGIN;				-- Xid = 110
		insert into foo values (2);
	COMMIT;
	
	BEGIN;				-- Xid = 120
		update foo set a=1;
	COMMIT;
COMMIT;

A backend starts just after Xid=120 has sub-committed.  Its snapshot
will be:

snapshot = {
	xmax = 150
	xmin = 90
	xip = { 100, ... }
}

So everytime I see a tuple with Xmin/Xmax between 90 and 150 I have to
look it up in pg_subtrans up to the topmost transaction (which will have
pg_subtrans=0) and see if the result is in the xip list.

For example, the tuple with Xid=110 will have pg_subtrans=100; Xid=100
will have pg_subtrans=0, and xip contains 100, so the tuple has xmin in
progress.

(I'd like to avoid the pg_subtrans lookup in the non-subtransaction case,
but I don't see how to do that.)

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"God is real, unless declared as int"

  15. Re: Multiple Xids in PGPROC?

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-05-05T17:38:02Z

    Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> (I'd like to avoid the pg_subtrans lookup in the non-subtransaction case,
> but I don't see how to do that.)

Could we afford to make xids self-identifying?  For instance, odd
numbers are base xacts, even numbers are sub xacts.  This would in the
worst case cause us to cycle through the XID space twice as fast as we
need to, but I'm not convinced that's a big problem.

			regards, tom lane

  16. Re: Multiple Xids in PGPROC?

    Manfred Koizar <mkoi-pg@aon.at> — 2004-05-05T18:02:55Z

    On Tue, 04 May 2004 23:21:07 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>I thought we had devised a solution that did not require expansible
>shared memory for this.  Bruce, Manfred, do you recall how that went?

AFAIR we did not discuss TransactionIdIsInProgress() specifically.
Currently this function is special insofar as it does not consult
pg_clog but loops over the PGPROC array.  The current implementation is
in sinval.c.  The straightforward pg_clog lookup is still in transam.c,
but has been deactivated:
 * Now this func in shmem.c and gives quality answer by scanning
 * PGPROC structures of all running backend. - vadim 11/26/96

What was the motivation for this change?  Consistency or speed?

With subtransactions we'd have to fall back to checking pg_clog (and
pg_subtrans) in certain cases.  There are lots of possible
implementations.  Here are some ideas (just brainstorming):

 .  We could first scan the PGPROC array.  If the xid is an active main
transaction, we're finished.

 .  If xid is older than RecentGlobalXmin, it cannot be active.

 .  We could include a small number of subtransaction xids in PGPROC.

 .  For additional subtransactions not fitting into this small array
there could be minsubxid and maxsubxid fields in PGPROC.  If the xid we
are looking for is outside all these ranges, it cannot be an active
subtransaction.

 .  If all these tests fail, we fall back to checking pg_clog.

Servus
 Manfred

  17. Re: Multiple Xids in PGPROC?

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-05-05T18:18:16Z

    Manfred Koizar <mkoi-pg@aon.at> writes:
> The straightforward pg_clog lookup is still in transam.c,
> but has been deactivated:
>  * Now this func in shmem.c and gives quality answer by scanning
>  * PGPROC structures of all running backend. - vadim 11/26/96

> What was the motivation for this change?  Consistency or speed?

Getting the right answer --- the other way can't tell the difference
between an open transaction and a crashed one.

>  .  We could include a small number of subtransaction xids in PGPROC.

Yeah, I was just thinking that myself.  If we only need to show open
subtrans xids, then the number you'd need would depend on nesting depth
not the total number of subxacts used.  So half-a-dozen or so would
probably suffice for 99% of situations.  You'd need a flag that could be
set to show "I'm so deeply nested I can't fit all my subxacts here",
but you'd only need to go to pg_subtrans when that happened.

On the other hand, I'm not sure how much that helps, considering you
probably have to resolve the subtrans XID up to its parent anyway to
check commit/abort status.

			regards, tom lane

  18. Re: Multiple Xids in PGPROC?

    Simon Riggs <simon@2ndquadrant.com> — 2004-05-05T22:07:33Z

    On Wed, 2004-05-05 at 05:30, Rod Taylor wrote:

> Yup.. And some of us intend on wrapping every single statement in a
> subtransaction so we can rollback on an error without aborting the main
> transaction.
> 

That is exactly what is needed to achieve full Oracle & DB2
compatibility.

I suggest that this should be a session settable parameter, to allow
session transaction semantics to mimic particular DBMS. 

I want the behaviour but not the effort...

Best Regards, Simon Riggs

  19. Re: Multiple Xids in PGPROC?

    Alvaro Herrera <alvherre@dcc.uchile.cl> — 2004-05-05T22:08:15Z

    On Wed, May 05, 2004 at 02:18:16PM -0400, Tom Lane wrote:
> Manfred Koizar <mkoi-pg@aon.at> writes:

> >  .  We could include a small number of subtransaction xids in PGPROC.
> 
> Yeah, I was just thinking that myself.  If we only need to show open
> subtrans xids, then the number you'd need would depend on nesting depth
> not the total number of subxacts used.  So half-a-dozen or so would
> probably suffice for 99% of situations.  You'd need a flag that could be
> set to show "I'm so deeply nested I can't fit all my subxacts here",
> but you'd only need to go to pg_subtrans when that happened.

There is a comment in varsup.c, GetNewTransactionId():

	 * XXX by storing xid into MyProc without acquiring SInvalLock, we are
	 * relying on fetch/store of an xid to be atomic, else other backends
	 * might see a partially-set xid here.	But holding both locks at once
	 * would be a nasty concurrency hit (and in fact could cause a
	 * deadlock against GetSnapshotData).  So for now, assume atomicity.
	 * Note that readers of PGPROC xid field should be careful to fetch
	 * the value only once, rather than assume they can read it multiple
	 * times and get the same answer each time.
	 *
	 * A solution to the atomic-store problem would be to give each PGPROC
	 * its own spinlock used only for fetching/storing that PGPROC's xid.
	 * (SInvalLock would then mean primarily that PGPROCs couldn't be added/
	 * removed while holding the lock.)


I think if we want to do nontrivial manipulations in PGPROC we should
make sure it's properly locked.  Maybe it's a good time to implement the
locking suggested here?  With a LWLock instead of a spinlock, of course;
we would need MaxBackends extra LWLocks.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Tiene valor aquel que admite que es un cobarde" (Fernandel)

  20. Re: Multiple Xids in PGPROC?

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-05-06T00:32:12Z

    Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> I think if we want to do nontrivial manipulations in PGPROC we should
> make sure it's properly locked.  Maybe it's a good time to implement the
> locking suggested here?  With a LWLock instead of a spinlock, of course;
> we would need MaxBackends extra LWLocks.

Given the performance issues we're currently seeing with spinlocks
on SMP machines, I'm not sure I want to turn GetSnapshot from a
get-one-lock operation into a get-one-lock-per-backend operation :-(
The comment you were looking at was written on the assumption that
grabbing a spinlock is cheap, but it seems it isn't ...

			regards, tom lane

  21. Re: Multiple Xids in PGPROC?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2004-05-06T02:36:10Z

    Alvaro Herrera wrote:
> On Tue, May 04, 2004 at 11:21:18PM -0400, Bruce Momjian wrote:
> > Sorry I haven't gotten your patches in yet.  Tom is working on some
> > other back patches.
> 
> I've been sloppy lately with #ifdef, because it takes some time to get
> right and testing it takes even more time.  I don't know if it's worth
> it -- do you still have the idea of incremental, non disturbing patches?

No.  I think we should forget about the #ifdef's and just keep adding to
your patch.  I am not sure if we are going to apply it incrementally or
as one big patch, but We are going to review your existing posted patch
in a few days.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  22. Re: Multiple Xids in PGPROC?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2004-05-06T02:39:04Z

    Tom Lane wrote:
> Manfred Koizar <mkoi-pg@aon.at> writes:
> > The straightforward pg_clog lookup is still in transam.c,
> > but has been deactivated:
> >  * Now this func in shmem.c and gives quality answer by scanning
> >  * PGPROC structures of all running backend. - vadim 11/26/96
> 
> > What was the motivation for this change?  Consistency or speed?
> 
> Getting the right answer --- the other way can't tell the difference
> between an open transaction and a crashed one.
> 
> >  .  We could include a small number of subtransaction xids in PGPROC.
> 
> Yeah, I was just thinking that myself.  If we only need to show open
> subtrans xids, then the number you'd need would depend on nesting depth
> not the total number of subxacts used.  So half-a-dozen or so would
> probably suffice for 99% of situations.  You'd need a flag that could be
> set to show "I'm so deeply nested I can't fit all my subxacts here",
> but you'd only need to go to pg_subtrans when that happened.
> 
> On the other hand, I'm not sure how much that helps, considering you
> probably have to resolve the subtrans XID up to its parent anyway to
> check commit/abort status.

I am confused.  Don't we need to know about all subtransctions, not just
opened ones?

		BEGIN; -- xid=100
		BEGIN; -- xid=101
		COMMIT;

At this point, don't backends need to know the parent of xid 101,
meaning we can't limit visibility to just the transactions that are
currently openly nested?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  23. Re: Multiple Xids in PGPROC?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2004-05-06T02:49:45Z

    Manfred Koizar wrote:
> With subtransactions we'd have to fall back to checking pg_clog (and
> pg_subtrans) in certain cases.  There are lots of possible
> implementations.  Here are some ideas (just brainstorming):
> 
>  .  We could first scan the PGPROC array.  If the xid is an active main
> transaction, we're finished.
> 
>  .  If xid is older than RecentGlobalXmin, it cannot be active.
> 
>  .  We could include a small number of subtransaction xids in PGPROC.
> 
>  .  For additional subtransactions not fitting into this small array
> there could be minsubxid and maxsubxid fields in PGPROC.  If the xid we
> are looking for is outside all these ranges, it cannot be an active
> subtransaction.
> 
>  .  If all these tests fail, we fall back to checking pg_clog.

Could we add a boolean to each PROC structure indicating if that xid has
subtransactions?  If none have subtransactions, we don't need to look in
pg_clog.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  24. Re: Multiple Xids in PGPROC?

    Jan Wieck <janwieck@yahoo.com> — 2004-05-17T14:58:31Z

    Bruce Momjian wrote:

> Tom Lane wrote:
>> Manfred Koizar <mkoi-pg@aon.at> writes:
>> > The straightforward pg_clog lookup is still in transam.c,
>> > but has been deactivated:
>> >  * Now this func in shmem.c and gives quality answer by scanning
>> >  * PGPROC structures of all running backend. - vadim 11/26/96
>> 
>> > What was the motivation for this change?  Consistency or speed?
>> 
>> Getting the right answer --- the other way can't tell the difference
>> between an open transaction and a crashed one.
>> 
>> >  .  We could include a small number of subtransaction xids in PGPROC.
>> 
>> Yeah, I was just thinking that myself.  If we only need to show open
>> subtrans xids, then the number you'd need would depend on nesting depth
>> not the total number of subxacts used.  So half-a-dozen or so would
>> probably suffice for 99% of situations.  You'd need a flag that could be
>> set to show "I'm so deeply nested I can't fit all my subxacts here",
>> but you'd only need to go to pg_subtrans when that happened.
>> 
>> On the other hand, I'm not sure how much that helps, considering you
>> probably have to resolve the subtrans XID up to its parent anyway to
>> check commit/abort status.
> 
> I am confused.  Don't we need to know about all subtransctions, not just
> opened ones?
> 
> 		BEGIN; -- xid=100
> 		BEGIN; -- xid=101
> 		COMMIT;
> 
> At this point, don't backends need to know the parent of xid 101,
> meaning we can't limit visibility to just the transactions that are
> currently openly nested?

That's right. You can only forget about nested rolled back ones, because 
no matter what, their changes will never become valid. But every 
committed subtransaction is from the outside still in progress until the 
outmost transaction commits/rolls back.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #