Thread: connection already closed error

connection already closed error

From

Mark Theisen

Date:

18 October 2011, 14:37:44

Hi all,

I am using python2.6 with twisted 10.1.0 and psycopg2 2.2.1. We are also using pgpooler with transaction pool mode set.
Iam using the twisted ConnectionPool and twistedpg.py, which is: 

from psycopg2 import *
from psycopg2 import _psycopg as _2psycopg
from psycopg2.extensions import connection as _2connection
from psycopg2.extras import RealDictCursor

del connect
def connect(*args, **kwargs):
    kwargs['connection_factory'] = connection
    return _2psycopg.connect(*args, **kwargs)

class connection(_2connection):
    def cursor(self):
        return _2connection.cursor(self, cursor_factory=RealDictCursor)

I connect to pgpooler with:
dbpool = ConnectionPool("twistedpg", self.DSN, cp_reconnect=cp_reconnect, cp_openfun = self.connect_func, **kwords)


I am getting this error when we restart pgpool:

Traceback (most recent call last):
  File "/usr/lib/python2.6/dist-packages/twisted/python/context.py", line 37, in callWithContext
    return func(*args,**kw)
  File "/usr/lib/python2.6/dist-packages/twisted/enterprise/adbapi.py", line 436, in _runInteraction
    conn.rollback()
  File "/usr/lib/python2.6/dist-packages/twisted/enterprise/adbapi.py", line 65, in rollback
    self._pool.disconnect(self._connection)
  File "/usr/lib/python2.6/dist-packages/twisted/enterprise/adbapi.py", line 412, in disconnect
    self._close(conn)
--- <exception caught here> ---
  File "/usr/lib/python2.6/dist-packages/twisted/enterprise/adbapi.py", line 420, in _close
    conn.close()
psycopg2.InterfaceError: connection already closed

This error keeps repeating for about 3 hours, even though the pooler was back up in running within a minute.

Would a possible solution be to change twistedpg.py to:

from psycopg2 import *
from psycopg2 import _psycopg as _2psycopg
from psycopg2.extensions import connection as _2connection
from psycopg2.extras import RealDictCursor

del connect
def connect(*args, **kwargs):
    kwargs['connection_factory'] = connection
    return _2psycopg.connect(*args, **kwargs)

class connection(_2connection):
    def cursor(self):
        return _2connection.cursor(self, cursor_factory=RealDictCursor)

    def close(self):
        try:
            _2connection.close(self)
        except psycopg2.InterfaceError:
            pass

Thanks for all the help,
Mark

Re: connection already closed error

From

Daniele Varrazzo

Date:

18 October 2011, 15:11:44

On Tue, Oct 18, 2011 at 3:32 PM, Mark Theisen
<mark.theisen@digitecinc.com> wrote:

Hi Mark,

> I am using python2.6 with twisted 10.1.0 and psycopg2 2.2.1. We are also using pgpooler with transaction pool mode
set.I am using the twisted ConnectionPool and twistedpg.py, which is: 

I'm afraid we don't maintain Twisted ConnectionPool. Also, are you are
using a pooling middleware (pgpool) with a python pool (Twisted's)?
This is at best redundant, at worse undefined.

Am I understanding right that "twistedpg" is some module of yours?
Googling for it, it suggest me that I wanted to look for "twisted
pig", which I would prefer to avoid.

> I am getting this error when we restart pgpool:
> [...]
>    conn.close()
> psycopg2.InterfaceError: connection already closed

It doesn't surprise me so much.

> Would a possible solution be to change twistedpg.py to:

>    def close(self):
>        try:
>            _2connection.close(self)
>        except psycopg2.InterfaceError:
>            pass

It seems so. You could also use "if not self.closed:
_2connection.close(self)" (if you have this attribute exposed... why
not just self.close()?). However, I think there is not really a reason
for which conn.close() should raise an error on a closed connection:
it is an idempotent method.

