Re: cursors outside transactions

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Why don't you like (1)? It seems fine to me, and I don't see how we are
> > magically going to do any better in the future.
>
> The restrictions of (1) seem pretty obvious to me ... but I don't
> see any prospect of doing better in the near future, either.
> Cross-transaction cursors are a *hard* problem for us.

We could do (2) by adding a new Proc field to show the oldest open
cursor transaction. Each backend could keep track of all its open
cursors, and update that field to show only the oldest one. VACUUM
could then use that to control vacuum of tuples. Of course, preventing
vacuum from removing those tuples isn't great, and we would have to keep
locks on those objects. Looking at those negatives, materialization
doesn't look that bad. It doesn't seem worth messing up our nice commit
semantics just to prevent materialization.

> The question here is do we want to offer a half-baked solution,
> recognizing that it's some improvement over no solution at all?
> Or do we feel it doesn't meet our standards?

My question is how would you do this if you need this functionality and
you don't have WITH HOLD cursors? My guess is you would dump the cursor
into a temp table with a serial column (materialize it), and do queries
to pull rows from the temp table based on that serial column.

So it seems we are implementing WITH HOLD cursors internally in the same
way it would be done by client code.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073