It will be great to have such a layer

In cases where all work with database is performing through Pony, it would be possible to have a consistent write-through redis cache, which can completely avoid database hits for some db sessions

Can you give an example session?

The main problem with caching layer is with data invalidation. Many queries share the same information. So, when an update is performed, it is hard to tell which query results need to be invalidated. One solutions is to invalidate all queries for specific entity if one instance of entity was changed. Another option is to have limited time-to-live for each query result, and to have a chance to get inconsistent result before that timeout. I have in mind the cache, in which each object is cached and invalidated separately by id. Then query can be automatically transfomed to retrieve only ids from the database, and other fields will be received from Redis. In some cases, such query will be more lightweight. If db session retrieve objects by id or by reference through attributes and collections, then all information can be loaded from Redis

Might it be most simple (and highest ROI) to do something like passing an optional TTL to a certain query, and if no TTL is passed, it's not cached?

For me, it's often only a few queries in a session that are intensive

there isn't too much value in pulling an entity by ID from redis rather than the database

Maybe: select(x for x in X where x.y == 10).cache(ttl=3600)

Sure, it is more simple, but sometimes the query result can be incorrect before TTL. If this is acceptable, then this approach is the most appropriate

Right, if it's a manual tagging of queries, the programmer can know that staleness isn't an issue

there isn't too much value in pulling an entity by ID from redis rather than the database

Yeah, putting in another db seems strange

Can then use machine learning if you want to work out which queries to cache :)

lol

Can then use machine learning if you want to work out which queries to cache :)

Hah, this would be fun at least

One way I get around staleness in my custom caching is having the ability to have a custom caching key. If the pony caching key was the raw SQL of the query, a custom key could be appended, like '{date}' or '{current_user_id}' or {unix_time}'

so the key might be "select * from x where y = 10.2017-07-10"

after midnight, the old cache is no longer used

so maybe query.cache(ttl=86400, key_template='{date}')

One way I get around staleness in my custom caching is having the ability to have a custom caching key. If the pony caching key was the raw SQL of the query, a custom key could be appended, like '{date}' or '{current_user_id}' or {unix_time}'

Are you caching queries or results?

the results of certain queries

https://gist.github.com/db6c492ac320b8608190b2fcf055866f

I do some weird stuff with functools.partial, not proposing that for pony :)

I just use cPickle to store the query results in redis

the gist I gave is probably a bad example, as it is quite complex

https://gist.github.com/anonymous/608bc523c0410ad73b5463581981252e

this seems to work

models.redis is a redis connection object

the query sql could be hashed if memory usage was a concern

It would need to only be used for SELECTs of course

Caching inserts ?

Is there a way to access get_sql() on a query that ends in count() ?

since count() is not lazy

Right now - only if you rewrite query as select(count() for x in ...). Also, right after query execution, you can see db.last_sql, but this is a bit too late

since count() is not lazy

Maybe you need to subclass it and implement that yourself?

If it was integrated into pony as query.cache() then I don't think subclassing would work

I think that caching code is at the wrong layer, it needs to be within pony, after sql is generated but before it is executed

Then count(), first(), limit() etc would work

I think some time later we will add such a layer

Hi Guys, We're trying to determine a way to add columns to a table on the fly with pony. for example remap... Table Customers(db.Entity) email = Required(str, unique=True) name = Required(str) ... to: Table Customers(Customers) surname = Required(str)

Is there a clever way to do this?

In order to do this you need a migration tool. I'm working on it right now. It should be ready soon, at first I plan to put experimental version on GitHub, and then we include it in official release

Nice.

In order to do this you need a migration tool. I'm working on it right now. It should be ready soon, at first I plan to put experimental version on GitHub, and then we include it in official release

Also there also is github.com/luckydonald/pony_up

Permanent link to the luckydonald/pony_up project you mentioned. (?)

