ON CONFLICT index_where (index_predicate) ... missing FROM-clause entry for table....

[Pawel]

PostgreSQL 9.5 SQLAlchemy 1.1.3

Hi, got this error and I'm pretty sure it didn't occur in version beta 1.1.b3 was it? I got a similar problem then and at first I thought it can be related to #3807. But after some digging I must admit I'm a bit puzzled by PostgreSQL behavior. Lack of good ON CONFLICT examples with index_where (index_predicate) and partial indexes in PostgreSQL doc doesn't help.

The problem seems to be that PostgreSQL doesn't allow any qualification in WHERE clause between ON CONFLICT and DO UPDATE phrases (index_predicate in doc).

So this is wrong:

insert into users_xtra AS u (name, login_email, lets_index_this) values ('bla', 'bli', 'ble')
ON CONFLICT (name, lets_index_this) where (user_xtra.lets_index_this = 'unique_name') do update
set name = excluded.name, lets_index_this = excluded.lets_index_this;

and gives an error:

ERROR:  missing FROM-clause entry for table "user_xtra"
LINE 2: ON CONFLICT (name, lets_index_this) where (user_xtra.lets_in...
                                                    ^

This executes without error:

insert into users_xtra AS u (name, login_email, lets_index_this) values ('bla', 'bli', 'ble')
ON CONFLICT (name, lets_index_this) where (lets_index_this = 'unique_name') do update
set name = excluded.name, lets_index_this = excluded.lets_index_this;

Here is the test for this behavior.

diff --git a/test/dialect/postgresql/test_on_conflict.py b/test/dialect/postgresql/test_on_conflict.py
index 154d3fe..bee30c9 100644
--- a/test/dialect/postgresql/test_on_conflict.py
+++ b/test/dialect/postgresql/test_on_conflict.py
@@ -1,5 +1,5 @@
 # coding: utf-8
+from sqlalchemy import text
 from sqlalchemy.testing.assertions import eq_, assert_raises
 from sqlalchemy.testing import fixtures
 from sqlalchemy import testing
@@ -30,6 +30,11 @@ class OnConflictTest(fixtures.TablesTest):
         )
         cls.unique_constraint = schema.UniqueConstraint(
             users_xtra.c.login_email, name='uq_login_email')
+        cls.unique_partial_index = schema.Index(
+            'idx_unique_partial_name',
+            users_xtra.c.name, users_xtra.c.lets_index_this,
+            unique=True,
+            postgresql_where=users_xtra.c.lets_index_this == 'unique_name')
         cls.bogus_index = schema.Index(
             'idx_special_ops',
             users_xtra.c.lets_index_this,
@@ -405,6 +410,44 @@ class OnConflictTest(fixtures.TablesTest):
                     lets_index_this='bogus')
             )

+    def test_on_conflict_do_update_exotic_targets_six(self):
+        users = self.tables.users_xtra
+
+        with testing.db.connect() as conn:
+            # use partial index
+            i = insert(users)
+            i = i.on_conflict_do_update(
+                index_elements=self.unique_partial_index.columns,
+                # or
+                #index_elements=[users.c.name, users.c.lets_index_this],
+                index_where=self.unique_partial_index.dialect_options['postgresql']['where'],
+                # or
+                #index_where=users.c.lets_index_this == 'unique_name',
+                # but this passes!
+                #  vvv
+                #index_where=text("lets_index_this = 'unique_name'"),
+                set_=dict(
+                    name=i.excluded.name,
+                    login_email=i.excluded.login_email),
+            )
+
+            conn.execute(
+                i,
+                [
+                    dict(name='same_name', login_email='[e-mail]',
+                         lets_index_this='unique_name'),
+                    dict(name='same_name', login_email='[e-mail]',
+                         lets_index_this='unique_name')
+                ]
+            )
+
+            eq_(
+                conn.execute(users.select()).fetchall(),
+                [
+                    (2, 'same_name', '[e-mail]', 'unique_name'),
+                ]
+            )
+
     def test_on_conflict_do_update_no_row_actually_affected(self):
         users = self.tables.users_xtra

So if this in fact is a valid behavior than compiler tests test_do_update_index_elements_where_target, test_do_update_index_elements_where_target_multivalues in file /test/dialect/postgresql/test_on_conflict.py validate wrong results!

[Mike Bayer]

if you're saying that PG is distinguishing between "where (user_xtra.lets_index_this = 'unique_name')" and "(lets_index_this = 'unique_name')", where it's the latter that is correct, that's like ridiculous. Yes, the behavior was different in 1.1b3 because it was also bug #3807, and the test there is expecting that "mytable.description" is present. I can't imagine why PG doesnt' want the table qualification there.

[Mike Bayer]

I really can't know when / if I have this right, or wrong, and am just going to have to keep changing it, putting out releases, and breaking it until it no longer causes problems.

[Mike Bayer]

Put include_table=True for DO UPDATE..WHERE, but not ON CONFLICT

Fixed regression caused by the fix in 3807 (version 1.1.0) where we ensured that the tablename was qualified in the WHERE clause of the DO UPDATE portion of PostgreSQL's ON CONFLICT, however you cannot put the table name in the WHERE clause in the actual ON CONFLICT itself. This was an incorrect assumption, so that portion of the change in 3807 is rolled back.

Change-Id: I442d8629496a8e405b54711cfcf487761810ae8a Fixes: #3846 Fixes: #3807

→ <<cset 942c2429c033>>

[Pawel]

Thanks for the quick response. As I said it is strange, but it looks like ON CONFLICT's WHERE doesn't behave like your 'standard' WHERE ?? If it only involves partial index predicate maybe no qualification is ever needed... but i wouldn't put money on that. Still, it is a PostgreSQL issue.