Postgres PL/Python

[Stuart Bishop]

Ksenia Marasanova wrote:

> I wonder if anyone on this list is using  Python as Postgres
> procedural language. I can't find a place for it i my mind. How would
> a typical MVC web application benefit from it  (besides performance)?
> I understand from the docs that Postgres 7.4 has PL/PythonU -
> unlimited functionality. It sounds like I have the whole Python
> available in Postgres. That means big parts of application logic can
> be moved to stored procedures, and dummy SQL layer becomes something
> else... sounds scary. Any opinions on this?

To start with, PL/PythonU procedures share the same use cases as any other
server side procedure:
	- Implementing triggers
	- Implementing non-trivial database constraints
	- Cases where code will be much faster when run in the RDBMS
          environment rather than in the client. (as an obvious example,
          you are better off running 'SELECT sum(foo) FROM bar' than
          sucking all the results to your client and summing them there.
        - Cases where the code can't be rolled out onto the clients. eg.
          it contains secrets, is proprietary and the client runs in
          untrusted environment.
        - You need to access a resource only available on the database
          server, such as reading a secret from a file, logging
          information to a central log, making use of cached information
          (if each of your clients implements their own caching, it can
          be inconsistant between them)
        - Code you want to run on transaction commit without having to
          put all that transactional logic in your client.
        - Providing an interface to data that should not be available
          to clients. eg. validating a password is correct without
          needing to send the plaintext, encrypted or hashed password
          to the client.
        - Wierd environments where it is easier to change code in the
          central database than on the clients (eg. the clients are
          installed on laptops that are not always on site or network
          connected, or internal beurocracy makes updating clients
          non trivial).
        - Allows logic to be coded and maintained in one place
          and in one language by someone who understands the database
          and the data, rather than having it reimplemented for each
          different client environment, probably incorrectly and
          possibly dangerously

Of course, only some or none of these may be appropriate to your
environment. Or you might have other use cases. I doubt anyone could
actually agree on what 'a typical MVC web application' is. If you have a
trivial system where the database and a single web app run on the same
server and are maintained and developed by the same people, I doubt there is
any reason to use stored procedures. But when you go beyond that you might
find you do.

PL/PythonU procedures have the extra nice use case of being able to share
code between database and clients when the clients are written in Python
(and similarly PL/Perl, PL/Tcl or PL/Java when the clients share the
language). This means your database constraints can use the same algorithms
that the clients are supposed to be using, providing a safety net in case
someone forgets in their code to validate the data before tossing it into
the database or a client for some reason ends up running an old version of
the validatation code.

In general it is less work to keep code client side because it is easier to
modify, test and debug. testing can be done once you have created suitable
fixtures (not trivial, but doable). Debugging is much easier with PL/Python
than, for examplle, PL/SQL because if you structure things correctly you can
run much of your code outside of the database. However, for debugging code
that talks to the internal database APIs you are still stuck with just the
equivalent of 'print' as your toolkit.

-- 
Stuart Bishop <stuart at stuartbishop.net>
http://www.stuartbishop.net/
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 196 bytes
Desc: OpenPGP digital signature
URL: <http://mail.python.org/pipermail/python-list/attachments/20050916/ca0d12ce/attachment.sig>