[Zope] ZPyGreSQL error, pg.error, cache lookup failed -- resolved

[Bruce Elrick]

This message is for posterity (for people who run into this problem and search
through the Zope mailing list archives).

In response to a problem I asked in this message:
http://lists.zope.org/pipermail/zope/1999-October/012655.html

about this error in relation to the ZPyGreSQL Database Adapter
<!--
 Error type:  pg.error
 Error value: ERROR:  fmgr_info: function 21121: cache lookup failed
 -->

Ross Reedstrom, author of ZPyGreSQL responded with this message:
http://lists.zope.org/pipermail/zope/1999-October/012868.html

and now I've finally gotten around to using Ross's info to solve the problem.

The upshot is this is a PostGres problem not a Zope problem and it has to do
with added functions.

I added the check_primary_key and check_foreign_key functions found in the
refint.so library that comes with the PostGreSQL documentation; these add the
ability to define foreign keys in PostGreSQL (they currently aren't supported
explicitly).  You define the functions to PostGreSQL using the DEFINE FUNCTION
command and add triggers against the appropriate tables using the DEFINE
TRIGGER command.  When you define the triggers postgres loads the functions
and caches them against a trigger function object id (tgfoid).  If you do a
'DROP FUNCTION' followed by a 'DEFINE FUNCTION' you invalidate the cache and
you get the above errors in Zope or even the command line when the functions
should be called.

The solution is to DROP and re-CREATE the trigger definitions after you've
DROPped and re-CREATEd the functions.  This will set this straight.

Obviously you don't want any DB activity that might rely on the triggers being
there when you do this.  Before you do the drop/create any attempt to perform
a delete/update/insert that requires these functions for referential integrity
will fail and your data is integral.  After you have it fixed the
delete/update/insert will succeed and necessary checks/cascades will occur. 
During the drop/create of the triggers/functions, a delete/update/insert could
succeed while not preserving referential integrity.

Cheers...
Bruce

--
Bruce Elrick, Ph.D.                       Saltus Technology Consulting Group
Personal: belrick@home.com                          IBM Certified Specialist
Business: belrick@saltus.ab.ca          ADSM, AIX Support, RS/6000 SP, HACMP