https://github.com/ponyorm/pony/issues/277 Jordi, check this issue. It can help.

0.7.1

We fixed this problem in 0.7.2, now you should see correct error message

Mariadb?

I'm using mysql, I'm going to update the library and recheck. Thanks

After that, check the issue. I think it's your case.

Hi, I have a query for scheduling some work, that I want to improve, so that there are only a maximum of three jobs per user in the results of the query. Is it possible to do this only in pony, with no extra python code to filter results? Assuming this model: Class Job(db.Entity): user = Required(User)

Some users could have no jobs, some could have more than 3 jobs

I'd initially select users with jobs, and then for each user with job select at most three jobs. I'm not sure it is important to pack this into a single query

Yeah it's no problem, just wondered if it was possible :)

I think it is possible in PostgreSQL to write such a query using windows functions, but currently you can express it in Pony only with raw sql

How come limit() isn't valid on a subquery?

like select(u, select(j for j in Job if j.user == u).limit(3)) for u in User)

As I remember, SQL doesn't allow limit N in subqueries

Result table consists of cells, and each cell should contain a single value only

If only you don't write hierarchical query in some database-specific syntax

A nice side effect of the 0.7.2 db_session bug fix, when tweaking a script to use a db_session per intensive set of queries, it's a lot faster, as well as using a lot less memory

> 10x faster

No idea why, but I like it :)

That's interesting :)

Maybe there is an O(n^2) or similar somewhere deep in pony?

that is no longer having a big n

it is a background cronjob that takes a "job", then does a bunch of calculations (so lots of pony queries) to produce a string which is cached in redis

the jobs are independent of each other

Maybe in 0.7.2 we made work easier for Python garbage collector

I imagine it's less work for all layers, going from max 8gb ram usage to ~140mb constant usage

I imagine reusing assigned memory is quicker than asking the kernel for new memory?

I think earlier in some usage scenarios all previously cached objects stay in memory forever, and each garbage collection cycle the garbage collector was needed to re-check all that growing set of objects in order to check they are still cannot be collected. Now only objects from the last db_session need to be collected

That makes sense

:)

I am getting pony.orm.core.OptimisticCheckError: Object X[29] was updated outside of current transaction errors in a scheduler function, which looks like this: @models.db_session def some_scheduler(): for x in pony_select_query: redis_enqueue(x.id) x.scheduled = datetime.datetime.now() If I changed it to the following, would the pony portion successfully retry up to the commit, meaning that once it reaches the redis part of the code, there can't be any OptimisticCheckErrors? @models.db_session(retry=3) def some_scheduler(): x_ids = [] for x in pony_select_query: x_ids.append(x.id) x.scheduled = datetime.datetime.now() models.commit() for x_id in x_ids: redis_enqueue(x_id) I now see in the docs that with retry, commit() shouldn't be explicitly used, is there a way around this? Thanks

OptimisticCheckError arises because several different processes are trying to redis_enqueue the same object. Then, when it's time to update the database, second process sees that the object was already enqueued and its scheduled time already changed. So, the question is, is it OK for object to be enqueued several times? If the answer is yes, you can define scheduled attribute as volatile and Pony will stop consider it as an error. On the other hand, if an object should be scheduled only once, you can use SELECT FOR UPDATE query: query = query.for_update() In that case, a db_session will lock object in the database when selecting it, and other parallel db_sessions will wait until the first db_session finished. I think it may be more efficient to mark scheduled attribute as volatile and then check an object upon retrieving it from the redis queue in order to see if the object was already processed.

I am only ever running the function once at a time, I think it's other code that is changing the object. redis_enqueue should only ever be run once in a given time period (hours). Can I use for_update() with my original code, or something like my latter code?

I think attributes other than scheduled are being changed, so not sure making scheduled volitile would help?

I'm not sure the latter code is bullet-proof. If some exception like NetworkError arises after commit(), but before redis_enqueue, the object will not be added to queue, but will have updated scheduled attribute value. Also, I don't see how latter code will prevent the error. It may shorten the transaction time and reduce the chance of conflict, but probably it will not prevent it absolutely. Also, operations with Redis should be fast and probably don't have big impact on transaction time. > I think attributes other than scheduled are being changed, so not sure making scheduled volitile would help? If your code snippet is complete, the code reads only two attributes of object: x.id and x.scheduled. x.id is a primary key and cannot be updated, so only attribute that can cause OptimisticError is x.scheduled

OptimisticError can arise only if two different processes access the same attribute of the same object: process 1 read the attribute, process 2 updates the same attribute, and then process 1 tries to update any attribute of the same obect

