use_insertmanyvalues=True case error when add record in Postgresql

[swuecho]

Describe the bug

in 2.0. use_insertmanyvalues=True case add error. In 1.4. works fine.

if set use_insertmanyvalues=False, works as in 1.4

Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected

No response

SQLAlchemy Version in Use

2.0.31

DBAPI (i.e. the database driver)

2.9.5

Database Vendor and Major Version

13.1

Python Version

3.11.9

Operating system

Ubuntu

To Reproduce

from sqlalchemy import Column, Integer, String, create_engine, select
from sqlalchemy.dialects import postgresql
from sqlalchemy.orm import declarative_base, sessionmaker

engine = create_engine(
    "postgresql://name:pass@localhost:5432/db",
    # use_insertmanyvalues=False,  # uncomment this line, works fine.
)

# Create a base class for declarative models
Base = declarative_base()


# Define the Answer model
class AnswerTest(Base):
    __tablename__ = "answer_test"
    id = Column(Integer, primary_key=True)
    rid = Column(String(10))
    qid = Column(String)
    value = Column(postgresql.ARRAY(Integer))  # PGArray
    text = Column(String)


# Create all tables in the database
Base.metadata.create_all(engine)

# Create a new session
Session = sessionmaker(bind=engine)
session = Session()

# text or value. 
answers = [
    {"qid": "Q1", "rid": "testS00000", "value": [3], "text": None},
    {"qid": "Q2", "rid": "testS00000", "value": [2, 4], "text": None},
    {"qid": "Q3", "rid": "testS00000", "value": None, "text": "text"},
    {"qid": "Q4", "rid": "testS00000", "value": None, "text": "text area"},
    {"qid": "Q5", "rid": "testS00000", "value": [2], "text": None},
    {"qid": "Q7", "rid": "testS00000", "value": None, "text": 33},
]

for answer in answers:
    answer_obj = AnswerTest(
        qid=answer["qid"], rid=answer["rid"], value=answer["value"], text=answer["text"]
    )
    session.add(answer_obj)

session.commit()

res = list(session.execute(select(AnswerTest)).scalars().all())
print(len(res))

Error

