Postgres multi-value insert fails with on_conflict statement

[Basti]

When creating a (postgres specific) insert statement for a table with a enum typed column, the compilation of the statement fails if the enum-column is used in the on_conflict where-condition.

Minimal Example:

import sqlalchemy as _sa
from sqlalchemy.dialects import postgresql as t
from st.db.schema import PrimaryKey as PrimaryKey

test_table = _sa.Table(u'test_table', _sa.MetaData(),
    _sa.Column('id', t.INTEGER, nullable=False, autoincrement=False),
    _sa.Column('state', t.ENUM(u'1st', u'2nd', u'3rd'), nullable=False),
)

PrimaryKey(
    test_table.c.id, 
    name=u'test_table_pkey',
)

PG_INSERT = t.insert

values = [dict(id=100, state=u'1st'),
          dict(id=200, state=u'1st')]

stmt = PG_INSERT(test_table, values)

stmt = stmt.on_conflict_do_update(
    index_elements=['id'], 
    set_=dict(id=1000), 
    where=(stmt.excluded.state == '2nd')
)

str(stmt.compile(dialect=t.dialect()))
# Fails with 'sqlalchemy.exc.CompileError: Bind parameter '%(4378430864 state)s'
# conflicts with unique bind parameter of the same name'

If instead of a list of values, a single dict is used in the insert() call everything is compiled correctly. Also multi-values work if the where-condition is replaced with any other (non-enum) column.

[Mike Bayer]

hi -

thanks for the great test case! It does reproduce for me but Enum is not needed. If I just change the type to String I still get the error. Also I don't know what st.db.schema.PrimaryKey is but replacing that with PrimaryKeyConstraint seems to maintain.

[Mike Bayer]

also, this is not too mysterious. Without the on conflict it wants to do this:

INSERT INTO test_table (id, state) VALUES (%(id_0)s, %(state_0)s), (%(id_1)s, %(state_1)s)

and the ON CONFLICT it wants to do this:

INSERT INTO test_table (id, state) VALUES (%(id)s, %(state)s) ON CONFLICT (id) DO UPDATE SET id = %(param_1)s WHERE excluded.state = %(state_1)s

that is, INSERT .. VALUES is not well designed to accept a WHERE clause as well.

[Basti]

Sorry for the primary-key import issue, forgot to clean up one of the production imports.

As it is not working for INSERT ... VALUES either, I assume there's no easy workaround for now?

[Mike Bayer]

it is actually a conflict of the "anonymous" parameter of the WHERE clause that wants to substitute state_1 at the end, and the "multi valued insert" which is interestingly not using anonymous parameters, just hardcoding <name>_<counter>.

[Mike Bayer]

dont use insert.values(), just pass the list of parameters to execute() to do an executemany.

[Mike Bayer]

Add a qualifying character to multi INSERT..VALUES parameter names

Changed the naming convention used when generating bound parameters for a multi-VALUES insert statement, so that the numbered parameter names don't conflict with the anonymized parameters of a WHERE clause, as is now common in a PostgreSQL ON CONFLICT construct.

Change-Id: I3188d100fe4d322a47d344d6a63d3e40b915f228 Fixes: #3828

→ <<cset f917b353e88a>>

[Basti]

That was blazing fast - Thank you!