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

Jan Wieck wrote:
>
> Bruce Momjian wrote:
> > Tom Lane wrote:
> > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > >> I forgot to mention that I'd like to implement a cross
> > > >> transaction insensitive(and read-only) cursors which
> > > >> any proper dbms seems to have the functionality.
> > > >
> > > > That is a good idea, especially read-only, that will not require any
> > > > locks.
> > >
> > > If it's not holding any locks, I can guarantee you it's not insensitive.
> > > Consider VACUUM, or even DROP TABLE.
> >
> > I assumed it would be an in-memory copy of the cursor, like a portal
> > that doesn't go away on transaction exit.
>
>     Ever realized what a portal is? So far it's a query for which
>     ExecutorStart() has been called, just sitting there,  waiting
>     for subsequent ExecutorRun() calls.
>
>     How  such a thing can live outside of any transaction context
>     isn't totally clear to me, even if I have to admit that I see
>     by  now  the  desire for cross transaction cursors. It's just
>     these lil' details like "how does the  portal  maintain  it's
>     snapshot  POV  after  the  transaction  creating  it  is long
>     gone?", that make me nervous.

Hiroshi, that's exactly what I need, though I am not sure if we are all
really talking about the same thing.

In case I misunderstood something: as far as I know, SQL92 defines that
a cursor is by default sensitive, which means that it displays the data
from all comitted transactions at any time. If the data changes, so does
what the cursor returns.

Bruce, as far as I understand, you really only need to hold an
AccessShareLock then, to keep the table structure from being modified.

In contrast, an insensitive cursor returns only those rows from the
query which were committed when the cursor was declared (or opened? I
don't remember). This requires at least a method to keep vacuum from
removing rows that still have to be returned, as Tom already said.


FYI, none of the other open source RDBMS implement insensitive cursors,
though it's probably the thing that would be the most useful in today's
interactive applications.

Firebird (ex Interbase): Implements FOR UPDATE and WHERE CURRENT OF for
UPDATE and DELETE, but no INSENSITIVE cursor.
http://www.ibphoenix.com/60sqlref.html#RSf40642

SAPDB (ex Adabas-D): Implements FOR UPDATE etc. etc., but is not clear
on wether the default cursor is sensitive or insensitive, as they are
talking about "named result tables" all the time and have a "FOR REUSE"
clause, which makes me wonder wether they always use a temporary table.
http://www.sapdb.org/htmhelp/e2/55683ab81fd846e10000000a11402f/frameset.htm
http://www.sapdb.org/htmhelp/40/13120f2fa511d3a98100a0c9449261/content.htm


The commercial databases all implement insensitive cursors of course,
though with different means.

Microsoft SQL Server: Implements read-only INSENSITIVE, by using a
temporary table. What did you expect anyway.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp

Sybase: Implements read-write (?) INSENSITIVE, by using a temporary
table. To quote, "INSENSITIVE cursors can be expensive if the cursor
defines a large result set." Oh really, and that's what I thought
cursors are to remedy.
http://manuals.sybase.com/onlinebooks/group-sas/awg0702e/dbugen7/@Generic__BookTextView/21130

IBM DB2: Implements read-write INSENSITIVE, but uses a temporary table
always, also for sensitive, as it provides a modified FETCH that can
fetch either sensitive or insensitive.
Sorry, no URL.

Oracle: Implements INSENSITIVE, though I don't know how.
Sorry, no URL either.


So, in conclusion, sensitive cross-transaction cursors are probably
easy, and everybody has them, but insensitive are not so, though it
should be possible without using a temporary table because of
PostgreSQL's storage management.