Re: behavior at the end of a transaction - Mailing list pgsql-jdbc

On Wed, 21 Sep 2011 15:40:16 +0200, Radosław Smogura wrote:
> 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@gmail.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.
Hmmm.... Did I read what I wrote? My apologies.

Result sets should be enqueued, but no info may be posted to server,
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.
In real world, in case of pool, unreferenced cursors should be closed
just before connection is taken from pool, because many servers performs
connection validation, which occur in executing some statement.

> 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