Accessing Zope from PostgreSQL
Created by pupq . Last modified 2004-01-21 09:38:29.
How to embed Zope server within PostgreSQL for super fast calls to the Zope API from within the database server. Useful for customized caching, and other DB/Zope integration tricks.
HOWTO: Accessing Zope from PostgreSQL
This HOWTO assumes that you are comfortable installing Zope and PostgreSQL.
In order to run Zope under PostgreSQL, you must be using PostgreSQL 7.4 or better. In addition, the Zope.configure() API call requires Zope 2.7.0b4.
Assume that you have a ZSQLMethod such that uses tables that rarely change. We could normally use ZSQLMethod caching to cache this data for a long period of time (say, an hour) to gain significant performance benefits.
However, if we do so, and the data changes, our users would see outdated data, which is often unacceptable.
A good solution is to cache the data, and have PostgreSQL be able to notify Zope when this ZSQLMethod should be reset.
To do this, we need to use a product such as CacheControlledZSQLMethods 2. This product, maintained by me but originally by Dieter Maurer, is a drop-in replacement for ZSQLMethods, except that a method shares its cache across threads and exposes an API call to clear the ZSQL Cache.
Alternatively, you could have the caching be done not in the ZSQLMethod but in a skin like a PythonScript or PageTemplate, and have these cached by the RAM Cache Manager, in which case it's this cache we'd want to clear. The idea is the same.
When the data changes, we can have a trigger in PostgreSQL inform our Zope of the change, triggering the cache clearing.
You can use this to re-index your ZCatalog, should you be using a solution where the ZCatalog holds searchable information about what is in your relational database. As the data in PG changes, we'd want to re-index our catalog.
Other uses for this are more sophisticated solutions to solve problems using stub-based object solutions, such as SQLStorage in Archetypes. These are cases where there is an object in the ZODB and a related object in the PostgreSQL database, and we want to make sure that when the object is deleted or added to PG, Zope makes necessary changes. For information on these cases, see SQLStorageHOWTO 3.
Of course, you'll want to use this technique with ZEO, so that PostgreSQL can connect to your ZODB at the same time as your live Zope server.
PostgreSQL can use PL/Python, an embedded Python interpreter that lets you execute Python functions in the database. This is an excellent solution for Zope projects, since it lets us use Python at all levels of our project. We'll use this capability here.
Before you can write PL/Python functions, you must enable this by adding this language to your database. From the shell:
$ createlang plpythonu your_db_name
Now, let's write a PL/Python functions to load the Zope application:
CREATE OR REPLACE FUNCTION zstart() RETURNS TEXT AS ' if GD.has_key("zope_app"): GD["app"]._p_jar.sync() return import sys sys.path.insert(0, "/usr/local/zope/zope_current/lib/python") sys.path.insert(0, "/usr/local/zope/zope_crrent/lib/python/Products") import Zope sys.argv=["zope"] Zope.configure("/var/zope/etc/zope.conf") GD["zope_app"]=Zope.app() from ZODB.Transaction import get_transaction GD["zope_get_trans"]=get_transaction ' LANGUAGE 'plpythonu';
GD is the persistent dictionary that is shared between all PL/Python functions on a database backend. By storing the Zope application object in GD, we'll be able to initialize the Zope database once, and be able to use it from any PL/Python function. First, we check to see if this has already been done, so we don't need to start Zope again if we've already started it. If we have started Zope, we call _p_jar_sync(), which aborts any existing transaction and lets us see changes from outside transactions.
If we haven't started Zope, we start it. First, we prepend the directories for Zope's source at the start of our Python path. Then we import the Zope object. Before we can configure Zope, though, we have to workaround one assumption that Zope makes: that we're calling from something like a command-line, where we'd have access to the command-line arguments. Since we're nowhere near a normal command line, we'll replace sys.argv with a list containing just one item, which Zope will interpret as the name of the executable.
Next, we call the .configure() method of the Zope object, to which we pass our Zope instance zope.conf file. Zope then uses the configuration file and we when call Zope.app(), below, we'll get the correct Zope application object, which we store in the GD persistent dictionary.
As a convenience, we'll also get the function get_transaction, and store that in GD as well. We'll use that later.
Now, we can access the ZODB and ZPublisher machinery from PG.
CREATE OR REPLACE FUNCTION show_root() RETURNS TEXT AS ' app=GD["zope_app"] return ",".join(app.objectIds()) ' LANGUAGE 'plpythonu'; CREATE OR REPLACE FUNCTION newfolder(text) RETURNS TEXT as ' app=GD["zope_app"] app.manage_addFolder(args) return args ' LANGUAGE 'plpythonu';
Our first function, show_root(), lists the objects at the root our Zope object. Our second function, newfolder(text), lets us add a new folder to the root of our Zope instance.
Before we can access the database, though, we need to call our zstart function:
database=# SELECT zstart();
And then we can call our real functions:
database=# SELECT show_root();
database=# SELECT newfolder('foo');
Our folder is created at the root, but unless we commit this transaction, it won't show up to other Zope clients or persist after the PostgreSQL connection. We'll add a function that commits our Zope transaction:
CREATE OR REPLACE FUNCTION zcommit() RETURNS TEXT AS ' GD["zope_get_trans"]().commit() ' LANGUAGE 'plpythonu';
We can call this function, and it will (using our conveniently-saved get_transaction function) commit our current transaction. If we commit this transaction, other clients of the ZODB can see our new folder.
So, normally, at the start of our commands, we'll execute zstart(), and, if all works well, we'll execute zcommit().
So, let's create a simple table:
CREATE TABLE test ( id SERIAL, t TEXT ); INSERT INTO test (t) VALUES ( 'apple' ); INSERT INTO test (t) VALUES ( 'banana' ); INSERT INTO test (t) VALUES ( 'carrot' ); INSERT INTO test (t) VALUES ( 'durian' );
We'll write a ZSQLMethod that requests data from this table; something like "SELECT * FROM test WHERE t = <dtml-var _whatever_>".
Now, when we add, delete, or change data in this table, we'll want to clear our cache:
CREATE FUNCTION test_clear_cache() RETURNS TRIGGER AS ' plpy.execute("SELECT * FROM zstart()") GD["zope_app"].cache_api_call_here() plpy.execute("SELECT * FROM zcommit()") ' language 'plpythonu';
This function initializes our connection, if needed, calls whatever our cache-clearing function is, and commits our transaction.
Our trigger that will call this is:
CREATE TRIGGER test_clear_cache_trig AFTER INSERT OR UPDATE OR DELETE ON test FOR EACH STATEMENT EXECUTE PROCEDURE test_clear_cache();
Now, whenever we make a chance to our table, the Zope cache will be cleared for us. Therefore, we can feel free to set longer-lived caches, knowing we can clear them as needed.
This document was written by Joel Burton 4. It is covered under GNU Free Documentation License, with one invariant section: this section, About this document, must remain unchanged. Otherwise, you can distribute it, make changes to it, etc.
If you have any comments, corrections, or changes, please let me know. Thanks!