Forum OpenACS Development: Response to Weird error invoking a plpgsql function (content_revision__delete)

I ran into the same problem when I was testing this.  The problem is that the pl/pgsql functions cache references to things like tables, foreign key triggers and other pl/pgsql functions.  So when you drop a function or a table, you need to track down all of the dependencies and recreate the objects that depended on the original object that you changed.  For intance, when I changed the cr_item_publish_audit table, I had to drop and recreate the cr_items_publish_update_tr trigger because it refered to the cr_item_publish_audit table.  I also had to drop and recreate the content_revision__delete method, because it also refered to the cr_item_publish_audit table.  I then had to drop and recreate the content_item__delete method. because it depended on the content_revision__delete method.  It's easy to see how this can get unmanagable very quickly.  If you need to drop and recreate some core piece of your db schema, it's usually easier to drop the whole db and recreate it from scratch with your new changes incorporated in the data-model.

It's very important to watch these dependencies when your're doing maintenance on a production site.  You might not have the luxury of taking the site down and reloading it from scratch, and you could end with something that is broken if you make a simple change and don't follow all of the dependencies.

This is a definite weakness for postgresql, but I think they have added it to their todo list, and they plan to resolve it in some future release.