[Django] #12180: ProgrammingError thrown with autocommit: True if first query on PostgreSQL >= 8.2 is an INSERT

[Django-3]

#12180: ProgrammingError thrown with autocommit: True if first query on PostgreSQL
>= 8.2 is an INSERT
------------------------------------------+---------------------------------
 Reporter:  Xof                           |       Owner:  nobody    
   Status:  new                           |   Milestone:            
Component:  Database layer (models, ORM)  |     Version:  1.1      
 Keywords:                                |       Stage:  Unreviewed
Has_patch:  0                             |  
------------------------------------------+---------------------------------
 There is a bug in the handling of
 InsertQuery.connection.features.can_return_id_from_insert, which is
 causing Django 1.1.1 to throw a ProgrammingError exception when inserting
 a new object/record into the database, using PostgreSQL 8.4.1, using
 psycopg2, if that INSERT is the first thing done by a view on a particular
 connection to the database, when DATABASE_OPTIONS autocommit: True is set.

 The details are:

 1. Django uses InsertQuery.connection.features.can_return_id_from_insert
 to decide whether or not append a RETURNING clause to the INSERT, so that
 it can get the primary key of a newly-inserted object
 (db/models/sql/subqueries.py, lines 311-315).

 2. That flag is set in the _cursor method of DatabaseWrapper
 (db/backends/postgresql_psycopg2/base.py, lines 106-121), but the cursor
 hasn't been created yet in step #1, so can_return_id_from_insert is always
 False.

 3. But Django then issues the query (creating the cursor, and correctly
 setting can_return_id_from_insert to True), and thus expects a return
 value to come back the INSERT statement, but since the RETURNING clause
 wasn't added in step #1, it throws a ProgrammingError exception when it
 tries to get the expected return value (db/models/sql/subqueries.py, lines
 323-324).

 This appears to be a bad interaction with autocommit: True, since the code
 is clearly expecting a transaction to already have been opened at this
 point, which would have set the version information and the
 can_return_id_from_insert to True.

--
Ticket URL: <http://code.djangoproject.com/ticket/12180>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django updates" group.
To post to this group, send email to django-updates@...
To unsubscribe from this group, send email to django-updates+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---

[Django-3]

#12180: ProgrammingError thrown with autocommit: True if first query on PostgreSQL
>= 8.2 is an INSERT
---------------------------------------------------+------------------------
          Reporter:  Xof                           |         Owner:  nobody
            Status:  new                           |     Milestone:        
         Component:  Database layer (models, ORM)  |       Version:  1.1  
        Resolution:                                |      Keywords:        
             Stage:  Unreviewed                    |     Has_patch:  0    
        Needs_docs:  0                             |   Needs_tests:  0    
Needs_better_patch:  0                             |  
---------------------------------------------------+------------------------
Changes (by Xof):

  * needs_better_patch:  => 0
  * needs_tests:  => 0
  * needs_docs:  => 0

Comment:

 Looking at it a bit further, I'm not quite sure why
 can_return_id_from_insert depends on uses_autocommit.

--
Ticket URL: <http://code.djangoproject.com/ticket/12180#comment:1>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django updates" group.
To post to this group, send email to django-updates@...
To unsubscribe from this group, send email to django-updates+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---

[Django-3]

#12180: ProgrammingError thrown with autocommit: True if first query on PostgreSQL
>= 8.2 is an INSERT
---------------------------------------------------+------------------------
          Reporter:  Xof                           |         Owner:  nobody
            Status:  new                           |     Milestone:        
         Component:  Database layer (models, ORM)  |       Version:  1.1  
        Resolution:                                |      Keywords:        
             Stage:  Unreviewed                    |     Has_patch:  0    
        Needs_docs:  0                             |   Needs_tests:  0    
Needs_better_patch:  0                             |  
---------------------------------------------------+------------------------
Comment (by Xof):

 Looking at #10467, I now get it: autocommit = True is being used as a
 (checked) assertion that the PG version is >= 8.2, so that the initially-
 constructed SQL on an INSERT doesn't include a RETURNING for PG versions
 <8.2... sadly, it seems to have just moved the bug to a different place.

--
Ticket URL: <http://code.djangoproject.com/ticket/12180#comment:2>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django updates" group.
To post to this group, send email to django-updates@...
To unsubscribe from this group, send email to django-updates+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---