raceback (most recent call last):
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2116, in _exec_insertmany_context
    dialect.do_execute(
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type integer: "text"
LINE 1: ..., NULL, 1), ('testS00000', 'Q3', NULL::INTEGER[], 'text', 2)...
                                                             ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/hwu/dev/surveyresearch/bin/add_all.py", line 49, in <module>
    session.commit()
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2017, in commit
    trans.commit(_to_root=True)
  File "<string>", line 2, in commit
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 1302, in commit
    self._prepare_impl()
  File "<string>", line 2, in _prepare_impl
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 1277, in _prepare_impl
    self.session.flush()
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 4341, in flush
    self._flush(objects)
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 4476, in _flush
    with util.safe_reraise():
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 4437, in _flush
    flush_context.execute()
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/orm/unitofwork.py", line 466, in execute
    rec.execute(self)
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/orm/unitofwork.py", line 642, in execute
    util.preloaded.orm_persistence.save_obj(
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj
    _emit_insert_statements(
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/orm/persistence.py", line 1143, in _emit_insert_statements
    result = connection.execute(
             ^^^^^^^^^^^^^^^^^^^
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1844, in _execute_context
    return self._exec_insertmany_context(dialect, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2124, in _exec_insertmany_context
    self._handle_dbapi_exception(
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2116, in _exec_insertmany_context
    dialect.do_execute(
  File "/home/hwu/.pyenv/versions/3.11.9/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type integer: "text"
LINE 1: ..., NULL, 1), ('testS00000', 'Q3', NULL::INTEGER[], 'text', 2)...
                                                             ^

[SQL: INSERT INTO answer_test (rid, qid, value, text) SELECT p0::VARCHAR, p1::VARCHAR, p2::INTEGER[], p3::VARCHAR FROM (VALUES (%(rid__0)s, %(qid__0)s, %(value__0)s::INTEGER[], %(text__0)s, 0), (%(rid__1)s, %(qid__1)s, %(value__1)s::INTEGER[], %(text__1)s, ... 283 characters truncated ... (p0, p1, p2, p3, sen_counter) ORDER BY sen_counter RETURNING answer_test.id, answer_test.id AS id__1]
[parameters: {'qid__0': 'Q1', 'value__0': [3], 'text__0': None, 'rid__0': 'testS00000', 'qid__1': 'Q2', 'value__1': [2, 4], 'text__1': None, 'rid__1': 'testS00000', 'qid__2': 'Q3', 'value__2': None, 'text__2': 'text', 'rid__2': 'testS00000', 'qid__3': 'Q4', 'value__3': None, 'text__3': 'text area', 'rid__3': 'testS00000', 'qid__4': 'Q5', 'value__4': [2], 'text__4': None, 'rid__4': 'testS00000', 'qid__5': 'Q7', 'value__5': None, 'text__5': 33, 'rid__5': 'testS00000'}]

Additional context

No response

[swuecho]

my guess is because text or value in records. The query is assuming value always appears.

[swuecho]

This is the only incompatible behaviour I found when upgrade from 1.4. The new 2.0 feel much better. Thanks for the great work.

[zzzeek]

this is looking like a psycopg2 bug. pg8000 and psycopg drivers work fine

[zzzeek]

here's a psycopg2 test case, however it raises on psycopg as well when i use this same SQL. so this might not be the driver. ive taken out all the problematic names "value" and "text"

import psycopg2

conn = psycopg2.connect(
    user="scott", password="tiger", host="localhost", dbname="test"
)

cursor = conn.cursor()

cursor.execute(
    """
CREATE TABLE IF NOT EXISTS answer_test (
	id SERIAL NOT NULL,
	rid VARCHAR(10),
	qid VARCHAR,
	val INTEGER[],
	tx VARCHAR,
	PRIMARY KEY (id)
)

"""
)


cursor.execute(
    """
INSERT INTO answer_test (rid, qid, val, tx)
SELECT p0::VARCHAR, p1::VARCHAR, p2::INTEGER[], p3::VARCHAR FROM
(
VALUES (
    %(rid__0)s, %(qid__0)s, %(val__0)s::INTEGER[], %(tx__0)s, 0),
    (%(rid__1)s, %(qid__1)s, %(val__1)s::INTEGER[], %(tx__1)s, 1),
    (%(rid__2)s, %(qid__2)s, %(val__2)s::INTEGER[], %(tx__2)s, 2)
) AS imp_sen(p0, p1, p2, p3, sen_counter)
ORDER BY sen_counter RETURNING answer_test.id, answer_test.id AS id__1

""",
    {
        "qid__0": "Q1",
        "val__0": [3],
        "tx__0": None,
        "rid__0": "testS00000",
        "qid__1": "Q3",
        "val__1": None,
        "tx__1": "somestring",
        "rid__1": "testS00000",
        "qid__2": "Q7",
        "val__2": None,
        "tx__2": 33,
        "rid__2": "testS00000",
    },
)

[zzzeek]

you're passing a raw integer 33 into a text field and this confuses PostgreSQL server for those driver backends where there is not an explicit ::VARCHAR cast. this cast is incidental and is not guaranteed to be present, there's no guarantee that you can pass integers into text fields and not have an error.

program as given works when correct data is passed:

answers = [
    {"qid": "Q1", "rid": "testS00000", "value": [3], "text": None},
    {"qid": "Q2", "rid": "testS00000", "value": [2, 4], "text": None},
    {"qid": "Q3", "rid": "testS00000", "value": None, "text": "text"},
    {"qid": "Q4", "rid": "testS00000", "value": None, "text": "text area"},
    {"qid": "Q5", "rid": "testS00000", "value": [2], "text": None},
    {"qid": "Q7", "rid": "testS00000", "value": None, "text": "33"},
]

[swuecho]

you're passing a raw integer 33 into a text field and this confuses PostgreSQL server for those driver backends where there is not an explicit ::VARCHAR cast. this cast is incidental and is not guaranteed to be present, there's no guarantee that you can pass integers into text fields and not have an error.

program as given works when correct data is passed:

answers = [
    {"qid": "Q1", "rid": "testS00000", "value": [3], "text": None},
    {"qid": "Q2", "rid": "testS00000", "value": [2, 4], "text": None},
    {"qid": "Q3", "rid": "testS00000", "value": None, "text": "text"},
    {"qid": "Q4", "rid": "testS00000", "value": None, "text": "text area"},
    {"qid": "Q5", "rid": "testS00000", "value": [2], "text": None},
    {"qid": "Q7", "rid": "testS00000", "value": None, "text": "33"},
]

indeed, thanks. I was confused by the wrong stack trace. Thanks for the insight.