LedgerSMB Dev Blog

I am actually thinking of writing one. My blog (yes I am the author of the article, http://ledgersmbdev.blogspot.com) is basically running a fairly large series on these features. The big thing to realize is that there are two aspects here: development efficiency and complexity cost on one hand, and db performance on the other. A lot of the O/R capabilities really do have a significant complexity cost and it is easy to code solutions that don't work very well performance-wise or otherwise. In a bit over a week, I will have general recommendations out on the blog entry. As a brief preview: 1: table methods are generally good 2: nested storage is generally bad 3: table inheritance is frequently used for the wrong things. It is a very powerful technique if you think in terms of composition, but not so great if you think in terms of inheritance. 4: avoid to the extent you can putting tuples in columns. Postgres gets really weird in these cases.

> I'm personally a little annoyed at PG's efforts to turn SQL into half an OO programming language (see http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-model... for examples) I read that link and didn't get it - has anyone a fuller explanation or other links to recommend?

Chris Travers from the LedgerSMB project has done a lot of thinking about this topic on his blog. http://ledgersmbdev.blogspot.com/ They use PL/pgSQL to provide a bunch of service interfaces, then have Perl that provides automatic service discovery and object generation. Basically ORM from the inside out.

Good post, but I do not understand how this involves with Postgres. Perhaps a link to a list thread or some other background might be included? N/m. This answers my question http://ledgersmbdev.blogspot.com/2023/03/the-coming-storm-geopolitics-… (same blog, different entry)

Regarding the inheritance features, he's been writing an extensive series on the subject recently: http://ledgersmbdev.blogspot.com/2012/08/intro-to-postgresql-as-object… (though he should provide forward/backward links in the posts -- as it stands you need to find the other parts in the archive links on the right)

If I was doing brand new development somewhere I'm sure I'd use Postgresql, since from a developer point of view it's the most consistent and flexible. While for the last few years I've worked way more with MySQL / MariaDB and at the moment the MySQL side of things is a bit more familiar to me, I still appreciate PG's vastly superior query planner and index features. Certainly in the SQLAlchemy world when i want to see how a database does something "correctly", Postgresql is where you go to see the "correct" way to do something (such as, the correct answer for "SELECT NULL IN (SELECT 1 WHERE 1=0)" - the answer is false, not NULL, or when I had to convince MariaDB developers, who just added real CHECK constraints in 10.2, that I should be able to drop a column that has a single-column CHECK constraint on it without first dropping the constraint, PG serves as the canonical example for "yes this is supposed to work"). I'm personally a little annoyed at PG's efforts to turn SQL into half an OO programming language (see http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-modelling-part-… for examples), but for selfish reasons; features like these are meant as replacements for what you get from a tool like SQLAlchemy Core and the unusual syntaxes they have are also really hard to implement yet I continually get asked to implement more of them. These aren't reasons I wouldn't use Postgresql it's just this little cloud of "ugh" that hangs over me when I…