Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d8-5.9.1d9.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d8-5.9.1d9.sql,v
diff -u -N
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d8-5.9.1d9.sql	7 Aug 2017 23:47:56 -0000	1.2
@@ -0,0 +1,132 @@
+--
+-- Type discrepancy cleanup for object_types in OpenACS:
+--
+-- Fixing an inconsistency introduced in 2002: In PostgreSQL the table
+-- "acs_object_types" the type of column "object_type" is
+-- varchar(1000), while the supertype has varchar(100);
+--
+--   https://github.com/openacs/openacs-core/blame/oacs-5-9/packages/acs-kernel/sql/postgresql/acs-metadata-create.sql#L26
+--
+-- Similarly, the type of column acs_objects.object_type has
+-- varchar(100). These attributes have a length of 1000 in the Oracle
+-- versions.  An additional consequence of this discrepancy is that
+-- casts are required when resolving the object-type-tree with
+-- recursive queries. So, aligning these column types is desirable.
+-- Another option would be to use type "text" instead if
+-- varchar(1000), but such a change would require a much larger
+-- cleanup and the result would not be compatible with Oracle.
+--
+-- Unfortunately, there are several other tables affected to address
+-- this type discrepancies, since these use the object_type as foreign
+-- keys.
+--
+
+ALTER TABLE acs_object_types           ALTER COLUMN supertype       TYPE varchar(1000);
+-- ALTER TABLE acs_objects             ALTER COLUMN object_type     TYPE varchar(1000);
+ALTER TABLE acs_attribute_descriptions ALTER COLUMN object_type     TYPE varchar(1000);
+-- ALTER TABLE acs_attributes          ALTER COLUMN object_type     TYPE varchar(1000); 
+ALTER TABLE acs_object_type_tables     ALTER COLUMN object_type     TYPE varchar(1000);
+-- ALTER TABLE acs_rel_types           ALTER COLUMN object_type_one TYPE varchar(1000);
+-- ALTER TABLE acs_rel_types           ALTER COLUMN object_type_two TYPE varchar(1000);
+-- ALTER TABLE acs_rel_types           ALTER COLUMN rel_type        TYPE varchar(1000);
+ALTER TABLE acs_static_attr_values     ALTER COLUMN object_type     TYPE varchar(1000);
+ALTER TABLE group_type_rels            ALTER COLUMN group_type      TYPE varchar(1000);
+ALTER TABLE group_types                ALTER COLUMN group_type      TYPE varchar(1000);
+ALTER TABLE group_rels                 ALTER COLUMN rel_type        TYPE varchar(1000);
+ALTER TABLE group_type_rels            ALTER COLUMN rel_type        TYPE varchar(1000);
+-- ALTER TABLE group_element_index     ALTER COLUMN rel_type        TYPE varchar(1000);
+
+--
+-- Unfortunately, we can't do simply
+--
+--    ALTER TABLE acs_objects      ALTER COLUMN object_type TYPE varchar(1000);
+--
+-- since many views include the attribute "object_type", including
+-- many application packages. The genererally recommended way is to
+-- drop and recreate the views, but this is for a kernel upgrade not
+-- feasible. Since the length change is not a real type change, we can
+-- simply update the the length information in the pg_attribute table.
+
+WITH RECURSIVE dependent_views AS (
+    SELECT c.oid::REGCLASS AS view_name
+      FROM pg_class c
+     WHERE c.relname = 'acs_objects'
+     UNION ALL
+    SELECT DISTINCT r.ev_class::REGCLASS AS view_name
+      FROM pg_depend d
+      JOIN pg_rewrite r ON (r.oid = d.objid)
+      JOIN dependent_views ON (dependent_views.view_name = d.refobjid)
+     WHERE d.refobjsubid != 0
+)
+UPDATE pg_attribute 
+   SET atttypmod = 1000 + 4
+  FROM dependent_views   
+WHERE pg_attribute.attrelid = dependent_views.view_name
+AND   pg_attribute.attname = 'object_type';
+
+--
+-- ALTER TABLE acs_attributes             ALTER COLUMN object_type     TYPE varchar(1000); --deps
+--
+WITH RECURSIVE dependent_views AS (
+    SELECT c.oid::REGCLASS AS view_name
+      FROM pg_class c
+     WHERE c.relname = 'acs_attributes'
+     UNION ALL
+    SELECT DISTINCT r.ev_class::REGCLASS AS view_name
+      FROM pg_depend d
+      JOIN pg_rewrite r ON (r.oid = d.objid)
+      JOIN dependent_views ON (dependent_views.view_name = d.refobjid)
+     WHERE d.refobjsubid != 0
+)
+UPDATE pg_attribute 
+   SET atttypmod = 1000 + 4
+  FROM dependent_views   
+WHERE pg_attribute.attrelid = dependent_views.view_name
+AND   pg_attribute.attname = 'object_type';
+
+
+--
+-- ALTER TABLE acs_rel_types           ALTER COLUMN object_type_one TYPE varchar(1000);
+-- ALTER TABLE acs_rel_types           ALTER COLUMN object_type_two TYPE varchar(1000);
+-- ALTER TABLE acs_rel_types           ALTER COLUMN rel_type        TYPE varchar(1000);
+--
+WITH RECURSIVE dependent_views AS (
+    SELECT c.oid::REGCLASS AS view_name
+      FROM pg_class c
+     WHERE c.relname = 'acs_rel_types'
+     UNION ALL
+    SELECT DISTINCT r.ev_class::REGCLASS AS view_name
+      FROM pg_depend d
+      JOIN pg_rewrite r ON (r.oid = d.objid)
+      JOIN dependent_views ON (dependent_views.view_name = d.refobjid)
+     WHERE d.refobjsubid != 0
+)
+UPDATE pg_attribute 
+   SET atttypmod = 1000 + 4
+  FROM dependent_views   
+WHERE pg_attribute.attrelid = dependent_views.view_name
+AND   pg_attribute.attname in ('object_type_one', 'object_type_two', 'rel_type');
+
+--- 
+-- ALTER TABLE group_element_index        ALTER COLUMN rel_type        TYPE varchar(1000);
+-- 
+WITH RECURSIVE dependent_views AS (
+    SELECT c.oid::REGCLASS AS view_name
+      FROM pg_class c
+     WHERE c.relname = 'group_element_index'
+     UNION ALL
+    SELECT DISTINCT r.ev_class::REGCLASS AS view_name
+      FROM pg_depend d
+      JOIN pg_rewrite r ON (r.oid = d.objid)
+      JOIN dependent_views ON (dependent_views.view_name = d.refobjid)
+     WHERE d.refobjsubid != 0
+)
+UPDATE pg_attribute 
+   SET atttypmod = 1000 + 4
+  FROM dependent_views   
+WHERE pg_attribute.attrelid = dependent_views.view_name
+AND   pg_attribute.attname = 'rel_type';
+
+
+
+