DROP COLUMN doesn't work if there is data in the table

[billykopecki]

Hello,

Has anyone else tried to test the DROP COLUMN feature in Vertica 6.0?

It seems to work when the table has no data, but as soon as it has data, it no longer works:

Code: Select all

dbadmin=> SELECT version();
              version
------------------------------------
 Vertica Analytic Database v6.0.0-0
(1 row)

dbadmin=> CREATE TABLE test (col1 INT, col2 VARCHAR(100));
CREATE TABLE
dbadmin=> ALTER TABLE test DROP COLUMN col2;
ALTER TABLE
dbadmin=> \d test;
                                List of Fields by Tables
 Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+------+------+---------+----------+-------------+-------------
 public | test  | col1   | int  |    8 |         | f        | f           |
(1 row)

dbadmin=> ALTER TABLE test ADD COLUMN col2 VARCHAR(100);
ALTER TABLE
dbadmin=> INSERT INTO test VALUES (1, 'test');
 OUTPUT
--------
      1
(1 row)

dbadmin=> ALTER TABLE test DROP COLUMN col2;
ROLLBACK 3128:  DROP failed due to dependencies
DETAIL:
Projection public.test_b0 has column col2 as part of its segmentation expression
Projection public.test_b1 has column col2 as part of its segmentation expression
HINT:  Use DROP .. CASCADE to drop or modify the dependent objects

Anyone know why this doesn't work?

Thanks!

[jpcavanaugh]

You are not specifying a segmentation clause for your table so the projections are being created with auto segmentation. The chosen segmentation contains the column you are trying to drop which is a violation of the restrictions of the command.

Restrictions
At the table level, you cannot drop or alter a primary key column or a column participating in the table's partitioning clause.
At the projection level, you cannot drop the first column in a projection's sort order or columns that participate in the segmentation expression of a projection.
All nodes must be up for the drop operation to succeed.

http://my.vertica.com/docs/6.0.0/HTML/i ... #18108.htm

[billykopecki]

Ok... The drop column feature works if I segment the table

Code: Select all

dbadmin=> create table test (col1 int, col2 varchar(100)) segmented by hash(col1) all nodes;
CREATE TABLE
dbadmin=> insert into test values (1, 'Finally');
 OUTPUT
--------
      1
(1 row)

dbadmin=> \d test;
                                    List of Fields by Tables
 Schema | Table | Column |     Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+--------------+------+---------+----------+-------------+-------------
 public | test  | col1   | int          |    8 |         | f        | f           |
 public | test  | col2   | varchar(100) |  100 |         | f        | f           |
(2 rows)

dbadmin=> alter table test drop column col2 cascade;
ALTER TABLE
dbadmin=> \d test;
                                List of Fields by Tables
 Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+------+------+---------+----------+-------------+-------------
 public | test  | col1   | int  |    8 |         | f        | f           |
(1 row)

Can I segment a table that already exists so that I will have the ability to drop columns if I need to in the future?

Thanks,
Bill

[jpcavanaugh]

You sure can sir. A table is logical and has one or more physical layouts (projections) and they have the segmentation that we are talking about. So to achieve what you are trying to do, simply create a new projection that has the segmentation that you want and then drop the old projection and then drop the column.

[yafeng]

I want to add my application a capability to support table structure refactoring/upgrade. To prevent failure from such limitation, I want to detect if a field is part of partition or segmentation. I scanned most tables under v_catalog but cannot found such tables. Does anyone know where I can find such information? Thanks.

[JimKnicely]

Take a look at the segment_expression column in the v_catalog.projections system table.

Example:

Code: Select all

dbadmin=> create table public.jim (c1 int, c2 varchar(100), c3 int) order by c1 segmented by hash(c1, c3) all nodes;
CREATE TABLE

dbadmin=> select projection_schema, projection_name, segment_expression from v_catalog.projections where projection_schema = 'public' and anchor_table_name = 'jim';
 projection_schema | projection_name |  segment_expression
-------------------+-----------------+----------------------
 public            | jim_b0          | hash(jim.c1, jim.c3)
 public            | jim_b1          | hash(jim.c1, jim.c3)
(2 rows)

[yafeng]

Thanks for your reply.
I guess you are using 7.1x right?
I cannot find that field in my 7.01 instance.

Regards