Spurious "prepared statement X does not exist" messages on second execution of bad SQL

[davclark]

Even if we don't solve the issue, I'm hoping this will at least serve as a guide to other folks who encounter this error (that said, the other issues mentioning prepared statements that I saw in the issue tracker were NOT related to this issue). In my specific case, I saw this error using executemany with misspelled columns.

Basically, when you run executemany() with a mis-spelled column (psycopg 3.1.9), there appears to be a failed attempt to prepare a statement, along with reporting the correct error (see details below). On subsequent runs, you don't get the SQL error. Instead, you get an error instead that the prepared statement doesn't exist. This is basically a documentation bug, except it may require additional logic to catch the failed prepare statement.

My guess is that this is at the level of something I can figure out, but I wanted to post this at least for visibility to others in case I don't get time to get to it - if folks see this error message, step one is to check the spelling of columns.

[dvarrazzo]

I am not able to reproduce:

>>> import psycopg
>>> conn = psycopg.connect(autocommit=True)
>>> conn.execute("create table testexm (id serial primary key, data text)")
>>> conn.cursor().executemany("insert into testexm (datta) values (%s)", [["foo"]])
UndefinedColumn: column "datta" of relation "testexm" does not exist
LINE 1: insert into testexm (datta) values ($1)
                             ^

can you provide a repro?

[davclark]

Continuing your example (which replicates for me), if you execute the failing insert with a type-o again, you'll get this (I'm running in a Jupyter notebook):

InvalidSqlStatementName                   Traceback (most recent call last)
Cell In[8], line 1
----> 1 conn.cursor().executemany("insert into testexm (datta) values (%s)", [["foo"]])

File /usr/local/lib/python3.9/dist-packages/psycopg/cursor.py:758, in Cursor.executemany(self, query, params_seq, returning)
    754             self._conn.wait(
    755                 self._executemany_gen_no_pipeline(query, params_seq, returning)
    756             )
    757 except e._NO_TRACEBACK as ex:
--> 758     raise ex.with_traceback(None)

InvalidSqlStatementName: prepared statement "_pg3_0" does not exist

I guess the first time, you're storing the name of a prepared statement so that if you run the command subsequently, executemany() tries to find it.

I'm updating the title to reflect this.

[dlax]

I can confirm the issue; it happens only when the pipeline mode is available (used implicitly by executemany()), otherwise, a proper SQL error is raised.

[dvarrazzo]

Thank you very much for the repro, @davclark! We will look into it.

[davclark]

I tried to find where in the code this is happening, but it's hard for me to follow as a newcomer. I'm happy to try to put a PR together if you can point me in the right direction. Thanks!

[dlax]

@davclark, indeed, it's probably not a "good first issue". I'll take a closer look today.

[dlax]

It does not appear to be related to executemany() specifically; just using prepare=True with the pipeline mode triggers the same issue:

def test_issue585(conn):                                                                                                                
    """Regression test for GH issue #585"""                                                                                             
    conn.autocommit = True                                                                                                              
    conn.execute("drop table if exists testexm")                                                                                        
    conn.execute("create table testexm (id serial primary key, data text)")                                                             
    cur = conn.cursor()                                                                                                                 
    with pytest.raises(psycopg.errors.UndefinedColumn):                                                                                 
        with conn.pipeline():                                                                                                           
            cur.execute(                                                                                                                
                "insert into testexm (datta) values (%s)", ["foo"], prepare=True                                                        
            )                                                                                                                           
    with pytest.raises(psycopg.errors.UndefinedColumn):                                                                                 
        cur.execute("insert into testexm (datta) values (%s)", ["foo"])                                                                 
    # ^fails, raise psycopg.errors.InvalidSqlStatementName: prepared statement "_pg3_0" does not exist                                  

[dvarrazzo]

Thank you very much for the fix, @dlax!

I have backported your changes on maint-3.1. f18bd10 didn't apply correctly because of the refactoring in 0123f15 so I've modified to 299958f. I assume it's ok as the test suite passed, but please let me know if you think that this change is not the right thing in 3.1.

[dlax]

The backport seems good; thank you for taking care of this.