Jira (PDB-5130) Adjust the updatetime.sql script to handle partitions

[Zachary Kent (Jira)]

Title: Message Title

Zachary Kent created an issue   PuppetDB / PDB-5130 Adjust the updatetime.sql script to handle partitions Issue Type: Bug Assignee: Unassigned Created: 2021/05/14 11:56 AM Priority: Normal Reporter: Zachary Kent The updatetime.sql script we pulled from the SLV team was written before PDB had partitioned reports and resource-events tables. If we use the script as-is to shift the timestamps for these partitions we'll hit the constraint error shown below. We'll need to update the sql script to account for this issue. It might be sufficient to create the partitions for the days we need to shift the timestamps to ahead of time. This could be difficult to replicate exactly in the sql script because we do this creation on demand here in storage.clj. We should first test that having newer partitions allows the update of timestamps in the reports and resource-events tables to succeed and then figure out the best way to create the newer partitions we'll need. Example error seen when shifting timestamps to a range outside of what's allowed in a partition: ERROR: new row for relation "reports_20210416z" violates check constraint "reports_20210416z_producer_timestamp_check" DETAIL: Failing row contains (22, \x62c7899ab6e6219bd2766bd053e4ca9fa8b8b905, 2bce4bec-ce8b-4d36-91d5-e204af508e98, host-4, 3.7.2, 4, a3adc55e-514f-4483-80d3-a258646ced2c, 2021-05-13 15:47:47.293-07, 2021-05-13 15:47:52.293-07, 2021-05-18 12:33:09.63-07, f, 1, 1, null, null, 2021-05-13 15:47:57.293-07, [{"name": "anchor", "value": 0.00046438, "category": "time"}, {"..., [{"file": null, "line": null, "tags": ["notice"], "time": "2015-..., [{"file": "/home/wyatt/.puppet/modules/concat/manifests/fragment..., 76e7a26a-f7a2-44bc-b0fd-8db6edd8ec9e, on_failure, 87bf8011-918c-40b3-aded-cee4ec9d5536, 1, f, t, null, agent). The error above can be replicated if you run the pdb-dataset command with a pg_dump from a PDB with partitioned tables where the date you're shifting to is beyond of the range of the existing partitions in the pg_dump. Add Comment   This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97) -- 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.399615.1621018567000.33514.1621018620193%40Atlassian.JIRA.