/me looks at the dbapi...

uhm, it doesn't explicitly require that. I think a more robust close()
should just return if the connection is closed. It shouldn't raise
exceptions, as in turn PQfinish doesn't. Oh yes, I see: we used to
execute a ROLLBACK on close() so we needed the connection to be open
to be properly closed... but we don't do this anymore. Currently we do
little more than calling PQfinish, which never fails (well, not
politely at least).

Fog, whaddyathink: "conn.close(); conn.close()" should raise an
exception or should just return?

-- Daniele

Re: connection already closed error

From

Mark Theisen

Date:

18 October 2011, 15:26:14

Also, are you are using a pooling middleware (pgpool) with a python pool (Twisted's)?
Yes, I have multiple programs that will use the twisted pool, but they all connect to the pgpooler.

Am I understanding right that "twistedpg" is some module of yours?
Not really, I found it at http://twistedmatrix.com/pipermail/twisted-python/2006-April/012955.html

________________________________________
From: Daniele Varrazzo [daniele.varrazzo@gmail.com]
Sent: Tuesday, October 18, 2011 10:11 AM
To: Mark Theisen
Cc: psycopg@postgresql.org
Subject: Re: [psycopg] connection already closed error

On Tue, Oct 18, 2011 at 3:32 PM, Mark Theisen
<mark.theisen@digitecinc.com> wrote:

Hi Mark,

> I am using python2.6 with twisted 10.1.0 and psycopg2 2.2.1. We are also using pgpooler with transaction pool mode
set.I am using the twisted ConnectionPool and twistedpg.py, which is: 

I'm afraid we don't maintain Twisted ConnectionPool. Also, are you are
using a pooling middleware (pgpool) with a python pool (Twisted's)?
This is at best redundant, at worse undefined.

Am I understanding right that "twistedpg" is some module of yours?
Googling for it, it suggest me that I wanted to look for "twisted
pig", which I would prefer to avoid.

> I am getting this error when we restart pgpool:
> [...]
>    conn.close()
> psycopg2.InterfaceError: connection already closed

It doesn't surprise me so much.

> Would a possible solution be to change twistedpg.py to:

>    def close(self):
>        try:
>            _2connection.close(self)
>        except psycopg2.InterfaceError:
>            pass

It seems so. You could also use "if not self.closed:
_2connection.close(self)" (if you have this attribute exposed... why
not just self.close()?). However, I think there is not really a reason
for which conn.close() should raise an error on a closed connection:
it is an idempotent method.

/me looks at the dbapi...

uhm, it doesn't explicitly require that. I think a more robust close()
should just return if the connection is closed. It shouldn't raise
exceptions, as in turn PQfinish doesn't. Oh yes, I see: we used to
execute a ROLLBACK on close() so we needed the connection to be open
to be properly closed... but we don't do this anymore. Currently we do
little more than calling PQfinish, which never fails (well, not
politely at least).

Fog, whaddyathink: "conn.close(); conn.close()" should raise an
exception or should just return?

-- Daniele

Re: connection already closed error

From

Date:

18 October 2011, 15:26:25

On Oct 18, 2011, at 11:11 AM, Daniele Varrazzo wrote:
>
>
>> Would a possible solution be to change twistedpg.py to:
>
>>    def close(self):
>>        try:
>>            _2connection.close(self)
>>        except psycopg2.InterfaceError:
>>            pass
>
> It seems so. You could also use "if not self.closed:
> _2connection.close(self)" (if you have this attribute exposed... why
> not just self.close()?). However, I think there is not really a reason
> for which conn.close() should raise an error on a closed connection:
> it is an idempotent method.
>
> /me looks at the dbapi...
>
> uhm, it doesn't explicitly require that. I think a more robust close()
> should just return if the connection is closed. It shouldn't raise
> exceptions, as in turn PQfinish doesn't. Oh yes, I see: we used to
> execute a ROLLBACK on close() so we needed the connection to be open
> to be properly closed... but we don't do this anymore. Currently we do
> little more than calling PQfinish, which never fails (well, not
> politely at least).
>
> Fog, whaddyathink: "conn.close(); conn.close()" should raise an
> exception or should just return?

