Re: [HACKERS] Nested transactions and tuple header info
I said One such possible caller is EvalPlanQual. It could go to sleep using XactLockTableWait() on the SnapshotDirty's xmax. But the tuple has something strange in its xmax -- it's the tuple's cmin actually. Leaving this would be probably a bug. However, if the tuple is new, then EvalPlanQual won't even try to see it. Or maybe it will. And then, maybe there are other callers (I can only see _bt_check_unique). Right, this is a worse problem than I thought :-( I can't get past initdb because _bt_check_unique calls XactLockTableWait() with it's own Xid, which we refuse. Actually not it's own Xid, but the phantom Xid that was just assigned to some tuple, in the same transaction. Not sure how to fix this -- I'm not even sure what the exact problem is, because it's trying to insert the oid of a toast table in pg_class_oid_index during the first ALTER TABLE ... CREATE TOAST TABLE. Why would it see an old tuple with the same value, I don't know. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Always assume the user will do much worse than the stupidest thing you can imagine.(Julien PUYDT) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested transactions and tuple header info
Alvaro Herrera wrote: On Tue, Jun 01, 2004 at 06:40:07PM -0400, Bruce Momjian wrote: When DELETE a happens, we remove the xmin=1 from the tuple header and replace it with xmin=3. xid=3 will be marked as committed if xid2 aborts, and will be marked as aborted if xid3 commits. So, if xid2 aborts, the insert of xid1 should be honored, and xid3 is marked as committed, and the opposite if xid2 commits. Ok, I've been looking at implementing this. However it just occurred to me that a transaction, different from the one modifying the tuple, could try to see its xmax. First, I assume it isn't a problem to change the cmin because we have a lock on the tuple while we are modifying it. Since the xmin signals the tuple as being updated concurrently by another transaction (it's in progress), this can only happen if the other transaction tries to read it using SnapshotDirty. As far as someone trying to read the xmax when it isn't there, I assumed we had a tuple bit set indicating we have xmin/cmin/cmax, and that the outside transaction doesn't need to look up the xmax. However, for ourselves, we need to look that phantom xid up in our local memory and find the xmin/xmax for ourselves, right? One such possible caller is EvalPlanQual. It could go to sleep using XactLockTableWait() on the SnapshotDirty's xmax. But the tuple has something strange in its xmax -- it's the tuple's cmin actually. Leaving this would be probably a bug. Again, the bit is set, everyone has to look up the phantom xid in their own phantom xid list, or look in pg_subtrans to find out if they own that xid, and if so, then lookup the xmin/xmax in their local memory. As far as SnapshotDirty(), can you explain why that is used and if a tuple being created in an open transaction is subject to that? I assume it would be if we were updating a tuple and need to sleep on it. Can't we use pg_subtrans to find the main transaction xid and sleep on that? Once the main transaction is done, we then need to relook at the phantom xid to see if it was marked as committed/aborted because it might not match the main transactions status. Sorry I didn't reply earlier. I had to think on this one. -- 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 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] Nested transactions and tuple header info
Alvaro Herrera [EMAIL PROTECTED] writes: Not sure how to fix this -- I'm not even sure what the exact problem is, because it's trying to insert the oid of a toast table in pg_class_oid_index during the first ALTER TABLE ... CREATE TOAST TABLE. Why would it see an old tuple with the same value, I don't know. This is probably an UPDATE operation not an INSERT. There are quite a few paths through CREATE TABLE that involve repeated updates of the new pg_class row. I would think however that these changes would occur as successive commands of a single transaction, not as subtransactions, unless you've done something odd to CommandCounterIncrement. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested transactions and tuple header info
On Sun, Jun 13, 2004 at 01:22:05PM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Not sure how to fix this -- I'm not even sure what the exact problem is, because it's trying to insert the oid of a toast table in pg_class_oid_index during the first ALTER TABLE ... CREATE TOAST TABLE. Why would it see an old tuple with the same value, I don't know. This is probably an UPDATE operation not an INSERT. There are quite a few paths through CREATE TABLE that involve repeated updates of the new pg_class row. Huh, right. I would think however that these changes would occur as successive commands of a single transaction, not as subtransactions, unless you've done something odd to CommandCounterIncrement. Right, but I've taken the XMAX_IS_XMIN bit and replaced it with the phantom Ids idea. Probably this problem wouldn't have shown up if I hadn't done that, but we need to cope anyway. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) La principal característica humana es la tontería (Augusto Monterroso) ---(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] Nested transactions and tuple header info
On Tue, Jun 01, 2004 at 06:40:07PM -0400, Bruce Momjian wrote: When DELETE a happens, we remove the xmin=1 from the tuple header and replace it with xmin=3. xid=3 will be marked as committed if xid2 aborts, and will be marked as aborted if xid3 commits. So, if xid2 aborts, the insert of xid1 should be honored, and xid3 is marked as committed, and the opposite if xid2 commits. Ok, I've been looking at implementing this. However it just occurred to me that a transaction, different from the one modifying the tuple, could try to see its xmax. Since the xmin signals the tuple as being updated concurrently by another transaction (it's in progress), this can only happen if the other transaction tries to read it using SnapshotDirty. One such possible caller is EvalPlanQual. It could go to sleep using XactLockTableWait() on the SnapshotDirty's xmax. But the tuple has something strange in its xmax -- it's the tuple's cmin actually. Leaving this would be probably a bug. However, if the tuple is new, then EvalPlanQual won't even try to see it. Or maybe it will. And then, maybe there are other callers (I can only see _bt_check_unique). Do I have to worry about this? Maybe (probably) it's not a problem, but I want to be sure. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) El sentido de las cosas no viene de las cosas, sino de las inteligencias que las aplican a sus problemas diarios en busca del progreso. (Ernesto Hernández-Novich) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested transactions and tuple header info
On Wed, Jun 02, 2004 at 10:57:05AM -0400, Bruce Momjian wrote: Tom Lane wrote: No, I said own xid --- so the phantom xid part is still there. But your idea definitely does *not* work unless you use a single CID sequence for the whole main xact; and I'm still wondering if there's not a simpler implementation possible given that assumption. I don't understand why a single counter is needed for phantom xids. We keep the cmin/cmax on the tuple already, and our own backend can look up the xmin/xmax that goes with the phantom. Not sure either way (maybe you are right), but I use the global counter anyway because some tests would become a very ugly mess if I didn't. I think the phantom idea is also simpler with the global counter. And I see no reason to use local counter. We certainly are not hitting the limit with the global counter, as Tom pointed out recently in a thread about the aborted CID bitmaps. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Crear es tan difícil como ser libre (Elsa Triolet) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested transactions and tuple header info
Alvaro Herrera wrote: On Wed, Jun 02, 2004 at 10:57:05AM -0400, Bruce Momjian wrote: Tom Lane wrote: No, I said own xid --- so the phantom xid part is still there. But your idea definitely does *not* work unless you use a single CID sequence for the whole main xact; and I'm still wondering if there's not a simpler implementation possible given that assumption. I don't understand why a single counter is needed for phantom xids. We keep the cmin/cmax on the tuple already, and our own backend can look up the xmin/xmax that goes with the phantom. Not sure either way (maybe you are right), but I use the global counter anyway because some tests would become a very ugly mess if I didn't. I think the phantom idea is also simpler with the global counter. And I see no reason to use local counter. We certainly are not hitting the limit with the global counter, as Tom pointed out recently in a thread about the aborted CID bitmaps. Sure, use the global counter if it helps. My only point was that I didn't see how a global counter could reduce the number of times we have to look up the phantom xid to find the xmin/xmax. However, if you have a map showing which command counter goes with which subtransaction, then you _certainly_ could reduce the number of phantom lookups. (The command counter cmin/cmax tells you the xmin/xmax). In fact, in that case, the phatom xid's are only used on commit so you can make certain tuples visible/invisible to other transactions. To find the phantom xid idea, I had to wander around the house for a few hours thinking of what was in that tuple header and how this could be accomplished. I am sorry I led Alvaro initially into thinking he didn't need to keep the cmin. -- 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 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] Nested transactions and tuple header info
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: This is exactly the same argument as not being able to overwrite cmin. Basically the phantom xid's are a shorthand for saying the tuple was created by xid1 and deleted by xid2, both part of the same main transaction. A cursor looking at the rows has to recognize the xid is a phantom (via pg_subtrans) and look up the creation xid. You still don't see the point. Consider BEGIN; DECLARE CURSOR c1 FOR SELECT * FROM a ...; INSERT INTO a VALUES(...); -- call this row x DECLARE CURSOR c2 FOR SELECT * FROM a ...; BEGIN; DELETE FROM a WHERE ...;-- assume this deletes row x ROLLBACK; FETCH FROM c1; -- must NOT see row x FETCH FROM c2; -- must see row x AFAICS your proposal does not support this. The two cursors' snapshots will differ only in the recorded current-cid for the outer transaction. If the subtrans has overwritten xmin/cmin, there is no way to make that decision correctly. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested transactions and tuple header info
On Wed, Jun 02, 2004 at 09:52:28AM -0400, Tom Lane wrote: BEGIN; DECLARE CURSOR c1 FOR SELECT * FROM a ...; INSERT INTO a VALUES(...); -- call this row x DECLARE CURSOR c2 FOR SELECT * FROM a ...; BEGIN; DELETE FROM a WHERE ...;-- assume this deletes row x ROLLBACK; FETCH FROM c1; -- must NOT see row x FETCH FROM c2; -- must see row x AFAICS your proposal does not support this. The two cursors' snapshots will differ only in the recorded current-cid for the outer transaction. If the subtrans has overwritten xmin/cmin, there is no way to make that decision correctly. Why would it overwrite cmin? Only a new xmin is needed (and cmax and xmax, but the cursors don't care about those) -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) La fuerza no está en los medios físicos sino que reside en una voluntad indomable (Gandhi) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested transactions and tuple header info
Alvaro Herrera [EMAIL PROTECTED] writes: On Wed, Jun 02, 2004 at 09:52:28AM -0400, Tom Lane wrote: AFAICS your proposal does not support this. The two cursors' snapshots will differ only in the recorded current-cid for the outer transaction. If the subtrans has overwritten xmin/cmin, there is no way to make that decision correctly. Why would it overwrite cmin? Only a new xmin is needed (and cmax and xmax, but the cursors don't care about those) If you overwrite xmin and not cmin, you've created a nonsensical situation. How do you distinguish this tuple from tuples created by the subxact itself? More generally, cmin is only meaningful in combination with a particular transaction ID; you can't just arbitrarily replace xmin without changing cmin. I've been trying to think of ways to solve these problems by having a main xact and all its subxacts share a common CID sequence (ie, a subxact would have its own xid but would not start CID over at one). If you assume that, then Bruce's idea may indeed work, since you would never replace xmin in a way that would shift the interpretation of cmin into a different CID sequence. But I suspect there is a simpler way to solve it given that constraint. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested transactions and tuple header info
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: This is exactly the same argument as not being able to overwrite cmin. Basically the phantom xid's are a shorthand for saying the tuple was created by xid1 and deleted by xid2, both part of the same main transaction. A cursor looking at the rows has to recognize the xid is a phantom (via pg_subtrans) and look up the creation xid. You still don't see the point. Consider BEGIN; DECLARE CURSOR c1 FOR SELECT * FROM a ...; INSERT INTO a VALUES(...); -- call this row x DECLARE CURSOR c2 FOR SELECT * FROM a ...; BEGIN; DELETE FROM a WHERE ...;-- assume this deletes row x ROLLBACK; FETCH FROM c1; -- must NOT see row x FETCH FROM c2; -- must see row x AFAICS your proposal does not support this. The two cursors' snapshots will differ only in the recorded current-cid for the outer transaction. If the subtrans has overwritten xmin/cmin, there is no way to make that decision correctly. I do not overwrite cmin or cmax. If xid=1 creates a row: xmin=1 cmin=1 and xid=2 goes to expire it, we get: xmin=3 (phantom for xmin=1, xmax=2) cmin=1 cmax=1 and we set a phantom bit on the tuple. When we see it later and need to know the xmin or xmax, we look it up in local memory. (Maybe we don't even need a hash, just a List because I can't imagine more than a few of these phantoms being used.) Other backends see the tuple with a xmin as in progress so they don't need to look any further. Then, on commit, we decide if a tuple created by xid=1 and expired by xid=2 should appear created or not, and mark the phantom commit status accordingly. -- 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] Nested transactions and tuple header info
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: I've been trying to think of ways to solve these problems by having a main xact and all its subxacts share a common CID sequence (ie, a subxact would have its own xid but would not start CID over at one). If you assume that, then Bruce's idea may indeed work, since you would never replace xmin in a way that would shift the interpretation of cmin into a different CID sequence. But I suspect there is a simpler way to solve it given that constraint. I thought about using a global command counter. The problem there is that there is no way to control the visibility of tuples by other transactions on commit except going back end fixing up tuples, which is unacceptable. No, I said own xid --- so the phantom xid part is still there. But your idea definitely does *not* work unless you use a single CID sequence for the whole main xact; and I'm still wondering if there's not a simpler implementation possible given that assumption. 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] Nested transactions and tuple header info
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: I've been trying to think of ways to solve these problems by having a main xact and all its subxacts share a common CID sequence (ie, a subxact would have its own xid but would not start CID over at one). If you assume that, then Bruce's idea may indeed work, since you would never replace xmin in a way that would shift the interpretation of cmin into a different CID sequence. But I suspect there is a simpler way to solve it given that constraint. I thought about using a global command counter. The problem there is that there is no way to control the visibility of tuples by other transactions on commit except going back end fixing up tuples, which is unacceptable. No, I said own xid --- so the phantom xid part is still there. But your idea definitely does *not* work unless you use a single CID sequence for the whole main xact; and I'm still wondering if there's not a simpler implementation possible given that assumption. I don't understand why a single counter is needed for phantom xids. We keep the cmin/cmax on the tuple already, and our own backend can look up the xmin/xmax that goes with the phantom. -- 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 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] Nested transactions and tuple header info
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: On Wed, Jun 02, 2004 at 09:52:28AM -0400, Tom Lane wrote: AFAICS your proposal does not support this. The two cursors' snapshots will differ only in the recorded current-cid for the outer transaction. If the subtrans has overwritten xmin/cmin, there is no way to make that decision correctly. Why would it overwrite cmin? Only a new xmin is needed (and cmax and xmax, but the cursors don't care about those) If you overwrite xmin and not cmin, you've created a nonsensical situation. How do you distinguish this tuple from tuples created by the subxact itself? More generally, cmin is only meaningful in combination with a particular transaction ID; you can't just arbitrarily replace xmin without changing cmin. I've been trying to think of ways to solve these problems by having a main xact and all its subxacts share a common CID sequence (ie, a subxact would have its own xid but would not start CID over at one). If you assume that, then Bruce's idea may indeed work, since you would never replace xmin in a way that would shift the interpretation of cmin into a different CID sequence. But I suspect there is a simpler way to solve it given that constraint. I thought about using a global command counter. The problem there is that there is no way to control the visibility of tuples by other transactions on commit except going back end fixing up tuples, which is unacceptable. By creating phantoms, we can decide if an specific xmin/xmax pair should be appear as committed and set it accordingly on commit. -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Nested transactions and tuple header info
Bruce Momjian [EMAIL PROTECTED] writes: I don't understand why a single counter is needed for phantom xids. We keep the cmin/cmax on the tuple already, and our own backend can look up the xmin/xmax that goes with the phantom. Oh, so you're thinking of an internal table that provides a mapping back to the replaced xmin? Ugh. Perhaps it could be made to work, but it's a lot of mechanism, and it will slow down visibility checks (since AFAICS you'd have to check every subxid against the phantoms table). If we go with a global CID counter then we don't have to add that step. A global CID counter would also simplify other visibility tests. Alvaro hasn't said anything about how he's doing visibility checks across different subxacts of the same main xact, but without global CID there would need to be some pretty ugly checks to determine whether a subxact happened before or after the CID cutoff your outer xact is interested in. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested transactions and tuple header info
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I don't understand why a single counter is needed for phantom xids. We keep the cmin/cmax on the tuple already, and our own backend can look up the xmin/xmax that goes with the phantom. Oh, so you're thinking of an internal table that provides a mapping back to the replaced xmin? Ugh. Perhaps it could be made to work, but it's a lot of mechanism, and it will slow down visibility checks (since AFAICS you'd have to check every subxid against the phantoms table). My idea was to have a tuple bit indicating the xid is a phantom. If we go with a global CID counter then we don't have to add that step. Seems Alvaro is already using a global counter. A global CID counter would also simplify other visibility tests. Alvaro hasn't said anything about how he's doing visibility checks across different subxacts of the same main xact, but without global CID there would need to be some pretty ugly checks to determine whether a subxact happened before or after the CID cutoff your outer xact is interested in. If a global counter will reduce the number of phantom checks, then good. However, I assume Alvaro has to access the creation/expire xid to determine if the subtransaction committed, so I didn't think a global counter would help reduce the number of lookups. -- 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] Nested transactions and tuple header info
On Wed, Jun 02, 2004 at 11:12:31AM -0400, Tom Lane wrote: A global CID counter would also simplify other visibility tests. Alvaro hasn't said anything about how he's doing visibility checks across different subxacts of the same main xact, but without global CID there would need to be some pretty ugly checks to determine whether a subxact happened before or after the CID cutoff your outer xact is interested in. Yes, I'm using a global CID counter. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) La vida es para el que se aventura ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested transactions and tuple header info
On Wed, Jun 02, 2004 at 12:23:37PM -0400, Bruce Momjian wrote: Tom Lane wrote: If we go with a global CID counter then we don't have to add that step. Seems Alvaro is already using a global counter. I think I stated already that I'm in fact using it. Not sure why it didn't show up in the list. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) ¿Que diferencia tiene para los muertos, los huérfanos, y aquellos que han perdido su hogar, si la loca destrucción ha sido realizada bajo el nombre del totalitarismo o del santo nombre de la libertad y la democracia? (Gandhi) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested transactions and tuple header info
Alvaro Herrera wrote: On Wed, Jun 02, 2004 at 12:23:37PM -0400, Bruce Momjian wrote: Tom Lane wrote: If we go with a global CID counter then we don't have to add that step. Seems Alvaro is already using a global counter. I think I stated already that I'm in fact using it. Not sure why it didn't show up in the list. Yes, I saw it. I wrote seems when I should have wrote it as a fact. -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested transactions and tuple header info
On Tue, Jun 01, 2004 at 06:40:07PM -0400, Bruce Momjian wrote: So, we need a way to record the xmin and xmax while keeping cmin and cmax in the tuple header. My idea is for subtransactions to create additional xid's that represent the opposite of the commit state for changing tuples created by earlier subtransactions. Hmm, interesting idea. What seems more interesting is that the change seems to be confined to HeapTupleHeaderSetXmax. Every transaction and subtransaction will need two Xids (I think we can even optimize it so the abort xid is taken only as needed). I don't see anything immediately that would invalidate this idea. I'll marinate it while I write the trigger stuff, and wait for other comments. If nothing bad arises I'll try an implementation and report back. Thanks, -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Nunca confiaré en un traidor. Ni siquiera si el traidor lo he creado yo (Barón Vladimir Harkonnen) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested transactions and tuple header info
Bruce Momjian [EMAIL PROTECTED] writes: BEGIN; xid=1 INSERT a; BEGIN; xid=2 INSERT b; DELETE a; xid=3 COMMIT; COMMIT; When DELETE a happens, we remove the xmin=1 from the tuple header and replace it with xmin=3. You can't change xmin --- this would break visibility tests. Consider a cursor opened in the outer transaction after the INSERT a. It should be able to see the a row (note that this depends on recognizing xid equality and then comparing cid's within the outer transaction). If the subtransaction mangles xmin then it is no longer possible to make this test correctly. This is exactly the same argument as not being able to overwrite cmin. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested transactions and tuple header info
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: BEGIN; xid=1 INSERT a; BEGIN; xid=2 INSERT b; DELETE a; xid=3 COMMIT; COMMIT; When DELETE a happens, we remove the xmin=1 from the tuple header and replace it with xmin=3. You can't change xmin --- this would break visibility tests. Consider a cursor opened in the outer transaction after the INSERT a. It should be able to see the a row (note that this depends on recognizing xid equality and then comparing cid's within the outer transaction). If the subtransaction mangles xmin then it is no longer possible to make this test correctly. This is exactly the same argument as not being able to overwrite cmin. Basically the phantom xid's are a shorthand for saying the tuple was created by xid1 and deleted by xid2, both part of the same main transaction. A cursor looking at the rows has to recognize the xid is a phantom (via pg_subtrans) and look up the creation xid. Also, we will need a phantom xid for every xid1/xid2 pair. You can't just create one phantom xid per subtransaction because you must be able to control independently commit/rollback rows based on the status of the insert transaction. In this case: BEGIN; BEGIN; xid=1 INSERT a; BEGIN; xid=2 INSERT b; BEGIN; xid=3 DELETE a; xid=4 DELETE b; xid=5 COMMIT; COMMIT; COMMIT; COMMIT; xid4 and xid5 has to be adjusted based on that status of xid1 and xid2. -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested transactions and tuple header info
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: You can't change xmin --- this would break visibility tests. Basically the phantom xid's are a shorthand for saying the tuple was created by xid1 and deleted by xid2, both part of the same main transaction. That would be fine if the shorthand were readable, but it's not. A cursor looking at the rows has to recognize the xid is a phantom (via pg_subtrans) and look up the creation xid. And it will find that how? Imagine that the creating transaction is itself a subtransaction, and the deleting one is a few nesting levels further down. I don't see how the tuple is going to carry enough information to let you determine what's what, if the deleting subxact overwrites the creating one's XID. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested transactions and tuple header info
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: You can't change xmin --- this would break visibility tests. Basically the phantom xid's are a shorthand for saying the tuple was created by xid1 and deleted by xid2, both part of the same main transaction. That would be fine if the shorthand were readable, but it's not. A cursor looking at the rows has to recognize the xid is a phantom (via pg_subtrans) and look up the creation xid. And it will find that how? Imagine that the creating transaction is itself a subtransaction, and the deleting one is a few nesting levels further down. I don't see how the tuple is going to carry enough information to let you determine what's what, if the deleting subxact overwrites the creating one's XID. The backend who created _and_ expired the tuple has to do a lookup to find the creation or expire xid. We need two things. First, we need to control the visibility of the tuple once the entire transaction is done. This does that. Second, we need to be able to find the creation and expire xid, and that information is only required to be visible by the main transation and its subtransactions. On commit we can adjust these xid status to show the proper visibility. How do they do the lookup? Well, one idea would be to just create a local backend hash of these xids and their creation/expire xids. Another idea is that pg_subtrans already points to a parent xid. We could use the same method and point to both creation and expire xids. Why does this not work? -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Nested transactions and tuple header info
On Tue, Jun 01, 2004 at 11:17:40PM -0400, Bruce Momjian wrote: Basically the phantom xid's are a shorthand for saying the tuple was created by xid1 and deleted by xid2, both part of the same main transaction. Hmm... this would spread the ugliness elsewhere (hopefully only HeapTupleHeaderGetXmin). A cursor looking at the rows has to recognize the xid is a phantom (via pg_subtrans) and look up the creation xid. No need to look at pg_subtrans because we know all the Xids of our transaction tree. I think this can be kept in local memory. Also, we will need a phantom xid for every xid1/xid2 pair. You can't just create one phantom xid per subtransaction because you must be able to control independently commit/rollback rows based on the status of the insert transaction. Oh, sure. This could get huge pretty fast. We still need to think on the effects this could have on crash recovery though -- we'd have to write the phantom Xids to Xlog somehow (indicating which ones are committed and which are aborted). And we still don't know what effect it would have on CPU cost for every visibility check. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Los dioses no protegen a los insensatos. Éstos reciben protección de otros insensatos mejor dotados (Luis Wu, Mundo Anillo) ---(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] Nested transactions and tuple header info
Alvaro Herrera wrote: Also, we will need a phantom xid for every xid1/xid2 pair. You can't just create one phantom xid per subtransaction because you must be able to control independently commit/rollback rows based on the status of the insert transaction. Oh, sure. This could get huge pretty fast. We still need to think on the effects this could have on crash recovery though -- we'd have to write the phantom Xids to Xlog somehow (indicating which ones are committed and which are aborted). And we still don't know what effect it would have on CPU cost for every visibility check. As I understand, this overhead would only be needed for subtransactions. I also don't think there will be a lot of them because it is only for creation/expire in the same main transaction, and it is only needed for unique creation/expire combinations, which should be pretty small. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
