h1. Some commands that work for me
I created a primary key on the same columns as the unique constraint and it worked in my test instance. However, it does change the property column in resource_events to be "not null" where as in the current schema "not null" is not enforced.
The question is do we actually ever insert null into the property column or is that a safe thing to change? If we do insert null into that column could we just update NULL rows to have some other value so we can make the primary key.
{code}
[root@master201810-centos ~]# su - pe-postgres -s /bin/bash -c "/opt/puppetlabs/server/bin/psql -d pe-puppetdb"
psql (9.6.8)
Type "help" for help.
pe-puppetdb=# \d+ resource_events
Table "public.resource_events"
Column | Type | Modifiers | Storage | Stats target | Description
-------------------+--------------------------+---------------------------------+----------+--------------+-------------
report_id | bigint | not null | plain | |
certname_id | bigint | not null | plain | |
status | text | not null | extended | |
timestamp | timestamp with time zone | not null | plain | |
resource_type | text | not null | extended | |
resource_title | text | not null | extended | |
property | text | | extended | |
new_value | text | | extended | |
old_value | text | | extended | |
message | text | | extended | |
file | text | default NULL::character varying | extended | |
line | integer | | plain | |
containment_path | text[] | | extended | |
containing_class | text | | extended | |
corrective_change | boolean | | plain | |
Indexes:
"resource_events_unique" UNIQUE CONSTRAINT, btree (report_id, resource_type, resource_title, property)
"resource_events_containing_class_idx" btree (containing_class)
"resource_events_property_idx" btree (property)
"resource_events_reports_id_idx" btree (report_id)
"resource_events_resource_timestamp" btree (resource_type, resource_title, "timestamp")
"resource_events_resource_title_idx" btree (resource_title)
"resource_events_status_for_corrective_change_idx" btree (status) WHERE corrective_change
"resource_events_status_idx" btree (status)
"resource_events_timestamp_idx" btree ("timestamp")
Foreign-key constraints:
"resource_events_report_id_fkey" FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE
^
pe-puppetdb=# ALTER TABLE resource_events ADD PRIMARY KEY (report_id, resource_type, resource_title, property);
ALTER TABLE
pe-puppetdb=# \d+ resource_events
Table "public.resource_events"
Column | Type | Modifiers | Storage | Stats target | Description
-------------------+--------------------------+---------------------------------+----------+--------------+-------------
report_id | bigint | not null | plain | |
certname_id | bigint | not null | plain | |
status | text | not null | extended | |
timestamp | timestamp with time zone | not null | plain | |
resource_type | text | not null | extended | |
resource_title | text | not null | extended | |
property | text | not null | extended | |
new_value | text | | extended | |
old_value | text | | extended | |
message | text | | extended | |
file | text | default NULL::character varying | extended | |
line | integer | | plain | |
containment_path | text[] | | extended | |
containing_class | text | | extended | |
corrective_change | boolean | | plain | |
Indexes:
"resource_events_pkey" PRIMARY KEY, btree (report_id, resource_type, resource_title, property)
"resource_events_unique" UNIQUE CONSTRAINT, btree (report_id, resource_type, resource_title, property)
"resource_events_containing_class_idx" btree (containing_class)
"resource_events_property_idx" btree (property)
"resource_events_reports_id_idx" btree (report_id)
"resource_events_resource_timestamp" btree (resource_type, resource_title, "timestamp")
"resource_events_resource_title_idx" btree (resource_title)
"resource_events_status_for_corrective_change_idx" btree (status) WHERE corrective_change
"resource_events_status_idx" btree (status)
"resource_events_timestamp_idx" btree ("timestamp")
Foreign-key constraints:
"resource_events_report_id_fkey" FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE
pe-puppetdb=# ALTER TABLE resource_events DROP CONSTRAINT IF EXISTS resource_events_unique;
ALTER TABLE
pe-puppetdb=# \d+ resource_events
Table "public.resource_events"
Column | Type | Modifiers | Storage | Stats target | Description
-------------------+--------------------------+---------------------------------+----------+--------------+-------------
report_id | bigint | not null | plain | |
certname_id | bigint | not null | plain | |
status | text | not null | extended | |
timestamp | timestamp with time zone | not null | plain | |
resource_type | text | not null | extended | |
resource_title | text | not null | extended | |
property | text | not null | extended | |
new_value | text | | extended | |
old_value | text | | extended | |
message | text | | extended | |
file | text | default NULL::character varying | extended | |
line | integer | | plain | |
containment_path | text[] | | extended | |
containing_class | text | | extended | |
corrective_change | boolean | | plain | |
Indexes:
"resource_events_pkey" PRIMARY KEY, btree (report_id, resource_type, resource_title, property)
"resource_events_containing_class_idx" btree (containing_class)
"resource_events_property_idx" btree (property)
"resource_events_reports_id_idx" btree (report_id)
"resource_events_resource_timestamp" btree (resource_type, resource_title, "timestamp")
"resource_events_resource_title_idx" btree (resource_title)
"resource_events_status_for_corrective_change_idx" btree (status) WHERE corrective_change
"resource_events_status_idx" btree (status)
"resource_events_timestamp_idx" btree ("timestamp")
Foreign-key constraints:
"resource_events_report_id_fkey" FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE
{code}
|
|
|
