[Martijn] On Tue, Apr 03, 2001 at 11:27:41AM -0400, Paul Everitt wrote: [Albert]
A third open source community has put major resources into turning postgresql 7.1 into an industrial strength RDBMS quite capable of replacing Oracle in many situations, which is also about to get python as a built in backend procedural language. [Paul] I hadn't heard that Python was going to be a stored procedure language for PostgreSQL. Can you send me a link?
[Martijn] Google gave me the following reference: http://www.postgresql.org/mhonarc/pgsql-hackers/2000-08/msg00432.html [Albert] No further work was published on that since August 2000 until a few days ago when a Postgresql 7.1 version was made available (31 March): http://users.ids.net/~bosma/plpython-310301.tar.gz Not sure whether it has been announced yet - author still planning better build integration plus handling of imports and postgresql vectors/arrays. Some significant features (to me) are: 1) Any arbitrary cPython module can be imported - though currently the list of permitted modules is statically compiled into the restricted environment bastion. 2) A dictionary SD is available shared among all calls to a function and another GD shared among all calls to any function. Both are per connection/backend and know nothing about statements or transactions. 3) Arbitrary SQL (which includes calls to other procedures including other procedural languages) can be prepared and executed separately within a function. This means it can work with the same efficiency of pre-compiled query plans as the "native" plpgsql procedural language. Should be possible to prepare the queries for an application after opening a database connection and then just reference the query plans via SD or GD. This is vastly more efficient for industrial strength databases that have serious query planning than the usual approach of just feeding the original SQL for parsing and query planning on each access. Looks to me like it's a significant advance compared with Oracle's embedded java. Combined with Postgresql's abilities to create abstract data types, operators, aggregates etc and it's incredible "Rules" system that effectively gives completely writable views on composite objects, with complex logic, this should be dynamite. BTW it was published following Oleg Broytmann's passing remark around 19 March in: "RE: [Zope] Re: [Zope-Annce] Localized DateTime classes" "PS. [offtopic] Did you see my question on Python in Postgres backend?" I did a quick search through Zope archives and found nothing, so wrote to Oleg for more info as I'd been wishing for such a thing and hadn't heard of one. He promptly provided some leads from an email reply from Hannu Krosing and I tracked down a list of about half a dozen people who had expressed interest in this at one time or another and fired off an email suggesting that and another unfinished attempt should be taken up as a Project to get the job finished (also BCCs to 1 staffer each at Zope and Active State in view of potential importance for them). Got a prompt response from Andrew Bosma (author of above), promising to finish it in a week or so, which he then did. (Aside to Tom Jenkins - he didn't seem to be at all insulted at my suggestion, despite my explicitly mentioning that I lacked the C and Postgresql skills to offer any help at all). I think I'll wait more than my customary 24 hours before responding to the rest of Paul's messages and also delay responding to Walter as that overlaps with responses to Paul. But I'll respond now to one item as it directly relates to above. [Paul] I'm not sure how much consulting you've done, but from what I've seen, people don't change database infrastructures because there's a new one that's free. Perhaps *new* customers will choose the free one, but that's a long-haul proposition. If you haven't, then read "Crossing the Chasm". Your arguments appeal to the early market, but scare the hell out of the mainstream market. It takes time for new things like Python, Zope, the ZODB, and PostgreSQL to penetrate the mainstream. Besides, you've lost me here. Is the goal to gain e-commerce (whether from OpenACS, RedHat Interchange, or some other solution), or is the goal to sell PostgreSQL to our consulting customers? [Albert] My impression is that Oracle shops tend to be stuck with Oracle for much the same reasons that Microsoft shops tend to be stuck with Microsoft etc. These things change over time and there are signs of significant changes concerning mainstream databases - eg when SAP recently released sap db (not the SAP applications) as open source, they mentioned that they thought databases were now pretty much a commodity infrastructure, which seems odd in view of Oracle's licensing fees - but they did do it - with some 200 staff still deployed on maintaining it. Certainly there isn't much DC can do about that as your core competency lies elsewhere. But the area you are in with a web application platform (sometimes) requires an RDBMS (eg for ecommerce as opposed to content management), for 2 quite different reasons: First, for integration with internal systems. For example the arsDigita ecommerce requirements documents indicate that they have no intention of doing much work on fulfilments or inventory or accounting because these just aren't what they know about so the integration with internal systems is done better by others (though they do of course provide the interfaces and database adaptors to be able to connect). For that, you certainly need Oracle and other mainstream adapters (insofar as you need more than ODBC), and the customers are stuck with having to buy whatever additional Oracle licenses they need to handle whatever additional volume their internal systems have to cope with as a result of ecommerce. A related issue is that even given the licensing fees for Oracle, there's an awful lot of SQL applications code and tools that won't just transfer smoothly to Postgresql's dialect, so despite now being rock solid and in many ways superior, there are still some rational arguments for preferring a mainstream RDBMS when you are *not* doing something more or less self-contained but needing a general purpose database platform for numerous projects. (This problem is greatly reduced by increasing SQL92 compliance of Postgresql and especially availability now of outer joins). It isn't just a matter of "new" customers, but what the DBMS will actually be used for. There's a second reason for needing an RDBMS despite the great strengths of an OODBMS like ZODB for content management and many other aspects of a web application platform - there are some things that work much better with an RDBMS - especially for online ecommerce. In my view it just doesn't make sense to handle standard order processing and financial transactions in an ODBMS - especially one aggressively optimized for reads rather than writes like ZODB. Whether that view is correct or not, the idea of doing so "scares the hell" out a lot of people who are used to being able to enforce certain business rule constraints, auditing policies etc in database schemas they can understand rather than trust to whatever the application programmers are up to, and need reports and OLAP as well as OLTP. So rightly or wrongly, an ecommerce platform that doesn't use an RDBMS for that will have great difficulty crossing the chasm. Furthermore, an ecommerce platform that doesn't use a mainstream RDBMS for that will have great difficulty too, although in my view for "fud" reasons rather than good reasons since a commerce server *is* far more self-contained and only needs the ability to *interface* with internal systems rather than replace them as a general purpose DBMS. Now where's the disagreement. Well, this is one of the things that makes recent developments with ACS and OpenACS so interesting. With ACS4, arsDigita has done, (for other reasons), the minimum necessary separation of a proper database API that makes it much easier for OpenACS to port to Postgresql. Instead of just doing an easier port to Postgresql, OpenACS has decided to support *both* immediately and would also like to be an "umbrella" for ports to other databases. If someone were trying to cross the chasm with a "whole product" solution, they would need to cover the "whole" of the online order and payment processing as well as catalog display etc as an integrated solution that can interface to internal systems. But the natural interface is with internal fulfilments (also done online for some things) and inventory and accounting etc - not with separate internal systems for order and payment processing since online orders and payments are significantly different and need integration with the web platform far more than with corresponding internal systems for other types of orders and payments. Having a solution that can *either* provide "reassurance" that their existing Oracle DBA and developer skills can be applied *or* offer a dramatically lower total cost of ownership plus the increasingly recognized benefits of open source with Postgresql for the web backend (as opposed to internal system interfaces), can *only* be an advantage. Especially when both the data modelling and SQL for that has been done for you elsewhere and is freely available. Demonstrating that you can also offer some add ons with the Postgresql version leveraging your python skills in the backend as well as in the web platform, that are far more difficult to do with java solutions, should also be a selling point - not for Postgresql but for what *you* can be consulted about (and for what your customers might think they can maintain and extend further in house with a simple scripting language rather than heavy weight java development). The *way* that OpenACS has gone about supporting ports for multiple databases is also important. They are using a Query Dispatcher to select dialect specific SQL for the configured database so their Tcl API is effectively database independent. (SQL phrasebook or Library pattern). This can largely separate the SQL porting work from the actual web application server and makes it dramatically easier to port to Zope. An automated SQLextractor tool is being developed (in python ;-) for collecting the embedded DQL and DML mingled with Tcl and re-writing the Tcl to get it from the Query Dispatcher instead. As Randall just mentioned, Postgresql is an ORDBMS not just an RDBMS. Now with a "cool" python stored procedural language (which does *far* more than just triggers). Looks to me like a perfect complement to Zope - especially since you already have a transaction manager that can distribute transactions across both internal ODBMS (ZODB) and ORDBMS (Postgresql) and "legacy" external RDBMS (like Oracle). If you've been managing to cross the chasm in the Content Management area using non-mainstream technologies like python and ZODB, I think you could do rather well in other areas as well with a python enabled ORDBMS that seems a perfect complement to ZODB plus what OpenACS has to offer. The metadata/operational separation in the ACS kernel is also *very* complementary to Transwarp. BTW in case I am giving the wrong impression, ecommerce as such is far from being central to what ACS does (they haven't even released the ecommerce module for ACS 4 java yet, though the skeleton framework plus workflow engine points to something very interesting about to happen). ACS stands for the arsDigita *Community* System. They understand a *lot* about the relationship between ecommerce and building an online community that fits very well with Zope's approach to content management. What they do isn't adequately described by "personalization" and it does need an RDBMS rather than an ODBMS aggressively optimized for reads over writes. (Their tuning is for a high ratio of queries to updates as with any web service, but they do a *lot* more tracking than is feasible with ZODB).