Re: persistent portals/cursors (between transactions)

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.
>
> > I'm wondering now why portals have to be dropped at the end of a
> > transaction.
>
> Because the table-level locks guaranteeing the existence and schema
> stability of the referenced tables will go away when the transaction
> ends.  Against that, there's not much point in sweating the small stuff
> like whether we could drop and reacquire buffer pins ...

Of course, never thought of that. But why does the lock (AccessShareLock
from what I see) keep UPDATE (that acquires a RowExclusiveLock from what
I see) from running?

Where is the problem in simply holding this lock, if it's really just an
AccessShareLock, for the lifetime of the cursor?

I've seen that this topic (cursors outside transactions) is also an item
on the TODO list, so it's probably worth investing some time.

I'd really like to have persistent insensitive cursors, and it'd
probably make life a lot easier for the ODBC guys as I already said, and
probably the JDBC guys would switch too transactions too. I've looked
through all the documents on the developer website and read the slides
to your talk about transaction processing (a *real* timesaver, thanks),
which works more or less as I expected from the name, and I wonder if
you could implement an insensitive cursor simply by declaring it inside
a transaction, ending the transaction and then using the information
from this transaction for returning the necessary consistent set of data
as if you were still inside this transaction.

To see your own updates though you would need some kind of accept-list
in addition to the ignore-list that is there already for those
transactions you did later.

Would this approach actually work? Or do you think it should be done
differently?