Hi guys, We are currently working on an upgrade of our Enity-Relationship Editor. Here you can take a look at the current beta version of the interface: https://beta.editor.ponyorm.com/. You can use the same login and password as for the current editor, as it is connected the production database. Your feedback is greately appreciated!

Hi guys, We are currently working on an upgrade of our Enity-Relationship Editor. Here you can take a look at the current beta version of the interface: https://beta.editor.ponyorm.com/. You can use the same login and password as for the current editor, as it is connected the production database. Your feedback is greately appreciated!

Oh gosh, he has a profile picture now. The red M is gone!

Oh gosh, he has a profile picture now. The red M is gone!

?

Before we release migrations we had to fix some bugs And we are happy to release Pony version 0.7.2!

# Pony ORM Release 0.7.2 (2017-07-17) ## New features * All arguments of db.bind() can be specified as keyword arguments. Previously Pony required the first positional argument which specified the database provider. Now you can pass all the database parameters using the dict: db.bind(**db_params). See https://docs.ponyorm.com/api_reference.html#Database.bind * The optimistic attribute option is added https://docs.ponyorm.com/api_reference.html#cmdoption-arg-optimistic ## Bugfixes * Fixes #219: when a database driver raises an error, sometimes this error was masked by the 'RollbackException: InterfaceError: connection already closed' exception. This happened because on error, Pony tried to rollback transaction, but the connection to the database was already closed and it masked the initial error. Now Pony displays the original error which helps to understand the cause of the problem. * Fixes #276: Memory leak * Fixes the __all__ declaration. Previously IDEs, such as PyCharm, could not understand what is going to be imported by 'from pony.orm import *'. Now it works fine. * Fixes #232: negate check for numeric expressions now checks if value is zero or NULL * Fixes #238, fixes #133: raise TransactionIntegrityError exception instead of AssertionError if obj.collection.create(**kwargs) creates a duplicate object * Fixes #221: issue with unicode json path keys * Fixes bug when discriminator column is used as a part of a primary key * Handle situation when SQLite blob column contains non-binary value

https://blog.ponyorm.com/2017/07/17/pony-orm-release-0-7-2/

@alexeymalashkevich yey cool

Great to have a dictionary for db_bind!

Thank you!

Matthew, I think we fixed the memory leak. You can try to use db_session without strict=True option, it should work without any problem

Happy to test that, is that listed in the above notes?

Yes, > Fixes #276: Memory leak

was it the cache building up objects, or something else?

memory usage seems stable without strict=True :)

The problem was in handling expressions like query = query.filter(lambda obj: obj.field == x) We have some internal function get_lambda_args which returns names of function arguments, like get_lambda_args(lambda obj: obj.field == x) -> ["obj"] That function caches lambdas for speed. But function can have closures with values of external variables, like x in this example. That values become cached too. If x is an instance of object, the function that sitting in a lambda cache holds reference to that object indefinitely. And object has a reference to a whole db_session cache with all loaded objects. As a result, all previously loaded objects stays in the memory. In order to fix that memory leak, now I cache function code object instead of function itself. Codeobject does not linked with any closure, so now all objects can be garbage collected properly

I'm very glad that's fixed!

Me too :)

So big db_sessions still can use up a lot of memory, but it'll be reclaimed upon exiting the session?

or is strict=True still needed in that case?

Yes, one big db_session can require a lot of memory, but then it should be reclaimed. strict=True option purge all objects upon exiting from db_session and can be useful if we have yet another undiscovered memory leak

How can the memory be recalimed (without strict=True) if objects can still be accessed outside of the session?

Right now, if you hold a link to an object after exiting from db_session without strict=True option, that object remians linked with session cache and the memory cannot be reclaimed. But I want to change that in the next release, so object will be linked with other objects only if it has references to them. In some cases it may allow to reclaim some memory, if loaded objects belongs to disjoint groups. But often all objects are related indirectly: the first object linked to second, second to third and so on, so it is hard to tell, how much memory will be reclaimed after such optimization

with db_session(): for i in range(1000000): print X[i].y

