[Psycopg] cur.fetchall() failing...

[Matt Goodall]

Jerry LeVan wrote:
> Hi,

I'm typing this quickly before I leave for the day so I hope it makes
sense and that I haven't misread anything ...

> 
> I am having some problems with threads in  my wxPython postgresql  
> browser.
> 
> I decided to try to execute sql statements in a thread in order to keep
> the interface alive...
> 
> I collect a user sql string in a text object and start a thread that
> basically does something like:
> 
>       try:
>          self.cur.execute(sqlstring)
>       except:
>           enqueue a call back to handle the error in the gui thread
>       else:
>            enqueue a call back to handle the display of the results  
> in the gui thread
> 
> I have a timer checking the queue 10 times a second and dispatches  
> the call to
> the error handler or the displayer back in the main thread.

(Off topic, but it should be possible to integrate into the wx mainloop
instead of polling for the results. That will make the UI respond
immediately instead of the 0.1s delay.)

> 
> Some times it works and somethings it does not work ;(

Ah, welcome to the wonderful world of threads ;-).

> 
> The error ,when it occurs, is generated by the following statement in  
> the
> display routine
> 
>     result = self.cur.fetchall()

> fails with a "nothing to fetch" error.  If I examine the
> self.cur object I see that everything looks "right", the number of
> rows is correct and the column info is correct.

OK, first of all *never* share anything between threads unless you are
absolutely sure that the shared object supports being called from
different threads.

In the case of psycopg2, "threads may share the module and connections",
because psycopg2 has a threadsafety of 2, as defined by the DB-API,
http://www.python.org/dev/peps/pep-0249/.

In terms of your application, you are allowed to create the connection
in the main thread (the one that wx owns) and use the connection in
secondary threads.

However, you should not share a cursor between threads and you
definitely shouldn't execute SQL in one thread and fetch the result in
another. And, from the snippet of code you've posted I suspect that's
what you're doing.

Try something like the following, it should be thread-safe with psycopg2:

	curs = self.conn.cursor()
	try:
	    curs.execute(...)
	    rows = curs.fetchall()
	finally:
	    curs.close()

>
> I then tried modifying the thread code to look like:
> 
> 	try:
>              self.cur.execute(sqlstring)
>              if self.cur.statusmessage == 'SELECT'
>                 self.result = self.cur.fetchall()
>           except:
>               as above
>           else:
>                as above
> 
> Again sometimes it works and sometimes it does not...
> 
> When I examine the self.curs object in the debugger I find again that
> just about everything looks 'right', the number of rows is correct and
> the column info is correct but the data in the result set is not all  
> present!
> ie out of 5000+ records only a couple of hundred might have data.
> 
> I have a 'checks' table with 5800 records, if I enter
> 
> select * from checks
> 
> The program will properly display the checks.
> 
> If I modify the above to be a self join that adds a "running total"  
> column
> 
> SELECT  *, (SELECT sum(amount) FROM checks  WHERE (x.date >date)
> OR (x.date = date AND x.amount >= amount ) ) AS total
>      FROM checks x
>      ORDER BY  date,amount
> 
> The above query will fail as above, ( it takes a quite a bit longer  
> to run)

The difference in behaviour is most likely down to timing.

> 
> I am using the thread model given in the book 'programming python'  
> third edition.
> 
> That code has a nice trick
> 
> try:                                     # raise ImportError to
>      import thread                        # run with gui blocking
> except ImportError:                      # if threads not available
>      class fakeThread:
>          def start_new_thread(self, func, args):
>              func(*args)
>      thread = fakeThread()
> 
> if force the exception to fire to use the "fakeTread" class I don't
> have any problems....

Heh, that's because it's totally not using a thread anymore. I think
that's a really silly "trick", it becomes a completely different piece
of code.

> 
> 
> Are there any known problems with "fetchall()" when the cur.execute is
> executed in a thread?
> 
> I am running out of ideas.
> 
> Thanks
> 
> Jerry
> 
> _______________________________________________
> Psycopg mailing list
> Psycopg at lists.initd.org
> http://lists.initd.org/mailman/listinfo/psycopg

-- 
Matt Goodall
Technical Director, Pollenation Internet Ltd

Registered Number: 4382123
Registered Office: 237 Lidgett Lane, Leeds, West Yorkshire, LS17 6QR
A member of the Brunswick MCL Group of Companies

w: http://www.pollenation.net/
e: matt at pollenation.net
t: +44 (0) 113 2252500

This message may be confidential and the views expressed may not reflect
the views of my employers. Please read http://eudaimon-group.com/email
if you are uncertain what this means.