[Zope] Postgres help

[Chris Kratz]

Karl,

It probably doesn't help any, but I am using PoPy and PoPyDA to access my
postgres database without problem.  I did some tests this morning and like
and ilike both work as expected both via psql and from a zope sql method.
So that would make one think that the problem is with ZpyGreSQLDA.

I will say it has been my experience that sometimes it can be difficult to
tell exactly where the error is showing up between the database and zope.
Especially if you are using some pretty complex queries built on the fly.
There is no easy way (that we have found) to see what is actually being sent
to the database from within Zope(feature request, hint, hint).  We have
found on our site that turning on logging on postgres during development is
invaluable.  I just keep a session logged in with a tail -f on the log file
and I can see exactly the SQL statements that are passed to the backend.
This at least can tell you if the problem is really in the database adaptor
or if the database itself is having problems.  I will warn you that it
significantly slows down your database and of course makes gargantuan log
files.  It about doubled the access time on each page (ie from 0.06s to
about .17s on a typical page).

Here is a good place to start for setting up postgres for logging:
http://www.postgresql.org/idocs/index.php?runtime-config.html.
http://www.postgresql.org/idocs/index.php?postmaster-start.html

In our postgresql.conf we use
debug_level = 2
debug_print_query = true
and then started the server with something like this:
postmaster -D /usr/local/pgsql/data > logfile 2>&1 &

I am not a postgres expert, but this has worked for us.

For reference, I am using RH 7.1, postgresql 7.1, PoPy and PoPyDA, and Zope
2.3.2.  Hope that helps.

-Chris