Partitioning problem + question?

EleanorWyattEleanorWyatt Vertica Customer
edited June 2021 in General Discussion

Hi Experts!
I have been trying to partition a billion rows table according to time by CALENDAR_HIERARCHY_DAY algorithm.omegle xender
when I run the statement:
ALTER TABLE public.spins
PARTITION BY time::DATE
GROUP BY CALENDAR_HIERARCHY_DAY(time::DATE, 2, 1) REORGANIZE;

I get the error:
ROLLBACK 2648: Column time in PARTITION BY expression is not allowed, since it is not present in some projections
HINT: Projection "spins_pk" does not contain column "time"

When I try to drop the projection (I don't really need it..) I get:

WARNING 7000: The projection 'spins_pk' was used to enforce the enabled key constraint 'pk', and may be regenerated to validate a DML statement on the base table

how can I workaround that issue?should I add the column time to the pk projection?

And a general partitioning question
If I want my partitions to be as follows: 24 hours - by hours, 7 days , 1 month, 3 month, 1 year..
what is the group by clause needed for the
GROUP BY CALENDAR_HIERARCHY algorithm.

Many thanks!!

Answers

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    @EleanorWyatt - You can drop the PK constraint, add the partition and then add the PK constraint back.

    Example:

    verticademos=> CREATE TABLE spins (c2 INT, c1 INT NOT NULL, time timestamp);
    CREATE TABLE
    
    verticademos=> INSERT INTO spins SELECT 1, 1, SYSDATE;
     OUTPUT
    --------
          1
    (1 row)
    
    verticademos=> ALTER TABLE spins ADD CONSTRAINT spins_pk PRIMARY KEY(c1) ENABLED;
    ALTER TABLE
    
    verticademos=> SELECT export_objects('', 'spins');
                                                                                                                                                                                                                                                                                                                                  export_objects                                                                                                                                                                                                
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    
    CREATE TABLE public.spins
    (
        c2 int,
        c1 int NOT NULL,
        "time" timestamp,
        CONSTRAINT spins_pk PRIMARY KEY (c1) ENABLED
    );
    
    
    CREATE PROJECTION public.spins_super /*+basename(spins),createtype(L)*/
    (
     c2,
     c1,
     "time"
    )
    AS
     SELECT spins.c2,
            spins.c1,
            spins."time"
     FROM public.spins
     ORDER BY spins.c2,
              spins.c1,
              spins."time"
    SEGMENTED BY hash(spins.c2, spins.c1, spins."time") ALL NODES OFFSET 0;
    
    CREATE PROJECTION public.spins_pk /*+createtype(N)*/
    (
     c1
    )
    AS
     SELECT spins.c1
     FROM public.spins
     ORDER BY spins.c1
    SEGMENTED BY hash(spins.c1) ALL NODES OFFSET 0;
    
    
    SELECT MARK_DESIGN_KSAFE(0);
    
    (1 row)
    
    verticademos=> ALTER TABLE public.spins
    verticademos-> PARTITION BY time::DATE
    verticademos-> GROUP BY CALENDAR_HIERARCHY_DAY(time::DATE, 2, 1) REORGANIZE;
    ROLLBACK 2648:  Column time in PARTITION BY expression is not allowed, since it is not present in some projections
    HINT:  Projection "spins_pk" does not contain column "time"
                                                     ^
    verticademos=> ALTER TABLE public.spins DROP CONSTRAINT spins_pk;
    ALTER TABLE
    
    verticademos=> ALTER TABLE public.spins
    verticademos-> PARTITION BY time::DATE
    verticademos-> GROUP BY CALENDAR_HIERARCHY_DAY(time::DATE, 2, 1) REORGANIZE;
    NOTICE 8364:  The new partitioning scheme will produce partitions in 1 physical storage containers per projection
    WARNING 9249:  Using PARTITION expression that may result in NULL values
    HINT:  DML statements which produce NULL values for partition expressions will roll back
    NOTICE 4785:  Started background repartition table task
    ALTER TABLE
    
    verticademos=> ALTER TABLE spins ADD CONSTRAINT spins_pk PRIMARY KEY(c1) ENABLED;
    ALTER TABLE
    

Leave a Comment

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