Re: behavior at the end of a transaction

From: Radosław Smogura <rsmogura(at)softperience(dot)eu>
To: Jean-Max Reymond <jmreymond(at)gmail(dot)com>
Cc: <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: behavior at the end of a transaction
Date: 2011-09-21 13:40:16
Message-ID: d8260d6920328aeb9ee22047f4b9593e@mail.softperience.eu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

On Wed, 21 Sep 2011 13:05:44 +0200, Jean-Max Reymond wrote:
> Le 20/09/2011 11:36, Radosław Smogura a écrit :
>> On Mon, 19 Sep 2011 15:34:19 -0500, Kevin Grittner wrote:
>>> Jean-Max Reymond <jmreymond(at)gmail(dot)com> wrote:
>>>
>>>> On commit or rollback statement, I expect that all the cursors
>>>> are closed (all my Resultset not closed) in the postgres database.
>>>> is it correct ?
>>>
>>> No.
>>>
>>> http://www.postgresql.org/docs/9.1/interactive/sql-declare.html
>>>
>>> In particular, see the WITH/WITHOUT HOLD section.
>>>
>>> -Kevin
>>
>> Don't worry, If you "forget" result sets, when those will be GCed
>> then
>> cursors will be closed soon, too.
>
> no sure that GC will free the resources in the postgres database
I think yes (I don't give head, because it's software), but driver
monitors statements and portals (cursor), if those are unreachable then
it sends close, so there are following requirements to auto-clean up (in
case of cursor)
1) cursor must be unreachable,
2) cursor must be garbage collected (so it may take some time),
3) processing of deads must be invoked
4) connection must be valid

Ad 3. processing of dead cursors must be invoked - this means you need
to send other query to server after 1) and 2). It doesn't means what you
will send, just You need to "wake up" some portions of code.

Ad 4. driver must be able to send data - there is possibility of
half-open connection, when server will have no knowledge that connection
died

And all of this in some way depends of environment, of pool you use.

Cursors will be closed but not immediatly. If for example you have
pool, you forgot about connection and connection is returned to pool,
then in fact this may not be closed even for months (similarly if you
have connection opened) (against 3).

Result sets should be enqueued, but there no info will be posted to
server. Should because this depends on pool implementation, actually
server may wrap all driver objects, and delay freeing how long it wants
- and it does it for connection and statements, and this is good stuff.

You may try to check this, by self.

By the way thanks for post, I will add background cleaning
functionality.

Regards,
Radosław Smogura
http://softperience.eu

In response to

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Radosław Smogura 2011-09-21 14:00:03 Re: behavior at the end of a transaction
Previous Message Jean-Max Reymond 2011-09-21 11:05:44 Re: behavior at the end of a transaction