on_conflict_do_update assumes the compiler statement is the insert

Issue #4074 resolved
Michael Bayer
repo owner created an issue
from sqlalchemy import table, column, select, literal_column
from sqlalchemy.dialects.postgresql import insert
from sqlalchemy.dialects import postgresql

foo = table("foo", column("foo_id"), column("foo_data"))
refreshed_foo = table("refreshed_foo", column("foo_id"), column("foo_data"))
new_data = table("new_data", column("foo_id"), column("foo_data"))

select_query = select([new_data])
ins = insert(foo).from_select(select_query.columns.keys(), select_query)

upsert = ins.on_conflict_do_update(
    index_elements=[foo.c.foo_id],
    set_={"foo_data": ins.excluded.foo_data}
).returning(literal_column('1').label('x')).cte("perform_inserts")

stmt = select([upsert.c.x])

print stmt.compile(dialect=postgresql.dialect())

output:

 File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 1412, in visit_cte
    self, asfrom=True, **kwargs
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 2104, in visit_insert
    insert_stmt._post_values_clause, **kw)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 242, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/dialects/postgresql/base.py", line 1535, in visit_on_conflict_do_update
    cols = insert_statement.table.c
AttributeError: 'Select' object has no attribute 'table'

patch:

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index b56ac5b10..821752870 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1529,7 +1529,9 @@ class PGCompiler(compiler.SQLCompiler):

         set_parameters = dict(clause.update_values_to_set)
         # create a list of column assignment clauses as tuples
-        cols = self.statement.table.c
+
+        insert_statement = self.stack[-1]['selectable']
+        cols = insert_statement.table.c
         for c in cols:
             col_key = c.key
             if col_key in set_parameters:

Comments (3)

  1. Michael Bayer reporter

    use the stack to get the insert statement in on conflict

    Fixed bug in Postgresql :meth:.postgresql.dml.Insert.on_conflict_do_update which would prevent the insert statement from being used as a CTE, e.g. via :meth:.Insert.cte, within another statement.

    Change-Id: Ie20972a05e194290bc9d92819750845872949ecc Fixes: #4074

    → <<cset 70516536107a>>

  2. Log in to comment