ALTER COLUMN DROP DEFAULT not effective immediately

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

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;

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=>