Re: persistent portals/cursors (between transactions)

Jan Wieck wrote:
>
> Tom Lane wrote:
> > Florian Wunderlich <fwunderlich(at)devbrain(dot)de> writes:
> > > But there is no check in CreatePortal or SPI_cursor_open, as far as I've
> > > seen, but as SPI doesn't allow transaction control statements I don't
> > > know if SPI_connect probably begins a transaction implicitly.
> >
> > Any sort of SPI operation is implicitly within a transaction, since it
> > can (by assumption) only be called from a function, which is being
> > called within a query, which is explicitly or implicitly within a
> > transaction. So I think the lack of check there is okay.
>
> 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?

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

I'm sorry if I completely miss the point but it's been years since I
looked at the source of PostgreSQL.

> So please, no cross transaction cursors only because they
> might be handy for ODBC!

Not only ODBC, I don't use ODBC anyway, I just thought that the current
ODBC driver probably has to considerably work around that.

Consider the following scenario: You present the user with a set of
records, which he can scroll, and which can be modified interactively.
As the SELECT takes quite a while, you can't re-execute the query
(re-declare the cursor) every time a row is modified, but as this view
might be open for an arbitrary time, you don't want to execute all
UPDATEs in this transaction.

Currently, you would need one transaction with the cursor, and another
one if you update a record, and you would need a cache which holds the
records that were actually updated as you don't see them yet in the
transaction with the cursor.