dropping a column from a table

Muthu_SundaramMuthu_Sundaram
in General Discussion
I am trying to drop a column from existing table. But I am not able to drop  a column because super projection is using that column. I tried dropping super projection but no luck. So I ended up dropping table with cascade. 

I do not want to lose history. Drop clears up the history. I could not go back point in time to get data.

At time '2013-01-01...' select --> does not work.

IS there any way around? How do I delete a column with out losing history. I am able to add a column with out losing history.

Comments

  • [Deleted User][Deleted User]
    Did you try to use "RESTRICT" option? Here is an example.
    ex) alter table te drop column col3 restrict;

    For example, if the dropped column is used for segmentation, we cannot drop it. However, if you can create another superprojection which has a differenct segmentation key and drop the old one, you would drop that column.
    You might have already checked, but you can find more detailed information at the following page of the manual.
    https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/AdministratorsGuide/Tables/DroppingATable...
  • Muthu_SundaramMuthu_Sundaram
    Thanks Kanako.
  • [Deleted User][Deleted User]
    My pleasure:)
  • joliveirajoliveira Community Edition User ✭

    Greetings everyone,
    I need to run or DROP [COLUMN] on a table, even with the procedure below, it doesn't work:

    https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Tables/ModifyTableDefinition/DroppingATableColumn.htm#Dropping

    Is there any other solution than to recreate the table?

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    Can you post an example? Or atleast an error message?

  • joliveirajoliveira Community Edition User ✭
    edited November 2021

    Hello good night,
    Here's an error message, here I'm replacing the table name with "TABLE_TESTE" for information protection.

    => ALTER TABLE "SCHEMA"."TABLE_TESTE" DROP COLUMN "COLUMN_TESTE" CASCADE;
ROLLBACK 4122: No up-to-date super projection left on the anchor table of projection "TABLE_TESTE_super"
HINT: Use DROP TABLE ... CASCADE to drop the anchor table and its last projection, or create a replacement super projection instead
=>
  • SruthiASruthiA Administrator

    @joliveira : could you please share the output of select * from system;

  • joliveirajoliveira Community Edition User ✭

    Follow:

    => select * from system;
 current_epoch | ahm_epoch | last_good_epoch | refresh_epoch | designed_fault_tolerance | node_count | node_down_count | current_fault_tolerance | catalog_revision_number | wos_used_bytes | wos_row_count | ros_used_bytes | ros_row_count | total_used_bytes | total_row_count
---------------+-----------+-----------------+---------------+--------------------------+------------+-----------------+-------------------------+-------------------------+----------------+---------------+----------------+---------------+------------------+-----------------
        183663 |    183662 |          183662 |            -1 |                        0 |          1 |               0 |                       0 |                  389286 |              0 |             0 |   257171933517 |   14005825553 |     257171933517 |     14005825553
(1 row)

=>
  • SruthiASruthiA Administrator

    @joliveira : Epochs look good. Could you please check if by any chance you are dropping column with the restrictions mentioned in the below documentation

    https://www.vertica.com/docs/11.0.x/HTML/Content/Authoring/AdministratorsGuide/Tables/ModifyTableDefinition/DroppingATableColumn.htm

    Restrictions
    You cannot drop or alter a primary key column or a column that participates in the table partitioning clause.
    You cannot drop the first column of any projection sort order, or columns that participate in a projection segmentation expression.
    In Enterprise mode, all nodes must be up. This restriction does not apply to Eon mode.
    You cannot drop a column associated with an access policy. Attempts to do so produce the following error:
    ERROR 6482: Failed to parse Access Policies for table "t1"

  • joliveirajoliveira Community Edition User ✭
    edited November 2021

    In this case the column is used in "SEGMENTED BY hash" by the projection, there is no option to "DROP" of column?
    Before, I performed the action recommended in the procedure.

    https://www.vertica.com/docs/11.0.x/HTML/Content/Authoring/AdministratorsGuide/Tables/ModifyTableDefinition/DroppingATableColumn.htm

    2. If the table is segmented, the target column is specified in the segmentation expression. In this case, the new superprojection must specify a segmentation expression that excludes the target column.

  • SruthiASruthiA Administrator

    @joliveira : yes.. there is no way... as mentioned in the hint, the workaround is to create a new projection

  • joliveirajoliveira Community Edition User ✭

    @SruthiA: Even with the creation of the new projection for the table "SCHEMA"."TABLE_TESTE" without the column "COLUMN_TESTE",
    then executed:
    select refresh('TABLE_TESTE');

    The projection "TABLE_TESTE_super" does not allow the drop of the exemplified column, is there any point that can be done?

    I validated the table projections with: SELECT GET_PROJECTIONS('SCHEMA.TABLE_TESTE');

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    Should work. Here is a quick example...

    dbadmin=> CREATE TABLE a (c1 INT, c2 INT);
