Re: persistent portals/cursors (between transactions)

> > 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.

Isn't that exactly what beginning a transaction and keeping it
uncommitted for a long time would do too?

I see the problem - your last sentence - but getting rid of that would
mean to not only save an oldest XMIN, but also a reference to all tables
that this not-quite-a-xact uses, kind of like a "selective transaction".
I doubt that there really are any problems in the real world though, so
having a naive implementation first would be fine too.

So from the vacuum perspective, it looks like more than just one
transaction is running per backend, right? Probably I don't understand
anything at all, or that's what I suggested way back in my second or
third mail. Whatever. Assuming I understood a bit here, a read-write
cross-transaction cursor shouldn't be too hard to implement then either.