2

I am new to using Greenplum DB, was working on Oracle DB. I understand conceptually Greenplum in columnar DB and has different workings then Oracle.

The question I have is regarding altering table column length. I created a table with a column with say data type character varying(50). On top of this table I created a view which is summarizing some information. Later I wanted to change the length of column to character varying(100). It is not allowed in GP.

I had to drop view, change the column length and then re-create the view. I do not believe that this was the case on Oracle. Not sure why GP has to to this.

Does someone has understanding as to how to avoid this?

1

It's not even about the type or subtype. Changing a column on a table requires you to drop and recreate all dependencies that reference it. You can do this in a single transaction.

CREATE TABLE foo ( a varchar(10) );
CREATE VIEW bar AS TABLE foo;

BEGIN;
  DROP VIEW bar;
  ALTER TABLE foo
    ALTER COLUMN a
    SET DATA TYPE varchar(100);
  CREATE VIEW bar AS TABLE foo;
COMMIT;
  • Thanks Evan for confirming what we were observing. Additional challenge is to find all dependencies i.e. these views are dependent on this table etc. from GP database. Currently we were making change and based on error message, were doing drop and re-create. Is there any better approach – user5319411 Jun 2 '17 at 12:43
  • Duplicate the database schema with pg_dump -s load it into a new database, then DROP TABLE foo CASCADE; and see what falls. – Evan Carroll Jun 3 '17 at 16:09
  • pgAdmin will do a dependents report which should list everything. – PhilHibbs Sep 26 '17 at 11:03
0

Whenever you alter or drop/create a table in Greenplum, the associated ID will change in metadata tables. Hence a view referring to old ID won't work. That's the reason why the view need to be recreated.

Steps:

  1. ALTER TABLE
  2. DROP VIEW
  3. RECREATE VIEW

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.