Here you go, its a psycopg2 bug. Familiarize yourself with the
attached test case, then post it on the psycopg2 mailing list.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
import psycopg2 as dbapi
# pg8000 produces correct results
#from pg8000 import dbapi
#dbapi.paramstyle = 'pyformat'
def connect():
"""returns a psycopg2 or pg8000 connection to a database"""
return dbapi.connect(user='scott', password='tiger', host='localhost', database='test')
def execute(conn, sql, params=None):
cursor = conn.cursor()
cursor.execute(sql, params)
return cursor
c = connect()
execute(c, """
CREATE TABLE ticket_project (
id SERIAL NOT NULL,
name VARCHAR(20),
PRIMARY KEY (id)
)
""")
execute(c, """
CREATE TABLE timereport_report (
id SERIAL NOT NULL,
job_id INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(job_id) REFERENCES ticket_project (id) DEFERRABLE INITIALLY DEFERRED
)
""")
c.commit()
execute(c, """INSERT INTO ticket_project (id, name) VALUES (%(id)s, %(name)s)""", {'id':1L, 'name':'name1'})
execute(c, """INSERT INTO timereport_report (id, job_id) VALUES (%(id)s, %(job_id)s)""", {'id': 1L, 'job_id': 1})
# doesn't raise since fk is DEFERRED
execute(c, """DELETE FROM ticket_project WHERE ticket_project.id = %(id)s""", {'id': 1})
try:
c.commit()
except Exception, e:
# raises on commit as expected.
print "Caught Expected Error:", e
# rollback
c.rollback()
try:
# BUG: transaction is not rolled back, further commands still fail
execute(c, "SELECT * FROM ticket_project")
except Exception, e:
print "Caught Unexpected Error:", e
try:
# BUG: connection seems to have been shut down
c.rollback()
except Exception, e:
print "Caught Unexpected Error:", e
# new connection works OK
c = connect()
print execute(c, "SELECT * FROM ticket_project").fetchall()
# cleanup
execute(c, """drop table timereport_report""")
execute(c, """drop table ticket_project""")
c.commit()
On Feb 1, 2009, at 3:55 PM, Alessandro Dentella wrote:
>
>> stack trace you posted doesn't make sense to me though, as its
>> issuing
>> a SELECT statement but PG is raising an exception for an UPDATE /
>> DELETE ? I've never seen that before. If you can provide a self-
>> contained test case which reproduces that behavior we can try it out.
>
> Here is is. The behaviuor is as explained both on pg8.2 and 8.3. The
> error is raised only if ForeignKey has initially='DEFERRED' (or the
> database
> has that even if the SA definition does not.
>
> sandro
> *:-)
>
> --------------------------------------------------------------------------
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy import Table, Column, ForeignKey, text, func
> from sqlalchemy.orm import relation, scoped_session, sessionmaker
> from sqlalchemy.types import *
> import sqlalchemy
>
> Base = declarative_base()
> Base.metadata.bind = "postgres://localhost/sa4"
> meta = Base.metadata
>
> Session = scoped_session(
> sessionmaker(autocommit=False, autoflush=False, bind=meta.bind))
> session = Session()
>
> class Project(Base):
> __tablename__ = 'ticket_project'
> id = Column(Integer, primary_key=True)
> name = Column(String(20))
>
> class Report(Base):
> __tablename__ = 'timereport_report'
> id = Column(Integer, primary_key=True)
> job_id = Column('job_id',
> ForeignKey(Project.id,
> deferrable=True, initially='DEFERRED'),
> nullable=False)
>
> def populate():
> meta.create_all()
> p1 = Project(name='TestPrj')
> session.add(p1)
> session.commit()
> p1 = session.query(Project).filter_by(name='TestPrj').one()
>
> r1= Report(job_id=p1.id)
> session.add(r1)
> session.commit()
>
>
> def delete():
> global p1
> p1 = session.query(Project).filter_by(name='TestPrj').one()
> session.delete(p1)
> try:
> session.commit()
> except Exception, e:
> print e
> session.rollback()
>
> populate()
> delete()
> print p1.name
>
>
>
> --~--~---------~--~----~------------~-------~--~----~
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en
> -~----------~----~----~----~------~----~------~--~---
>
