Re: Help to review the with X cursor option.
| От | alex lock |
|---|---|
| Тема | Re: Help to review the with X cursor option. |
| Дата | |
| Msg-id | CALi0+Bp9GibkYCU6ThLprs2_8fYsUPCNjgiQOS75KmAV-5YLnQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Help to review the with X cursor option. (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: Help to review the with X cursor option.
(alex lock <alock303@gmail.com>)
|
| Список | pgsql-hackers |
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.
regards, tom lane
В списке pgsql-hackers по дате отправления: