psycopg, transactions and multiple cursors

[Diez B. Roggisch]

> psycopg.IntegrityError: ERROR:  insert or update on table "y" violates
> foreign key constraint "$1"
> DETAIL:  Key (x_id)=(1) is not present in table "x".
> 
> INSERT INTO y (x_id) VALUES (1)

As the error is about integrity violation, its safe to assume that the
problem is postgresql related.

And changes made in one transaction can't be seen in another until they are
commited _and_ the other transaction is opened after the first one is
commited - that makes sense, as otherwise the second transaction could
create references that then are invalidated by rolling back the first
transaction, creating undefined state.

Now looking at your code, one sees that BEGIN is called on the cursor1 - so
the transaction boundaries are around cursor1, thus they are not visible to
the transaction going on in cursor2. From the psycopg site:

"""
 Every time a new cursor is created, a new connection does not need to be
opened;
"""

So the cursor2 seems to be using its own connection, thus its own
transaction. This is also in the README for psycopg.

As I don't have psycopg running here, I can't experiment myself, but I think
you should use cursor1 for all of your sql statements that belong to one
transaction. 

-- 
Regards,

Diez B. Roggisch