CREATE TABLE

dbadmin=> INSERT INTO a SELECT 1, 2;
 OUTPUT
--------
      1
(1 row)

dbadmin=> ALTER TABLE a DROP COLUMN c1 CASCADE;
ROLLBACK 4122:  No up-to-date super projection left on the anchor table of projection a_super
HINT:  Use DROP TABLE ... CASCADE to drop the anchor table and its last projection, or create a replacement super projection instead

dbadmin=> CREATE PROJECTION a_new_super AS SELECT c1, c2 FROM a ORDER BY c2 SEGMENTED BY HASH(c2) ALL NODES;
WARNING 4468:  Projection <public.a_new_super> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION

dbadmin=> SELECT refresh('a');
                                                                                                                                           refresh
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Refresh completed with the following outcomes:
Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
----------------------------------------------------------------------------------------
"public"."a_new_super": [a] [refreshed] [scratch] [0] [0]

(1 row)

dbadmin=> ALTER TABLE a DROP COLUMN c1 CASCADE;
ALTER TABLE
  • joliveirajoliveira Community Edition User ✭
    edited November 2021

    @Jim_Knicely and @SruthiA,

    The procedure was redone, compared to your log, I noticed that the step below did not return any data, what point can I check?

    dbadmin=> SELECT REFRESH('TABLE_TESTE');
                                                                                                              REFRESH                                           
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Refresh completed with the following outcomes:
Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
----------------------------------------------------------------------------------------


(1 row)

dbadmin=>
  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited November 2021

    @joliveira - What do you get when you run this SQL?

    SELECT node_name, projection_name, is_super_projection, is_key_constraint_projection, has_expressions, is_aggregate_projection, aggregate_type, is_segmented, segment_expression, is_up_to_date FROM projections WHERE projection_schema = '<<table_schema>>' AND anchor_table_name = '<<table_name>>';

    Note: Replace 'table_schema' and 'table_name' with your table's schema name and table's name, respectively.

  • joliveirajoliveira Community Edition User ✭

    @Jim_Knicely,

    Here's spool:

    dbadmin=> SELECT node_name, projection_name, is_super_projection, is_key_constraint_projection, has_expressions, is_aggregate_projection, aggregate_type, is_segmented, segment_expression, is_up_to_date FROM projections WHERE projection_schema = 'SCHEMA' AND anchor_TABLE_TESTE = 'TABLE_TESTE';
 node_name |           projection_name           | is_super_projection | is_key_constraint_projection | has_expressions | is_aggregate_projection | aggregate_type | is_segmented |                                                                segment_expression                                                                | is_up_to_date
-----------+-------------------------------------+---------------------+------------------------------+-----------------+-------------------------+----------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------+---------------
           | TABLE_TESTE_b1                      | f                   | f                            | f               | f                       |                | t            | hash(TABLE_TESTE.C1, TABLE_TESTE.C2, TABLE_TESTE.C3, TABLE_TESTE.C5, TABLE_TESTE.C6, TABLE_TESTE.C7)                                             | t
           | TABLE_TESTE_b0                      | f                   | f                            | f               | f                       |                | t            | hash(TABLE_TESTE.C1, TABLE_TESTE.C2, TABLE_TESTE.C3, TABLE_TESTE.C5, TABLE_TESTE.C6, TABLE_TESTE.C7)                                             | t
           | TABLE_TESTE_old_b1                  | t                   | f                            | f               | f                       |                | t            | hash(TABLE_TESTE.C1, TABLE_TESTE.C2, TABLE_TESTE.C3, TABLE_TESTE.C5, TABLE_TESTE.C6, TABLE_TESTE.C7, TABLE_TESTE.COLUMN_TESTE)                   | t
           | TABLE_TESTE_old_b0                  | t                   | f                            | f               | f                       |                | t            | hash(TABLE_TESTE.C1, TABLE_TESTE.C2, TABLE_TESTE.C3, TABLE_TESTE.C5, TABLE_TESTE.C6, TABLE_TESTE.C7, TABLE_TESTE.COLUMN_TESTE)                   | t
(4 rows)


dbadmin=>

    Below is a new test in another homologation environment, in the example below the column I run the "DROP" is "COLUMN_TESTE" which is part of the TABLE_TESTE_old projection (renamed to _old),
    the TABLE_TESTE projection was created without the column "COLUMN_TESTE", then I ran:

    dbadmin=> SELECT REFRESH('TABLE_TESTE');
                                                                                                                                                                                                                      REFRESH
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Refresh completed with the following outcomes:
Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
----------------------------------------------------------------------------------------
"SCHEMA"."TABLE_TESTE_b1": [TABLE_TESTE] [refreshed] [scratch] [0] [0]
"SCHEMA"."TABLE_TESTE_b0": [TABLE_TESTE] [refreshed] [scratch] [0] [0]