I wonder if this is two scheduler processes actually running at the same time then, as it uses cron. I think I'm going to try switching it to being a process running under supervisor, that might stop the errors

> redis_enqueue should only ever be run once in a given time period (hours). It may be possible, that when it starts once per hour, it starts in multiple processes at the same time

a slow query seems to have been causing multiple schedulers to run at once. Thanks for your help!

Is there a way to update an attribute, without loading the full model data,for example: x = X[1] x.y = 2

it seems to be a major part of my execution time is loading a lot of data for each instance

What is the type of attribue?

the type i want to update is datetime

there are a lot of Unicode attributes which seem to take a long time to load

At this moment you have three options: 1) You can specify lazy=True option for all string attributes you don't want to load. The drawback is, if you later want to access such attributes they will be loaded one-by-one using separate SQL queries. 2) Maybe the slowdown is caused not by loading string attributes, but by multiple SQL queries. It may be possible to reorganize code to load all objects of the same type in a single query. 3) You can use internal method _get_by_raw_pkval_ to represent object in memory without loading it. Then Pony will not load object attributes from the database until you access any attribute. But you need to be sure that the object is really exists in the database, because Pony will not check it for you: x = X._get_by_raw_pkval_((1,)) x.y = 2

I fixed the typo in the method name

Hi, I want to ask about Pony ORM License . Does it okay if I make a propietary software by using PonyOrm License as a free license under Apache?

Sure

Previously it was dusal licensed (AGPL and commercial), but now it just Apache2

so it's okay

I read it,

I just need to make sure

cause I am developing Point of Sales and I am going to use PonyORM for my commercial products

Is it ready for REAL production? Did anybody make something big and complex with it?

i use pony for multiple projects that have plenty of scale and paying customers

It's a fairly minor part of a complex project really

important for productivity etc, but projects are possible without it

I use RAW SQL in my projects, but I want to migrate by using Pony ORM as well.

What makes you think that Pony ORM doesn't quite ready in Deployment / Production?

So- I'm trying to defnie a date in my models, and 'Required(date)' as it shows on the docs is giving me a NameError. Any idea why that would be?

I'm using datetime with no issues

are you doing any import other that from pony.orm import * ?

yes... you're importing date from datetime because that's the type used... that's my bad

Yes, you got it right :D

If I do the exact same query twice within the same database session, but within different model methods, is the result cached?

def x(self): query() def y(self): query()

instance.x(); instance.y()

If these queries use different generators (even with identical text), then no. To be cached, they need to be based on the same code object: def x(self): query = self._get_query() def y(self): query = self._get_query() def _get_query(self): query = select(x for x in X): return query

ah thank you

is it the same with count() and first() queries?

yes

I think

Is there a direct way to "jsonify" the result from a query?

I want to add it, but at this moment I'm busy with migrations. Right now you can use something like jsonify([obj.to_dict() for obj in query])

Thank you as usual :D

I want to add it, but at this moment I'm busy with migrations. Right now you can use something like jsonify([obj.to_dict() for obj in query])

orm.jsonify()?

I think its Flask.jsonify();

select(<generator>).extract(<hierarchical query>)

https://gist.github.com/anonymous/49f28115700ea3db3a5bd346839ced3f This seems to be "leaking" memory, like before the db_session bug was fixed. Could it be because of the start / stop methods using a db_session? the generate function does a large amount of pony queries, but it should be releasing the cache once exiting. The process is currently using 37GB of memory.

That's after 3 hours of the process running

Maybe the code holds reference to some object from each generate_estimade_data() call, and so prevent garbage collection of db_session data. I think I can change Pony code to remove link from object to session cache upon db_session exit, it may help to garbage collect at least some objects.

We just pushed on github some fixes which should improve garbage collection. So, Matthew, you can try your code again using github version of Pony.

Hello @akozlovsky I've used Pony in the past but I just got back to checking it out again. Its license has changed in the time I left it. Well done for all the hard work.

Thanks for the kind words :)

What would be the easiest way to say to_dict should only return Json compatable types

Ignore that, no longer relavent

We just pushed on github some fixes which should improve garbage collection. So, Matthew, you can try your code again using github version of Pony.

Your avatar has panties on her head. Just wanted to remind everyone about that.

Your avatar has panties on her head. Just wanted to remind everyone about that.

What does it mean for you?

Nothing specific. It's more that condensed boredom lead to creation of this fact.

Ok then.

weird question, how often is the PyPI package updated compared to the GitHub?

PyPi package updated at new release, while GitHub repository updated on each bug fix. Currently Pony releases are irregular, maybe one release per several month