Re: [Django] #32688: The ON CONFLICT sql suffix creates a syntax error on m2m inserts

[Django]

#32688: The ON CONFLICT sql suffix creates a syntax error on m2m inserts
-+-
 Reporter:  Malik A. Rumi|Owner:  nobody
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  3.2
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  needsinfo
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Mariusz Felisiak):

 * status:  new => closed
 * resolution:   => needsinfo


Comment:

 I cannot reproduce this issue on PostgreSQL 11. Can you provide an SQL
 statement which crashes? or a sample project? and confirm your database
 version.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.144067c25dab409a2cebce49fb279d3b%40djangoproject.com.

Re: [Django] #32688: The ON CONFLICT sql suffix creates a syntax error on m2m inserts

[Django]

#32688: The ON CONFLICT sql suffix creates a syntax error on m2m inserts
-+-
 Reporter:  Malik A. Rumi|Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  3.2
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Patrick K):

 * cc: Patrick K (added)
 * status:  closed => new
 * resolution:  invalid =>
 * type:  Uncategorized => Bug


Comment:

 I'm not 100% sure, but I think this needs to be reopened. I can easily
 reproduce this with the admin interface when trying to save an M2M with
 Postgres 11. I do understand that Postgres 9.x is not supported anymore,
 but IMHO this should not happen with Postgres 11.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.28743b84f290e55a1854317fe32bc278%40djangoproject.com.

Re: [Django] #32688: The ON CONFLICT sql suffix creates a syntax error on m2m inserts

[Django]

#32688: The ON CONFLICT sql suffix creates a syntax error on m2m inserts
-+-
 Reporter:  Malik A. Rumi|Owner:  nobody
 Type:  Uncategorized|   Status:  closed
Component:  Database layer   |  Version:  3.2
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  invalid
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by sehmaschine):

 I'm having the same issue with Postgres 11. Just trying to update an M2M
 field (with both `set` or `add`) leads to this error.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.b4a0b905ce71d591d757f7624767da55%40djangoproject.com.

Re: [Django] #32688: The ON CONFLICT sql suffix creates a syntax error on m2m inserts

[Django]

#32688: The ON CONFLICT sql suffix creates a syntax error on m2m inserts
-+-
 Reporter:  Malik A. Rumi|Owner:  nobody
 Type:  Uncategorized|   Status:  closed
Component:  Database layer   |  Version:  3.2
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  invalid
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Simon Charette):

 This is certainly the clause. PostgreSQL < 9.5 simply doesn't support the
 `ON CONFLICT` statement.

 https://www.postgresql.org/docs/9.5/release-9-5.html#AEN131764

 FWIW PostgresSQL 9.5 is EOL and 9.6 will also be in this situation a few
 weeks from now.

 https://www.postgresql.org/support/versioning/

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.91eac5f64a156bbe20dbd96843d7b061%40djangoproject.com.

Re: [Django] #32688: The ON CONFLICT sql suffix creates a syntax error on m2m inserts

[Django]

#32688: The ON CONFLICT sql suffix creates a syntax error on m2m inserts
-+-
 Reporter:  Malik A. Rumi|Owner:  nobody
 Type:  Uncategorized|   Status:  closed
Component:  Database layer   |  Version:  3.2
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  invalid
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Malik A. Rumi):

 Yes, I am upgrading tonight. Do you think this is the cause? Can you
 explain how, or point me to some documents that will do the explaining?
 Thanks.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.72fb73d14afd686b9e63900a58c917f3%40djangoproject.com.

Re: [Django] #32688: The ON CONFLICT sql suffix creates a syntax error on m2m inserts

[Django]

#32688: The ON CONFLICT sql suffix creates a syntax error on m2m inserts
-+-
 Reporter:  Malik A. Rumi|Owner:  nobody
 Type:  Uncategorized|   Status:  closed
