Forum OpenACS Development: Use plpython stored procedures in PostgreSQL to replace Oracle embedded java?

Re Don's question about Oracle Java.

There's now a python stored procedure language for postgresql
which can import arbitrary cPython modules (currently list
of modules statically compiled but will be fixed). Should be
able to provide postgresql equivalent for anything aD does in java for Oracle version - eg the unit tests include a hash function similar to current needs for commented out routines in kernel.

Crypto hash functions running in C from python standard modules should be more efficient than java as well as easier to program. Might also speed up the trees stuff.

Plenty of stuff for mail etc in python modules - it's got a
comprensive collection of add-on modules like perl (still
smaller but much easier to use).

http://users.ids.net/~bosma/

BTW It uses the SPI backend same way as native plpgsql with
prepared queries so can be just as efficient as native query
planning. (Prepare the query plans when first establishing
a connection, then reference them from the global dictionary
they are stored in to use them later. The global dictionary
is per connection/backend and there is also another per
function - neither know anything about statements or
transactions).

Could also be interesting way to make use of the
Query Dispatcher in Postgresql specific version.

Instead of parsing each query each time it is called (no
big deal) and preparing an expensive postgresql query plan
each time (sometimes a big deal), might be possible to get the
highest possible performance with pre-planned queries using
auto-generated functions corresponding to the same apis but
work with the query plans dictionary rather than directly.

Also perhaps relevant to problem I see coming when aD migrates
logic from PL/SQL to web platform java as they move from
ACS 4.x java to ACS 5.

Postgresql version could just keep the logic inside
the database where it belongs - especially with an ORDBMS
like postgresql cf a primitive RDBMS like Oracle 😉

plpython released 31 March, still being polished:

http://users.ids.net/~bosma/

Author: Andrew Bosma [mailto:andrew@corvus.biomed.brown.edu]

Note this is entirely independent of porting to python
web platforms. Functions created are just like any
other postgresql functions used the same way from
any adaptor for any web platform.

Ask author for any further questions after reading the docs
and code - I'm unlikely to know the answers.