Обсуждение: behavior at the end of a transaction

Поиск
Список
Период
Сортировка

behavior at the end of a transaction

От
Jean-Max Reymond
Дата:
hi,
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 ?

Re: behavior at the end of a transaction

От
"Kevin Grittner"
Дата:
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

Re: behavior at the end of a transaction

От
Radosław Smogura
Дата:
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.

Regards,
Radosław Smogura

Re: behavior at the end of a transaction

От
Jean-Max Reymond
Дата:
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 ressources in the postgres database


Re: behavior at the end of a transaction

От
Radosław Smogura
Дата:
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.

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




Re: behavior at the end of a transaction

От
Radosław Smogura
Дата:
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