Re: Help to review the with X cursor option.

On Wed, Apr 24, 2019 at 11:30 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

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

>
Thanks tones. I know that and that's just something I want to change.

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

I think this can be done with snapshot read, like we want the data at time
1, even the data is not there at time 2, we provide the snapshot, we can
read the data. Oracle has a similar function called flashback query
https://docs.oracle.com/cd/B14117_01/appdev.101/b10795/adfns_fl.htm#1008580
.

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

that's something I want to change, as I said at the beginning. include
avoid some memory release (like the EState and so on), snapshot release.

> The only way to avoid that would
> be to not advance xmin, which is pretty much just as bad as not committing
> the transaction.

there is something different between "not advance xmin" or "not committing
the transaction" for me. "not commit the transaction" will take up the
connection, but "not advance xmin" one not. without this reason,
non-holdable cursor is good for me.

> Not releasing the transaction's locks is also bad.

Assume that if the table was dropped among the fetches, we can just raise
error, we can releasing the lock? I am still not sure about this part,
but keep the lock is still acceptable for me since it will not take up the
connection already(my purpose). but releasing the lock can be better.

> So it doesn't seem like there's anything to be gained here that you don't
> have today by just not committing yet.
>

it is connection:) I want to run dml or other stuff on the current
connection.

>
> 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).
>
> Thanks for your tip, I have thought the possibility but I can think
more. the business model is a bit of complex and I don't want to talk more
here.

> regards, tom lane
>