How to drop the projection in this error case??

HyeontaeJu

The error message is this.
"SQL Error [4455] [55006]: [Vertica]VJDBC ROLLBACK: Projection products_image_DBD_1_rep_wshop_prod_srch2 cannot be dropped because K-safety would be violated
[Vertica][VJDBC]Detail: Current K-safety value = 1"

Hibiki

I guess products_image_DBD_1_rep_wshop_prod_srch2 is a buddy projection.
https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AdministratorsGuide/Projections/K-SafeDatabase.htm
If so, you can drop the projection by using the projection base name you specified when creating that projection.

=> CREATE TABLE tab1 (id1 INT, id2 INT);
CREATE TABLE
=> CREATE PROJECTION tab1_super (id1 ENCODING AUTO, id2 ENCODING AUTO) AS SELECT id1, id2 FROM tab1 ORDER BY id1 SEGMENTED BY hash(id1) ALL NODES KSAFE 1;
CREATE PROJECTION
=> CREATE PROJECTION tab1_proj (id2 ENCODING AUTO) AS SELECT id2 FROM tab1 ORDER BY id2 SEGMENTED BY hash(id2) ALL NODES KSAFE 1;
CREATE PROJECTION

=> SELECT projection_schema, projection_name FROM projections WHERE anchor_table_name = 'tab1' AND projection_basename = 'tab1_proj' ORDER BY 2;
 projection_schema | projection_name
-------------------+-----------------
 public            | tab1_proj_b0
 public            | tab1_proj_b1

=> DROP PROJECTION tab1_proj_b0;
ROLLBACK 4455:  Projection tab1_proj_b0 cannot be dropped because K-safety would be violated
DETAIL:  Current K-safety value = 1
=> DROP PROJECTION tab1_proj;
DROP PROJECTION

HyeontaeJu

@Hibiki Unfortunately.. In this case, the projection_basename and projection_name is same,,,
Look at this screen shot plz

How to solve this problem? T T

Hibiki

You are using DBeaver, right? Please show me the screenshot of Text view, not Grid view.
How did you create this projection? Can you show me the DDL for it?

HyeontaeJu

@Hibiki
Yes,, this is text view screen shot

and this is DDL

CREATE PROJECTION products_image_DBD_1_rep_wshop_prod_srch2 /+createtype(D)/
(
product_id ENCODING AUTO
)
AS
SELECT product_id
FROM [schema].products_image
ORDER BY product_id
UNSEGMENTED ALL NODES;

select refresh('newbiz_product_dev.products_image');

Hibiki

I can drop the unsegmented projection without any error.

=> CREATE TABLE tab1 (id1 INT, id2 INT);
CREATE TABLE
=> CREATE PROJECTION tab1_super (id1 ENCODING AUTO, id2 ENCODING AUTO) AS SELECT id1, id2 FROM tab1 ORDER BY id1 SEGMENTED BY hash(id1) ALL NODES KSAFE 1;
CREATE PROJECTION
=> CREATE PROJECTION tab1_proj /*+createtype(D)*/ (id2 ENCODING AUTO) AS SELECT id2 FROM tab1 ORDER BY id2 UNSEGMENTED ALL NODES;
CREATE PROJECTION

=> SELECT projection_schema, projection_name FROM projections WHERE anchor_table_name = 'tab1' AND projection_basename = 'tab1_proj' ORDER BY 2;
 projection_schema | projection_name
-------------------+-----------------
 public            | tab1_proj
 public            | tab1_proj
 public            | tab1_proj

=> DROP PROJECTION tab1_proj;
DROP PROJECTION

Can you try to drop it by specifying the schema name?

HyeontaeJu

@Hibiki
Yeah, I'm trying to drop by specifying the schema name, and I can see the above error message , T _ T
something different thing you and I is I'm trying to refresh the table.

Hibiki

Which version are you using now?

=> SELECT version();
               version
-------------------------------------
 Vertica Analytic Database v10.0.0-2

Can you provide the DDLs associated with products_image table? If it is difficult to provide it on User Forum, please send it to me by email.

=> SELECT export_objects('/tmp/products_image.sql', '[schema].products_image');

HyeontaeJu

@Hibiki
My version is version
|
-----------------------------------|
Vertica Analytic Database v10.0.0-2|
and DDL send to you

Hibiki

Thank you for sending the information. I could reproduce your issue. I need time to look into it. If this is urgent, please avoid this issue with the following workaround.

=> DROP PROJECTION [schema].products_image_pk_v1;
=> DROP PROJECTION [schema].products_image_DBD_1_rep_wshop_prod_srch2;
=> CREATE PROJECTION [schema].products_image_pk_v1 ...;

HyeontaeJu

@Hibiki
Oh.. that is working thank you.
Even if you are late, leave a comment if the reason is revealed

Hibiki

According to the product documentation, we have to create the unsegmented projection on all nodes. newbiz_product.products_image_pk_v1 projection has been created only on one node. Please change this projection created on all nodes.
https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/Statements/unsegmented-clause.htm
I have raised the incident Vertica allowed to create it only on the particular node.

Hibiki

When creating any projections, to specify segmentation on specific nodes has been deprecated in 10.0.1. Please avoid creating the unsegmented projection on the specific node.
https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/NewFeatures/_VersionIndependent/DeprecatedandRetiredFunctionality.htm