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

Zachary Kent (Jira)

unread,

May 14, 2021, 2:57:04 PM5/14/21

to puppe...@googlegroups.com

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.