Stuck connection/query?

Greetings,
I have a long-running python program that continually cycles through a 
list of servers and queries them to retrieve data.  Occasionally (every 
few days) it will get stuck in a pyscopg2 function call.   For 
reference, the program is running on linux and using psycopg2 2.0.14 
with python 2.6.

I've seen it get stuck in both cur.execute() and also in 
pyscopg2.connect().   I'm guessing it's due to some network error or an 
unresponsive postgres server.

Is there any way to specify a timeout or some other recommended solution?

Thanks
-Brien

Re: Stuck connection/query?

On 15/06/10 23:43, Brien Voorhees wrote:
> Greetings,
> I have a long-running python program that continually cycles through a
> list of servers and queries them to retrieve data.  Occasionally (every
> few days) it will get stuck in a pyscopg2 function call.   For
> reference, the program is running on linux and using psycopg2 2.0.14
> with python 2.6.
> 
> I've seen it get stuck in both cur.execute() and also in
> pyscopg2.connect().   I'm guessing it's due to some network error or an
> unresponsive postgres server.
> 
> Is there any way to specify a timeout or some other recommended solution?

Depends if you are waiting for the network to respond or for the
database to finish processing.

If it's the former, you will have to add timeouts in your code. If your
problem are queries that take too long then you can look at the
statement_timeout parameter on PostgreSQL, but even then you should
probably have some timeouts in the app code (implementing which is
outside of the scope of psycopg2).

Cheers,
Jan

Status of Python 3 Port?

_______________________________________________
Psycopg mailing list
Psycopg@...
http://lists.initd.org/mailman/listinfo/psycopg

Re: problem with connection.commit

[ thread back from Feb 20, 2010 ], found it from archives...
In reply to http://lists.initd.org/pipermail/psycopg/2010-February/006814.html

I can concurr, but using an older version of psycopg2 [ __version__  
says '2.0.5.1 (dec dt ext pq3)' ]

Happens on both mac [ osx 10.5.8 ] and older linux (kernel  
2.6.22.9-61) against postgres 8.2.11 and 8.4.2; the exception does not  
get raised until the second cursor gets used in the following case:

create table table1 (id integer primary key);
create table table2 (id integer references table1(id) deferrable  
initially deferred);

with python:
# -----
import psycopg2

dsn = '...'
conn = psycopg2.connect(.dsn)

cursor = conn.cursor()
cursor.execute("insert into table2 values (1)")
conn.commit() # Does not throw an exception

cursor2 = conn.cursor()
cursor2.execute("select * from table2")
# -------

Python fails not at conn.commit(), but later at cursor2.execute():

   File "test2.py", line 10, in ?
     cursor2.execute("select * from table2")
psycopg2.OperationalError:   insert or update on table "table2"  
violates foreign key constraint "table2_id_fkey"
DETAIL:  Key (id)=(1) is not present in table "table1".

I'll try to update to latest psycopg2 on osx to see if it still is the  
case.

Doing the insert at psql shows the expected behavior -- complaint and  
forced rollback right at commit().

jlrobins$ psql
psql (8.4.2)
Type "help" for help.

social=#
social=# begin;
BEGIN
social=# insert into table2 values (1);
INSERT 0 1
social=# commit;
ERROR:  insert or update on table "table2" violates foreign key  
constraint "table2_id_fkey"
DETAIL:  Key (id)=(1) is not present in table "table1".

----
James Robinson
Socialserve.com

ThreadedConnectionPool and Cherrypy

Hi,

I'm using psycopg2 2.0.14 and cherrypy 3.1.2.  I have been using 
SQLAlchemy to manage connections, but when I found out about 
ThreadedConnectionPool I decided to give it a try since I don't need the 
rest of SA.

I've got it working but there is an annoying side effect of using 
psycopg's pool.  CherryPy implements logging so it writes to stdout or 
to a designated log file messages like these:

[24/Jun/2010:21:37:05] ENGINE Bus STARTING
[24/Jun/2010:21:37:05] ENGINE Serving on 127.0.0.1:8080
[24/Jun/2010:21:37:05] ENGINE Creating the SQL database engine

However, psycopg2's pool.py implements its own logger so now everything 
in the log ends up duplicated:

[24/Jun/2010:21:38:58] ENGINE Bus STARTING
2010-06-24 21:38:58,900 INFO [24/Jun/2010:21:38:58] ENGINE Bus STARTING
[24/Jun/2010:21:38:59] ENGINE Serving on 127.0.0.1:8085
2010-06-24 21:38:59,105 INFO [24/Jun/2010:21:38:59] ENGINE Serving on 
127.0.0.1:8085
[24/Jun/2010:21:38:59] ENGINE Creating the SQL database engine
2010-06-24 21:38:59,106 INFO [24/Jun/2010:21:38:59] ENGINE Creating the 
SQL database engine

Not a big deal, but as I said it's annoying.  I'm wondering if there is 
some way to turn off psycopg2's logging.

TIA.

Joe

Re: ThreadedConnectionPool and Cherrypy

