Forum OpenACS Q&A: Upgrade problems for acs-kernel 4.6.1 -> 4.6.2 on PostgreSQL

Collapse
1: Upgrade problems for acs-kernel 4.6.1 -> 4.6.2 on PostgreSQL
Posted by Peter Marklund on 05/22/03 02:17 PM
We are experiencing the following strange behaviour: 
gmt-mdb=# drop view acs_privilege_descendant_map_view;
NOTICE:  identifier "acs_privilege_descendant_map_view" will be truncated to "acs_privilege_descendant_map_view"
ERROR:  view "acs_privilege_descendant_map_view" does not exist

gmt-mdb=# create view acs_privilege_descendant_map_view
as select distinct h1.privilege, h2.child_privilege as descendamnt
   from acs_privilege_hierarchy_index h1, acs_privilege_hierarchy_index h2
   where h2.tree_sortkey between h1.tree_sortkey and tree_right(h1.tree_sortkey)
   union
   select privilege, privilege
   from acs_privileges;

NOTICE:  identifier "acs_privilege_descendant_map_view" will be truncated to "acs_privilege_descendant_map_view"
ERROR:  Attempt to insert rule "_RETacs_privilege_descendant_ma" failed: already exists
We cannot drop the view and we cannot create it. What is the rule being referred to by PostgreSQL here?
Collapse
2: Re: Upgrade problems for acs-kernel 4.6.1 -> 4.6.2 on PostgreSQL (response to 1)
Posted by Don Baccus on 05/22/03 05:37 PM
PostgreSQL implements a query rewriting system that goes far beyond views in terms of functionality.  This goes 'way back in the history of PostgreSQL, before it's SQL days, when it was just Postgres (with its own query language.)

This query rewriting system allows you to define rules on insert, delete and update statements on a table.

When SQL's VIEW functionality was added to Postgres, the implementor chose to implement them using the existing rules system rather than implement a separate, parallel facility for VIEWs.  The rule you're seeing is a magic rule created as a side effect of the CREATE VIEW statement that originally created the 4.6.1 version of the view.

As to why it's failing, beats me.  That's very strange.  Looks like there's a "dangling rule" for some reason.  Or perhaps a name clash due to truncation?  Yeah, that's probably it ... the generated rule name is probably clashing with another generated rule name in the permissions system due to truncation.

What version of PG are you using?

Collapse
3: Re: Upgrade problems for acs-kernel 4.6.1 -> 4.6.2 on PostgreSQL (response to 1)
Posted by Jade Rubick on 05/22/03 06:35 PM
Peter, I had the same problem, as did someone else. Here's the bug for it:

https://openacs.org/bugtracker/openacs/bug?filter%2estatus=closed&bug%5fnumber=496

My guess was that I thought I had upgraded to 4.6.1, but I actually hadn't. There is a longer description on this link which describes how I fixed it, and what the problem was (as far as I could tell).