ALTER COLUMN DROP DEFAULT not effective immediately

brian3hu

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

eli_revach

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

brian3hu

Hi,

 

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

 

Brian

brian3hu

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

 

Vertica Analytic Database v7.1.2-6

 

Same error there.

 

kkara

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

brian3hu

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

ckotsidimos

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."

brian3hu

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

 

Thanks,

Brian

Nicolas_R

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.

Grace

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;

SruthiA

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