(1 row)


dbadmin=> SELECT MAKE_AHM_NOW();
           MAKE_AHM_NOW
----------------------------------
 AHM set (New AHM Epoch: 3544023)
(1 row)

dbadmin=> ALTER TABLE SCHEMA.TABLE_TESTE DROP COLUMN COLUMN_TESTE CASCADE;
ROLLBACK 4122:  No up-to-date super projection left on the anchor table of projection TABLE_TESTE_old_b1
HINT:  Use DROP TABLE ... CASCADE to drop the anchor table and its last projection, or create a replacement super projection instead
dbadmin=>
  • joliveirajoliveira Community Edition User ✭

    @Jim_Knicely and @SruthiA,

    Greetings, any news on this subject?
    Grateful for the support.

  • SruthiASruthiA Administrator

    @joliveira : Could you please share me the output of the following query

    select export_objects('','SCHEMA.TABLE_TESTE');

  • joliveirajoliveira Community Edition User ✭

    @SruthiA:

    Firstly grateful for the feedback.
    Follows the requested information.

    CREATE TABLE SCHEMA.TABLE_TESTE
(
    V1 numeric(40,0) NOT NULL,
    V2 numeric(40,0) NOT NULL,
    V3 numeric(40,0) NOT NULL,
    V4 numeric(40,0) NOT NULL,
    V5 numeric(40,0) NOT NULL,
    V6 numeric(40,0) NOT NULL,
    V7 numeric(40,0) NOT NULL,
    V8 numeric(40,0) NOT NULL,
    D1 numeric(20,0),
    D2 numeric(20,0),
    C1 numeric(20,0) NOT NULL,
    C2 numeric(20,0),
    C3 numeric(20,0),
    COLUMN_TESTE numeric(10,2),
    C4 numeric(20,0),
    C5 numeric(10,0),
    C6 numeric(10,0),
    C7 numeric(10,0) NOT NULL
);


CREATE PROJECTION SCHEMA.TABLE_TESTE_old /*+createtype(L)*/
(
 V1,
 V2,
 V3,
 V4,
 V5,
 V6,
 V7,
 V8,
 D1,
 D2,
 C1,
 C2,
 C3,
 COLUMN_TESTE,
 C4,
 C5,
 C6,
 C7
)
AS
 SELECT TABLE_TESTE.V1,
        TABLE_TESTE.V2,
        TABLE_TESTE.V3,
        TABLE_TESTE.V4,
        TABLE_TESTE.V5,
        TABLE_TESTE.V6,
        TABLE_TESTE.V7,
        TABLE_TESTE.V8,
        TABLE_TESTE.D1,
        TABLE_TESTE.D2,     
        TABLE_TESTE.C1,
        TABLE_TESTE.C2,
        TABLE_TESTE.C3,
        TABLE_TESTE.COLUMN_TESTE,
        TABLE_TESTE.C4,
        TABLE_TESTE.C5,
        TABLE_TESTE.C6,
        TABLE_TESTE.C7
 FROM SCHEMA.TABLE_TESTE
 ORDER BY TABLE_TESTE.V1,
          TABLE_TESTE.V2,
          TABLE_TESTE.V3,
          TABLE_TESTE.V4,
          TABLE_TESTE.V5,
          TABLE_TESTE.V6,
          TABLE_TESTE.V7,
          TABLE_TESTE.V8
SEGMENTED BY hash(TABLE_TESTE.C1, 
                  TABLE_TESTE.C2, 
                  TABLE_TESTE.C3, 
                  TABLE_TESTE.C4, 
                  TABLE_TESTE.C5, 
                  TABLE_TESTE.C6, 
                  TABLE_TESTE.COLUMN_TESTE, 
                  TABLE_TESTE.C7) ALL NODES KSAFE 1;

CREATE PROJECTION SCHEMA.TABLE_TESTE /*+createtype(L)*/
(
 V1,
 V2,
 V3,
 V4,
 V5,
 V6,
 V7,
 V8,
 D1,
 D2,
 C1,
 C2,
 C3,
 C4,
 C5,
 C6,
 C7
)
AS
 SELECT TABLE_TESTE.V1,
        TABLE_TESTE.V2,
        TABLE_TESTE.V3,
        TABLE_TESTE.V4,
        TABLE_TESTE.V5,
        TABLE_TESTE.V6,
        TABLE_TESTE.V7,
        TABLE_TESTE.V8,
        TABLE_TESTE.D1,
        TABLE_TESTE.D2,     
        TABLE_TESTE.C1,
        TABLE_TESTE.C2,
        TABLE_TESTE.C3,
        TABLE_TESTE.C4,
        TABLE_TESTE.C5,
        TABLE_TESTE.C6,
        TABLE_TESTE.C7
 FROM SCHEMA.TABLE_TESTE
 ORDER BY TABLE_TESTE.V1,
          TABLE_TESTE.V2,
          TABLE_TESTE.V3,
          TABLE_TESTE.V4,
          TABLE_TESTE.V5,
          TABLE_TESTE.V6,
          TABLE_TESTE.V7,
          TABLE_TESTE.V8
