Help to review the with X cursor option.

alex lock <alock303@gmail.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

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

alex lock <alock303@gmail.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.

On Thu, Apr 25, 2019 at 9:53 AM alex lock <alock303@gmail.com> wrote:

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.

I check my original statement, I found "snapshot release" was missed, that
obviously is a key point..