To mimic python's standard close(), it would indeed need to be idempotent.

Cheers,
M

Re: connection already closed error

From

Federico Di Gregorio

Date:

18 October 2011, 15:38:29

On 18/10/11 17:11, Daniele Varrazzo wrote:
[snip]
> Fog, whaddyathink: "conn.close(); conn.close()" should raise an
> exception or should just return?

Just return. If we don't have a good reason to throw lets don't.

--
Federico Di Gregorio                                       fog@initd.org
           Purtroppo i creazionisti non si sono ancora estinti. -- vodka

Re: connection already closed error

From

Mark Theisen

Date:

19 October 2011, 15:47:53

A note of clarification. We are using pgbouncer instead of pgpool. Sorry about the confusion.

Thanks,
Mark
________________________________________
From: psycopg-owner@postgresql.org [psycopg-owner@postgresql.org] On Behalf Of Mark Theisen
[mark.theisen@digitecinc.com]
Sent: Tuesday, October 18, 2011 9:32 AM
To: psycopg@postgresql.org
Subject: [psycopg] connection already closed error

Hi all,

I am using python2.6 with twisted 10.1.0 and psycopg2 2.2.1. We are also using pgpooler with transaction pool mode set.
Iam using the twisted ConnectionPool and twistedpg.py, which is: 

from psycopg2 import *
from psycopg2 import _psycopg as _2psycopg
from psycopg2.extensions import connection as _2connection
from psycopg2.extras import RealDictCursor

del connect
def connect(*args, **kwargs):
    kwargs['connection_factory'] = connection
    return _2psycopg.connect(*args, **kwargs)

class connection(_2connection):
    def cursor(self):
        return _2connection.cursor(self, cursor_factory=RealDictCursor)

I connect to pgpooler with:
dbpool = ConnectionPool("twistedpg", self.DSN, cp_reconnect=cp_reconnect, cp_openfun = self.connect_func, **kwords)


I am getting this error when we restart pgpool:

Traceback (most recent call last):
  File "/usr/lib/python2.6/dist-packages/twisted/python/context.py", line 37, in callWithContext
    return func(*args,**kw)
  File "/usr/lib/python2.6/dist-packages/twisted/enterprise/adbapi.py", line 436, in _runInteraction
    conn.rollback()
  File "/usr/lib/python2.6/dist-packages/twisted/enterprise/adbapi.py", line 65, in rollback
    self._pool.disconnect(self._connection)
  File "/usr/lib/python2.6/dist-packages/twisted/enterprise/adbapi.py", line 412, in disconnect
    self._close(conn)
--- <exception caught here> ---
  File "/usr/lib/python2.6/dist-packages/twisted/enterprise/adbapi.py", line 420, in _close
    conn.close()
psycopg2.InterfaceError: connection already closed

This error keeps repeating for about 3 hours, even though the pooler was back up in running within a minute.

Would a possible solution be to change twistedpg.py to:

from psycopg2 import *
from psycopg2 import _psycopg as _2psycopg
from psycopg2.extensions import connection as _2connection
from psycopg2.extras import RealDictCursor

del connect
def connect(*args, **kwargs):
    kwargs['connection_factory'] = connection
    return _2psycopg.connect(*args, **kwargs)

class connection(_2connection):
    def cursor(self):
        return _2connection.cursor(self, cursor_factory=RealDictCursor)

    def close(self):
        try:
            _2connection.close(self)
        except psycopg2.InterfaceError:
            pass

Thanks for all the help,
Mark



--
Sent via psycopg mailing list (psycopg@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/psycopg