Re: persistent portals/cursors (between transactions)

Florian Wunderlich <fwunderlich(at)devbrain(dot)de> writes:
> When you're talking about in-transaction cursors for the above example,
> why would the cursor need anything more than the transaction A needs
> anyway?

It wouldn't.

> And for cross-transaction cursors, why lock the whole table when
> you could use the transaction information from the transaction in which
> the cursor was declared?

The problem is to keep the rows that are supposed to be still visible to
you from disappearing. If other backends think that transaction A is
history, they will not think that they need to preserve rows that would
have been visible to A, but are not visible to any still-running
transaction.

[ ... thinks for awhile ... ] Maybe we could extend the notion of
"oldest XMIN" a little. Perhaps what each backend should record in the
PROC array is not just the oldest XMIN visible to its current
transaction, but the oldest XMIN visible to either its current xact or
any of its open cross-transaction cursors. That together with an
AccessShareLock on tables referenced by the cursors might work.

A drawback of this approach is that opening a cursor and sitting on it
for a long time would effectively defeat VACUUM activity --- it wouldn't
be blocked, but it wouldn't be able to reclaim rows either. Anywhere,
not only in the tables actually used by the cursor.

regards, tom lane