How to drop the projection in this error case??

HyeontaeJuHyeontaeJu Vertica Customer

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"

Answers

  • HibikiHibiki Vertica Employee Employee

    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
    
  • HyeontaeJuHyeontaeJu Vertica Customer

    @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

  • HibikiHibiki Vertica Employee Employee

    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?

  • HyeontaeJuHyeontaeJu Vertica Customer
    edited September 2020

    @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');

  • HibikiHibiki Vertica Employee Employee

    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?

  • HyeontaeJuHyeontaeJu Vertica Customer
    edited September 2020

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

  • HibikiHibiki Vertica Employee Employee

    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');
    
    
  • HyeontaeJuHyeontaeJu Vertica Customer
    edited September 2020

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

  • HibikiHibiki Vertica Employee Employee

    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 ...;
    
  • HyeontaeJuHyeontaeJu Vertica Customer

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

  • HibikiHibiki Vertica Employee Employee

    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.

  • HibikiHibiki Vertica Employee Employee

    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

Leave a Comment

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