RE: 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
"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
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
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
Import Notes
Resolved by subject fallback
"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
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
Import Notes
Resolved by subject fallback