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.

What to do?

How to implement custom algorithm of fk_name generation? Thanks.

And one more question: class FridgeServiceParameter(db.Entity): ... ValueElementListSet = ... attribute ValueElementListSet replace to valueelementlistset in the process of schema generation. How to change the generation process so that the name of the attribute is generated as is? With double quotes "ValueElementListSet"?

Only option with the indication of attribute parameter "column"?

I just saw this on Hacker News, seems relevant to pony users :) http://tatiyants.com/postgres-query-plan-visualization/

I just saw this on Hacker News, seems relevant to pony users :) http://tatiyants.com/postgres-query-plan-visualization/

This is cool!)

Also https://github.com/simon-engledew/gocmdpev

postgres, pony 0.7.2: class SalePointParameter(db.Entity): _table_ = ['core', 'SalePointParameter'] ID = PrimaryKey(int, auto=True, column='ID') Name = Required(str, 150, column='Name') ...... class SalePointParameterElementList(db.Entity): _table_ = ['core', 'SalePointParameterElementList'] ID = PrimaryKey(int, auto=True, column='ID') List = Required('SalePointParameterList', column='ListID') # ListID -> ID ..... generate sql CREATE TABLE "core"."SalePointParameterList" ( "ID" SERIAL PRIMARY KEY, "Name" VARCHAR(256) NOT NULL, ... CREATE TABLE "core"."SalePointParameterElementList" ( "ID" INTEGER NOT NULL, "ListID" INTEGER NOT NULL, Whyyyyyy...? :(

Hi Andrew, what do you mean? Your code contains many missing parts, and cannot be executed. Probably, there is some SalePointParameterList entity which Python definition is missed here. Also, reverse attributes for relations are not shown

How to implement custom algorithm of fk_name generation? Thanks.

We need to add the possibility to specify custom foreign key names. It will be great if you open an issue for that: https://github.com/ponyorm/pony/issues

We need to add the possibility to specify custom foreign key names. It will be great if you open an issue for that: https://github.com/ponyorm/pony/issues

I think he is asking about overriding the method

Problem with custom fk name is solved: replace get_default_index_name and get_default_fk_name by MethodType before call generate_mapping.

Yeah that's similar to what I'd do

Yes. Also it is possible to replace normalize_name() method which uses for all names, not only foreign keys and indexes. In 0.8 release we will change the algorithm of name cutting, the new algorithm will add unique hash to the name. This change is tied with migration, because Pony will need to upgrade existing databases to new name convention

Good news! :)

> How to change the generation process so that the name of the attribute is generated as is? With double quotes "ValueElementListSet"? You can replace get_default_column_names() method. Probably you already discover that as well

I tracing code now. With Optional/Required to null/not null same problems.

You can explicitly set nullable attribute option

hi

Does Pony ORM support Mariadb?

You can explicitly set nullable attribute option

Script and result. It may be necessary to specify reverse?

ALTER TABLE "core"."SalePointParameterElementList" ADD CONSTRAINT "fk_('core', 'salepointparameterelementlist')__distributionregio" FOREIGN KEY ("DistributionRegionElementListID") REFERENCES "core"."SalePointParameterElementList" ("ID") It seems we have a bug in get_default_fk_name method which shows itself when a table name contains a schema name

We need to fix that

Does Pony ORM support Mariadb?

It should, although we didn't test that specifically. AFAIK, most syntax should be the same as for MySQL. We have an open issue that JSON fields in Pony don't work in MariaDB.

We need to fix that

Script generated without replaced method - pure pony 0.7.2 (reinstalled): CREATE TABLE "core"."SalePointParameterElementList" ( "ID" INTEGER NOT NULL, ... but class SalePointParameterElementList(db.Entity): _table_ = ['core', 'SalePointParameterElementList'] ID = PrimaryKey(int, auto=True, column='ID') ...

This result after replace methods.

It should, although we didn't test that specifically. AFAIK, most syntax should be the same as for MySQL. We have an open issue that JSON fields in Pony don't work in MariaDB.

this is what I found exactly the same with sqlalchemy

What ways exitst to handle a OptimisticCheckError?

pony.orm.core.OptimisticCheckError: Object WebSession[UUID('c9541cfd-4458-4aba-8d0e-a768a93befd2')] was updated outside of current transaction

Am still trying to figure out what started them ~8 hours ago

Am still trying to figure out what started them ~8 hours ago

I realized the last_used column of that UUID in the database is set to 2017-07-25 08:02:06.213536, which is where the error started

Optimistic check error arises in the following situation: 1) db_session A loads object X and read the value a1 of attribute a 2) db_session B loads object X, updates attribute a to value a2 and commits successfully 3) db_session A continues to working. It modify attribute X.b to value b2 and try to save the object Pony issues the update query: update object X and set value of X.b to b2 if value of X.a is still v1. But the update fails, because other transaction already changed the value of X.a In order to prevent Optimistic check error you can do one of the following: 1) Make transactions fully isolated: db_session(serializable=True). This make sense if value of X.a is very important for transaction consistency 2) Lock X object by loading it using select_for_update: x = X.get_for_update(id=123). Other transactions will not be able to work with this object in parallel. 3) Mark X.a attribute as volatile if its value is not essential for transaction integrity and can be changed at any time: volatile=True 4) Turn off optimistick checks for X.a attribute: optimistic=False. This way the attribute will not be included in optimistick checks, but still may raise an error if the object was re-read from the database and the value of attribute was changed.

Probably the best option for you is to mark last_used attribute as volatile

Problem here is, I don't know which attribute was changed.

Might indeed be only that field.

Hi. How to get generated script without execute to db? Thanks.

Bug found. Great imperative programming with side effect :( if field PrimaryKey(Id) is in additional index, then on second index create DBIndex.init() modify attribute in column: for column in columns: column.is_pk = len(columns) == 1 and is_pk column.is_pk_part = bool(is_pk) column.is_unique = is_unique and len(columns) == 1 step 1: create is_pk = 'auto' and column.is_pk = 'auto' step 2: create regular index is_pk = False and column.is_pk = False and finish: infformation about PrimaryKey(ID) is dropped.

# Python 3.6.2 (v3.6.2:5fd33b5, Jul 8 2017, 04:57:36) [MSC v.1900 64 bit (AMD64)] from pony.orm.core import * sql_debug(True) db = Database() class SPParamElemList(db.Entity): ID = PrimaryKey(int, auto=True) Name = Required(str, 100) IsDeleted = Required(int) composite_index(ID, Name) db.bind('postgres', user='postgres', password='superuser', host='localhost', database='core_db') db.generate_mapping(create_tables=True) generated CREATE TABLE "spparamelemlist" ( "id" INTEGER NOT NULL, "name" VARCHAR(100) NOT NULL, "isdeleted" INTEGER NOT NULL ) CREATE INDEX "idx_spparamelemlist__id_name" ON "spparamelemlist" ("id", "name")

if composite_index(ID, Name) change to composite_index(IsDeleted, Name) then: CREATE TABLE "spparamelemlist" ( "id" SERIAL PRIMARY KEY, "name" VARCHAR(100) NOT NULL, "isdeleted" INTEGER NOT NULL ) CREATE INDEX "idx_spparamelemlist__isdeleted_name" ON "spparamelemlist" ("isdeleted", "name")

How to fix?

fast fix dbschema.py DBIndex.init after for column in columns: add if hasattr(table.pk_index, 'columns') and column in table.pk_index.columns: continue

https://pastebin.com/3BMfJ1tp I don't know why does this appear even though i didn't write to db in this project

;/

I think i should create issue

You didn't write to db in this db session or in any db session?