Tom>So if that's what you want, don't commit the transaction.
What are the options then?
I would argue, users expect cursors to work across commits, and they expect the database to stream the data instead of materializing.
Tom>so that the rows the cursor still needed to fetch
Tom>would not get cleaned by VACUUM
That is understandable.
Tom>If we didn't materialize, then we'd
Tom>have to retain the locks held by the transaction
It is not clear why locks have to retain. Transaction commit should release the locks, shouldn't it?
Tom>nor would the tables containing them get
Tom>dropped or significantly altered
One does not usually drop a table being selected, but when (s)he does, he does that in production. What I mean is I do not see why "committing a transaction" involves restrictions on table drop/alter.
It looks like all the drop/alter can happen with current implementation (e.g. one connection starts "select", and another connection tries to drop/alter the table). I expect "fetch across commit" to work exactly like a cursor from a separate connection with its own transaction.
----
What if 1000 sessions perform an innocent "select * from big_table" WITH HOLD cursor (e.g. to process the table in a stream fashion)? It would easily fill all the disk space at the server-side.
In my experience, "fetch across commit" is typically used to process data in batches. That is cursor provides IDs to be processed, and the transaction is committed from time to time to avoid the need of doing everything from scratch in case the transaction fails.
Commit is needed, well, to commit, the (partial) work. Of course, locks are released, and I just do not expect for the cursor to dissappear.
Long-running cursor enables to process data serially, and it enables to use just a simple table with no additional indices.
I do understand it is a bad idea to keep long-running "with hold" cursor for a table that is actively changing. However, that would be awful anyway, so I don't expect sane applications to do that kind of thing (even for Oracle DB).
Vladimir
