Re: persistent portals/cursors (between transactions)

Tom Lane wrote:
>
> Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp> writes:
> > Tom Lane wrote:
> >> If it's not holding any locks, I can guarantee you it's not insensitive.
> >> Consider VACUUM, or even DROP TABLE.
>
> > It's already possible to keep a lock accross transactions.
> > So it would keep an AccessShareLock across transactions.
>
> AccessShareLock would fend off DROP/ALTER TABLE, but not VACUUM anymore.
> We'd need to invent Yet Another lock type that would prevent VACUUM.
> Clearly that's perfectly doable.
>
> But: having just finished a lot of work to ensure that VACUUM could run
> in parallel with all "normal" database operations, I'm not that thrilled
> at the prospect of introducing a new mechanism that will block VACUUM.
> Especially not one that's *designed* to hold its lock for a long period
> of time. This will just get us right back into all the operational
> problems that lazy VACUUM was intended to get around. For example, this
> one: if transaction A has an insensitive-cursor lock on table T, and a
> VACUUM comes along to vacuum T and blocks waiting for the lock, then
> when subsequent transaction B wants to create an insensitive cursor on T
> it's going to be forced to queue up behind the VACUUM.

Why do you have to lock the whole table when all you want is just one
set of rows from a set of versions? Am I missing something here?

When you're talking about in-transaction cursors for the above example,
why would the cursor need anything more than the transaction A needs
anyway? And for cross-transaction cursors, why lock the whole table when
you could use the transaction information from the transaction in which
the cursor was declared?

Generally spoken, where's the difference between an insensitive
persistent cursor and a still running transaction?

> While temp tables may seem like an ugly, low-tech way to support
> insensitive cursors, I think they may have more merit than you realize.

Obviously, that's the easy way to do it, and lots of other databases
make use of that already to implement insensitive cursors (see my other
post). But as the long-term goal should be updateable insensitive
persistent cursors, I think the temp table solution will get really
messy.