Re: persistent portals/cursors (between transactions)

Florian Wunderlich wrote:
> Jan Wieck wrote:
> > Since you cannot escalate from an implicit transaction to a
> > transaction block from inside a function, this was the only
> > way to enable cursors in PL/pgSQL without the requiremet to
> > call them inside of an explicit begin/commit block allways.
>
> I don't understand that.
> What do you mean by "this"? The omission of a check? But there's a
> transaction anyway as Tom said?

Yes, there is at least an implicit transaction allways. But
not necessarily an explicit transaction block (BEGIN/COMMIT).
Cursors used to be possible only inside of explicit
transaction blocks. That's "this".

> > But I don't like the idea of cross transaction cursors. The
> > locking issues, mentioned in the code by MAO, which are the
> > reason for rejecting FOR UPDATE on cursors, should be gone.
> > And the capability to select for update is a requirement for
> > updateable cursors, that I intend to work on for 7.3.
>
> But can't cursors not only be updateable inside transactions and
> read-only outside transactions, as a work-around, which would bring
> PostgreSQL at least a little closer to the standard?
>
> And in the long term, can't the lock that is acquired with FOR UPDATE be
> released when the cursor is closed and not when the transaction is
> finished?

The way it has to be is that you say

UPDATE ... WHERE CURRENT OF <cursor>

My idea is to hold the CTID, retrieved via a junk attribute,
of the last FETCH'ed row (of the table the locks are for)
inside of the cursor information, and basically rewrite the
WHERE CURRENT OF into a WHERE ctid = ... during parse.

As long as we cannot safely hold such locks across Xact
boundaries and guarantee that rows locked that way don't get
moved by vacuum, I'd vote for making cursors that are FOR
UPDATE inaccessable at Xact end.

Reminds me that Al Dev is right. The law's of physics apply
to software! Proof: vacuum sucks!

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com