psycopg2 weirdness

[Neha Gupta]

Hey,

I only have little experience with web.py and psycopg2 and am running
into a weird problem, I'd appreciate any help I can get with debugging
it.

I wrote a simple program that works and I don't see any crash:
----
import psycopg2

try:
database_conn = psycopg2.connect("dbname='dbname' user='username'
host='hostname'");
except:
print "Unable to connect to the database!"

database_conn.set_isolation_level(0)
cur = database_conn.cursor();

while True:
query = "SELECT avg(dep_delay), extract(hour from crs_dep_time) as
crs_dep_hour, origin from flightdata where date = '01-05-2007' group
by origin, crs_dep_hour order by origin, crs_dep_hour";
cur.execute(query)
rows = cur.fetchall()
print rows
-----

However, I have a small website built using web.py framework which has
a date picker that lets the user pick a date and it takes the user to
a new url such as: localhost:8080/departures/01-05-2007. I issue a
query to my database for the date selected by the user and retrieve
the results. The problem now is that if I select different dates
directly by changing the url then everything works but as soon as I
pick a date from date picker the server crashes. I removed the date
picker and made it just a text box but as soon as I hit the submit
button, server crashes so I know it is not the date picker that
causing trouble.
---
class departures:
def buildDepartureTableHtml(self, date):
web.debug('date', date)
# Issue the query.
# select avg(dep_delay), extract(hour from crs_dep_time) as
crs_dep_hour, origin from flightdata where date = '2007-02-15' group
by origin, crs_dep
# _hour order by origin, crs_dep_hour;
try:
web.debug("About to issue query")
# query = "SELECT avg(dep_delay), extract(hour from crs_dep_time) as
crs_dep_hour, origin from flightdata where date = '" + date + "' group
by origin, crs_dep_hour order by origin, crs_dep_hour";
query = "SELECT avg(dep_delay), extract(hour from crs_dep_time) as
crs_dep_hour, origin from flightdata where date = '01-05-2007' group
by origin, crs_dep_hour order by origin, crs_dep_hour";
cur.execute(query)
web.debug('query executed!')
rows = cur.fetchall()
web.debug('rows fetched!')
web.debug(rows)
except Exception, e:
print repr(e)
database_conn.rollback()
return "<div id='welcome-text'>Invalid Date</div>"
--
// JS code
function submitForm() {
var date = ($("date").value).replace(/\//g,"-");
window.location = "http://" + window.location.host + "/
departures/" + date;
}

You can see above that I even ignored the date passed from the form
and I have hardcoded '01-05-2007'. The message "About to issue query"
gets printed as well as the right date chosen from the date picker but
then I see the following:

Assertion failed: (str != NULL), function PyString_FromString, file
Objects/stringobject.c, line 107.
Abort trap

with a pop that says: "The application Python quit unexpectedly. The
problem may have been caused by the _psycopg.so plug-in".
--
I don't understand the error message above. The date did get passed
correctly and am now not even using it, I use the hard coded date. So
what is going on?

Any help would be great.

Thank you!
Neha

 

[Philip Semanchuk]

Hey,

I only have little experience with web.py and psycopg2 and am running
into a weird problem, I'd appreciate any help I can get with debugging
it.

Hi Neha,
There's a lot of pieces involved here and your subject implies you've
isolated the problem to psycopg2, but I'm not convinced.

I know it is not the date picker that
causing trouble.

So don't confuse us by mentioning it. =)

class departures:
def buildDepartureTableHtml(self, date):
web.debug('date', date)
try:
web.debug("About to issue query")
query = "SELECT avg(dep_delay), extract(hour from crs_dep_time) as
crs_dep_hour, origin from flightdata where date = '01-05-2007' group
by origin, crs_dep_hour order by origin, crs_dep_hour";
cur.execute(query)
web.debug('query executed!')
rows = cur.fetchall()
web.debug('rows fetched!')
web.debug(rows)
except Exception, e:
print repr(e)
database_conn.rollback()
return "<div id='welcome-text'>Invalid Date</div>"

You can see above that I even ignored the date passed from the form
and I have hardcoded '01-05-2007'.

That's a good first step. Simplify even further. What happens if you
simplify the query? FOr instance:
SELECT 1, 2 as crs_dep_hour, origin
from flightdata
where date = '01-05-2007'

If a simple query like that doesn't work, I suspect you have something
fundamental wrong with your setup.

HTH
Philip

 

[Yang Zhang]

For posterity: the problem turned out to be a second request being made
in quick succession by the client-side Javascript, causing the web.py
request handler to run in multiple threads concurrently. The request
handlers don't create their own Postgresql connections, but instead
share one across all sessions. The absence of any synchronization
protecting this connection resulted in myriad errors and crashes in the
C extension module (in both pygresql and psycopg2).