[sqlalchemy] Re: Insertion order not respecting FK relation
Michael Bayer wrote: The most crucial, although not the issue in this specific example, is that the relations table is used both as the secondary table in a relation(), and is also mapped directly to the Relation class. SQLA does not track this fact and even in a working mapping will attempt to insert multiple, redundant rows into the table if you had, for example, appended to the records collection and also created a Relation object. Right; this did seem wrong in the first place. The next issue which is the specific cause of the problem here is that SQLA's topological sort is based off of the relationships between classes and objects, and not directly the foreign key relationships between tables. Specifically, there is no stated relationship between the Record class and the Soup/Collection classes - yet you append a Record object to the records collection which is only meant to store Soup objects. SQLA sees no dependency between the Collection and Record mappers in this case, and the order of table insertion is undefined. This collection append is only possible due to the enable_typechecks=False setting which essentially causes SQLA to operate in a slightly broken mode to allow very specific use cases to work (which are not this one- hence SQLA's behavior is still undefined). enable_typechecks , as the initial error message implied when it mentioned polymorphic mapping, is meant to be used only with inheritance scenarios, and only with objects that are subclasses of the collected object. It suggests that a certain degree of typechecking should remain even if enable_typechecks is set to False (something for me to consider in 0.5). Thank you for clarifying this; at a certain point it was clear to us that SQLA was not equipped to understand what we were doing. I think we somehow expected it to look at the FKs. I've considered someday doing a rewrite of UOW that ultimately bases topological off of ForeignKey and the actual rows to be inserted, and that's it. It's nothing that will happen anytime soon as its a huge job and our current UOW is extremely stable and does a spectacular job for almost two years at this point. But even then, while such an approach might prevent this specific symptom with this specific mapping, it seems like a bad idea in any case to support placing arbitrary, unrelated types into collections that have been defined as storing a certain type. I'm not sure at all if that approach to UOW wouldn't ultmately have all the same constraints as our current approach anyway. Certainly stable is good; strictly looking at FKs only might ultimately make for a simpler implementation though. Fortunately, the solution here is very simple as your table setup is a pure classic joined table inheritance configuration. The attached script (just one script; sorry, all the buildout stuff seemed a little superfluous here) illustrates a straightforward mapping against these tables which only requires that Record and Collection subclass Soup (which is the nature of the joins on those tables). The joins themselves are generated automatically by SQLA so theres no need to spell those out. The enable_typechecks flag is still in use here in its stated use case; that you have a collection which can flush subtypes of Soup, but when queried later, will only return Soup objects. You can improve upon that by using a polymorphic discriminator (see the docs for info on that). Hmm, this solution hadn't occured to me; but it makes a lot of sense. This is great. For what it's worth, we do have a polymorphic rebuilder function in place to bring back to life these soup items. With regards to buildout---it's a habit acquired from the Zope community; it really is a lot less overhead that you might think :-) The script illustrates using the secondary table in the records collection; this is what seems reasonable considering that there is no other meaningful data in the relations table (the surrogate PK in that table is also superfluous). If there are meaningful columns in your actual application's version of the table, then you'd want to do away with secondary and use the association object pattern. We did start out without the secondary table, manually setting up relations, because in fact, we're trying to do an ordered list, which requires a ``position`` column. I'll try to adapt all this into our existing package* and see how it works. Your help is much appreciated. \malthe *) http://pypi.python.org/pypi/z3c.dobbin --~--~-~--~~~---~--~~ 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
[sqlalchemy] Re: Insertion order not respecting FK relation
On Jun 12, 2008, at 4:48 AM, Malthe Borch wrote: Certainly stable is good; strictly looking at FKs only might ultimately make for a simpler implementation though. It starts out as simpler, but that simplicity breaks down almost immediately as the dependency rules, which include rules for populating foreign key columns from source columns, as well as delete/ update operations which need to be cascaded, also need to execute in the proper sequence (largely because newly generated PK values are created in tandem with INSERTs in all cases). Those rules are all derived from the actual objects at play, so it would still be quite complex to link the tables/rows for insert/delete/update to the classes/objects they represent. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Insertion order not respecting FK relation
I can add to this that the issue occurs only on consequent appends. Here's the excerpt that leads to the IntegrityError, demonstrating this. collection = Collection() session.save(collection) session.flush() vinyl = Vinyl() colletion.records.append(vinyl) session.flush() vinyl = Vinyl() colletion.records.append(vinyl) session.flush() The last two flushes are based on the following units-of-work: First append and flush: (Pdb) pp self.tasks.values() [UOWTask(0x27a5f90) Mapper: 'Mapper/Join object on soup(40067600) and __builtin__:ICollection(41259088)', UOWTask(0x27a5ff0) Mapper: 'Mapper/Join object on Join object on soup(40067600) and __builtin__:IAlbum(40262960)(41171024) and __builtin__:IVinyl(41171728)', UOWTask(0x27a5fd0) Mapper: 'Relation/relation'] Second append and flush: (Pdb) pp self.tasks.values() [UOWTask(0x2799fd0) Mapper: 'Mapper/Join object on Join object on soup(40067600) and __builtin__:IAlbum(40262960)(41171024) and __builtin__:IVinyl(41171728)', UOWTask(0x27993b0) Mapper: 'Relation/relation'] For some reason, on the first append, there's a save task defined on the collection-object; I'm not sure what this means, since it should already be saved and flushed at this point. \malthe --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Insertion order not respecting FK relation
you'd have to work this into a full self-contained script which I can run locally since it seems theres some specific usage pattern creating the issue. (i.e. its very difficult for me to piece together snippets and guess where the issue might be occuring). On Jun 11, 2008, at 5:43 AM, Malthe Borch wrote: I can add to this that the issue occurs only on consequent appends. Here's the excerpt that leads to the IntegrityError, demonstrating this. collection = Collection() session.save(collection) session.flush() vinyl = Vinyl() colletion.records.append(vinyl) session.flush() vinyl = Vinyl() colletion.records.append(vinyl) session.flush() The last two flushes are based on the following units-of-work: First append and flush: (Pdb) pp self.tasks.values() [UOWTask(0x27a5f90) Mapper: 'Mapper/Join object on soup(40067600) and __builtin__:ICollection(41259088)', UOWTask(0x27a5ff0) Mapper: 'Mapper/Join object on Join object on soup(40067600) and __builtin__:IAlbum(40262960)(41171024) and __builtin__:IVinyl(41171728)', UOWTask(0x27a5fd0) Mapper: 'Relation/relation'] Second append and flush: (Pdb) pp self.tasks.values() [UOWTask(0x2799fd0) Mapper: 'Mapper/Join object on Join object on soup(40067600) and __builtin__:IAlbum(40262960)(41171024) and __builtin__:IVinyl(41171728)', UOWTask(0x27993b0) Mapper: 'Relation/relation'] For some reason, on the first append, there's a save task defined on the collection-object; I'm not sure what this means, since it should already be saved and flushed at this point. \malthe --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Insertion order not respecting FK relation
Michael Bayer wrote: you'd have to work this into a full self-contained script which I can run locally since it seems theres some specific usage pattern creating the issue. (i.e. its very difficult for me to piece together snippets and guess where the issue might be occuring). This is reasonably self-contained; I've tried to make it as short as possible. src/example/tables.py: All tables and mappers src/example/README.txt: Short demonstration which leads to error You can run the example using: $ python bootstrap.py $ bin/buildout $ bin/test Note that the example requires a Python with a working psycopg2; the testrunner expects a database called test to be available on a running postgres. \malthe --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- example.tar.gz Description: GNU Zip compressed data
[sqlalchemy] Re: Insertion order not respecting FK relation
thanks for this example. There's several issues with this mapping.
The most crucial, although not the issue in this specific example, is
that the relations table is used both as the secondary table in a
relation(), and is also mapped directly to the Relation class. SQLA
does not track this fact and even in a working mapping will attempt to
insert multiple, redundant rows into the table if you had, for
example, appended to the records collection and also created a
Relation object. This is mentioned at the bottom of
http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_patterns_association
but is also more strongly emphasized in the 0.5 docs, since its a
very common mistake (its also not entirely a mistake if the mappings
are used carefully or with the viewonly=True flag, hence we haven't
built a check for this, although its probably something we should do).
The next issue which is the specific cause of the problem here is that
SQLA's topological sort is based off of the relationships between
classes and objects, and not directly the foreign key relationships
between tables. Specifically, there is no stated relationship
between the Record class and the Soup/Collection classes - yet you
append a Record object to the records collection which is only meant
to store Soup objects. SQLA sees no dependency between the
Collection and Record mappers in this case, and the order of table
insertion is undefined. This collection append is only possible due
to the enable_typechecks=False setting which essentially causes SQLA
to operate in a slightly broken mode to allow very specific use
cases to work (which are not this one- hence SQLA's behavior is still
undefined). enable_typechecks , as the initial error message
implied when it mentioned polymorphic mapping, is meant to be used
only with inheritance scenarios, and only with objects that are
subclasses of the collected object. It suggests that a certain
degree of typechecking should remain even if enable_typechecks is
set to False (something for me to consider in 0.5).
I've considered someday doing a rewrite of UOW that ultimately bases
topological off of ForeignKey and the actual rows to be inserted, and
that's it. It's nothing that will happen anytime soon as its a huge
job and our current UOW is extremely stable and does a spectacular job
for almost two years at this point. But even then, while such an
approach might prevent this specific symptom with this specific
mapping, it seems like a bad idea in any case to support placing
arbitrary, unrelated types into collections that have been defined as
storing a certain type. I'm not sure at all if that approach to UOW
wouldn't ultmately have all the same constraints as our current
approach anyway.
Fortunately, the solution here is very simple as your table setup is a
pure classic joined table inheritance configuration. The attached
script (just one script; sorry, all the buildout stuff seemed a little
superfluous here) illustrates a straightforward mapping against these
tables which only requires that Record and Collection subclass Soup
(which is the nature of the joins on those tables). The joins
themselves are generated automatically by SQLA so theres no need to
spell those out. The enable_typechecks flag is still in use here in
its stated use case; that you have a collection which can flush
subtypes of Soup, but when queried later, will only return Soup
objects. You can improve upon that by using a polymorphic
discriminator (see the docs for info on that).
The script illustrates using the secondary table in the records
collection; this is what seems reasonable considering that there is no
other meaningful data in the relations table (the surrogate PK in
that table is also superfluous). If there are meaningful columns in
your actual application's version of the table, then you'd want to do
away with secondary and use the association object pattern.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('postgres://scott:[EMAIL PROTECTED]/test', echo=True)
# set up session
connection = engine.connect()
Session = sessionmaker(autoflush=True, transactional=True)
session = Session(bind=connection)
# set up metadata
metadata = MetaData(engine)
class Soup(object):
pass
class Collection(Soup):
pass
class Relation(object):
pass
class Record(Soup):
pass
soup = Table(
'soup',
metadata,
Column('id', Integer,
[sqlalchemy] Re: Insertion order not respecting FK relation
On Jun 10, 10:34 am, Malthe Borch [EMAIL PROTECTED] wrote:
I have an issue with SQLAlchemy planning to execute insertion tasks in
the wrong order.
Basically, I have a utility table Relations which is used to maintain
ordered list relations:
table = rdb.Table(
'relation',
metadata,
rdb.Column('id', rdb.Integer, primary_key=True, autoincrement=True),
rdb.Column('left', rdb.String(length=32),
rdb.ForeignKey(soup.uuid), index=True),
rdb.Column('right', rdb.String(length=32),
rdb.ForeignKey(soup.uuid)),
rdb.Column('order', rdb.Integer, nullable=False))
Now, I append a new, transient object to such an ordered list. That
means that SQLAlchemy would make two inserts. The problem is that the
tasks are ordered such that the *relation* is inserted before the object
that is the target of the relation!
This obviously raises an IntegrityError, since the foreign key
constraint is not satisfied.
My question is then: How do I tell SQLAlchemy to order them correctly?
\malthe
A self-referential relationship, when configured as many-to-one,
requires the remote_side argument to indicate this, as described in
http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_selfreferential
. Otherwise it defaults to one-to-many.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Insertion order not respecting FK relation
Michael Bayer wrote: A self-referential relationship, when configured as many-to-one, requires the remote_side argument to indicate this, as described in http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_selfreferential . Otherwise it defaults to one-to-many. That sounds correct, but this was not about a self-referential relationship. The Relations table maps a one-to-many relationship from some object to a number of objects (ordered). Or am I missing something? \malthe --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Insertion order not respecting FK relation
would need to see mappings. On Jun 10, 2008, at 11:06 AM, Malthe Borch wrote: Michael Bayer wrote: A self-referential relationship, when configured as many-to-one, requires the remote_side argument to indicate this, as described in http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_selfreferential . Otherwise it defaults to one-to-many. That sounds correct, but this was not about a self-referential relationship. The Relations table maps a one-to-many relationship from some object to a number of objects (ordered). Or am I missing something? \malthe --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Insertion order not respecting FK relation
Michael Bayer wrote:
would need to see mappings.
First, let me mention that this issue only occurs on Postgres; I can't
replicate it on SQLite.
This is the many-to-many relation table (posted previously):
table = rdb.Table(
'relation',
metadata,
rdb.Column('id', rdb.Integer, primary_key=True, autoincrement=True),
rdb.Column('left', rdb.String(length=32),
rdb.ForeignKey(soup.uuid), index=True),
rdb.Column('right', rdb.String(length=32),
rdb.ForeignKey(soup.uuid)),
rdb.Column('order', rdb.Integer, nullable=False))
The soup table:
table = rdb.Table(
'soup',
metadata,
rdb.Column('id', rdb.Integer, primary_key=True, autoincrement=True),
rdb.Column('uuid', rdb.String(length=32), unique=True, index=True),
rdb.Column('spec', rdb.String, index=True),
)
The relation property that should behave like an ordered list:
-
orm.relation(
bootstrap.Relation,
primaryjoin=soup_table.c.uuid==relation_table.c.left,
collection_class=RelationList,
enable_typechecks=False)
I reproduce the problem like so:
1) Append some new item to the list, save and commit.
2) Repeat (1); an ``IntegrityError`` is raised:
IntegrityError: (IntegrityError) insert or update on table relation
violates foreign key constraint relation_right_fkey
DETAIL: Key (right)=(tcbb53226374211dd8a730017f2d1db9) is not
present in table soup.
'INSERT INTO relation (id, left, right, order) VALUES
(%(id)s, %(left)s, %(right)s, %(order)s)' {'left':
'tcbb31e28374211dd8a730017f2d1db9', 'right':
'tcbb53226374211dd8a730017f2d1db9', 'order': 1, 'id': 2L}
Any clues are greatly appreciated.
\malthe
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
