Re: Hey Mr. PG! End my transaction, BUT KEEP MY CURSORS OPEN!!

Hi,

Please don't top-post. It makes it more difficult to follow
a discussion, and makes it easy to lose context. First quote
the relevant part of the original message, then put your text
below that (the natural reading-order in the English-speaking
part of the world is from top to bottom). And BTW, it's not
necessary to quote mail headers and signatures.

(I've tried to fix it.)

Gastón Simone wrote:
> Oliver Fromme wrote:
> > Gastón Simone wrote:
> > > Can I Commit a transaction without closing my cursors??
> >
> > Maybe I'm totally misinterpreting your question, but what
> > is wrong with using the "COMMIT" SQL command? It commits
> > the current transaction, and of course it doesn't close
> > the cursor (why should it?).
> >
> > http://www.postgresql.org/docs/7.4/static/sql-commit.html
>
> I thought the COMMIT SQL command closes every opened cursor when it's
> executed!!
> Am I wrong?

I think you're wrong.

> So, what is the command that closes every opened cursor by default?

That depends on your programming language.
In Python (my preferred one) I write "mycursor.close()".
You can also close your database connection, which will
automatically close (invalidate) all cursors that had been
associated with that connection.

It seems that I should try to explain what a cursor really
is. The following is my personal view of this, and might
not be totally accurate, but experience has shown that it
comes close enough to reality. :-)

Cursors are just "handles" for sending commands (Queries,
Transactions, etc.) to the database server, just like a
filehandle is used to access a plain file (i.e. read from
it or write to it). The cursor doesn't care what SQL
commands you send through it to the database. The cursor
is only closed when you close it excplicitely, or when
the connection to the database is lost.

If you have a multi-threaded application, each thread can
use its own cursor, so they can send SQL commands to the
database without interfering with each other, and without
having to open a separate connection for every thread.

When writing non-threaded applications, you typically open
a connection at the very beginning, aquire a cursor, and
then use that cursor throughout your application. At least
that's how I do it, and it works perfectly fine so far.
Of course, you can still use multiple cursors, even in
single-threaded applications. That can be very useful
when mixing queries and transactions, or when you have to
perform concurrent transactions on multiple things at once.

At the very end, you close the cursor and then close the
database connection -- but that's purely optional (though
it's good practice), because when a program exits, all of
its connections are closed automatically by the operating
system, and of course a cursor is also closed automati-
cally when the connection is closed for which it had been
aquired.

Basically, cursors are an abstraction level that could
also be implemented using multiple connections to a data-
base, but cursors are more efficient and require less
resources on both sides (client and server). In fact,
there are databases which don't support cursors natively,
and where the API emulates them.

I hope that clarifies things a bit.

Best regards
Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"That's what I love about GUIs: They make simple tasks easier,
and complex tasks impossible."
-- John William Chambless