Jira (PDB-4881) Add primary key to catalog_inputs

[Austin Blatt (Jira)]

Austin Blatt created an issue

PuppetDB / PDB-4881 Add primary key to catalog_inputs Issue Type: Improvement Assignee: Unassigned Created: 2020/09/09 8:38 AM Priority: Normal Reporter: Austin Blatt When scoping this feature we specifically omitted any work to optimize storage/queries in order to limit the first round work. By doing this we also omitted a primary key, which means the table cannot be cleaned up by pg_repack. We should add a primary key to the table. I believe this would work as a primary key. code ALTER TABLE catalog_inputs ADD CONSTRAINT catalog_inputs_pkey PRIMARY KEY (certname_id, type, name); code If we allow customers to add it ahead of time as a workaround, we'll need to preface the ADD CONSTRAINT command in the migration with the below command to drop the primary key because ADD CONSTRAINT doesn't support IF NOT EXISTS. code ALTER TABLE catalog_inputs DROP CONSTRAINT IF EXISTS catalog_inputs_pkey; code Add Comment

This message was sent by Atlassian Jira (v8.5.2#805002-sha1:a66f935)

[Austin Blatt (Jira)]

Austin Blatt updated an issue

PuppetDB / PDB-4881 Add primary key to catalog_inputs

Change By: Austin Blatt Fix Version/s: PDB 6.12.1

Add Comment

[Austin Blatt (Jira)]

Austin Blatt updated an issue

PuppetDB / PDB-4881 Add primary key to catalog_inputs Change By: Austin Blatt

When scoping this feature we specifically omitted any work to optimize storage/queries in order to limit the first round work. By doing this we also omitted a primary key, which means the table cannot be cleaned up by {{pg_repack}}. We should add a primary key to the table.

I believe this would work as a primary key. {

{ code} }

ALTER TABLE catalog_inputs ADD CONSTRAINT catalog_inputs_pkey PRIMARY KEY (certname_id, type, name); {

{ code} }

If we allow customers to add it ahead of time as a workaround, we'll need to preface the {{ADD CONSTRAINT}} command in the migration with the below command to drop the primary key because {{ADD CONSTRAINT}} doesn't support {{IF NOT EXISTS}}.

{ { code} }

ALTER TABLE catalog_inputs DROP CONSTRAINT IF EXISTS catalog_inputs_pkey; {

{ code} }

Add Comment

[Austin Blatt (Jira)]

Austin Blatt commented on PDB-4881

Re: Add primary key to catalog_inputs Since that index covers all three columns, it could well be just as large as the table itself. Another option would be to add a new column id or hash to act as the primary key

Add Comment

[Austin Blatt (Jira)]

Austin Blatt updated an issue

PuppetDB / PDB-4881

Add primary key to catalog_inputs

Change By: Austin Blatt Affects Version/s: PDB 6.7.3

Add Comment

[Austin Blatt (Jira)]

Austin Blatt updated an issue

PuppetDB / PDB-4881 Add primary key to catalog_inputs Change By: Austin Blatt

When scoping this feature we specifically omitted any work to optimize storage/queries in order to limit the first round work. By doing this we also omitted a primary key, which means the table cannot be cleaned up by {{pg_repack}}. We should add a primary key to the table. I believe this would work as a primary key. {code}

ALTER TABLE catalog_inputs ADD CONSTRAINT catalog_inputs_pkey PRIMARY KEY ( certname_id, type, name , certname_id ); {code}

If we allow customers to add it ahead of time as a workaround, we'll need to preface the {{ADD CONSTRAINT}} command in the migration with the below command to drop the primary key because {{ADD CONSTRAINT}} doesn't support {{IF NOT EXISTS}}. {code} ALTER TABLE catalog_inputs DROP CONSTRAINT IF EXISTS catalog_inputs_pkey; {code}

Add Comment

[Austin Blatt (Jira)]

Austin Blatt commented on PDB-4881

Re: Add primary key to catalog_inputs We can add a pkey to the table on-the-fly with this CREATE UNIQUE INDEX CONCURRENTLY catalog_inputs_type_name_certnameid_idx ON catalog_inputs (type, name, certname_id); ALTER TABLE catalog_inputs ADD CONSTRAINT catalog_inputs_pkey PRIMARY KEY USING INDEX catalog_inputs_type_name_certnameid_idx;

Add Comment

[Austin Blatt (Jira)]

Austin Blatt updated an issue

PuppetDB / PDB-4881

Add primary key to catalog_inputs

Change By: Austin Blatt Epic Link: PDB-4894

Add Comment

[Austin Blatt (Jira)]

Austin Blatt assigned an issue to Austin Blatt

PuppetDB / PDB-4881 Add primary key to catalog_inputs

Change By: Austin Blatt Assignee: Austin Blatt

Add Comment

[Austin Blatt (Jira)]

Austin Blatt updated an issue

PuppetDB / PDB-4881 Add primary key to catalog_inputs

Change By: Austin Blatt Fix Version/s: PDB 6.13.0 Fix Version/s: PDB 6.12.1

Add Comment

[Rob Browning (Jira)]

Rob Browning updated an issue

PuppetDB / PDB-4881 Add primary key to catalog_inputs

Change By: Rob Browning Release Notes: Enhancement Release Notes Summary: Some additional indexing has been added to the catalog inputs storage which should improve query performance in some cases.

Add Comment

[Rob Browning (Jira)]

Rob Browning updated an issue

PuppetDB / PDB-4881 Add primary key to catalog_inputs

Change By: Rob Browning Story Points: 2

Add Comment