Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-jdbc by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group