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

Search Discussions

  • Tom Lane at Aug 7, 2001 at 6:01 pm

    Ian Lance Taylor 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
  • Hiroshi Inoue at Aug 8, 2001 at 12:10 am

    Tom Lane wrote:

    Ian Lance Taylor <ian@airs.com> 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
  • Ian Lance Taylor at Aug 8, 2001 at 12:46 am

    Hiroshi Inoue writes:

    Ian Lance Taylor <ian@airs.com> 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
  • Hiroshi Inoue at Aug 8, 2001 at 1:02 am

    Ian Lance Taylor wrote:

    Hiroshi Inoue <Inoue@tpf.co.jp> writes:
    Ian Lance Taylor <ian@airs.com> 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
  • Ian Lance Taylor at Aug 8, 2001 at 1:05 am

    Hiroshi Inoue writes:

    Ian Lance Taylor <ian@airs.com> 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
  • Hiroshi Inoue at Aug 8, 2001 at 1:09 am
    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
  • Tom Lane at Aug 8, 2001 at 4:30 pm

    Hiroshi Inoue writes:
    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.
    Yes, you could either declare an error (if serializable mode) or follow
    the TID links to find the latest version of the tuple, and update that
    (if read-committed mode). This is no different from the situation for
    any other UPDATE, AFAICS.
    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.
    Of course not. Concurrent VACUUM has to follow the same rules as
    old-style VACUUM: it must never remove or move any tuple that is still
    visible to any open transaction. (Actually, it never moves tuples at
    all, but the point is that it cannot remove any tuple that the open
    cursor could have seen.) So, the fact that SQL cursors don't survive
    across transactions is enough to guarantee that a TID returned by a
    cursor is good as long as the cursor is open.

    The reason you have a harder time with ODBC cursors is that you aren't
    restricting them to be good only within a transaction (or at least
    that's how I interpreted what you said earlier).

    regards, tom lane
  • Hiroshi Inoue at Aug 9, 2001 at 12:19 am

    Tom Lane wrote:

    Hiroshi Inoue <Inoue@tpf.co.jp> 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.
    Of course not. Concurrent VACUUM has to follow the same rules as
    old-style VACUUM: it must never remove or move any tuple that is still
    visible to any open transaction. (Actually, it never moves tuples at
    all, but the point is that it cannot remove any tuple that the open
    cursor could have seen.) So, the fact that SQL cursors don't survive
    across transactions is enough to guarantee that a TID returned by a
    cursor is good as long as the cursor is open.

    The reason you have a harder time with ODBC cursors is that you aren't
    restricting them to be good only within a transaction (or at least
    that's how I interpreted what you said earlier).
    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.

    The page is pinned by tx1, so the new VACUUM refuses
    to change the page ? I there could be another story.

    2)' Very old backend tx_old updated the row and deletes
    the updated row and commits.
    3)' The new VACUUM starts to run and find the updated
    row to be completely dead but the page may not be
    pinned.

    Both seems to be detected by FULL VACUUM as
    'NOTICE: Child itemid in update-chain marked as unused - can't
    continue repair_frag' though it may be too late.

    regards,
    Hiroshi Inoue
  • Tom Lane at Aug 9, 2001 at 1:23 am

    Hiroshi Inoue 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. This is true
    *whether or not the row has been fetched yet*, or ever will be fetched,
    by the cursor.

    If cursors had this risk then ordinary UPDATE would be equally broken.
    What is a cursor except an externally-accessible scan-in-progress?
    There is no difference.
    The page is pinned by tx1, so the new VACUUM refuses
    to change the page ? I there could be another story.
    The pin stuff doesn't have anything to do with whether TIDs remain
    valid. A pin guarantees that a *physical pointer* into a shared buffer
    will remain valid --- it protects against VACUUM reshuffling the page
    data to compact free space after it's deleted completely-dead tuples.
    But reshuffling doesn't invalidate non-dead TIDs. A TID remains valid
    until there are no open transactions that could possibly consider the
    tuple visible.
    Both seems to be detected by FULL VACUUM as
    'NOTICE: Child itemid in update-chain marked as unused - can't
    continue repair_frag' though it may be too late.
    AFAICS, that code cannot be executed unless someone has violated the
    update protocol (or the on-disk tuple status bits have gotten trashed
    somehow). We are never supposed to update a tuple that has been
    inserted or deleted by another, not-yet-committed transaction.
    Therefore the child tuple should have been inserted by a
    later-committing transaction. There is no way that VACUUM can see the
    child tuple as dead and the parent tuple as not dead.

    Or have I missed something?

    regards, tom lane
  • Hiroshi Inoue at Aug 9, 2001 at 2:02 am

    Tom Lane wrote:

    Hiroshi Inoue <Inoue@tpf.co.jp> 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
  • Tom Lane at Aug 9, 2001 at 2:09 am

    Hiroshi Inoue 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
  • Hiroshi Inoue at Aug 9, 2001 at 4:21 am

    Tom Lane wrote:

    Hiroshi Inoue <Inoue@tpf.co.jp> 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
  • Tom Lane at Aug 9, 2001 at 4:13 am

    Hiroshi Inoue 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
  • Hiroshi Inoue at Aug 9, 2001 at 4:14 am

    Tom Lane wrote:

    Hiroshi Inoue <Inoue@tpf.co.jp> 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
  • Andreas Zeugswetter at Aug 8, 2001 at 12:46 pm

    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
  • Ian Lance Taylor at Aug 8, 2001 at 3:36 pm

    "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> 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
  • Tom Lane at Aug 8, 2001 at 4:42 pm

    Ian Lance Taylor writes:
    Unfortunately, the portal code explicitly rejects FOR UPDATE
    (transformSelectStmt in parser/analyze.c).
    AFAIK, that error check is there specifically because we don't have
    UPDATE WHERE CURRENT. Try removing it and see what happens --- AFAIK,
    things might "just work".

    regards, tom lane
  • Hiroshi Inoue at Aug 8, 2001 at 11:52 pm

    Zeugswetter Andreas SB SD 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. If we switch to an overwriting
    storage manager, TID would be no longer transient and we need
    another item like xmin to detect the change of rows.
    I agree with you that detecting the change of rows is very
    critical and xmin may be needed in the future.

    regards,
    Hiroshi Inoue
  • Andreas Zeugswetter at Aug 23, 2001 at 7:56 am

    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
  • Tom Lane at Aug 23, 2001 at 3:02 pm

    "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> 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
  • Hiroshi Inoue at Aug 24, 2001 at 12:21 am

    Tom Lane wrote:

    "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> 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.
    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.
    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.

    regards,
    Hiroshi Inoue
  • Mikheev, Vadim at Aug 24, 2001 at 1:15 am

    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
  • Hiroshi Inoue at Aug 24, 2001 at 1:30 am

    "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
  • Mikheev, Vadim at Aug 24, 2001 at 5:07 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedAug 7, '01 at 3:59p
activeAug 24, '01 at 5:07p
posts25
users5
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase