I've found that, surprisingly, the attempt to declare a cursor outside a
transaction is caught already in the parser, and this code is preceeded
by a comment from 1991 that this was introduced in Postgres 3.0 because
of locking issues.
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.
I've #ifdef'd out the elog in the parser, but then the cursor is
obviously immediately dropped after the statement, so I guess
auto-commit really means what it says in psql.
I'm wondering now why portals have to be dropped at the end of a
transaction. I've #ifdef'd the AtEOXact_portals calls out too and a
fetch from a cursor in such circumstances now seems to return the
correct data, but the server says "NOTICE: Buffer Leak: [004]
(freeNext=-3, freePrev=-3, rel=1058334/1058690, blockNum=0, flags=0x4,
refcount=1 2)" (multiple times, different values).
Additionally, the server seems to keep some lock on the table or rows,
as I can't update them in another session, and, uh, seems to hang then.
Ahem. ps ax says UPDATE but state is S so I assume it's some kind of
lock that is not released.
So before I dig in deeper I thought I'd simply ask here why cursors have
to be dropped at the end of a transaction, and where this buffer leak
comes from. And what's up with the other session that hangs, why do you
need a lock for a SELECT, besides implementing an INSENSITIVE cursor?
Wouldn't persistent cursors make life a lot easier for the ODBC guys?
I've seen that the official JDBC driver fetches the whole ResultSet at
once, but ODBC seems to use a cursor.
