Primary Key Constraints
Moderator: NorbertKrupa
Primary Key Constraints
I'm relatively new to Vertica. We are on Vertica 8.1. I'm going thru all our projections and figuring out if they should be segmented or unsegmented. We have some small tables that have primary key constraints. I made those primary key/unique key projections unsegmented but it looks like Vertica automatically creates a UK projection that is segmented when a record is inserted into the table. Am I right about that? If so, is that a database setting or is that just the way it is? If it is just the way it is, should I go ahead and drop the unsegmented projections I created on the primary keys?
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Primary Key Constraints
Hi,
If you have an enabled PK constraint there has to be at least one "special" Projections to enforce it.
Check if the new unsegmented projection you created is one of those (in the PROJECTIONS system table, IS_KEY_CONSTRAINT_PROJECTION will be TRUE).
If so, you can drop the segmented projection.
Example:
dbadmin=> create table pk_test (c1 int, c2 varchar(10));
CREATE TABLE
dbadmin=> alter table pk_test add constraint pk_test_pk primary key (c1) enabled;
WARNING 2623: Column "c1" definition changed to NOT NULL
ALTER TABLE
dbadmin=> insert into pk_test select 1;
OUTPUT
--------
1
(1 row)
dbadmin=> commit;
COMMIT
dbadmin=> select projection_name, is_key_constraint_projection from projections where anchor_table_name = 'pk_test';
projection_name | is_key_constraint_projection
-----------------+------------------------------
pk_test_super | t
(1 row)
dbadmin=> create projection pk_test_new as select * from pk_test order by c1 unsegmented all nodes;
WARNING 4468: Projection <public.pk_test_new> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION
dbadmin=> select refresh('pk_test');
refresh
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Refresh completed with the following outcomes:
Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
----------------------------------------------------------------------------------------
"public"."pk_test_new": [pk_test] [refreshed] [scratch] [0] [0]
(1 row)
dbadmin=> select make_ahm_now();
make_ahm_now
-------------------------------
AHM set (New AHM Epoch: 6751)
(1 row)
dbadmin=> select projection_name, is_key_constraint_projection from projections where anchor_table_name = 'pk_test';
projection_name | is_key_constraint_projection
-----------------+------------------------------
pk_test_super | t
pk_test_new | t
(2 rows)
dbadmin=> drop projection pk_test_super;
DROP PROJECTION
dbadmin=> select projection_name, is_key_constraint_projection from projections where anchor_table_name = 'pk_test';
projection_name | is_key_constraint_projection
-----------------+------------------------------
pk_test_new | t
(1 row)
If you have an enabled PK constraint there has to be at least one "special" Projections to enforce it.
Check if the new unsegmented projection you created is one of those (in the PROJECTIONS system table, IS_KEY_CONSTRAINT_PROJECTION will be TRUE).
If so, you can drop the segmented projection.
Example:
dbadmin=> create table pk_test (c1 int, c2 varchar(10));
CREATE TABLE
dbadmin=> alter table pk_test add constraint pk_test_pk primary key (c1) enabled;
WARNING 2623: Column "c1" definition changed to NOT NULL
ALTER TABLE
dbadmin=> insert into pk_test select 1;
OUTPUT
--------
1
(1 row)
dbadmin=> commit;
COMMIT
dbadmin=> select projection_name, is_key_constraint_projection from projections where anchor_table_name = 'pk_test';
projection_name | is_key_constraint_projection
-----------------+------------------------------
pk_test_super | t
(1 row)
dbadmin=> create projection pk_test_new as select * from pk_test order by c1 unsegmented all nodes;
WARNING 4468: Projection <public.pk_test_new> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION
dbadmin=> select refresh('pk_test');
refresh
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Refresh completed with the following outcomes:
Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
----------------------------------------------------------------------------------------
"public"."pk_test_new": [pk_test] [refreshed] [scratch] [0] [0]
(1 row)
dbadmin=> select make_ahm_now();
make_ahm_now
-------------------------------
AHM set (New AHM Epoch: 6751)
(1 row)
dbadmin=> select projection_name, is_key_constraint_projection from projections where anchor_table_name = 'pk_test';
projection_name | is_key_constraint_projection
-----------------+------------------------------
pk_test_super | t
pk_test_new | t
(2 rows)
dbadmin=> drop projection pk_test_super;
DROP PROJECTION
dbadmin=> select projection_name, is_key_constraint_projection from projections where anchor_table_name = 'pk_test';
projection_name | is_key_constraint_projection
-----------------+------------------------------
pk_test_new | t
(1 row)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Primary Key Constraints
Jim,
I checked the projection table and both the unsegmented projection I created and the segmented projection that the system created have is_key_constraint_projection = 'true'. I checked both projections again and they are both on the exact same two fields. Below is the portion of code after I exported the object. FYI, this table is about 20,000 rows.
ALTER TABLE SCHEMA.TABLE1 ADD CONSTRAINT TABLE1_PK PRIMARY KEY (NCT_NUMBER, NCT_LINE_ID) ENABLED;
I created this projection:
CREATE PROJECTION SCHEMA.TABLE1_pk_unseg
(
NCT_LINE_ID,
NCT_NUMBER
)
AS
SELECT TABLE1.NCT_LINE_ID,
TABLE1.NCT_NUMBER
FROM SCHEMA.TABLE1
ORDER BY TABLE1.NCT_LINE_ID,
TABLE1.NCT_NUMBER
UNSEGMENTED ALL NODES;
The system created this projection:
CREATE PROJECTION SCHEMA.TABLE1_UK
(
NCT_LINE_ID,
NCT_NUMBER
)
AS
SELECT TABLE1.NCT_LINE_ID,
TABLE1.NCT_NUMBER
FROM SCHEMA.TABLE1
ORDER BY TABLE1.NCT_LINE_ID,
TABLE1.NCT_NUMBER
SEGMENTED BY hash(TABLE1.NCT_LINE_ID, TABLE1.NCT_NUMBER) ALL NODES KSAFE 1;
I dropped PROJECTION SCHEMA.TABLE1_UK several times but it keeps coming back.
I checked the projection table and both the unsegmented projection I created and the segmented projection that the system created have is_key_constraint_projection = 'true'. I checked both projections again and they are both on the exact same two fields. Below is the portion of code after I exported the object. FYI, this table is about 20,000 rows.
ALTER TABLE SCHEMA.TABLE1 ADD CONSTRAINT TABLE1_PK PRIMARY KEY (NCT_NUMBER, NCT_LINE_ID) ENABLED;
I created this projection:
CREATE PROJECTION SCHEMA.TABLE1_pk_unseg
(
NCT_LINE_ID,
NCT_NUMBER
)
AS
SELECT TABLE1.NCT_LINE_ID,
TABLE1.NCT_NUMBER
FROM SCHEMA.TABLE1
ORDER BY TABLE1.NCT_LINE_ID,
TABLE1.NCT_NUMBER
UNSEGMENTED ALL NODES;
The system created this projection:
CREATE PROJECTION SCHEMA.TABLE1_UK
(
NCT_LINE_ID,
NCT_NUMBER
)
AS
SELECT TABLE1.NCT_LINE_ID,
TABLE1.NCT_NUMBER
FROM SCHEMA.TABLE1
ORDER BY TABLE1.NCT_LINE_ID,
TABLE1.NCT_NUMBER
SEGMENTED BY hash(TABLE1.NCT_LINE_ID, TABLE1.NCT_NUMBER) ALL NODES KSAFE 1;
I dropped PROJECTION SCHEMA.TABLE1_UK several times but it keeps coming back.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Primary Key Constraints
Hi,
I cannot reproduce this issue
I noticed that your PK has the column order different (reversed) than the column order in the projections (by ordinal position) and by the ORDER BY and SEGMENTED BY positions.
Can you send me via email the DDL for the table including projections? Use the EXPORT_OBJECTS() function for that. Also, please send an example of the SQL command you run the causes Vertica is create the segmented projection.
My email: james.knicely@microfous.com
I cannot reproduce this issue
I noticed that your PK has the column order different (reversed) than the column order in the projections (by ordinal position) and by the ORDER BY and SEGMENTED BY positions.
Can you send me via email the DDL for the table including projections? Use the EXPORT_OBJECTS() function for that. Also, please send an example of the SQL command you run the causes Vertica is create the segmented projection.
My email: james.knicely@microfous.com
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Primary Key Constraints
Thanks Jim. Email sent.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Primary Key Constraints
Oops... I put the email address! Sorry!
Here is the correct email address:
james.knicely@microfocus.com
Here is the correct email address:
james.knicely@microfocus.com
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Primary Key Constraints
I sent my examples to this email. I hope you received them.