c2c-oerpscenario team mailing list archive

["Ferdinand @ Camptocamp" <853198@xxxxxxxxxxxxxxxxxx>]

it's a general postgres problem
if a column is used in a view it can't be modified 

Example
psql (9.0.3)

create table test (col_1 varchar(64));

create view test_view as select col_1 from test;

alter table test alter col_1 type varchar(128);
ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view test_view depends on column "col_1"

More and more  columns are used in views used by analytics and reports
hence it becomes more and more cumbersome or even impossible to write
modules which must change column length or precision.

a possible solution for length is described here
http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data

SELECT atttypmod FROM pg_attribute
WHERE attrelid = 'test'::regclass
AND attname = 'col_1';
 atttypmod 
-----------
        68


 UPDATE pg_attribute SET atttypmod = 68+64
WHERE attrelid = 'test'::regclass
AND attname = 'col_1';


\d test
             Table "public.test"
 Column |          Type          | Modifiers 
--------+------------------------+-----------
 col_1  | character varying(128) | not null

\d test_view
          View "public.test_view"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 col_1  | character varying(64) | 

UPDATE pg_attribute SET atttypmod = 68+64
WHERE attrelid = 'test'::regclass
AND attname = 'col_1';

postgres=# \d test_view
           View "public.test_view"
 Column |          Type          | Modifiers 
--------+------------------------+-----------
 col_1  | character varying(128) | 


 insert into test values ('1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');

select length(col_1) from test_view;
 length 
--------
    100


probalby a similar solution can be found for precision or numeric types

-- 
You received this bug notification because you are a member of C2C
OERPScenario, which is subscribed to OpenERP Project Group.
https://bugs.launchpad.net/bugs/853198

Title:
  modifying columns which are included in a view fails

Status in OpenERP Server:
  Incomplete

Bug description:
  Example

  DETAIL: cannot alter type of a column used by a view or rule DETAIL:
  rule _RETURN on view hr_expense_report depends on column "unit_amount"

  IMHO if this type of error occurs the dependent view must be deleted using 
  - tools.drop_view_if_exists(cr, 'hr_expense_report')
  and the operation must be reexecuted to allow clean automated installation

  otherwise the
  - tools.drop_view_if_exists(cr, 'hr_expense_report')
  has to be included in a module which is installed independant  of the one creating the view.
  which has some advantages for the devloper to know but

To manage notifications about this bug go to:
https://bugs.launchpad.net/openobject-server/+bug/853198/+subscriptions