persistent portals/cursors (between transactions)

From: Florian Wunderlich <fwunderlich(at)devbrain(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: persistent portals/cursors (between transactions)
Date: 2002-01-23 17:26:23
Message-ID: 3C4EF23F.5295576F@hq.factor3.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Roderick A. Anderson 2002-01-23 17:28:44 Re: postgresql 7.2b5 and vserver: statistics sockets
Previous Message Doug McNaught 2002-01-23 17:13:06 Re: Permissions on non-owned database