Hi, I have a problem using SQLAlchemy 0.4 when deleting referenced objects in a PostgreSQL database and adding new ones within the same transaction. Originally, I found the problem with Elixir 0.4.0-pre (svn r216) and SQLAlchemy 0.3.11 - http://groups.google.com/group/sqlelixir/browse_thread/thread/b03a96e3ba9e61ea
Probably the problem can only be reproduced using a database which enforces ForeignKey constraints (Postgresql does). I don't know much about SQLAlchemy but I tried to create an SQLAlchemy script which reproduces the behavior (the original Elixir test case can be found on http://pastebin.com/f3307e3c0 ). I hope the script exhibits really the same problem as my Elixir script... Example snippet (complete script http://pastebin.com/f6057bdbf ): ----------------------------------------------------------------------------------- foo = session.query(User).filter_by(name='Foo Bar').one() session.save(foo) for address in foo.addresses: foo.addresses.remove(address) session.delete(address) session.delete(foo) foo = User() session.save(foo) foo.id = 1 foo_addr = Address() session.save(foo_addr) foo_addr.street = "Picadelly Circus" foo.addresses.append(foo_addr) transaction.commit() ----------------------------------------------------------------------------------- This gives me the following traceback (complete output: http://pastebin.com/f28f0e198 , original Elixir traceback http://pastebin.com/f5ae5c7c ): ----------------------------------------------------------------------------------- Traceback (most recent call last): File "./sqlalchemy_foreignkeys.py", line 88, in ? transaction.commit() ... File "/home/fs/lokal/python-modules/SQLAlchemy-0.4.0-py2.4.egg/sqlalchemy/engine/base.py", line 852, in __execute_raw self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File "/home/fs/lokal/python-modules/SQLAlchemy-0.4.0-py2.4.egg/sqlalchemy/engine/base.py", line 869, in _cursor_execute raise exceptions.DBAPIError.instance(statement, parameters, e) sqlalchemy.exceptions.IntegrityError: (IntegrityError) Aktualisieren oder Löschen in Tabelle »Address« verletzt Fremdschlüssel-Constraint »users_addresses__Address_Address_id_fkey« von Tabelle »users_addresses__Address« DETAIL: Auf Schlüssel (id)=(1) wird noch aus Tabelle »users_addresses__Address« verwiesen. 'DELETE FROM "Address" WHERE "Address".id = %(id)s' {'id': 1} ----------------------------------------------------------------------------------- Sorry for the German exception message, I did not manage to get an English one despite switching the system locale to en_US. Here is a rough translation to English: Update or deletion of table »Address« violates foreign key constraint »users_addresses__Address_Address_id_fkey« of table »users_addresses__Address« DETAIL: Table »users_addresses__Address« still references key (id)=(1). SQL trace: ----------------------------------------------------------------------------------- BEGIN SELECT users.id AS users_id, users.name AS users_name FROM users WHERE users.name = %(users_name)s ORDER BY users.id LIMIT 2 OFFSET 0 {'users_name': 'Foo Bar'} SELECT "Address".id AS "Address_id", "Address".street AS "Address_street" FROM "Address", "users_addresses__Address" WHERE %(param_1)s = "users_addresses__Address".user_id AND "users_addresses__Address"."Address_id" = "Address".id {'param_1': 1} UPDATE users SET name=%(name)s WHERE users.id = %(users_id)s {'users_id': 1, 'name': None} select nextval('"Address_id_seq"') None INSERT INTO "Address" (id, street) VALUES (%(id)s, %(street)s) {'street': 'Picadelly Circus', 'id': 2L} INSERT INTO "users_addresses__Address" (user_id, "Address_id") VALUES (%(user_id)s, %(Address_id)s) {'Address_id': 2L, 'user_id': 1} DELETE FROM "Address" WHERE "Address".id = %(id)s {'id': 1} ROLLBACK ----------------------------------------------------------------------------------- I think the problem is the order of the SQL deletion statements. The item in »users_addresses__Address« must be deleted before deleting the address. Is this behavior by design? Do I abuse the SQLAlchemy api? fs --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
