Primary Key Constraints

Moderator: NorbertKrupa

dougwwt
Newbie
Newbie
Posts: 5
Joined: Fri May 25, 2018 7:01 pm

Primary Key Constraints

Post by dougwwt » Fri May 25, 2018 7:13 pm

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?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1824
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Primary Key Constraints

Post by JimKnicely » Fri May 25, 2018 8:46 pm

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)


Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

dougwwt
Newbie
Newbie
Posts: 5
Joined: Fri May 25, 2018 7:01 pm

Re: Primary Key Constraints

Post by dougwwt » Tue May 29, 2018 1:35 pm

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.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1824
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Primary Key Constraints

Post by JimKnicely » Fri Jun 01, 2018 4:02 pm

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
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

dougwwt
Newbie
Newbie
Posts: 5
Joined: Fri May 25, 2018 7:01 pm

Re: Primary Key Constraints

Post by dougwwt » Fri Jun 01, 2018 9:35 pm

Thanks Jim. Email sent.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1824
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Primary Key Constraints

Post by JimKnicely » Mon Jun 04, 2018 1:22 pm

Oops... I put the email address! Sorry!

Here is the correct email address:

james.knicely@microfocus.com
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

dougwwt
Newbie
Newbie
Posts: 5
Joined: Fri May 25, 2018 7:01 pm

Re: Primary Key Constraints

Post by dougwwt » Mon Jun 04, 2018 7:09 pm

I sent my examples to this email. I hope you received them.

Post Reply

Return to “General”