Issue with cursor.fetchone() raising an exception after doing 'SELECT' on a non-existent row

[olegus-maximus]

According to pretty much everything I read about psycopg2, doing cursor.fetchone() on a result of a 'SELECT' command that finds nothing should return a 'None' object (which makes sense and makes it easy to work with). Instead, however, it throws a "psycopg2.ProgrammingError: no results to fetch," as if a cursor.fetchone() was used on a result of a command that's not supposed to return something by default (e.g. DELETE, where it is totally understandable) instead of SELECT.

Code samples.

How it is supposed to be:

>>cursor.execute("SELECT * FROM my_table WHERE id = 'nonexisting_id';")
>>cursor.fetchone()
None

How it is:

>>cursor.execute("SELECT * FROM my_table WHERE id = 'nonexisting_id';")
>>cursor.fetchone()
ProgrammingError: no results to fetch

Please, confirm whether it is an actual bug or how it is actually supposed to function.

[dvarrazzo]

I assume your second example is wrong. However yes, that's right: psycopg differentiates between a command returning tuples (even if it returns no tuple) from one returning no tuples (such as INSERT without RETURNING or an ALTER TABLE). This is consistent with what the dbapi requires.

[coleman-rik]

I can confirm that a SELECT statement that returns no results, the original poster's 'How it is:' example, does in fact throw a "ProgrammingError: no results to fetch" error.

In my case I get the above error when calling cursor.fetchall()

dvarrazzo's answer doesn't appear to apply to this situation. Unfortunately filoleg's example is not wrong.

Can you confirm this is a bug? If so, any idea when it might get fixed?

Thanks

Running:
Python 2.7.10.
psycop2 2.6.1
PostgreSQL 9.1
Xubuntu 15.10

[dvarrazzo]

I don't know what are you talking about.

In [1]: import psycopg2
In [2]: cnn = psycopg2.connect('')
In [3]: cur = cnn.cursor()
In [4]: cur.execute("create temp table foo ()")
In [5]: cur.execute("select * from foo")
In [7]: cur.fetchone()

# with a final semicolon
In [8]: cur.execute("select * from foo;")
In [9]: cur.fetchone()

# a named cursor
In [10]: cur = cnn.cursor('name')
In [11]: cur.execute("select * from foo;")
In [12]: cur.fetchone()

Please provide a complete test.

[coleman-rik]

dvarrazzo thanks for responding. Your example isn't indicative of the reported problem, in both his example, and my previous experience, it happened on a WHERE clause against a table filled with records (or at least in my case it did).

I have a not insubstantial program littered with try/catch blocks to prevent this from crashing the program again.

Unfortunately I can't repeat this behavior consistently. The try/catch blocks are remaining as I can't risk this bug crashing the program.

If/when I can repeat this behavior constantly I will post that block of code and a test table to run it against.

Until then......

[dvarrazzo]

I have never seen the behaviour you report.

Please add a print and showing cur.statusmessage when fetchone() fails: I guess the statement won't be a select.

[pylover]

I have never seen this before also.

Thanks for cur.statusmessage it's very useful for debugging.

[jmoraleda]

I have also observed this bug. I reuse my connection heavily among multiple threads, including to update rows and insert new ones. I have also multiple processes reading and modifying the same table simultaneously.

I never reuse cursors. In fact, I create a new cursor locally every time I execute a query. So I am certain the cursor throwing the error just executed a select statement.

My experience is similar to that reported by @coleman-rik above: Most of the time, the code below (when run in parallel with a lot of other code running in other threads and other processes) returns None (the right result) but sometimes (maybe once every 10000 calls) it throws ProgrammingError: no results to fetch. I am running python 3.5 on debian stretch and using psycopg2-2.6.2 installed via apt.

def get_row(self):
    cursor = self.connection.cursor()
    cursor.execute("SELECT * FROM my_table WHERE id = 'nonexisting_id'")
    return cursor.fetchone()

[b-l-a-c-k-b-e-a-r-d]

I am seeing this as well. Same scenario:

• not re-using cursors
• multiple processes reading from the same table (10k total queries spread over 64 processes)
• most of the time it returns None, but occasionally seeing ProgrammingError: no results to fetch

I am on RHEL 7, Python 2.7.13, psycopg2 2.7.3.2.

I have not seen issues at 2, 4, 8, 16, 32 concurrent processes.

[b-l-a-c-k-b-e-a-r-d]

Couple more details:

Printing out pgerror gives ERROR: failed to execute task 2 (or some other integer task number).

The cur.statusmessage is empty.

[b-l-a-c-k-b-e-a-r-d]

Dug in a bit more, these errors start when there are more than 40 concurrent connections.

Not sure if this is hitting a connection limit on postgres (which I am doubtful of as I have max connections in postgres set to 256) or some other connection related issue.

Given that it occurs at the certain number of concurrent connections, it has to be a postgres or OS related limit and not a client limitation. However, psycopg2 is misinterpreting the actual issue as no results.

[hi117]

I have also observed this issue with a high commit load but only 2 connections. The connection that is having the isue only uses select statements.

[hugollm]

I've seen this happening but it was totally my fault.
TLDR: I accidentally "forgot" to run execute on the cursor.

In case it helps someone, here was my scenario:

I had a helper function so my queries would be less verbose (probably not one of my brightest ideas). But the thing is, in some very specific cases (running a query without any argument), there was a flaw in my function that skipped cursor.execute.

Obviously psycopg2 was not a fault in my case, but I'll leave this thread with a suggestion: If the error message could make it clear that execute didn't run, that would be way easier to debug. Maybe something like:

"no results to fetch (execute didn't run)"

[ifo20]

I would also request a more helpful error message. In my case, I was running cur.execute(), but I was not awaiting the call (I was porting code from gevent). As a result I saw "no results to fetch" on any query that I ran

[j0nimost]

Any solution? I have the same error

[dvarrazzo]

@b-l-a-c-k-b-e-a-r-d @hi117 @jmoraleda @coleman-rik I have fixed some suspicious management of the cursor state that may lead to the problems you describe.

I have released psycopg 2.7.7 with the correction: please test with this package and let me know if the problem disappear.

This is only valid for the people who have experienced problems in multithread environments. If you just run fetchone() without running execute() is not our bug: it's your bug.

[topoleov]

I get this error too (some times!)
I use psycopg2-binary==2.9.3
and
cursor_factory=psycopg2.extras.DictCursor

code example:

cursor.execute("SELECT * FROM employee WHERE tg_nick=%(tg_nick)s", {'tg_nick': username})

for user in cursor:
    return Employee(**user)

[imandr]

I have the same problem.
I have a "select ..." query, which I know is not supposed to find anything. so I expect that

c = connection.cursor()
c.execute("""
    select ...
")
tuple = c.fetchone()

will produce None, and it does when I run this query alone interactively.
When I run this thing in my app, it gives me the "no results to fetch" error.

I think I was able to reproduce it interactively too.

If I run:

c = connection.cursor()
c.execute("""
    select ...
    ;
    commit   --         <----- add this
"""
)
tuple = c.fetchone()

I get the no results to fetch error, which actually makes sense. I will be looking where exactly I am doing the commit or rollback or something like that on the same cursor ...

---

Update: yes indeed there was a commit between my select... and call to cursor.fetchone(). I found and removed it and now everything is fixed