Re: Help to review the with X cursor option.

alex lock <alock303(at)gmail(dot)com> writes:
> The cursor means something like declare c cursor for select * from t;
> The holdable cursor means declare c cursor WITH HOLD for select * from t;

> Holdable cursor is good at transaction, user can still access it after the
> transaction is commit. But it is bad at it have to save all the record to
> tuple store before we fetch 1 row.

> what I want is:
> 1. The cursor is still be able to fetch after the transaction is
> committed.
> 2. the cursor will not fetch the data when fetch statement is issue (just
> like non-holdable cursor).

> I called this as with X cursor..

> I check the current implementation and think it would be possible with the
> following methods:
> 1. allocate the memory in a {LongerMemoryContext}, like EState to
> prevent they are
> 2. allocate a more bigger resource owner to prevent the LockReleaseAll
> during CommitTransaction.
> 3. add the "with X" option to cursor so that Precommit_portals will not
> drop it during CommitTransaction.

> Before I implement it, could you give some suggestions?

You don't actually understand the problem.

The reason a holdable cursor forcibly reads all the data before commit is
that the data might not be there to read any later than that. Once we end
the transaction and release its snapshot (specifically, advance the
backend's advertised global xmin), it's possible and indeed desirable for
obsoleted row versions to be vacuumed. The only way to avoid that would
be to not advance xmin, which is pretty much just as bad as not committing
the transaction. Not releasing the transaction's locks is also bad.
So it doesn't seem like there's anything to be gained here that you don't
have today by just not committing yet.

If you're concerned about not losing work due to possible errors later in
the transaction, you could prevent those from causing problems through
subtransactions (savepoints).

regards, tom lane