autocommit, INSERT... RETURNING and PostgreSQL 8.2+

[Christophe Pettus-2]

Greetings,

In looking around the code for the psycogp2 backend, it looks like  
autocommit is used, in part, as a checked assertion that the database  
being used is PG 8.2 or greater.  Comments lead me to believe that the  
reason that autocommit is limited to 8.2+ is that INSERT ... RETURNING  
was introduced into 8.2, and that syntax is required for correct  
operation while autocommit is True.  But I'm not sure I understand the  
reasoning; does anyone know why INSERT ... RETURNING is required in  
that case?

Thanks!
--
-- Christophe Pettus
    xof@...


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django developers" group.
To post to this group, send email to django-developers@...
To unsubscribe from this group, send email to django-developers+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en
-~----------~----~----~----~------~----~------~--~---

[Seb Potter]

Hi Christophe,

When you're running postgres with autocommit and transaction pooling (ie: the lifetime of a connection is a single transaction - essential for high-performance sites), you run the risk of returning an incorrect id by calling:

cursor.execute("SELECT CURRVAL('\"%s_%s_seq\"')" % (table_name, pk_name)).

Because that select query would be running over a different connection.

INSERT ... RETURNING guarantees data integrity when returning the id inserted by a transaction.

Seb Potter
http://www.woome.com/

2009/11/8 Christophe Pettus <xof@...>

Greetings,

In looking around the code for the psycogp2 backend, it looks like
autocommit is used, in part, as a checked assertion that the database
being used is PG 8.2 or greater.  Comments lead me to believe that the
reason that autocommit is limited to 8.2+ is that INSERT ... RETURNING
was introduced into 8.2, and that syntax is required for correct
operation while autocommit is True.  But I'm not sure I understand the
reasoning; does anyone know why INSERT ... RETURNING is required in
that case?

Thanks!
--
-- Christophe Pettus
   xof@...

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django developers" group.
To post to this group, send email to django-developers@...
To unsubscribe from this group, send email to django-developers+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en
-~----------~----~----~----~------~----~------~--~---

[Christophe Pettus-2]

On Nov 8, 2009, at 8:39 AM, Seb Potter wrote:
> transaction pooling

Ah, of course.  Thank you!
--
-- Christophe Pettus
    xof@...


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django developers" group.
To post to this group, send email to django-developers@...
To unsubscribe from this group, send email to django-developers+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en
-~----------~----~----~----~------~----~------~--~---