Re: [HACKERS] CURRENT OF cursor without OIDs
Oops I'm referring to client side cursors in our ODBC driver. We have no cross-transaction cursors yet though I'd like to see a backend cross-transaction cursor also. Ops, sorry. BTW, what are "visibility" rules for ODBC cross-tx cursor? No Repeatable reads, no Serializability? Do you hold some locks over table while cursor opened (I noticed session locking in lmgr recently)? Could ODBC cross-tx cursors be implemented using server cross-tx cursors? I think we'll be able to restore old tid along with other tuple data from rollback segments, so I don't see any problem from osmgr... How do we detect the change of tuples from clients ? What version of tuple client must see? New one? TIDs are invariant under osmgr. xmin is about to be unreliable for the purpose. Seems I have to learn more about ODBC cross-tx cursors -:( Anyway, *MSQL*, Oracle, Informix - all have osmgr. Do they have cross-tx cursors in their ODBC drivers? Vadim ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] CURRENT OF cursor without OIDs
Zeugswetter Andreas SB SD [EMAIL PROTECTED] writes: Hiroshi wrote: In addtion, xmin wouldn't be so reliable in the near future because it would be updated to FrozenXID (=2) by vacuum. I thought concurrent vacuum with an open cursor is not at all possible. If it were, it would not be allowed to change ctid (location of row) and could be made to not change xmin. New-style vacuum can certainly run concurrently with an open cursor (wouldn't be of much use if it couldn't). However, new-style vacuum never changes ctid, period. It could change the xmin of a tuple though, under my not-yet-implemented proposal for freezing tuples. AFAICS, if you are holding an open SQL cursor, it is sufficient to check that ctid hasn't changed to know that you have the same, un-updated tuple. Under MVCC rules, VACUUM will be unable to delete any tuple that is visible to your open transaction, and so new-style VACUUM cannot recycle the ctid. Old-style VACUUM might move the tuple and make the ctid available for reuse, but your open cursor will prevent old-style VACUUM from running on that table. So, there's no need to look at xmin. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [HACKERS] CURRENT OF cursor without OIDs
Hiroshi wrote: There could be DELETE operations for the tuple from other backends also and the TID may disappear. Because FULL VACUUM couldn't run while the cursor is open, it could neither move nor remove the tuple but I'm not sure if the new VACUUM could remove the deleted tuple and other backends could re-use the space under such a situation. If you also save the tuple transaction info (xmin ?) during the select in addition to xtid, you could see whether the tupleslot was reused ? I think TID itself is available for the purpose as long as PostgreSQL uses no overwrite storage manager. If the tuple for a saved TID isn't found, the tuple may be update/deleted. If the tuple is found but the OID is different from the saved one, the space may be re-used. space *was* reused (not "may be") But I meant in lack of an OID (per not mandatory oid), that xmin might be a valid replacement for detecting, no ? Does *current (ctid, xmin) == saved (ctid, xmin)* mean that they are same ? Yes? but better ask Vadim ? Wraparound issue would be solved by FrozenXID and frequent vacuum. In addtion, xmin wouldn't be so reliable in the near future because it would be updated to FrozenXID (=2) by vacuum. I thought concurrent vacuum with an open cursor is not at all possible. If it were, it would not be allowed to change ctid (location of row) and could be made to not change xmin. If we switch to an overwriting smgr we have no item to detect the change of tuples. It may be one of the critical reasons why we shouldn't switch to an overwriting smgr:-). If we still want MVCC, we would still need something like xmin for overwrite smgr (to mark visibility). Andreas ---(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] CURRENT OF cursor without OIDs
AFAICS, if you are holding an open SQL cursor, it is sufficient to check that ctid hasn't changed to know that you have the same, un-updated tuple. Under MVCC rules, VACUUM will be unable to delete any tuple that is visible to your open transaction, and so new-style VACUUM cannot recycle the ctid. ... As Tom mentiond once in this thread, I've referred to non-SQL cursors which could go across transaction boundaries. TIDs aren't that reliable across transactions. We could avoid reassignment of MyProc-xmin having cursors opened across tx boundaries and so new-style vacuum wouldn't remove old tuple versions... OIDs and xmin have already lost a part of its nature. Probably I have to guard myself beforehand and so would have to mention repeatedly from now on that if we switch to an overwriting smgr, there's no system item to detect the change of tuples. So, is tid ok to use for your purposes? I think we'll be able to restore old tid along with other tuple data from rollback segments, so I don't see any problem from osmgr... Vadim ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] CURRENT OF cursor without OIDs
"Mikheev, Vadim" wrote: AFAICS, if you are holding an open SQL cursor, it is sufficient to check that ctid hasn't changed to know that you have the same, un-updated tuple. Under MVCC rules, VACUUM will be unable to delete any tuple that is visible to your open transaction, and so new-style VACUUM cannot recycle the ctid. ... As Tom mentiond once in this thread, I've referred to non-SQL cursors which could go across transaction boundaries. TIDs aren't that reliable across transactions. We could avoid reassignment of MyProc-xmin having cursors opened across tx boundaries and so new-style vacuum wouldn't remove old tuple versions... Oops I'm referring to client side cursors in our ODBC driver. We have no cross-transaction cursors yet though I'd like to see a backend cross-transaction cursor also. OIDs and xmin have already lost a part of its nature. Probably I have to guard myself beforehand and so would have to mention repeatedly from now on that if we switch to an overwriting smgr, there's no system item to detect the change of tuples. So, is tid ok to use for your purposes? No. I need an OID-like column which is independent from the physical position of tuples other than TID. I think we'll be able to restore old tid along with other tuple data from rollback segments, so I don't see any problem from osmgr... How do we detect the change of tuples from clients ? TIDs are invariant under osmgr. xmin is about to be unreliable for the purpose. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] CURRENT OF cursor without OIDs
Zeugswetter Andreas SB SD [EMAIL PROTECTED] writes: There could be DELETE operations for the tuple from other backends also and the TID may disappear. Because FULL VACUUM couldn't run while the cursor is open, it could neither move nor remove the tuple but I'm not sure if the new VACUUM could remove the deleted tuple and other backends could re-use the space under such a situation. If you also save the tuple transaction info (xmin ?) during the select in addition to xtid, you could see whether the tupleslot was reused ? (This might need a function interface to make it reasonably portable to future versions) Of course the only thing you can do if you notice it has changed is bail out. But that leaves the question to me on what should actually be done when the tuple has changed underneath. I for one would not like the update to succeed if someone else modified it inbetween my fetch and my update. If PL/pgSQL doesn't lock the table before doing the select, then I think it has to mark the tuples for update when it does the select. Unfortunately, the portal code explicitly rejects FOR UPDATE (transformSelectStmt in parser/analyze.c). Ian ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] CURRENT OF cursor without OIDs
Tom Lane wrote: Hiroshi Inoue [EMAIL PROTECTED] writes: Yes mainly but I want the verification by OID even in *inside a transaction* cases. For example, 1) A backend tx1 fetch a row using cursor. 2) Very old backend tx_old deletes the row and commits. 3) The new VACUUM starts to run and find the row to be completely dead. This cannot happen. If VACUUM thought that, VACUUM would be completely broken. Although the row is committed dead, it is still visible to the transaction using the cursor, so it must not be deleted. Yes it should be but it could happen. GetXmaxRecent() ignores the backend tx_old because it had been committed when VACUUM started and may return the xid the very old xid of tx_old. As far as I see, the current VACUUM considers the row completely dead. This is true *whether or not the row has been fetched yet*, or ever will be fetched, by the cursor. I must apologize for leaving the bug unsolved. Unfortunately VACUUM and MVCC are ill-suited. For example, complicated update chain handling wasn't needed before MVCC. regards, Hiroshi Inoue ---(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] CURRENT OF cursor without OIDs
Hiroshi Inoue [EMAIL PROTECTED] writes: GetXmaxRecent() ignores the backend tx_old because it had been committed when VACUUM started and may return the xid the very old xid of tx_old. Absolutely not; things would never work if that were true. GetXmaxRecent() returns the oldest TID that was running *when any current transaction started*, not just VACUUM's transaction. Thus, no transaction that could be considered live by the cursor-holding transaction will be considered dead by VACUUM. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] CURRENT OF cursor without OIDs
Hiroshi Inoue [EMAIL PROTECTED] writes: Hmm is there any place setting proc-xmin other than the following ? [in storage/ipc/sinval.c] if (serializable) MyProc-xmin = snapshot-xmin; AFAICT that's the only place that sets it. It's cleared to zero during transaction commit or abort in xact.c. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] CURRENT OF cursor without OIDs
Tom Lane wrote: Hiroshi Inoue [EMAIL PROTECTED] writes: Hmm is there any place setting proc-xmin other than the following ? [in storage/ipc/sinval.c] if (serializable) MyProc-xmin = snapshot-xmin; AFAICT that's the only place that sets it. It's cleared to zero during transaction commit or abort in xact.c. You are right. Now I understand I've completely misunderstood 'NOTICE: Child itemid in update-chain marked as unused - can't continue repair_frag'. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] CURRENT OF cursor without OIDs
Tom Lane wrote: Hiroshi Inoue [EMAIL PROTECTED] writes: GetXmaxRecent() ignores the backend tx_old because it had been committed when VACUUM started and may return the xid the very old xid of tx_old. Absolutely not; things would never work if that were true. GetXmaxRecent() returns the oldest TID that was running *when any current transaction started*, not just VACUUM's transaction. Thus, no transaction that could be considered live by the cursor-holding transaction will be considered dead by VACUUM. Oops I've misunderstood GetXmaxRecent() until now. Now I'm checking the current source. Hmm is there any place setting proc-xmin other than the following ? [in storage/ipc/sinval.c] if (serializable) MyProc-xmin = snapshot-xmin; regards, Hiroshi Inoue ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [HACKERS] CURRENT OF cursor without OIDs
There could be DELETE operations for the tuple from other backends also and the TID may disappear. Because FULL VACUUM couldn't run while the cursor is open, it could neither move nor remove the tuple but I'm not sure if the new VACUUM could remove the deleted tuple and other backends could re-use the space under such a situation. If you also save the tuple transaction info (xmin ?) during the select in addition to xtid, you could see whether the tupleslot was reused ? (This might need a function interface to make it reasonably portable to future versions) Of course the only thing you can do if you notice it has changed is bail out. But that leaves the question to me on what should actually be done when the tuple has changed underneath. I for one would not like the update to succeed if someone else modified it inbetween my fetch and my update. Andreas ---(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
[HACKERS] CURRENT OF cursor without OIDs
Oracle PL/SQL supports a very convenient feature in which you can say something like DECLARE CURSUR cur IS SELECT * FROM RECORD; BEGIN OPEN cur; UPDATE record SET field = value WHERE CURRENT OF cur; CLOSE cur; END We have cursors in the development version of PL/pgSQL, but they don't support CURRENT OF. In the patch I wrote a few months back to add cursor support to PL/pgSQL, which was not adopted, I included support for CURRENT OF. I did it by using OIDs. Within PL/pgSQL, I modified the cursor select statement to also select the OID. Then I change WHERE CURRENT OF cur to oid = oidvalue. Of course this only works in limited situations, and in particular doesn't work after OID wraparound. Anyhow, I see that there is a move afoot to eliminate mandatory OIDs. My question now is: if there is no OID, is there any comparable way to implement CURRENT OF cursor? Basically what is needed is some way to identify a particular row between a SELECT and an UPDATE. Ian ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] CURRENT OF cursor without OIDs
Ian Lance Taylor [EMAIL PROTECTED] writes: Anyhow, I see that there is a move afoot to eliminate mandatory OIDs. My question now is: if there is no OID, is there any comparable way to implement CURRENT OF cursor? Basically what is needed is some way to identify a particular row between a SELECT and an UPDATE. I'd look at using TID. Seems like that is more efficient anyway (no index needed). Hiroshi has opined that TID is not sufficient for ODBC cursors, but it seems to me that it is sufficient for SQL cursors. regards, tom lane ---(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] CURRENT OF cursor without OIDs
Tom Lane wrote: Ian Lance Taylor [EMAIL PROTECTED] writes: Anyhow, I see that there is a move afoot to eliminate mandatory OIDs. My question now is: if there is no OID, is there any comparable way to implement CURRENT OF cursor? Basically what is needed is some way to identify a particular row between a SELECT and an UPDATE. I'd look at using TID. Seems like that is more efficient anyway (no index needed). Hiroshi has opined that TID is not sufficient for ODBC cursors, but it seems to me that it is sufficient for SQL cursors. Yes TID is available and I introduced Tid Scan in order to support this kind of implementation. However there are some notices. 1) Is *FOR UPDATE* cursor allowed in PL/pgSQL ? (It doesn't seem easy for me). 2) If no, there could be UPDATE operations for the current tuple from other backends between a SELECT and an UPDATE and the TID may be changed. In that case, you couldn't find the tuple using saved TID but you could use the functions to follow the UPDATE link which I provided when I I introduced Tis Scan. There could be DELETE operations for the tuple from other backends also and the TID may disappear. Because FULL VACUUM couldn't run while the cursor is open, it could neither move nor remove the tuple but I'm not sure if the new VACUUM could remove the deleted tuple and other backends could re-use the space under such a situation. If it's possible, there must be another information like OID to iden- tify tuples. Anyway optional OIDs aren't preferable IMHO. regards, Hiroshi Inoue ---(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] CURRENT OF cursor without OIDs
Hiroshi Inoue [EMAIL PROTECTED] writes: Ian Lance Taylor [EMAIL PROTECTED] writes: Anyhow, I see that there is a move afoot to eliminate mandatory OIDs. My question now is: if there is no OID, is there any comparable way to implement CURRENT OF cursor? Basically what is needed is some way to identify a particular row between a SELECT and an UPDATE. I'd look at using TID. Seems like that is more efficient anyway (no index needed). Hiroshi has opined that TID is not sufficient for ODBC cursors, but it seems to me that it is sufficient for SQL cursors. Yes TID is available and I introduced Tid Scan in order to support this kind of implementation. However there are some notices. 1) Is *FOR UPDATE* cursor allowed in PL/pgSQL ? (It doesn't seem easy for me). No, it is not supported right now. Conceptually, however, PL/pgSQL could pull out the FOR UPDATE clause and turn it into an explicit LOCK statement. The TID hack will only work for a cursor which selects from a single table, so this is the only case for which turning FOR UPDATE into LOCK has to work. Admittedly, this is not the same as SELECT FOR UPDATE, because I think PL/pgSQL would have to lock the table in ROW EXCLUSIVE mode. But I think it would work, albeit not with maximal efficiency. Ian ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] CURRENT OF cursor without OIDs
Ian Lance Taylor wrote: Hiroshi Inoue [EMAIL PROTECTED] writes: Ian Lance Taylor [EMAIL PROTECTED] writes: Anyhow, I see that there is a move afoot to eliminate mandatory OIDs. My question now is: if there is no OID, is there any comparable way to implement CURRENT OF cursor? Basically what is needed is some way to identify a particular row between a SELECT and an UPDATE. I'd look at using TID. Seems like that is more efficient anyway (no index needed). Hiroshi has opined that TID is not sufficient for ODBC cursors, but it seems to me that it is sufficient for SQL cursors. Yes TID is available and I introduced Tid Scan in order to support this kind of implementation. However there are some notices. 1) Is *FOR UPDATE* cursor allowed in PL/pgSQL ? (It doesn't seem easy for me). No, it is not supported right now. Conceptually, however, PL/pgSQL could pull out the FOR UPDATE clause and turn it into an explicit LOCK statement. It's impossible to realize *FOR UPDATE* using LOCK statement. Each row must be locked individually to prevent UPDATE/DELETE operations for the row. You could acquire an EXCLUSIVE LOCK on the table but it doesn't seem preferable. I'm planning to implement updatable cursors with no lock using TID and OID. TID is for the fast access and OID is to verify the identity. OID doesn't provide a specific access method in the first place and the access would be veeery slow for large tables unless there's an index on OID. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] CURRENT OF cursor without OIDs
Hiroshi Inoue [EMAIL PROTECTED] writes: Ian Lance Taylor [EMAIL PROTECTED] writes: Anyhow, I see that there is a move afoot to eliminate mandatory OIDs. My question now is: if there is no OID, is there any comparable way to implement CURRENT OF cursor? Basically what is needed is some way to identify a particular row between a SELECT and an UPDATE. I'd look at using TID. Seems like that is more efficient anyway (no index needed). Hiroshi has opined that TID is not sufficient for ODBC cursors, but it seems to me that it is sufficient for SQL cursors. Yes TID is available and I introduced Tid Scan in order to support this kind of implementation. However there are some notices. 1) Is *FOR UPDATE* cursor allowed in PL/pgSQL ? (It doesn't seem easy for me). No, it is not supported right now. Conceptually, however, PL/pgSQL could pull out the FOR UPDATE clause and turn it into an explicit LOCK statement. It's impossible to realize *FOR UPDATE* using LOCK statement. Each row must be locked individually to prevent UPDATE/DELETE operations for the row. You could acquire an EXCLUSIVE LOCK on the table but it doesn't seem preferable. It's definitely not preferable, but how else can it be done? I'm planning to implement updatable cursors with no lock using TID and OID. TID is for the fast access and OID is to verify the identity. OID doesn't provide a specific access method in the first place and the access would be veeery slow for large tables unless there's an index on OID. I apologize if I've missed something, but how will that work when OIDs become optional? Ian ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] CURRENT OF cursor without OIDs
Ian Lance Taylor wrote: [snip] Yes TID is available and I introduced Tid Scan in order to support this kind of implementation. However there are some notices. 1) Is *FOR UPDATE* cursor allowed in PL/pgSQL ? (It doesn't seem easy for me). No, it is not supported right now. Conceptually, however, PL/pgSQL could pull out the FOR UPDATE clause and turn it into an explicit LOCK statement. It's impossible to realize *FOR UPDATE* using LOCK statement. Each row must be locked individually to prevent UPDATE/DELETE operations for the row. You could acquire an EXCLUSIVE LOCK on the table but it doesn't seem preferable. It's definitely not preferable, but how else can it be done? I'm planning to implement updatable cursors with no lock using TID and OID. TID is for the fast access and OID is to verify the identity. OID doesn't provide a specific access method in the first place and the access would be veeery slow for large tables unless there's an index on OID. I apologize if I've missed something, but how will that work when OIDs become optional? So I've objected optional OIDs. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
