Re: [HACKERS] Multiple Xids in PGPROC?
Bruce Momjian wrote: Tom Lane wrote: Manfred Koizar [EMAIL PROTECTED] 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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Multiple Xids in PGPROC?
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) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Multiple Xids in PGPROC?
Christopher Kings-Lynne [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Multiple Xids in PGPROC?
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
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] Multiple Xids in PGPROC?
Manfred Koizar [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Multiple Xids in PGPROC?
On Tue, 04 May 2004 23:21:07 -0400, Tom Lane [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Multiple Xids in PGPROC?
Alvaro Herrera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Multiple Xids in PGPROC?
On Tue, May 04, 2004 at 11:21:07PM -0400, Tom Lane wrote:
Alvaro Herrera [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Multiple Xids in PGPROC?
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Multiple Xids in PGPROC?
On Wed, May 05, 2004 at 02:18:16PM -0400, Tom Lane wrote: Manfred Koizar [EMAIL PROTECTED] 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) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Multiple Xids in PGPROC?
Alvaro Herrera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Multiple Xids in PGPROC?
Tom Lane wrote: Manfred Koizar [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Multiple Xids in PGPROC?
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Multiple Xids in PGPROC?
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] 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) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Multiple Xids in PGPROC?
Alvaro Herrera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Multiple Xids in PGPROC?
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Multiple Xids in PGPROC?
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Multiple Xids in PGPROC?
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Multiple Xids in PGPROC?
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Multiple Xids in PGPROC?
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Multiple Xids in PGPROC?
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. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
