autocommit, INSERT... RETURNING and PostgreSQL 8.2+

[Christophe Pettus]

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
x...@thebuild.com

[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 <x...@thebuild.com>

[Christophe Pettus]

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

Ah, of course. Thank you!