suggestion to use deferrable foreign key constraints

Discussion:

Luke Stebbing

2007-02-22 18:15:37 UTC

Are there any plans to handle circular dependencies by using
deferrable foreign key constraints when available?

In my case, I had made the foreign key constraints deferred, but
SQLAlchemy didn't pick up on that when I reflected the database
metadata. I eliminated the circular dependency by using
post_update=True, but that meant dropping a NOT NULL constraint since
postgres can't defer those (sigh).

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to ***@googlegroups.com
To unsubscribe from this group, send email to sqlalchemy-***@googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---

Michael Bayer

2007-02-22 20:20:54 UTC

Permalink

ive heard of foreign key constraints that dont take effect until the
tranasction actually commits, but I have never actually seen this in
practice. which databases support this feature ? i didnt think it
was so common (though not surprised PG supports it).

Post by Luke Stebbing
Are there any plans to handle circular dependencies by using
deferrable foreign key constraints when available?
In my case, I had made the foreign key constraints deferred, but
SQLAlchemy didn't pick up on that when I reflected the database
metadata. I eliminated the circular dependency by using
post_update=True, but that meant dropping a NOT NULL constraint since
postgres can't defer those (sigh).

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to ***@googlegroups.com
To unsubscribe from this group, send email to sqlalchemy-***@googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---

Luke Stebbing

2007-02-23 20:03:41 UTC

Permalink

PG and Oracle allow you to defer foreign key constraints (Oracle
apparently lets you defer *all* constraints, mmm), and MySQL and
SQLite (of course) don't. I'm not sure about other databases. The SQL
keyword in question is DEFERRABLE.

References:

http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html
http://www.postgresql.org/docs/8.2/interactive/sql-set-constraints.html

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/general.htm#i1006803
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses002.htm#sthref2933
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10003.htm#i2066960

Post by Michael Bayer
ive heard of foreign key constraints that dont take effect until the
tranasction actually commits, but I have never actually seen this in
practice. which databases support this feature ? i didnt think it
was so common (though not surprised PG supports it).

Michael Bayer

2007-02-24 19:35:06 UTC

Permalink

well there would have to be some flag to not register a "dependency"
between classes A and B (or rows C and D). the "post_update" flag
actually does this, but comes with the extra "update this value
later" behavior.

with the dependency removed, the topological sort wont need to
fulfill that part of the sort and wont raise the circular sorting
issue. however, with constraints removed, doesnt mean we can do
operations in just any old order. namely, if a row in table B
references table A, and A is to be inserted, if A does not yet have a
primary key value generated we would still have to insert A first,
since the generation of primary keys is necessarily bundled with
INSERT operations, which of course is because not every database
supports sequences and we have to rely on cursor.lastrowid and stuff
like that.

this might be something youre better off doing by not even
establishing the post_updated relation() at all (or establishing the
relation as "viewonly"), and just implementing yourself a series of
before_insert() MapperExtensions that populate the "post_updated"
value and possibly also pre-generates the primary key column ahead of
when its normally generated....since the model you want depends on
the primary key value of the child object being generated before the
parent object is inserted, and thats not normal ORM behavior.
remember that you can set all the PK/FK attributes you want on your
instances, either before flush() or within before_insert()
operations, and SA will use those values when inserting the rows for
the instance if they are present.

Post by Luke Stebbing
PG and Oracle allow you to defer foreign key constraints (Oracle
apparently lets you defer *all* constraints, mmm), and MySQL and
SQLite (of course) don't. I'm not sure about other databases. The SQL
keyword in question is DEFERRABLE.
http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html
http://www.postgresql.org/docs/8.2/interactive/sql-set-
constraints.html
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/
general.htm#i1006803
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/
clauses002.htm#sthref2933
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/
statements_10003.htm#i2066960

Luke Stebbing

2007-02-24 23:24:42 UTC

Permalink

Wow, I didn't know that MapperExtensions made Mappers so flexible.
Thanks, I'll look into doing that.

Post by Michael Bayer
well there would have to be some flag to not register a "dependency"
between classes A and B (or rows C and D). the "post_update" flag
actually does this, but comes with the extra "update this value
later" behavior.
with the dependency removed, the topological sort wont need to
fulfill that part of the sort and wont raise the circular sorting
issue. however, with constraints removed, doesnt mean we can do
operations in just any old order. namely, if a row in table B
references table A, and A is to be inserted, if A does not yet have a
primary key value generated we would still have to insert A first,
since the generation of primary keys is necessarily bundled with
INSERT operations, which of course is because not every database
supports sequences and we have to rely on cursor.lastrowid and stuff
like that.
this might be something youre better off doing by not even
establishing the post_updated relation() at all (or establishing the
relation as "viewonly"), and just implementing yourself a series of
before_insert() MapperExtensions that populate the "post_updated"
value and possibly also pre-generates the primary key column ahead of
when its normally generated....since the model you want depends on
the primary key value of the child object being generated before the
parent object is inserted, and thats not normal ORM behavior.
remember that you can set all the PK/FK attributes you want on your
instances, either before flush() or within before_insert()
operations, and SA will use those values when inserting the rows for
the instance if they are present.