Re: persistent portals/cursors (between transactions) - Mailing list pgsql-general

Jan Wieck wrote:
>
> Tom Lane wrote:
> > Florian Wunderlich <fwunderlich@devbrain.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.