Thread: Multiple Xids in PGPROC?
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)
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, Pennsylvania19073
> 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, Pennsylvania19073
> 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.
> 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
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.
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
> 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
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 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 runningbackend. - 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. ServusManfred
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, 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 = 100insert 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 = 150xmin = 90xip = { 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"
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, elseother backends * might see a partially-set xid here. But holding both locks at once * would be a nasty concurrencyhit (and in fact could cause a * deadlock against GetSnapshotData). So for now, assume atomicity. * Note thatreaders 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 PGPROCscouldn'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
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, Pennsylvania19073
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,
Pennsylvania19073
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, Pennsylvania19073
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 #