Component:  Database layer   |  Version:  3.2
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  invalid
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Mariusz Felisiak):

 * status:  new => closed
 * resolution:   => invalid
 * component:  Uncategorized => Database layer (models, ORM)


Comment:

 It looks that you're using unsupported version of PostgreSQL i.e. < 9.5.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.0e16696e22e8c67bd709d3662e2d4c18%40djangoproject.com.

[Django] #32688: The ON CONFLICT sql suffix creates a syntax error on m2m inserts

[Django]

#32688: The ON CONFLICT sql suffix creates a syntax error on m2m inserts
-+
   Reporter:  Malik A. Rumi  |  Owner:  nobody
   Type:  Uncategorized  | Status:  new
  Component:  Uncategorized  |Version:  3.2
   Severity:  Normal |   Keywords:
   Triage Stage:  Unreviewed |  Has patch:  0
Needs documentation:  0  |Needs tests:  0
Patch needs improvement:  0  |  Easy pickings:  0
  UI/UX:  0  |
-+
 When I made the leap from 1.11.5 to 3.1.1, I ran into this issue: Whenever
 I tried to insert on an m2m related model / field, I got this error:
 {{{
 ProgrammingError('syntax error at or near "ON"\nLINE 1: ...ry_tags"
 ("entry_id", "tag_id") VALUES (3353, 31) ON CONFLIC...\n
 }}}
 I have never seen this error before, and the fact that it is excessively
 truncated [ one might even say, 'butchered' ], didn't help. The problem
 was that Django was adding this 'sql suffix' - "ON CONFLICT, DO NOTHING",
 to the end of my insert statement, and then Django turned around and
 treated it as a syntax error on my inserts. ''But there was **no**
 conflict!  So the solution was obvious: stop Django from adding this
 unnecessary and irrelevant suffix.

 A lot of digging led me to two files:
 /home/malikarumi/.virtualenvs/legacy_insert/lib/python3.8/site-
 packages/django/db/backends/base/operations.py
 /home/malikarumi/.virtualenvs/legacy_insert/lib/python3.8/site-
 packages/django/db/backends/postgresql/operations.py

 In base/operations, the kwarg ''ignore_conflicts'' is set to **False**,
 while in postgresql/operations.py, it is set to **None**. Was this
 intentional? Because as I will show you in a moment, the code has examples
 of this variable taking on [True, False, None, and
 self.query.ignore_conflicts]. Digging further, I looked at
 /home/malikarumi/.virtualenvs/legacy_insert/lib/python3.8/site-
 packages/django/db/models/sql/subqueries.py, where ignore_conflicts is
 initially set to False in class InsertQuery(Query), and
 /home/malikarumi/.virtualenvs/legacy_insert/lib/python3.8/site-
 packages/django/db/models/sql/compiler.py, where it is set to
 self.query.ignore_conflicts.

 I found what I was looking for on lines 1382-3 of compiler.py:
 # if ignore_conflicts_suffix_sql:
 # result.append(ignore_conflicts_suffix_sql)
 and on lines 284-5 of postgresql/operations.py:
 # def ignore_conflicts_suffix_sql(self, ignore_conflicts=None):
 # return 'ON CONFLICT DO NOTHING' if ignore_conflicts else
 super().ignore_conflicts_suffix_sql(ignore_conflicts)

 After commenting them both out, my inserts proceed as normal. So, maybe
 I'm the only one with this issue. I don't know. I don't know why
 insert_conflicts seems to be able to take any arbitrary value. Even if it
 is a nullable Boolean, True, False, and None are not ==, so if False is
 passed down to a method expecting it to be None, then the alternate
 condition is going to be triggered. I don't know why this sql suffix was
 attached to my insert when there was no conflict, causing a syntax error
 and preventing me from adding new data.

 If you think this is a bug, great. If not, at least I fixed the issue for
 me. Thanks.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/052.ae222a1e00d532562c1e48b11e1bcf84%40djangoproject.com.