SEGMENTED BY hash(TABLE_TESTE.C1, 
                  TABLE_TESTE.C2, 
                  TABLE_TESTE.C3, 
                  TABLE_TESTE.C4, 
                  TABLE_TESTE.C5, 
                  TABLE_TESTE.C6, 
                  TABLE_TESTE.C7) ALL NODES KSAFE 1;


SELECT MARK_DESIGN_KSAFE(1);

    Note: here I am replacing the table name with "TABLE_TESTE" and also the column names with aliases for information protection.

  • SruthiASruthiA Administrator
    edited November 2021

    @joliveira : There is an issue with new replacement projection which you created.. vertica will create super projection only if we include all the columns in your projection definition and select list. Please include column teste in the projection definition and try it. I am able to drop the column after refreshing.

    CREATE PROJECTION TABLE_TESTE_super /+createtype(L)/
    (
    V1,
    V2,
    V3,
    V4,
    V5,
    V6,
    V7,
    V8,
    D1,
    D2,
    C1,
    C2,
    C3,
    COLUMN_TESTE,
    C4,
    C5,
    C6,
    C7
    )
    AS
    SELECT TABLE_TESTE.V1,
    TABLE_TESTE.V2,
    TABLE_TESTE.V3,
    TABLE_TESTE.V4,
    TABLE_TESTE.V5,
    TABLE_TESTE.V6,
    TABLE_TESTE.V7,
    TABLE_TESTE.V8,
    TABLE_TESTE.D1,
    TABLE_TESTE.D2,
    TABLE_TESTE.C1,
    TABLE_TESTE.C2,
    TABLE_TESTE.C3,
    TABLE_TESTE.COLUMN_TESTE,
    TABLE_TESTE.C4,
    TABLE_TESTE.C5,
    TABLE_TESTE.C6,
    TABLE_TESTE.C7
    FROM TABLE_TESTE
    ORDER BY TABLE_TESTE.V1,
    TABLE_TESTE.V2,
    TABLE_TESTE.V3,
    TABLE_TESTE.V4,
    TABLE_TESTE.V5,
    TABLE_TESTE.V6,
    TABLE_TESTE.V7,
    TABLE_TESTE.V8
    SEGMENTED BY hash(TABLE_TESTE.C1,
    TABLE_TESTE.C2,
    TABLE_TESTE.C3,
    TABLE_TESTE.C4,
    TABLE_TESTE.C5,
    TABLE_TESTE.C6,
    TABLE_TESTE.C7) ALL NODES KSAFE 1;

    WARNING 4468: Projection <public.TABLE_TESTE_super_super> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
    The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
    CREATE PROJECTION
    dbadmin=> select start_refresh();
    -[ RECORD 1 ]-+---------------------------------------
    start_refresh | Starting refresh background process.

    dbadmin=> select make_ahm_now();
    -[ RECORD 1 ]+----------------------------
    make_ahm_now | AHM set (New AHM Epoch: 65)

    dbadmin=> ALTER TABLE TABLE_TESTE DROP COLUMN COLUMN_TESTE CASCADE;
    ALTER TABLE
    dbadmin=>

  • joliveirajoliveira Community Edition User ✭

    @SruthiA:

    First of all, grateful for the feedback.
    I noticed in your example that you simply do not inform the column TABLE_TESTE.COLUMN_TESTE in the condition "SEGMENTED BY hash",
    solution works only for one copy of TABLE_TESTE, did not work for original table TABLE_TESTE, I get the same message posted earlier.

    dbadmin => ALTER TABLE SCHEMA.TABLE_TESTE DROP COLUMN COLUMN_TESTE CASCADE;
ROLLBACK 4122: No overprojection updated left in projection anchor table TABLE_TESTE_old_b1
TIP: Use DROP TABLE ... CASCADE to drop the docking table and its last projection, or create a replacement super projection.
dbadmin =>

    Any items I didn't notice?
    Thank you for your support.

  • SruthiASruthiA Administrator

    @joliveira : could you please check if all the columns are present in your select list of projection creation statement?

  • joliveirajoliveira Community Edition User ✭
    edited December 2021

    @SruthiA:
    Yes, all columns are present in the selection list, including COLUMN_TEST, the only difference from the "Old" projection to the "New" projection is
    that as instructed, I removed the column COLUMN_TESTE from the condition "SEGMENTED BY hash".

  • SruthiASruthiA Administrator

    @joliveira : Could you please open a support case for this issue? It requires webex.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...