What happened to the ORM https://news.ycombinator.com/item?id=14660895

Hello guys, Is it possible to create a nullable attribute when it’s defined Optional?

Sure. If it is optional non-string attribute, it will be nullable by default. For optional string attributes you can specify nullable=True

Thanks Alexander, I had issues only with the string attributes 😃. How can I check the core objects type ? if I check Optional.__class__ the type is 'type'

Optional is already a type. When you write year = Optional(int) you create an instance of that type. You can check isinstance(year, Optional) and check isinstance(year.py_type, int)

Wenn running the update test from Framework Benchmarks (http://frameworkbenchmarks.readthedocs.io/en/latest/Project-Information/Framework-Tests/#database-updates) with Morepatha nd PonyORM I get some of these errors: Server morepath: [2017-07-01 11:10:37 +0000] [10768] [ERROR] Traceback (most recent call last): Server morepath: File "/home/vagrant/FrameworkBenchmarks/installs/py3/lib/python3.6/site-packages/pony/orm/dbapiprovider.py", line 48, in wrap_dbapi_exceptions Server morepath: try: return func(provider, *args, **kwargs) Server morepath: File "/home/vagrant/FrameworkBenchmarks/installs/py3/lib/python3.6/site-packages/pony/orm/dbproviders/postgres.py", line 216, in execute Server morepath: else: cursor.execute(sql, arguments) Server morepath: psycopg2.extensions.TransactionRollbackError: deadlock detected Server morepath: DETAIL: Process 10960 waits for ShareLock on transaction 111522; blocked by process 10959. Server morepath: Process 10959 waits for ShareLock on transaction 111518; blocked by process 10960. Server morepath: HINT: See server log for query details. There are running ten thousands of request and only about 5 are failing, but I still would like to know if this is avoidable and why it happens. Any idea?

I'm running the benchmark in a vagrant environment.

Do the test use SELECT FOR UPDATE?

The model is class World(db.Entity): randomnumber = Optional(int) class WorldUpdates(): def __init__(self, queries): self.queries = queries And the view @App.json(model=WorldUpdates) def test_5(self, request): """Test 5: Database updates""" try: queries = int(self.queries) except ValueError: queries = 1 else: if queries < 1: queries = 1 elif queries > 500: queries = 500 result = [] for id_ in [randint(1, 10000) for _ in range(queries)]: randomNumber = randint(1, 10000) World[id_].randomnumber = randomNumber result.append({'id': id_, 'randomNumber': randomNumber}) return result

'queries' is the number of queries to execute.

I don't see any db_session here. Where it is applied?

The source code is here: https://github.com/henri-hulski/FrameworkBenchmarks/tree/add_morepath/frameworks/Python/morepath/app

I use more.pony which uses a tween to wrap the dbsession inside the morepath session.

See https://github.com/morepath/more.pony/blob/master/more/pony/app.py#L21-L34

So db_session is closed quite before sending the response.

At this moment I don't quite understand any details about what tween_factory is, so maybe I need to look into it more, in order to be sure that db_session is used correctly. But actually the reason for the deadlock is simple. test_5 function wraped in some outer db_session, and that means that all code of test_5 executes as a single transaction by default. This can lead to the following situations: 1) Process A starts a transaction 2) Process B starts another transaction 3) Process A randomly updates World[123]. The corresponding database row became locked until the end of transaction. 4) Process B randomly updates World[456]. The corresponding row became locked as well. 5) Process A tries to update World[456] and needs to wait, because transaction B holds the lock. 6) Process B tries to update Word[123] and needs to wait, because transaction A holds the lock. 7) PostgreSQL sees that the waiting may be infinite long because of deadlock, and terminates some of transactions. This looks like the correct behavior of ORM and PostgreSQL, and can happens with any ORM. In order to avoid the deadlock you need to change the test logic. One way to avoid deadlock is to perform each update in a separate transaction. In order to do that, you can add commit() or db.commit() after updating randomnumber attribute. But this will lead to performance degradation, because commit operation is very slow. The proper way to fix the situation is to order updates in such a way that two different transactions which are trying to update the same pair of objects always do it in the same order. To do this, you can sort objects in some consistent way, for example by id, and apply updates in the same order in all processes. To do this, you need to change the for loop of your test: replace for id_ in [randint(1, 10000) for _ in range(queries)]: to for id_ in sorted(randint(1, 10000) for _ in range(queries)): I think that after that all deadlocks should go away

Yeah that sounds correct. Seems that flask uses a similar solution: ids = [rp() for _ in xrange(num_queries)] ids.sort() # To avoid deadlock

Thanks! It works. No deadlocks anymore. 😃 Here some docs about Tweens: http://morepath.readthedocs.io/en/latest/tweens.html

does Pony works in zope frameworks?

especially CMS

derived from Zope?

Do you mean integration with transaction (http://transaction.readthedocs.io)?

We tried to integrate Pony with more.transaction (https://github.com/morepath/more.transaction) which is a wrapper around transaction, but it doesn't worked (at least it's not easy) because PonyORM doesn't have the necessary hooks.

especially CMS

Hmm about which CMS you're talking?

Hmm about which CMS you're talking?

karlcms

karlcms

karlcms uses Pyramid not Zope and the database is accessed using pyramid_tm which is also a wrapper around transaction. Actually more.transaction is a Morepath port of pyramid_tm. So you will have similar problems integrating PonyORM as I mentioned above.

ah I see

ah I see

Here is the issue where we discuss Pony integration with transaction: https://github.com/morepath/morepath/issues/485

Especially this comment: https://github.com/morepath/morepath/issues/485#issuecomment-265971633

Hello everyone. I a, getting a TypeError: datetime.datetime(2017, 7, 5, 20, 56, 52, 700967) is not JSON serializable for a datetime column. I have flask app. The strange thing is that in the shell with the app context, simple manual creation of the entity using datetime.now is valid and ok. But then I create the entity over a background Thread process I am getting this TypeError. Mayde someone had similar problem ? Any ideas?

It seems that the problem is not with creating the entity object, but with converting its attributes to JSON, because datetime type indeed dies not have a standard JSON representation. Maybe you don't need to convert it to JSON?

you can try this https://stackoverflow.com/questions/11875770/how-to-overcome-datetime-datetime-not-json-serializable

I tried all the above. I will search more on. Ty anyways

I think the problem is not with Pony but with some other library that trying to convert data to JSON

Explicitly cast the datetime to either an integer (unix time) or string, depending on what your use case is

@akozlovsky Defently not a pony issue (but I will create a simplified example to show the case). @matthewrobertbell that was my solution in the end. A date string.

I finally got around to using @db_session(strict=True), and it is working very well, thank you! Took a process from using 45GB of ram after 35 minutes of runtime to 46MB and not growing, with minimal code changes

How does strict mode behave with nested db_sessions?

I'm not near computer right now, but if I remember correctly strict mode ignores nested db_sessions just as normal mode

Ok, it works fine with this kind of pattern: @db_session(strict=True) def process(id): x = X.get(id=id) # do processing with db_session(strict=True): ids = select(x.id for x in X) for id in ids: process(id)

If I have two or more lambda functions: a = lambda r: r.position < 10 b = lambda r: r.position > 5 Is there a way at run time to do something like: Result.select(a | b) where it is turned into "r.position < 10 or r.position > 5" ?

Currently no, you can somewhat circumvent it using textual queries: params = {} ors = [] params['param1'] = 10 ors.append("r.position < params['param1']") params['param2'] = 5 ors.append("r.position > params['param2']") q = Result.select() if ors: q = q.filter("lambda r:" + " or ".join(ors))

I imagine supporting arbritary lambda combining would be very tricky

especially with nesting

I think it may be error-prone, because top-level OR may give surprising results delete(x for x in Result if <...pretty complex condition...> or <...too broad criteria...>)

Yeah, it would be a case of "user beware"

I think it wouldn't need to support "and", as filter covers that?

filter(a).filter(b) means a AND b

yes

Unless you want nesting, like (( a &b) | c ), built from lambdas, then you need a way to combine with AND

something like lambda_and(a, b) or lambda_or(a, b)

more generally, lambda_and/or(*args)

Maybe it will be better to add query uion/intersect at some point like query1 & query2 | query3, this API looks easier to understand

Do you think either is better than the other in terms of query efficiency?

I think it is not necessary to translate it to SQL UINON/INTERSECT, in most cases we can combine it to a single query with and/or

that would be clever

would nesting like: new_query = (q.filter(a) | q.filter(b)) & q.filter(c) be possible in theory?

I think yes, if all queries have the same result type, which is some entity

Cool, I think it would add a lot of power to Pony

I think we need migration tool first of all 😜

Yep.

I think it is not necessary to translate it to SQL UINON/INTERSECT, in most cases we can combine it to a single query with and/or

Performance says thank you :-)

Is there a more ponic way to write this : roles = [r.to_dict() for r in select(r for r in Role)] or I am in the right path . I just asking to start understading best practices with pony..

do you need all of the attributes of Role?

if so,, and if you need it as a dict, then that makes sense

I usually just access the values directly, r.x and r.y etc

I am trying to access db.Entity attrs in order to create a model diagnostic form. Assume that I have a User Entity from the source code, User._adict will return `{'dt_last_visit': User.dt_last_visit, 'dt_registered': User.dt_registered, 'email': User.email, 'first_name': User.first_name, 'id': User.id, 'last_name': User.last_name, 'password': User.password, 'roles': User.roles, 'username': User.username, 'uuid': User.uuid}. I can not find a way to get the type of Entity field so I can return { 'dt_last_visit': datatime, 'email': str }` etc. Any way to do that?

User.first_name.py_type

You can do: {attr.name: attr.py_type for attr in User._attrs_}

@akozlovsky ty , awesome

so- I'm sorry for being 'that guy' as this question isn't Pony related. But I've not found great material in PEP 8 or anything definitive on stackoverflow, etc. When creating a class, what's the best practice for checking that the data coming in is clean? assert statements? and where- I saw someone reference putting that in the __init__ method was bad practice. Any advice would be greatly appreciated

Problem with assert is it raises an AssertionError, but you should probably raise an ValueError or TypeError.

like with == type() ?

or is there something cleaner

I would say it could be the job of the code that uses the class to make sure its data is correct, separation of concerns etc

like with == type() ?

if isinstance(variable, SomeObject): This also allows subclasses

@luckydonald would that be good practice?

@matthewrobertbell you think people would feel that way if you were coding for a job interview?

Yeah, isinstance is way cleaner. You rarly use type(...)

and checks in the constructor, ok - or bad practice?