"attempting to use unexecuted cursor" when a previous query failed in the pg code

nrainer-materialize commented 11 months ago

Problem

A query with a cursor succeeds in the database but fails in the Python code (due to a parser error). Calling cursor.fetchall() after performing first a rollback and then another successful query with the same cursor will fail with attempting to use unexecuted cursor.

Code

Setup

DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (timestamp_val TIMESTAMP, bool_val BOOL);
INSERT INTO tbl VALUES ('99999-12-31 23:59:59'::TIMESTAMP, FALSE::BOOL);

Execution

def test_mwe(connection: Connection) -> None:
    cursor = connection.cursor()

    execute_with_error_handling(cursor, "BEGIN ISOLATION LEVEL SERIALIZABLE;")
    # this will succeed in the database but fail in Python with to dateutil.parser._parser.ParserError
    execute_with_error_handling(cursor, "SELECT timestamp_val FROM tbl;")
    execute_with_error_handling(cursor, "ROLLBACK;")
    execute_with_error_handling(cursor, "BEGIN ISOLATION LEVEL SERIALIZABLE;")
    execute_with_error_handling(cursor, "SELECT bool_val FROM tbl;")
    cursor.fetchall()
    # this statement is not reached, the previous one fails
    print("SUCCESS")

def execute_with_error_handling(cursor: Cursor, sql: str) -> None:
    try:
        print(f"TRY: {sql}")
        cursor.execute(sql)
        print(f"OK: {sql}")
    except Exception as e:
        print(f"ERROR: {e}")

Output

TRY: BEGIN ISOLATION LEVEL SERIALIZABLE;
SUCCESS: BEGIN ISOLATION LEVEL SERIALIZABLE;
TRY: SELECT timestamp_val FROM tbl;
ERROR: year 99999 is out of range: 99999-12-31 23:59:59
TRY: ROLLBACK;
SUCCESS: ROLLBACK;
TRY: BEGIN ISOLATION LEVEL SERIALIZABLE;
SUCCESS: BEGIN ISOLATION LEVEL SERIALIZABLE;
TRY: SELECT bool_val FROM tbl;
SUCCESS: SELECT bool_val FROM tbl;
Traceback (most recent call last):
  File "/repo/misc/python/venv/lib/python3.11/site-packages/pg8000/legacy.py", line 414, in __next__
    return next(self._row_iter)
           ^^^^^^^^^^^^^^^^^^^^
StopIteration

Outcome

Expected: cursor.fetchall() succeeds and contains results Actual: cursor.fetchall() fails with

  File "/repo/misc/python/venv/lib/python3.11/site-packages/pg8000/legacy.py", line 373, in fetchall
    raise ProgrammingError("attempting to use unexecuted cursor")

Remarks

SELECT timestamp_val FROM tbl; does not succeed due to year 99999 is out of range: 99999-12-31 23:59:59 but control flow continues.
The error year 99999 is out of range occurs in the Python code and not in the database. The error is of type dateutil.parser._parser.ParserError.
If the first select statement fails because of an error in the database, the behavior will be different.

nrainer-materialize commented 11 months ago

This still happens if I execute cursor = connection.cursor() after the rollback.

tlocke commented 11 months ago

Hi @nrainer-materialize, thanks for the bug report, it was really clear. I've made a new release of pg8000, version 1.30.3 which should fix this problem. Now if pg8000 encounters a date that's too big for the Python types, then it returns the date in string form. With pg8000, it can't recover from a failure to parse any incoming data. Anyway, hopefully this fixes your problem, but let me know if there's still something wrong.

nrainer-materialize commented 11 months ago

Hi @nrainer-materialize, thanks for the bug report, it was really clear. I've made a new release of pg8000, version 1.30.3 which should fix this problem. Now if pg8000 encounters a date that's too big for the Python types, then it returns the date in string form. With pg8000, it can't recover from a failure to parse any incoming data. Anyway, hopefully this fixes your problem, but let me know if there's still something wrong.

This is great! 🎉 Thank you very much for the quick resolution, @tlocke!

tlocke / pg8000