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.
dropping a column from a table
+1
+2Did 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...
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...
+1Thanks Kanako.
Greetings everyone,
I need to run or DROP [COLUMN] on a table, even with the procedure below, it doesn't work:
Is there any other solution than to recreate the table?
+2Can you post an example? Or atleast an error message?
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 =>
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)
=>
+1@joliveira : Epochs look good. Could you please check if by any chance you are dropping column with the restrictions mentioned in the below documentation
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"
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.
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.
+1@joliveira : yes.. there is no way... as mentioned in the hint, the workaround is to create a new projection
@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');
+2Should 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
@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=>
+2@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.
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=>
+1@joliveira : Could you please share me the output of the following query
select export_objects('','SCHEMA.TABLE_TESTE');
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.
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.
+1@joliveira : could you please check if all the columns are present in your select list of projection creation statement?
@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".
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