in this case, will there be no links to objects after the session, therefore no memory still used up?

Consider the following schema: db = Database() class UniversirtyGroup(db.Entity): id = PrimaryKey(int) major = Required(str) students = Set(lambda: Student) class Student(db.Entity): id = PrimaryKey(int) name = Required(str) group = Required(lambda: Group) In that case, Student table has group column which contains a reference to UniversityGroup If we write: with db_session: for id in range(1, 1000): print Student[id].name then Pony will load each Student together with group column, and create Group objects as well even without accessing Group table. Each group will be linked with its loaded students. We don't know major of group, because we don't retrieve any row from Group table, but we know that the group exists in the database and linked with specific students. If, after exiting from db_session, the program holds a link to some student, that student remains linked with its group, and the group is linked with other students of the same group. So, all students of the same group will remain in the memory, while students of other groups will be garbage collected

ok, but in the range example, there will be no links afterwards?

If X entity you mentioned does not have Required or Optional attributes linked to other entities, then yes, all objects except a single one that you hold should be deleted (not in this release, but after I make the change I speak above)

Which object would be held in that example? I assumed that if something is printed, there is no reference to it held after the session?

In your example, you don't hold any reference to any object after exiting from db_session, so all objects will be garbage collected

If you keep a reference to any object, then all other loaded objects will remain in memory, and I plan to change that in the next release

Understood, thank you

I think not having any references after a session is pretty common, especially in "clean up" and other maintenance code

Also, crunching database data to produce new data, summaries etc

https://schneems.com/2017/07/18/how-i-reduced-my-db-server-load-by-80/ always use database level constraints

https://schneems.com/2017/07/18/how-i-reduced-my-db-server-load-by-80/ always use database level constraints

Which is hard with pony

The hardest thing is to catch constraint violation. For example, unique constraint raises exception that doesn't look like it designed only for it (TransactionIntegrity, I think, not sure), so I am afraid to catch it as it can lead to strange errors in future. :D

Yeah with https://docs.ponyorm.com/entities.html#composite-keys it works fine, with the ocassional constraint violation

in my app, this only happens very rarely, so I just let things fail, there's usually no sensible way to recover from it for me

I have a question. Does: def define(db): MyClass(db.Entity): *definition* db = Database() define(db) db.bind() db.generate_mappings() have any advanteges over def init_db(): from database import db #schema and db here db.bind() db.generate_mappings()

The first version allows to declare several db objects and define the same set of entities in all these db s. In other aspects I don't see any differences

And second won't allow this? :(

I thought it will, bc we import it locally

In the second version we have a single instance of db objects, if I read correctly

I often have models which are similar to this: class InputKeyword(db.Entity): job = Required(lambda: Job) keyword = Required(unicode) composite_key(job, keyword) For a given job, these are only ever created during job creation, are there any disadvantages to using a JSON field on the job to have a list of keywords instead? class Job(db.Entity): ... keyword = Required(Json)

It's not a good practice. Not for pony but for relational databases.

@matthewrobertbell https://stackoverflow.com/a/15367769 This can help

Hi! How to declare the index with descending order?

Currently Pony does not have an ability to specify index with descending order, you need to create it manually

It doesn't look to hard to add such feature, you can create an issue on GitHub for that. But I think we need to close some other issues first as more important

Thanks.

Convert data structure from mssql to pg class FridgeServiceParameter(db.Entity): ... ValueElementListSet = Set('FridgeServiceParameterValueElementList') FridgeServiceRequestStatusActionMapTypeFSPSet = Set('FridgeServiceRequestStatusActionMapTypeFSP') pony.orm.core.DBSchemaError: Foreign key fk_('jeans', 'fridgeservicerequeststatusactionmaptypefsp')__fri cannot be created, name is already in use default fk name generated in DBAPIProvider.get_default_fk_name 'fk_%s__%s' % (child_table_name, '__'.join(child_column_names)) and cut to 63 characters.