The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

ALTER COLUMN DROP DEFAULT not effective immediately

Hi,

 

On Vertica 6, I tried to run the following sequence of DDL to modify a column type:

 

ALTER TABLE t1 ADD COLUMN c1_new NUMERIC(18, 2) NOT NULL DEFAULT c1;
ALTER TABLE t1 ALTER COLUMN c1_new DROP DEFAULT;
ALTER TABLE t1 DROP COLUMN c1;
ALTER TABLE t1 RENAME COLUMN c1_new TO c1;

 

When executed in a script the first 2 statements run without problem but the 3rd statement fails with the error message:

 

[ALTER - 0 row(s), 0.197 secs]  [Error Code: 100081, SQL State: HY000]  [Vertica][VJDBC](100081) ROLLBACK: Cannot drop column "c1" since it was referenced in the default expression of added column "c1_new"

 

If I wait for a while (no more than a minute) and run the 3rd statement again, it would succeed.

 

The table has about 7500 rows.

 

It feels like DROP DEFAULT does not get the job done immediately, or something. Anyway, how do I make sure the DDL can be executed successfully as a script (by DBAs)? Is there a way to explicitly wait for the DROP DEFAULT to take effect before running the next statement?

 

Thanks,

Brian

Comments

  • Hi ,

    I think this was fix is higher Vertica versions , Try to run explicit Moveout task before dropping c1  as flow :

     

     

    SELECT DO_TM_TASK('moveout', 't1');

     

     

    I hope you will find it useful

     

    Thanks

  • Hi,

     

    Thanks for the suggestion. However, it did not do the trick. I got the same error message as before.

     

    Brian

  • I've tried the same commands on our 7.x Vertica database.

     

    Vertica Analytic Database v7.1.2-6

     

    Same error there.

     

  • Is there any resolution for this? I keep getting it on 7.1 and the amount of time that I need to wait is completely random, from seconds to minutes... Obviously for a live migration this is not acceptable...

  • Hi,

     

    Short of any official response from HPE, my own solution is to run the following as superuser:

     

    select make_ahm_now();

     

    And make sure this function succeeds, fixing any issues that it reports. Afterwards you can drop the defaults and old columns at will.

     

    Hope this helps those who face the same issues.

     

    Regards,

    Brian

  • Try

     

    ALTER TABLE t1 DROP COLUMN c1 CASCADE;'

    Below the documentation

     

    manual

     

    "Using CASCADE to force a drop

    Use the CASCADE keyword to drop a column if that column:

    • Has a constraint on it.
    • Is specified in the projection's sort order.
    • Participates in a pre-join projection or participates in the projection's segmentation expression. When a pre-join projection contains a column to be dropped with CASCADE, HP Vertica tries to drop the projection.

    In all cases, CASCADE tries to drop the projection(s). If K-safety is compromised, HP Vertica rolls back the transaction.

    Use the RESTRICT keyword to drop the column only from the given table."

  • This looks promising. Will give it a try and see how well it works.

     

    Thanks,

    Brian

  • edited February 2018

    Any progress on this issue ? I'm also facing it with Vertica v8.1.0-1

    The following script fails on "drop column col2" unless I wait for the AHM to advance after reseting to null the col2_tmp default expression :

    create table nicolas_fa2_0.test(id INTEGER, col1 varchar(5000), col2 varchar(5000)) segmented by hash(id) all nodes;
    alter table nicolas_fa2_0.test add COLUMN col2_tmp varchar(5000) default col2 || 'suffix';
    alter table nicolas_fa2_0.test alter COLUMN col2_tmp set default null;
    alter table nicolas_fa2_0.test drop column col2 CASCADE;
    alter table nicolas_fa2_0.test RENAME COLUMN col2_tmp to col2;
    

    The returned error is :

    [Vertica][VJDBC](2413) ROLLBACK: Cannot drop column "col2" since it was referenced in the default expression of added column "col2_tmp"
    

    I worked around the issue by renaming col2 instead of dropping it immediately.

  • Hi all,
    I'm also facing this issue. And dropping with CASCADE doesn't resolve anything for me, unfortunately.
    Looking forward to finding a solution!
    Best,
    Grace

    ALTER TABLE "T" ADD COLUMN NAME_TEMP DATE DEFAULT NAME::date;
    ALTER TABLE "T" ALTER COLUMN NAME DROP DEFAULT;
    ALTER TABLE "T" DROP COLUMN NAME CASCADE;
    ALTER TABLE "T" RENAME COLUMN NAME_TEMP TO NAME;

  • SruthiASruthiA Employee

    I just tried a sample example and it works for me. what is your vertica version?

    sruthi_db93=> CREATE TABLE x (a int) UNSEGMENTED ALL NODES;
    CREATE TABLE
    sruthi_db93=> ALTER TABLE x ADD COLUMN b int DEFAULT a;
    ALTER TABLE
    sruthi_db93=> insert into x values(1,2);

    OUTPUT

      1
    

    (1 row)

    sruthi_db93=> insert into x values(1,2);

    OUTPUT

      1
    

    (1 row)

    sruthi_db93=> commit;
    COMMIT

    sruthi_db93=> ALTER TABLE x ALTER COLUMN b DROP DEFAULT;
    ALTER TABLE
    sruthi_db93=>

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.