Chris Travers, of the LedgerSMB project, writes about PostgreSQL, object-relational features, Perl, and accounting software design.
HN commenters point to his long series on PostgreSQL inheritance and object-relational modeling, though one finds its SQL-as-OO approach confusing or annoying.
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-2-intro-to.html 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…
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.
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.html
(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)
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-and.html?m=1
(same blog, different entry)
> 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?