[sqlalchemy] Error in the behaviour of dynamic relation backreferences
I believe that I may have found an error in dynamic relation backrefences. When the dynamic query is executed, it appears to fetch all of the rows in the referenced table instead of only the ones linked to the object from which the dynamic relation was obtained. The following test script illustrates the problem: from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.orm.interfaces import MapperExtension class Parent(object): pass class Child(object): pass meta = ThreadLocalMetaData() Session = sessionmaker(autoflush=True, transactional=True) session = None class ThreadLocalSession(MapperExtension): def get_session(self): return session parents_table = Table( parents, meta, Column(id, Integer, Sequence(parents_id_seq), primary_key=True), Column(name, String(255), nullable=False)) mapper(Parent, parents_table) children_table = Table( children, meta, Column(id, Integer, Sequence(children_id_seq), primary_key=True), Column(name, String(255), nullable=False), Column(parent_id, Integer, ForeignKey(parents.id), nullable=False)) # The mapper extension is necessary, otherwise the dynamic query returned by # Parent.children will not be associated with a session. mapper(Child, children_table, properties=dict( parent=relation(Parent, backref=backref(children, lazy=dynamic))), extension=ThreadLocalSession()) engine = create_engine(sqlite:///:memory:, echo=False) meta.bind = engine session = Session(bind=engine) parents_table.create() for name in [parent1, parent2]: parent = Parent() parent.name = name session.save(parent) session.flush() firstParent = session.query(Parent).get(1) secondParent = session.query(Parent).get(2) children_table.create() for name in [child1, child2, child3, child4]: child = Child() child.name = name if name == child1: child.parent = firstParent else: child.parent = secondParent session.save(child) session.flush() session.commit() session.clear() first_parent = session.query(Parent).get(1) second_parent = session.query(Parent).get(2) num_first_children = first_parent.children.count() num_second_children = second_parent.children.count() assert num_first_children == 1 and num_second_children == 3, \ Children in first parent = %s, in second parent = %s % \ (num_first_children, num_second_children) --~--~-~--~~~---~--~~ 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: SQL error for mapped select (new in version 0.4) on PostgreSQL
Hi, I was looking at this bug fix, and I couldn't figure out where part 2 of the problem was actually fixed. If you can ignore the absurdity of this example, it shows a situation where the second problem still appears to exist: from sqlalchemy import * from sqlalchemy.orm import * metadata = MetaData(mysql://mrlabs:[EMAIL PROTECTED]:3306/test) metadata.bind.echo=True table = Table(test, metadata, Column(id, Integer, primary_key=True), Column(other_id, Integer), Column(active, Boolean)) table2 = Table(test2, metadata, Column(id, Integer, primary_key=True), Column(other_id, Integer), Column(active, Boolean)) table.create() table.insert().execute([{other_id:1, active: False}, {other_id: 2, active: True}]) table2.create() table2.insert().execute([{other_id:1, active: False}, {other_id: 2, active: True}]) test = table.select(table.c.active).alias(test) class Test(object): pass class Test2(object): pass mapper(Test, table) mapper(Test2, table2) session = create_session() q = join(table, table2, table2.c.other_id == table.c.id) s = q.select(use_labels=True).alias(inner_query) s2 = s.select(use_labels=True).alias(outer_query) print session.query(Test, Test2).instances(s2.execute(use_labels=True)) for which I get the output: 2007-12-14 00:27:59,637 INFO sqlalchemy.engine.base.Engine.0x..30 SELECT inner_query.test_id AS inner_query_test_id, inner_query.test_other_id AS inner_query_test_other_id, inner_query.test_active AS inner_query_test_active, inner_query.test2_id AS inner_query_test2_id, inner_query.test2_other_id AS inner_query_test2_other_id, inner_query.test2_active AS inner_query_test2_active FROM (SELECT test.id AS test_id, test.other_id AS test_other_id, test.active AS test_active, test2.id AS test2_id, test2.other_id AS test2_other_id, test2.active AS test2_active FROM test JOIN test2 ON test2.other_id = test.id) AS inner_query 2007-12-14 00:27:59,637 INFO sqlalchemy.engine.base.Engine.0x..30 [] Traceback (most recent call last): File test2.py, line 41, in ? print session.query(Test, Test2).instances(s2.execute(use_labels=True)) File /Users/david/working/env_dandd/lib/python2.4/SQLAlchemy-0.3.11- py2.4.egg/sqlalchemy/orm/query.py, line 1047, in instances self.select_mapper._instance(context, row, result) File /Users/david/working/env_dandd/lib/python2.4/SQLAlchemy-0.3.11- py2.4.egg/sqlalchemy/orm/mapper.py, line 1443, in _instance identitykey = self.identity_key_from_row(row) File /Users/david/working/env_dandd/lib/python2.4/SQLAlchemy-0.3.11- py2.4.egg/sqlalchemy/orm/mapper.py, line 950, in identity_key_from_row return (self.class_, tuple([row[column] for column in self.pks_by_table[self.mapped_table]]), self.entity_name) File /Users/david/working/env_dandd/lib/python2.4/SQLAlchemy-0.3.11- py2.4.egg/sqlalchemy/engine/base.py, line 1172, in __getitem__ return self.__parent._get_col(self.__row, key) File /Users/david/working/env_dandd/lib/python2.4/SQLAlchemy-0.3.11- py2.4.egg/sqlalchemy/engine/base.py, line 993, in _get_col rec = self._convert_key(key) File /Users/david/working/env_dandd/lib/python2.4/SQLAlchemy-0.3.11- py2.4.egg/sqlalchemy/engine/base.py, line 930, in _convert_key raise exceptions.NoSuchColumnError(Could not locate column in row for column '%s' % (str(key))) sqlalchemy.exceptions.NoSuchColumnError: Could not locate column in row for column 'test.id' This is from a checkout of the rel_0_3 branch rev 3936. Thanks, David On Oct 17, 7:07 am, Michael Bayer [EMAIL PROTECTED] wrote: On Oct 17, 2007, at 4:07 AM, klaus wrote: Thanks a lot! The solution is so simple that I feel a little embarassed... im embarrased that bug's been present for so long ! --~--~-~--~~~---~--~~ 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: SQL error for mapped select (new in version 0.4) on PostgreSQL
This example can't work in this sequence, or this query can't be run and mapped? If I wanted to execute this query: SELECT inner_query.test_id AS inner_query_test_id, inner_query.test_other_id AS inner_query_test_other_id, inner_query.test_active AS inner_query_test_active, inner_query.test2_id AS inner_query_test2_id, inner_query.test2_other_id AS inner_query_test2_other_id, inner_query.test2_active AS inner_query_test2_active FROM (SELECT test.id AS test_id, test.other_id AS test_other_id, test.active AS test_active, test2.id AS test2_id, test2.other_id AS test2_other_id, test2.active AS test2_active FROM test JOIN test2 ON test2.other_id = test.id) AS inner_query is my only option to do this query through text and build out my own column_labels dictionary? On Dec 14, 8:06 am, Michael Bayer [EMAIL PROTECTED] wrote: this example as is cant really work in any version because the query doesnt know to map the s2 alias to the mappers that its using, so you have to explicitly connect them. 0.3 has more limited capability to do this. anyway in 0.4 you can do it like this: print session .query (Test ).options (contains_alias(s2)).add_entity(Test2,alias=s2).instances(s2.execute()) and if youre on trunk also like this: print session.query(Test).select_from(s2).add_entity(Test2, alias=s2).all() On Dec 14, 2007, at 3:30 AM, dykang wrote: from sqlalchemy import * from sqlalchemy.orm import * metadata = MetaData(mysql://mrlabs:[EMAIL PROTECTED]:3306/test) metadata.bind.echo=True table = Table(test, metadata, Column(id, Integer, primary_key=True), Column(other_id, Integer), Column(active, Boolean)) table2 = Table(test2, metadata, Column(id, Integer, primary_key=True), Column(other_id, Integer), Column(active, Boolean)) table.create() table.insert().execute([{other_id:1, active: False}, {other_id: 2, active: True}]) table2.create() table2.insert().execute([{other_id:1, active: False}, {other_id: 2, active: True}]) test = table.select(table.c.active).alias(test) class Test(object): pass class Test2(object): pass mapper(Test, table) mapper(Test2, table2) session = create_session() q = join(table, table2, table2.c.other_id == table.c.id) s = q.select(use_labels=True).alias(inner_query) s2 = s.select(use_labels=True).alias(outer_query) print session.query(Test, Test2).instances(s2.execute(use_labels=True)) --~--~-~--~~~---~--~~ 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] 'lazy=False' with polymorphic joined tables
Hi, I was wondering why lazy=False on a polymorphic joined table relation worked fine, until I pass an extra join() to filter with like(%foo%) statments from my search() method. I found an alternative where I just enable the relation's lazyness by providing lazyload(): #OK session.query(Client).all() #(OperationalError) ambiguous column name: companies.id_address session.query(Client).join(address).filter(or_(*OR)).all() #OK session.query(Client).options(lazyload(address)).join(address).filter(or_(*OR)).all() Is this how I should do it ? Or could this be detected, thus avoided ? Test case attached. Thank you. Regards, -- Alexandre CONRAD --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- SA_lazy_polymorphic.py Description: application/python
[sqlalchemy] Re: Error in the behaviour of dynamic relation backreferences
On Friday 14 December 2007 7:41 am, Michael Bayer wrote: seems to work in SVN trunk, have you tried there ? Sorry - I forgot to mention that I ran this test against 0.4.1. I have not tried pulling from the trunk. --~--~-~--~~~---~--~~ 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: LIMIT in queries
Chris M wrote: Or you could use .limit(1). I've always found that more clear than the Python indexing notation because I think of it slicing a list, not applying a limit to a query. Thank you very much for your help. I already found limit before my posting but it did not work, because I used .limit(1).one() where .one() will override the effect of limit(1). After reading your answer I just digged a bit deeper :-) fs smime.p7s Description: S/MIME Cryptographic Signature
[sqlalchemy] Re: Error in the behaviour of dynamic relation backreferences
Works for me on trunk. --~--~-~--~~~---~--~~ 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: LIMIT in queries
On Dec 12, 2007 6:20 AM, King Simon-NFHD78 [EMAIL PROTECTED] wrote: Felix Schwarz wrote: Hi, after reading the docs [1] I thought that something like session.query(User).filter(User.c.id 3)[0] should work even if the filter clause does not return any rows. But - compliant with Python's behavior - SQLAlchemy raises an IndexError. (...) File /usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py, line 577, in __getitem__ return list(self[item:item+1])[0] IndexError: list index out of range I expected that [0] applied to query without results would return None. Did I miss another possibility for LIMIT'ing queries (using sqlalchemy.orm)? fs [1] http://www.sqlalchemy.org/docs/04/ormtutorial.html#datamapping _querying I think being matching Python's behaviour is definitely the right way to go. If you want to get None specifically for index [0], you can use the 'first' method on query. If you are happy get an empty list, you could use a slice and then call .all() The issue is that [0] in Python superficially looks like [A:B] but is in fact something different. [A:B] was added to SQLAlchemy to represent .offset(A).limit(B-A). [0] came along for the ride but has incompatible semantics when the record does not exist. I guess I would favor None because [N]'s closest equivalent is .fetchone() which does that, and if they really wanted [] they should have done [:1]. Raising IndexError is the native Python equivalent, but the whole concept of operator overloading is that we do what makes sense for the object. A query is not a list. If we define [N] as skip N-1 records and do .fetchone(), that's defensible. The only people who will be bothered are existing programs that are expecting IndexError. Are there that many of those, given that I don't think [N] is even documented? -- Mike Orr [EMAIL PROTECTED] --~--~-~--~~~---~--~~ 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: SQL error for mapped select (new in version 0.4) on PostgreSQL
On Dec 14, 2007, at 1:34 PM, dykang wrote: This example can't work in this sequence, or this query can't be run and mapped? If I wanted to execute this query: SELECT inner_query.test_id AS inner_query_test_id, inner_query.test_other_id AS inner_query_test_other_id, inner_query.test_active AS inner_query_test_active, inner_query.test2_id AS inner_query_test2_id, inner_query.test2_other_id AS inner_query_test2_other_id, inner_query.test2_active AS inner_query_test2_active FROM (SELECT test.id AS test_id, test.other_id AS test_other_id, test.active AS test_active, test2.id AS test2_id, test2.other_id AS test2_other_id, test2.active AS test2_active FROM test JOIN test2 ON test2.other_id = test.id) AS inner_query is my only option to do this query through text and build out my own column_labels dictionary? im not seeing how you have that impression? the constructed s2 query in your example produces that exact SQL. as I said, you only need to inform the Query about the s2 alias, using the contains_alias() option for the primary mapper and add_entity() for additional mappers, so that it can translate incoming columns for the mappers. print session .query (Test ).options (contains_alias(s2)).add_entity(Test2,alias=s2).instances(s2.execute()) print session .query (Test ).from_statement (s2).options(contains_alias(s2)).add_entity(Test2,alias=s2).all() --~--~-~--~~~---~--~~ 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] Many-To-One...What I'm doing wrong?
Hello, first at all, sorry about my english... I'm from Mendoza, Argentina, and I'm starting with this. Because I can't describe my problem, I will show the next: from sqlalchemy import * from sqlalchemy.orm import * class Producto(object): pass class Iva(object): pass metadata = MetaData() engine = create_engine('postgres://postgres:[EMAIL PROTECTED]:5432/ marcos') connection = engine.connect() Session = sessionmaker(bind=connection, autoflush=True, transactional=True) session = Session() iva_tabla = Table( 'iva' , metadata , Column('id' , Integer , primary_key = True) , Column('valor', Float) ) productos_tabla = Table ( 'productos' , metadata , Column('id', Integer , primary_key = True ) , Column('nro' , Integer ) , Column('descripcion' , String(100) ) , Column('iva' , Integer , ForeignKey('iva.id')) , Column('precioUnitario' , Float ) ) mapper(Producto, productos_tabla , properties={'iva':relation(Iva)}) mapper(Iva,iva_tabla) - And when i run it: raceback (most recent call last): File /home/instancia_webware/MyContext/Pruebas/sql.py, line 29, in ? mapper(Producto, productos_tabla , properties={'iva':relation(Iva)}) File /usr/lib/python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/ sqlalchemy/orm/__init__.py, line 518, in mapper return Mapper(class_, local_table, *args, **params) File /usr/lib/python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/ sqlalchemy/orm/mapper.py, line 154, in __init__ self._compile_properties() File /usr/lib/python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/ sqlalchemy/orm/mapper.py, line 612, in _compile_properties self._compile_property(column_key, column, init=False, setparent=True) File /usr/lib/python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/ sqlalchemy/orm/mapper.py, line 654, in _compile_property raise exceptions.ArgumentError(WARNING: column '%s' not being added due to property '%s'. Specify 'allow_column_override=True' to mapper() to ignore this condition. % (column.key, repr(prop))) sqlalchemy.exceptions.ArgumentError: WARNING: column 'iva' not being added due to property 'sqlalchemy.orm.properties.PropertyLoader object at 0xb7aba1cc'. Specify 'allow_column_override=True' to mapper() to ignore this condition. -- I've read a lot, and I can't or I'm incapable to resolve this by myself. Can anybody help me? Sorry again, Marcos --~--~-~--~~~---~--~~ 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: LIMIT in queries
Mike Orr wrote: Are there that many of those, given that I don't think [N] is even documented? It is in the official documentation: http://www.sqlalchemy.org/docs/04/ormtutorial.html#datamapping_querying fs smime.p7s Description: S/MIME Cryptographic Signature
[sqlalchemy] Re: LIMIT in queries
On Dec 14, 2007 1:31 PM, Felix Schwarz [EMAIL PROTECTED] wrote: Mike Orr wrote: Are there that many of those, given that I don't think [N] is even documented? It is in the official documentation: http://www.sqlalchemy.org/docs/04/ormtutorial.html#datamapping_querying OK. But the doc says nothing about what happens if the record doesn't exist. -- Mike Orr [EMAIL PROTECTED] --~--~-~--~~~---~--~~ 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: Many-To-One...What I'm doing wrong?
your 'iva' table-column AND 'iva' attribute/relation/property have same name, Thats what the error says. either rename one of them (e.g. the column to become iva_id), or use that allow_column_override=True flag to the producto mapper. Marcos wrote: Hello, first at all, sorry about my english... I'm from Mendoza, Argentina, and I'm starting with this. Because I can't describe my problem, I will show the next: from sqlalchemy import * from sqlalchemy.orm import * class Producto(object): pass class Iva(object): pass metadata = MetaData() engine = create_engine('postgres://postgres:[EMAIL PROTECTED]:5432/ marcos') connection = engine.connect() Session = sessionmaker(bind=connection, autoflush=True, transactional=True) session = Session() iva_tabla = Table( 'iva' , metadata , Column('id' , Integer , primary_key = True) , Column('valor', Float) ) productos_tabla = Table ( 'productos' , metadata , Column('id', Integer , primary_key = True ) , Column('nro' , Integer ) , Column('descripcion' , String(100) ) , Column('iva' , Integer , ForeignKey('iva.id')) , Column('precioUnitario' , Float ) ) mapper(Producto, productos_tabla , properties={'iva':relation(Iva)}) mapper(Iva,iva_tabla) - And when i run it: raceback (most recent call last): File /home/instancia_webware/MyContext/Pruebas/sql.py, line 29, in ? mapper(Producto, productos_tabla , properties={'iva':relation(Iva)}) File /usr/lib/python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/ sqlalchemy/orm/__init__.py, line 518, in mapper return Mapper(class_, local_table, *args, **params) File /usr/lib/python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/ sqlalchemy/orm/mapper.py, line 154, in __init__ self._compile_properties() File /usr/lib/python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/ sqlalchemy/orm/mapper.py, line 612, in _compile_properties self._compile_property(column_key, column, init=False, setparent=True) File /usr/lib/python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/ sqlalchemy/orm/mapper.py, line 654, in _compile_property raise exceptions.ArgumentError(WARNING: column '%s' not being added due to property '%s'. Specify 'allow_column_override=True' to mapper() to ignore this condition. % (column.key, repr(prop))) sqlalchemy.exceptions.ArgumentError: WARNING: column 'iva' not being added due to property 'sqlalchemy.orm.properties.PropertyLoader object at 0xb7aba1cc'. Specify 'allow_column_override=True' to mapper() to ignore this condition. -- I've read a lot, and I can't or I'm incapable to resolve this by myself. Can anybody help me? Sorry again, Marcos --~--~-~--~~~---~--~~ 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: SQL error for mapped select (new in version 0.4) on PostgreSQL
On Dec 14, 2007, at 4:02 PM, dykang wrote: Sorry, I didn't clarify, I was speaking about with 0.3, not with 0.4. 0.3 has a very limited add_entity() method, with no ability to set it against an arbitrary alias, so its impossible in 0.3 for an ORM Query to load parallel instances where the non-primary instance is against an alias or other table other than its mapper's mapped table. if you remove the add_entity() but keep the contains_alias(), the primary Test objects are loaded from the Query object against the s2 selectable in the same manner as 0.4. --~--~-~--~~~---~--~~ 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: LIMIT in queries
On Dec 14, 2007, at 5:22 PM, Mike Orr wrote: On Dec 14, 2007 1:31 PM, Felix Schwarz [EMAIL PROTECTED] wrote: Mike Orr wrote: Are there that many of those, given that I don't think [N] is even documented? It is in the official documentation: http://www.sqlalchemy.org/docs/04/ormtutorial.html#datamapping_querying OK. But the doc says nothing about what happens if the record doesn't exist. offical specs of behavior are better suited for docstrings on Query...patches welcome ! --~--~-~--~~~---~--~~ 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: concurent modification
your s.close() is not being reached I've checked and found that every thread closes connection. And this traceback : QueuePool limit of size 5 overflow 10 reached, connection timed out most probably caused by long running thread which walking over list of filenames ~ 35 000 long. It renaming files and causing app to start another thread. if you have a deadlock situation occurring, that can also quickly cause many connections to remain opened, all waiting on the same lock. if youre locking rows and such, you have to be careful that other transactions dont try to lock the same rows in a different order. by different transactions this could be a trans in the same thread, or in a different thread, and youll have to narrow down which. as far as I understand 'SHARE ROW EXCLUSIVE' ( http://www.postgresql.org/docs/8.1/static/sql-lock.html ) lock is necessary here, but I was unable to find how to set it up in sqlalchemy. With choosing proper lock in postgres disappears necessity to touch threadframe mentioned above, right ? if youre stiill doing that thing where you delete a row and then immediately re-insert it, i will ask again that you consider using an UPDATE instead. There was deleting every record from table and inserting another one instead. P.S. sqlalchemy revision 3942 was used. --~--~-~--~~~---~--~~ 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: concurent modification
On Dec 14, 2007, at 9:24 PM, imgrey wrote: as far as I understand 'SHARE ROW EXCLUSIVE' ( http://www.postgresql.org/docs/8.1/static/sql-lock.html ) lock is necessary here, but I was unable to find how to set it up in sqlalchemy. connection.execute(LOCK TABLE IN SHARE ROW EXCLUSIVE) With choosing proper lock in postgres disappears necessity to touch threadframe mentioned above, right ? if you have a deadlock and cant find it, you need to use threadframe. on the other hand, if your app just spawns threads with no bound because one thread is busy, and each thread opens a conneciton, then you have to increase the size of your connection pool. if that opens so many connections that you run out of memory, then obviously you have to revisit your application's approach such that it doesnt have to request endless resources. none of this has anything to do with an ORM. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---