Re: problem with connection.commit

[ thread back from Feb 20, 2010 ], found it from archives...
In reply to http://lists.initd.org/pipermail/psycopg/2010-February/006814.html


I can concurr, but using an older version of psycopg2 [ __version__  
says '2.0.5.1 (dec dt ext pq3)' ]

Happens on both mac [ osx 10.5.8 ] and older linux (kernel  
2.6.22.9-61) against postgres 8.2.11 and 8.4.2; the exception does not  
get raised until the second cursor gets used in the following case:

create table table1 (id integer primary key);
create table table2 (id integer references table1(id) deferrable  
initially deferred);

with python:
# -----
import psycopg2

dsn = '...'
conn = psycopg2.connect(.dsn)

cursor = conn.cursor()
cursor.execute("insert into table2 values (1)")
conn.commit() # Does not throw an exception

cursor2 = conn.cursor()
cursor2.execute("select * from table2")
# -------

Python fails not at conn.commit(), but later at cursor2.execute():

   File "test2.py", line 10, in ?
     cursor2.execute("select * from table2")
psycopg2.OperationalError:   insert or update on table "table2"  
violates foreign key constraint "table2_id_fkey"
DETAIL:  Key (id)=(1) is not present in table "table1".

I'll try to update to latest psycopg2 on osx to see if it still is the  
case.

Doing the insert at psql shows the expected behavior -- complaint and  
forced rollback right at commit().

jlrobins$ psql
psql (8.4.2)
Type "help" for help.

social=#
social=# begin;
BEGIN
social=# insert into table2 values (1);
INSERT 0 1
social=# commit;
ERROR:  insert or update on table "table2" violates foreign key  
constraint "table2_id_fkey"
DETAIL:  Key (id)=(1) is not present in table "table1".



----
James Robinson
Socialserve.com