Re: persistent portals/cursors (between transactions)

Tom Lane wrote:
>
> Florian Wunderlich <fwunderlich(at)devbrain(dot)de> writes:
> > 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?
>
> It shouldn't do that. Can you provide an example?

With a trivial example, the server doesn't hang. I'm still checking out
what exactly happened before that caused the server to hang.

> > I'd really like to have persistent insensitive cursors,
>
> Seems a lot easier to just select the data you want into a temp table.

Sure, that was the first thing I thought about, but one table has at
least 100,000 records in 28 columns with a data-only row length of about
256 bytes. At least 50 user will do this concurrently. With at least two
tables. Concurrently.

Which amounts to at least 2.38 GB temporary data, counting only the
payload.

And yes, the whole table has to be scrolled. This was not my idea.

> You *cannot* expect deleted data in a table to hang around for you after
> you close your transaction --- there is nothing to protect it from being
> VACUUMed, for example.

I see. So not only the transaction information would have to be retained
after the transaction with the cursor declaration finished, but you'd
also have to have this transaction marked as kind of still in progress,
while it really has to be finished because there can only be one
transaction per backend.

Is a cursor that has a transaction associated with it a concept that can
be cleanly implemented?

Is there any simpler solution to the problem? Has anyone ever thought
about that before? I searched the mailing lists but couldn't find
anything.