[Django-3]

#12180: ProgrammingError thrown with autocommit: True if first query on PostgreSQL
>= 8.2 is an INSERT
---------------------------------------------------+------------------------
          Reporter:  Xof                           |         Owner:  nobody
            Status:  new                           |     Milestone:        
         Component:  Database layer (models, ORM)  |       Version:  1.1  
        Resolution:                                |      Keywords:        
             Stage:  Unreviewed                    |     Has_patch:  0    
        Needs_docs:  0                             |   Needs_tests:  0    
Needs_better_patch:  0                             |  
---------------------------------------------------+------------------------
Comment (by Xof):

 I've attached a proposed patch. The patch just sets
 "self.features.can_return_id_from_insert" based on the autocommit setting.
 This is, of course, a hack (although not really any more hacky than the
 current code). This allows SQL-generation operations before cursor
 creation to get a correct value for
 self.features.can_return_id_from_insert. Of course, autocommit could be
 lying and the version of PostgreSQL could be such that autocommit (and
 self.features.can_return_id_from_insert) don't apply, but this will be
 checked before any queries are actually sent to the db.

--
Ticket URL: <http://code.djangoproject.com/ticket/12180#comment:3>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django updates" group.
To post to this group, send email to django-updates@...
To unsubscribe from this group, send email to django-updates+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---

[Django-3]

#12180: ProgrammingError thrown with autocommit: True if first query on PostgreSQL
>= 8.2 is an INSERT
---------------------------------------------------+------------------------
          Reporter:  Xof                           |         Owner:  nobody
            Status:  new                           |     Milestone:        
         Component:  Database layer (models, ORM)  |       Version:  1.1  
        Resolution:                                |      Keywords:        
             Stage:  Unreviewed                    |     Has_patch:  0    
        Needs_docs:  0                             |   Needs_tests:  0    
Needs_better_patch:  0                             |  
---------------------------------------------------+------------------------
Comment (by Xof):

 I've attached another patch, this one with some redundant code removed.
 This doesn't solve the full issue described in #10509, but it does not
 reopen the prior bug of sending an INSERT involving a RETURNING to a
 version of PostgreSQL < 8.2, as the autocommit will cause the version to
 be checked, and an error raised if it's not >=8.2.

--
Ticket URL: <http://code.djangoproject.com/ticket/12180#comment:4>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django updates" group.
To post to this group, send email to django-updates@...
To unsubscribe from this group, send email to django-updates+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---

[Django-3]

#12180: ProgrammingError thrown with autocommit: True if first query on PostgreSQL
>= 8.2 is an INSERT
---------------------------------------------------+------------------------
          Reporter:  Xof                           |         Owner:  Xof
            Status:  assigned                      |     Milestone:    
         Component:  Database layer (models, ORM)  |       Version:  1.1
        Resolution:                                |      Keywords:    
             Stage:  Unreviewed                    |     Has_patch:  0  
        Needs_docs:  0                             |   Needs_tests:  0  
Needs_better_patch:  0                             |  
---------------------------------------------------+------------------------
Changes (by Xof):

  * owner:  nobody => Xof
  * status:  new => assigned

--
Ticket URL: <http://code.djangoproject.com/ticket/12180#comment:5>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django updates" group.
To post to this group, send email to django-updates@...
To unsubscribe from this group, send email to django-updates+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---

[Django-3]

#12180: ProgrammingError thrown with autocommit: True if first query on PostgreSQL
>= 8.2 is an INSERT
---------------------------------------------------+------------------------
          Reporter:  Xof                           |         Owner:  Xof
            Status:  assigned                      |     Milestone:    
         Component:  Database layer (models, ORM)  |       Version:  1.1
        Resolution:                                |      Keywords:    
             Stage:  Unreviewed                    |     Has_patch:  1  
        Needs_docs:  0                             |   Needs_tests:  0  
Needs_better_patch:  0                             |  
---------------------------------------------------+------------------------
Changes (by Xof):

  * has_patch:  0 => 1

--
Ticket URL: <http://code.djangoproject.com/ticket/12180#comment:6>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django updates" group.
To post to this group, send email to django-updates@...
To unsubscribe from this group, send email to django-updates+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---