Uploaded image for project: 'PuppetDB'
  1. PuppetDB
  2. PDB-3911

Resource events table should have a primary key

    Details

    • Template:
    • Sprint:
      Hopper/Triage
    • Release Notes:
      Enhancement
    • Release Notes Summary:
      Hide
      This adds a primary key to the resource_events table, which allows the use of pg_repack to reclaim space without taking the table offline, like the current "vacuum full" approach does.

      This will rewrite the entire resource_events table, so you will need to have more than the existing table's size available during the upgrade. The upgrade time is relative to the size of your table.
      Show
      This adds a primary key to the resource_events table, which allows the use of pg_repack to reclaim space without taking the table offline, like the current "vacuum full" approach does. This will rewrite the entire resource_events table, so you will need to have more than the existing table's size available during the upgrade. The upgrade time is relative to the size of your table.
    • QA Risk Assessment:
      Needs Assessment

      Description

      The Problem

      When running pg_repack on the PuppetDB database it won't repack the resource_events table because it doesn't have a primary key.

      As far as I can tell resource_events has a unique index on 4 columns that could just be changed to a primary key and we'd get the unique index as a side effect of the Primary Key.

      Suggested Solution

      Drop the unique index and create a primary key on the same columns.

      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.

      [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
      

        Attachments

          Issue Links

            Activity

              jsd-sla-details-panel

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  nick.walker Nick Walker
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  3 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: