[Bug] Wrong number of columns Error

[serjflint]

• asyncpg version: 0.23
• PostgreSQL version: 13
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?: Yandex Cloud
• Python version: 3.7
• Platform: Ubuntu 18.04
• Do you use pgbouncer?: yes
• Did you install asyncpg with pip?: no
• If you built asyncpg locally, which version of Cython did you use?: no
• Can the issue be reproduced under both asyncio and uvloop?: don't know

We have a web server on Python which is sending queries to the PostgreSQL cluster. After adding a column to the table using a migration without disconnecting I encounter errors with some queries to that table. Example of the query below:

WITH row_info as ( SELECT id FROM UNNEST($1::scheme.affected_table[]) ) DELETE FROM scheme.affected_table AS entities USING row_info WHERE entities.id = row_info.id;

To $1 I pass the list of dictionaries like [{'id': 1, 'field1': 'value1', ...}].

Before the migration or from new connections the query works perfectly. But for queries from web-server I get "wrong number of columns: 11, expected 12". Rebooting the web-server resolves the problem.

I think the problem can be related to Prepared Statements or encoding of Mapping.

[elprans]

asyncpg has no way of knowing that the schema has changed under it, so you are supposed to deliver that notification to your webserver and call reload_schema_state explicitly.

[serjflint]

I thought it is possible to intercept this kind of error and force schema reload. If not, then the issue can be closed.

[elprans]

Are you able to provide a traceback?

[serjflint]

Are you able to provide a traceback?

File "/usr/lib/python3.7/site-packages/asyncpg/connection.py", line 588, in fetch
    record_class=record_class,
File "/usr/lib/yandex/taxi-workforce-management-web/taxi/pg/connection.py", line 53, in _execute
    **kwargs,
File "/usr/lib/python3.7/site-packages/asyncpg/connection.py", line 1632, in _execute
    ignore_custom_codec=ignore_custom_codec,
File "/usr/lib/python3.7/site-packages/asyncpg/connection.py", line 1655, in __execute
    ignore_custom_codec=ignore_custom_codec,
File "/usr/lib/python3.7/site-packages/asyncpg/connection.py", line 1697, in _do_execute
    result = await executor(stmt, None)
File "asyncpg/protocol/protocol.pyx", line 199, in bind_execute
asyncpg.exceptions.DatatypeMismatchError: wrong number of columns: 16, expected 15

[elprans]

Unfortunately, the datatype_mismatch SQL error is not specific enough. In some cases it indicates outdated prepared statement query plans, which would be safe to retry automatically, but in other cases those are legitimate query errors. That said, you can handle asyncpg.DatatypeMismatchError and call reload_schema_state() at the application level.