[sqlalchemy] sqlalchemy gc and memory leak
Hi, inspecting the gc I see a sqlalchemy memory leak in my application, here is the output from the gc: class 'sqlalchemy.engine.base.Connection': 2 - 3 (+1) class 'sqlalchemy.engine.base.RootTransaction': 2 - 3 (+1) class 'sqlalchemy.util.LRUCache': 1 - 2 (+1) class 'sqlalchemy.util.PopulateDict': 2 - 3 (+1) type 'tuple': 5987 - 5990 (+3) class 'sqlalchemy.sql.expression._BindParamClause': 73 - 80 (+7) type 'dict': 8943 - 8954 (+11) class 'sqlalchemy.sql.expression.Insert': 1 - 2 (+1) class 'sqlalchemy.util.OrderedDict': 180 - 181 (+1) type 'instancemethod': 490 - 487 (-3) class 'sqlalchemy.dialects.sqlite.base.SQLiteCompiler': 2 - 3 (+1) type 'collections.defaultdict': 34 - 35 (+1) type 'weakref': 3830 - 3831 (+1) type 'list': 2953 - 2963 (+10) every time I make some database object a reference is added to sqlalchemy objects and never released. I'm sure the problem is my application and not sa, however I would like to know how to force sqlalchemy to delete objects references. I'm using these function to query gc: def gcHistogram(self): import gc result = {} for o in gc.get_objects(): t = type(o) count = result.get(t, 0) result[t] = count + 1 print len(result) return result def diffHists(self,h1, h2): for k in h1: if k in h2: if h1[k] != h2[k]: print %s: %d - %d (%s%d) % ( k, h1[k], h2[k], h2[k] h1[k] and + or , h2[k] - h1[k]) thanks Nicola -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] sqlalchemy gc and memory leak
On Dec 18, 2010, at 5:32 AM, drakkan wrote: Hi, inspecting the gc I see a sqlalchemy memory leak in my application, here is the output from the gc: class 'sqlalchemy.engine.base.Connection': 2 - 3 (+1) class 'sqlalchemy.engine.base.RootTransaction': 2 - 3 (+1) class 'sqlalchemy.util.LRUCache': 1 - 2 (+1) class 'sqlalchemy.util.PopulateDict': 2 - 3 (+1) type 'tuple': 5987 - 5990 (+3) class 'sqlalchemy.sql.expression._BindParamClause': 73 - 80 (+7) type 'dict': 8943 - 8954 (+11) class 'sqlalchemy.sql.expression.Insert': 1 - 2 (+1) class 'sqlalchemy.util.OrderedDict': 180 - 181 (+1) type 'instancemethod': 490 - 487 (-3) class 'sqlalchemy.dialects.sqlite.base.SQLiteCompiler': 2 - 3 (+1) type 'collections.defaultdict': 34 - 35 (+1) type 'weakref': 3830 - 3831 (+1) type 'list': 2953 - 2963 (+10) every time I make some database object a reference is added to sqlalchemy objects and never released. I'm sure the problem is my application and not sa, however I would like to know how to force sqlalchemy to delete objects references. I'm using these function to query gc: def gcHistogram(self): import gc result = {} for o in gc.get_objects(): t = type(o) count = result.get(t, 0) result[t] = count + 1 print len(result) return result def diffHists(self,h1, h2): for k in h1: if k in h2: if h1[k] != h2[k]: print %s: %d - %d (%s%d) % ( k, h1[k], h2[k], h2[k] h1[k] and + or , h2[k] - h1[k]) SQLA doesn't maintain strong references to anything at the module level. The objects you have above appear to be related to the compiled cache used by an individual mapper. This is an LRU-enabled dictionary that holds onto Insert constructs and their compiled form up to approximately 100 elements. The dictionary is associated with a mapper, which in turn is associated with your mapped class. If you dereference your mapped class (and all instances of that class), the mapper and the LRU cache will be gone.You can also say mapper._compiled_cache.clear() which will probably remove the Insert/SQLiteCompiler objects above. As for Sessions, if you close(), commit(), rollback(), or expire_all() the session object, all user objects inside are weakly referenced by the session (though related objects will have strong references to each other). thanks Nicola -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Specifying a type for primary key results in strange behaviors
On Dec 18, 2010, at 2:45 PM, Lenza McElrath wrote: I am writing a system where I want my DB primary keys to be represented in the system by a type other than an integer. I have run into several issues with this: 1) I tried using a TypeDecorator, but SQLAlchemy does not like this. Seems like the main problem is the auto_increment is not set on the column when creating the table, but I think I ran into other issues even after making the table by hand. This issue was discussed here: http://groups.google.com/group/sqlalchemy/browse_thread/thread/4b13261da8c4c932/d0b84b4c14a6645e. However, specifying a Sequence does not solve the problem for me (I'm using MySQL). Its using isinstance(type, Integer) so in this case possibly adding Integer as a mixin, though the isinstance() check should probably be changed to use _type_affinity. ticket #2005 is added. Targeted at 0.7 for starters, would need further research to ensure its backwards compatible for 0.6.6. So, I resorted to using a straight Type, and have a couple of other problems: 2) After session.flush() the ID is an integer, not the type returned from my result processor. After a query the types are correct. ResultProxy doesn't check for result processors when using cursor.lastrowid. Ticket #2006 is added. This one stays at 0.7 as we have been refactoring the default dialect to optimize things like this. 3) When doing queries, filters on the ID are not passed through the bind process, so they return no results. This broke after 0.6beta1. Don't have time to check this right now. ticket #2007 is added to create a single test case. Bugs in expression coercion are of highest priority so this is targeted at 0.6.6 - though the usage of Integer instead of TypeDecorator is probably the cause here. Direct subclassing of the built in types is not the usual way to do things. I have created a test case the illustrates all of these issues here: http://pastebin.com/CnAmptck for now I'd recommend using class-level descriptors to proxy the integer ID value to a string on the Python side. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Money data type for Postgres
Postgres deprecated the Money data type around 8.2 but then brought it back with better support soon after (I think 8.3 or 8.4 and after). I found the following message on this group, where Mike welcomes anyone to just roll their own type stuff for it or possibly submit a patch. http://groups.google.com/group/sqlalchemy/msg/77efa08097492b1a This should be easy enough to do; I'm just wondering if anyone has already done so and is willing to share, just to avoid reinventing a wheel. Thanks! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Money data type for Postgres
Hmm, I read through all of the relevant files, postgresql.py and the rest, and of course this section of the SA docs: http://www.sqlalchemy.org/docs/core/types.html#custom-types But I'm confused about a couple of things. One: what I'm reading seems to be suggesting that I just ignore the existence of a postgres Money type and just TypeDecorate a Numeric to get what I want. I was hoping to do the necessary plumbing to get SA to recognize columns in reflected tables that are defined as Money type and work with them appropriately. Am I missing something? Two, and closely related, If I just make a TypeDecorator, or even my own UserDefinedType, I'm not yet seeing how SA will know to make use of it when reflecting. I guess what I'm really asking is this: If SA doesn't 'know about' postgresql's Money type, it doesn't know about it! I understand how to fake it by making a type decorator so I can store what I need to in a Numeric column but have it represented in my python objects via any interface I please. What I don't understand is how to make SA actually USE the native postgresql Money type. It's probably right in front of my face but maybe I'm misinterpreting some of the code so I keep falling back into the same mental orbit about it. Thanks in advance if you can help clear my view. On Dec 18, 1:15 pm, Eric Ongerth ericonge...@gmail.com wrote: Postgres deprecated the Money data type around 8.2 but then brought it back with better support soon after (I think 8.3 or 8.4 and after). I found the following message on this group, where Mike welcomes anyone to just roll their own type stuff for it or possibly submit a patch. http://groups.google.com/group/sqlalchemy/msg/77efa08097492b1a This should be easy enough to do; I'm just wondering if anyone has already done so and is willing to share, just to avoid reinventing a wheel. Thanks! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Money data type for Postgres
Meanwhile... meh. I'll be more than happy to go with Numeric(16,2) as suggested by #postgresql. Should work fine and the only disadvantage is the most minor of semantic objections. On Dec 18, 1:57 pm, Eric Ongerth ericonge...@gmail.com wrote: Hmm, I read through all of the relevant files, postgresql.py and the rest, and of course this section of the SA docs:http://www.sqlalchemy.org/docs/core/types.html#custom-types But I'm confused about a couple of things. One: what I'm reading seems to be suggesting that I just ignore the existence of a postgres Money type and just TypeDecorate a Numeric to get what I want. I was hoping to do the necessary plumbing to get SA to recognize columns in reflected tables that are defined as Money type and work with them appropriately. Am I missing something? Two, and closely related, If I just make a TypeDecorator, or even my own UserDefinedType, I'm not yet seeing how SA will know to make use of it when reflecting. I guess what I'm really asking is this: If SA doesn't 'know about' postgresql's Money type, it doesn't know about it! I understand how to fake it by making a type decorator so I can store what I need to in a Numeric column but have it represented in my python objects via any interface I please. What I don't understand is how to make SA actually USE the native postgresql Money type. It's probably right in front of my face but maybe I'm misinterpreting some of the code so I keep falling back into the same mental orbit about it. Thanks in advance if you can help clear my view. On Dec 18, 1:15 pm, Eric Ongerth ericonge...@gmail.com wrote: Postgres deprecated the Money data type around 8.2 but then brought it back with better support soon after (I think 8.3 or 8.4 and after). I found the following message on this group, where Mike welcomes anyone to just roll their own type stuff for it or possibly submit a patch. http://groups.google.com/group/sqlalchemy/msg/77efa08097492b1a This should be easy enough to do; I'm just wondering if anyone has already done so and is willing to share, just to avoid reinventing a wheel. Thanks! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Double relation cascade problem?
I have the schema described in the test script located here: http://pastebin.com/qeB0vKVV If you run the script, you can see that there is a ConcurrentModificationError: when I go to delete the contest. If I delete the contestants first, there is no problem. The problem seems to be caused by the double link between Contest and Contestant. The first, an indirect link via TeamContestant through polymorphic inheritance created by the m1 mapper, and the second a backref created by the m2 mapper. If I leave the Contest.team_contestant relation off, the script executes successfully. Removing that relation changes the order of the operations: when it works successfully, all Slot updates happen before the first delete operation. When it fails, the contestants are deleted before the slots are updated as follows: Echo output of a successful run: 2010-12-18 21:37:56,788 INFO sqlalchemy.engine.base.Engine.0x...2890 UPDATE slot SET contest_id=? WHERE slot.id = ? 2010-12-18 21:37:56,788 INFO sqlalchemy.engine.base.Engine.0x...2890 (None, 1) 2010-12-18 21:37:56,788 INFO sqlalchemy.engine.base.Engine.0x...2890 UPDATE slot SET contestant_id=? WHERE slot.id = ? 2010-12-18 21:37:56,788 INFO sqlalchemy.engine.base.Engine.0x...2890 (None, 2) 2010-12-18 21:37:56,789 INFO sqlalchemy.engine.base.Engine.0x...2890 UPDATE slot SET contestant_id=? WHERE slot.id = ? 2010-12-18 21:37:56,789 INFO sqlalchemy.engine.base.Engine.0x...2890 (None, 3) 2010-12-18 21:37:56,789 INFO sqlalchemy.engine.base.Engine.0x...2890 DELETE FROM team_contestant WHERE team_contestant.id = ? 2010-12-18 21:37:56,789 INFO sqlalchemy.engine.base.Engine.0x...2890 ((1,), (2,)) 2010-12-18 21:37:56,790 INFO sqlalchemy.engine.base.Engine.0x...2890 DELETE FROM contestant WHERE contestant.id = ? 2010-12-18 21:37:56,790 INFO sqlalchemy.engine.base.Engine.0x...2890 ((1,), (2,)) 2010-12-18 21:37:56,790 INFO sqlalchemy.engine.base.Engine.0x...2890 DELETE FROM contest WHERE contest.id = ? 2010-12-18 21:37:56,790 INFO sqlalchemy.engine.base.Engine.0x...2890 (1,) Echo from an unsuccessful run: 2010-12-18 17:25:59,017 INFO sqlalchemy.engine.base.Engine.0x...2490 DELETE FROM contestants WHERE contestants.contest_id = %(contest_id)s 2010-12-18 17:25:59,017 INFO sqlalchemy.engine.base.Engine.0x...2490 ({'contest_id': 267}, {'contest_id': 267}) 2010-12-18 17:25:59,025 INFO sqlalchemy.engine.base.Engine.0x...2490 UPDATE slots SET contest_id=%(contest_id)s WHERE slots.id = %(slot_id)s 2010-12-18 17:25:59,025 INFO sqlalchemy.engine.base.Engine.0x...2490 {'contest_id': None, 'slot_id': 4} 2010-12-18 17:25:59,026 INFO sqlalchemy.engine.base.Engine.0x...2490 UPDATE slots SET contestant_id=%(contestant_id)s WHERE slots.id = %(slot_id)s 2010-12-18 17:25:59,027 INFO sqlalchemy.engine.base.Engine.0x...2490 {'contestant_id': None, 'slot_id': 8} 2010-12-18 17:25:59,027 INFO sqlalchemy.engine.base.Engine.0x...2490 UPDATE slots SET contestant_id=%(contestant_id)s WHERE slots.slot_id = %(slot_id)s 2010-12-18 17:25:59,027 INFO sqlalchemy.engine.base.Engine.0x...2490 {'contestant_id': None, 'slot_id': 9} I would expect the Contest.team_contestants relation, since it contains no explicit cascade, to not take precedence over the more inclusive Contest.contestants. It looks like however that the rowcount calculation takes one relation, while the delete takes the other. Is there a bug there? I can actually use viewonly on the team_contestants relation. This seems to cause the correct behavior to occur. Luckily I'm not writing to the team_contestant relation, so I don't think this will adversely affect my application. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.