Deletion of ForeignKeys

[Felix Schwarz]

Hi,

I have a problem using Elixir 0.4.0-pre (svn r216) and SQLAlchemy 0.3.11 when deleting referenced
objects in a PostgreSQL database and adding new ones without flushing.

Example snippet (complete script http://pastebin.com/f3307e3c0):
-----------------------------------------------------------------------------------
for address in foo.addresses:
foo.addresses.remove(address)
address.delete()
foo.delete()
# flushing is a workaround
# objectstore.flush()

foo = User(id=2, name="Foo")
foo.addresses.append(Address(street="Wall Street"))
objectstore.flush()
-----------------------------------------------------------------------------------

This gives me the following traceback (complete output: http://pastebin.com/f5ae5c7c):
(sorry for the German exception message, I did not manage to get an English one despite
switching the system locale to en_US.
-----------------------------------------------------------------------------------
Traceback (most recent call last):
File "./elixir_foreignkeys.py", line 40, in ?
objectstore.flush()
...
File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py", line 581, in _execute_raw
self._execute(context)
File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py", line 599, in _execute
raise exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.SQLError: (IntegrityError) Aktualisieren oder Löschen in Tabelle »Address« verletzt Fremdschlüssel-Constraint
»users_addresses_inverse_fk« 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}
-----------------------------------------------------------------------------------

SQL trace:
-----------------------------------------------------------------------------------
BEGIN
select nextval('"Address_id_seq"')
INSERT INTO "Address" (street, id) VALUES (%(street)s, %(id)s)
{'street': 'Wall Street', 'id': 2L}
UPDATE users SET name=%(name)s WHERE users.id = %(users_id)s
{'users_id': 2, 'name': 'Foo'}
INSERT INTO "users_addresses__Address" (users_id, "Address_id") VALUES (%(users_id)s, %(Address_id)s)
{'users_id': 2, 'Address_id': 2L}
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.

Do you think this is a problem in Elixir/SQLAlchemy or is this expected and I have to use the workaround
by flushing manually before inserting new data?

fs

[Felix Schwarz]

just a short addition: I can reproduce the problem with revision 243 from
trunk. (Probably it's an SQLAlchemy problem but I would like to ensure
that it is really no Elixir problem before asking the SQLAlchemy
developers.)

fs

[Gaetan de Menten]

Could you translate the error message to english?

> SQL trace:
> -----------------------------------------------------------------------------------
> BEGIN
> select nextval('"Address_id_seq"')
> INSERT INTO "Address" (street, id) VALUES (%(street)s, %(id)s)
> {'street': 'Wall Street', 'id': 2L}
> UPDATE users SET name=%(name)s WHERE users.id = %(users_id)s
> {'users_id': 2, 'name': 'Foo'}
> INSERT INTO "users_addresses__Address" (users_id, "Address_id") VALUES (%(users_id)s, %(Address_id)s)
> {'users_id': 2, 'Address_id': 2L}
> 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.
>
> Do you think this is a problem in Elixir/SQLAlchemy or is this expected and I have to use the workaround
> by flushing manually before inserting new data?

I don't think it's a problem with Elixir. That's probably a problem
with SQLAlchemy, but it might be already resolved in SQLAlchemy 0.4. I
think I've seen something related to your problem in the changes. I'm
not really sure though since I don't understand the german SQL error.

--
Gaëtan de Menten
http://openhex.org

[Felix Schwarz]

Gaetan de Menten schrieb:

>> sqlalchemy.exceptions.SQLError: (IntegrityError) Aktualisieren oder Löschen in Tabelle »Address« verletzt Fremdschlüssel-Constraint
>> »users_addresses_inverse_fk« 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}
>> -----------------------------------------------------------------------------------
>
> Could you translate the error message to english?

Update or deletion of table »Address« violates foreign key
constraint »users_addresses_inverse_fk« of table »users_addresses__Address«
DETAIL: Table »users_addresses__Address« still references key (id)=(1).

(I would like to stay with SQLAlchemy 0.3 if possible at all because I am not sure if
TurboGears 1.0.x is already compatible with the new version.)

fs

[Victor Godoy Poluceno]

This problem happens for me too.

The message:

(IntegrityError) null value in column "caso_id" violete the not null
constraint
'UPDATE caso_acao SET caso_id=%(caso_id)s WHERE caso_acao.id = %
(caso_acao_id)s' {'caso_id': None, 'caso_acao_id': 1}

The models:

The class caso:

caso_acao = OneToMany("CasoAcao")

The class caso_acao:

caso = ManyToOne("Caso", required=True, ondelete='cascade')

Elixir-0.4.0.dev_r247
SQLAlchemy-0.4.1dev_r3663
PostgreSQL 8.2

On 25 out, 07:13, Felix Schwarz <felix.schw...@web.de> wrote:
> Hi,
>
> I have a problem using Elixir 0.4.0-pre (svn r216) and SQLAlchemy 0.3.11 when deleting referenced
> objects in a PostgreSQL database and adding new ones without flushing.
>

> Example snippet (complete scripthttp://pastebin.com/f3307e3c0):

[Felix Schwarz]

On Oct 25, 10:39 am, "Gaetan de Menten" <gdemen...@gmail.com> wrote:
> I don't think it's a problem with Elixir.

Yepp, I managed to create a test case with SQLAlchemy 0.4 which shows
the same behavior and will post this on the SQLAlchemy list.

fs