Multiple Xids in PGPROC?

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

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

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

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

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

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

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.

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.

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

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

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)

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

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"

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

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

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

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

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)

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