Jira (PDB-3911) Resource events table should have a primary key
Title: Message Title Morgan Rhodes updated an issue PuppetDB / PDB-3911 Resource events table should have a primary key Change By: Morgan Rhodes Sprint: Release Engineering Hopper Add Comment This message was sent by Atlassian Jira (v8.5.2#805002-sha1:a66f935) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.246652.1524182728000.164055.1615490885263%40Atlassian.JIRA.
Jira (PDB-3911) Resource events table should have a primary key
Title: Message Title Zachary Kent updated an issue PuppetDB / PDB-3911 Resource events table should have a primary key Change By: Zachary Kent Sprint: Release Engineering Hopper Add Comment This message was sent by Atlassian Jira (v8.5.2#805002-sha1:a66f935) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.246652.1524182728000.162744.1615408442840%40Atlassian.JIRA.
Jira (PDB-3911) Resource events table should have a primary key
Title: Message Title Zachary Kent updated an issue PuppetDB / PDB-3911 Resource events table should have a primary key Change By: Zachary Kent Sprint: Add Comment This message was sent by Atlassian Jira (v8.5.2#805002-sha1:a66f935) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.246652.1524182728000.162642.1615408320142%40Atlassian.JIRA.
Jira (PDB-3911) Resource events table should have a primary key
Title: Message Title Heston Hoffman updated an issue PuppetDB / PDB-3911 Resource events table should have a primary key Change By: Heston Hoffman Labels: cspapercuts resolved-issue-added Add Comment This message was sent by Atlassian JIRA (v7.7.1#77002-sha1:e75ca93) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-3911) Resource events table should have a primary key
Title: Message Title Austin Blatt updated an issue PuppetDB / PDB-3911 Resource events table should have a primary key Change By: Austin Blatt Fix Version/s: PDB 6.3.0 Add Comment This message was sent by Atlassian JIRA (v7.7.1#77002-sha1:e75ca93) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-3911) Resource events table should have a primary key
Title: Message Title Rob Browning updated an issue PuppetDB / PDB-3911 Resource events table should have a primary key Change By: Rob Browning Affects Version/s: PDB 6.3.0 Add Comment This message was sent by Atlassian JIRA (v7.7.1#77002-sha1:e75ca93) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-3911) Resource events table should have a primary key
Title: Message Title Robert Roland updated an issue PuppetDB / PDB-3911 Resource events table should have a primary key Change By: Robert Roland Release Notes Summary: 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. Release Notes: Enhancement Add Comment This message was sent by Atlassian JIRA (v7.7.1#77002-sha1:e75ca93) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://gr
Jira (PDB-3911) Resource events table should have a primary key
Title: Message Title Rob Browning updated an issue PuppetDB / PDB-3911 Resource events table should have a primary key Change By: Rob Browning Sprint: PDB Hopper/ Triage Add Comment This message was sent by Atlassian JIRA (v7.7.1#77002-sha1:e75ca93) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-3911) Resource events table should have a primary key
Title: Message Title Nick Walker updated an issue PuppetDB / PDB-3911 Resource events table should have a primary key Change By: Nick Walker Sprint: PDB Triage Add Comment This message was sent by Atlassian JIRA (v7.7.1#77002-sha1:e75ca93) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-3911) Resource events table should have a primary key
Title: Message Title Nick Walker updated an issue PuppetDB / PDB-3911 Resource events table should have a primary key Change By: Nick Walker h1. The ProblemWhen 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 unique index Primary Key .h1. Suggested SolutionDrop the unique index and create a primary key on the same columns. 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|
Jira (PDB-3911) Resource events table should have a primary key
Title: Message Title Nick Walker updated an issue PuppetDB / PDB-3911 Resource events table should have a primary key Change By: Nick Walker h1. The ProblemWhen 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 unique index.h1. Suggested SolutionDrop the unique index and create a primary key on the same columns. 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| |Indexe
Jira (PDB-3911) Resource events table should have a primary key
Title: Message Title Nick Walker created an issue PuppetDB / PDB-3911 Resource events table should have a primary key Issue Type: Improvement Assignee: Unassigned Created: 2018/04/19 5:05 PM Labels: cspapercuts Priority: Normal Reporter: Nick Walker 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 unique index. Suggested Solution Drop the unique index and create a primary key on the same columns. Add Comment