On 25/06/2010 03:45, Joe Abbate wrote:
> [24/Jun/2010:21:38:58] ENGINE Bus STARTING
> 2010-06-24 21:38:58,900 INFO [24/Jun/2010:21:38:58] ENGINE Bus STARTING
> [24/Jun/2010:21:38:59] ENGINE Serving on 127.0.0.1:8085
> 2010-06-24 21:38:59,105 INFO [24/Jun/2010:21:38:59] ENGINE Serving on
> 127.0.0.1:8085
> [24/Jun/2010:21:38:59] ENGINE Creating the SQL database engine
> 2010-06-24 21:38:59,106 INFO [24/Jun/2010:21:38:59] ENGINE Creating the
> SQL database engine
> 
> Not a big deal, but as I said it's annoying.  I'm wondering if there is
> some way to turn off psycopg2's logging.

psycopg should not initialize the logging module if already initialized.
I supposed logging.basicConfig() would take care of that but it doesn't
seems so. Can you please comment that line in pool.py an tell us what
happens?

federico

--

-- 
Federico Di Gregorio                                       fog@...
 A desobediência é uma virtude necessária à criatividade. -- Raul Seixas

_______________________________________________
Psycopg mailing list
Psycopg@...
http://lists.initd.org/mailman/listinfo/psycopg

Re: ThreadedConnectionPool and Cherrypy

Hi Federico,

On 06/25/2010 04:04 AM, Federico Di Gregorio wrote:
>
> psycopg should not initialize the logging module if already initialized.
> I supposed logging.basicConfig() would take care of that but it doesn't
> seems so. Can you please comment that line in pool.py an tell us what
> happens?
>    

If I comment out the logging.basicConfig() in pool.py, the duplicate 
INFO-level log lines go away.

Joe

Re: ThreadedConnectionPool and Cherrypy

On 25/06/2010 10:47, Joe Abbate wrote:
> Hi Federico,
> 
> On 06/25/2010 04:04 AM, Federico Di Gregorio wrote:
>>
>> psycopg should not initialize the logging module if already initialized.
>> I supposed logging.basicConfig() would take care of that but it doesn't
>> seems so. Can you please comment that line in pool.py an tell us what
>> happens?
>>    
> 
> If I comment out the logging.basicConfig() in pool.py, the duplicate
> INFO-level log lines go away.

The documentation for basicConfig says:

Does basic configuration for the logging system by creating a
StreamHandler with a default Formatter and adding it to the root logger.
The function does nothing if any handlers have been defined for
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
the root logger. The functions debug(), info(), warning(), error() and
critical() will call basicConfig() automatically if no handlers are
defined for the root logger.

So, probably, psycopg.pool is imported before other modules and the
other modules add handlers anyway. I'd say this is a psycopg bug that
being a library should not initialize the logging system. Will fix this
when I understand exactly what I library should do; in the meantime just
leave the comment in place.

federico

--

-- 
Federico Di Gregorio    <mailto:fog@...> <jid:fog@...>
DISCLAIMER. If I receive a message from you, you are agreeing that:
 1. I am by definition, "the intended recipient".
 2. All information in the email is mine to do with as I see fit and
 make such financial profit, political mileage, or good joke as it lends
 itself to. In particular, I may quote it on USENET or the WWW.
 3. I may take the contents as representing the views of your company.
 4. This overrides any disclaimer or statement of confidentiality that
 may be included on your message.

_______________________________________________
Psycopg mailing list
Psycopg@...
http://lists.initd.org/mailman/listinfo/psycopg

Re: ThreadedConnectionPool and Cherrypy

On Fri, Jun 25, 2010 at 11:00 AM, Federico Di Gregorio <fog@...> wrote:

> So, probably, psycopg.pool is imported before other modules and the
> other modules add handlers anyway. I'd say this is a psycopg bug that
> being a library should not initialize the logging system. Will fix this
> when I understand exactly what I library should do;

Yep, organizing the logging system between libraries is tricky. I
think the correct thing to do in libraries is just not to call
basicConfig: use the desired logger objects and leave their
configuration as a policy of the calling application. This is the
guideline I usually adopt to get rid of duplicate lines in projects
where loggings starts becoming messy.

> in the meantime just
> leave the comment in place.

If Joe wants to avoid duplicate logging in his app without changing
psycopg code (e.g. if he's using a system version) he may try to fool
the logging system by one of:

- if his application installs handlers on the root logger, he may
ensure that logging init is performed before importing psycopg
- if he's not using handlers on the root logger, he may install a
no-op handler before importing psycopg, e.g.
logging.basicConfig(stream=open(os.devnull, 'a'))
- if he thinks basicConfig is not a good idea, he may monkeypatch it
away before importing psycopg: logging.basicConfig = lambda **kwargs:
None

-- Daniele

Re: ThreadedConnectionPool and Cherrypy

Hi Daniele,

On 06/25/2010 08:16 AM, Daniele Varrazzo wrote:
>
> If Joe wants to avoid duplicate logging in his app without changing
> psycopg code (e.g. if he's using a system version) he may try to fool
> the logging system by one of:
>
> - if his application installs handlers on the root logger, he may
> ensure that logging init is performed before importing psycopg
> - if he's not using handlers on the root logger, he may install a
> no-op handler before importing psycopg, e.g.
> logging.basicConfig(stream=open(os.devnull, 'a'))
> - if he thinks basicConfig is not a good idea, he may monkeypatch it
> away before importing psycopg: logging.basicConfig = lambda **kwargs:
> None
>    

Just to clarify, my application doesn't take any explicit action with 
respect to logging (except for defining log.error_file in the production 
configuration).  Therefore, CherryPy is the one that controls logging.  
The CP code is here:

http://www.cherrypy.org/browser/tags/cherrypy-3.1.2/cherrypy/_cplogging.py

Joe