[PDB-2527] support latest_report? as a query field on event-counts/aggregate-event-counts Created: 2016/03/11  Updated: 2017/03/15  Resolved: 2016/05/06

Status: Closed
Project: PuppetDB
Component/s: None
Affects Version/s: None
Fix Version/s: PDB 4.1.0

Type: Bug Priority: Normal
Reporter: Wyatt Alt Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Blocks
Template:
Story Points: 3

 Description   

We document these endpoints as being queryable with the same fields as events, but latest_report? is not included. I'm not sure yet why it doesn't work, but Verne Lindner has an interest in this fix.

To be concrete, this should work

[wyatt@desktop puppetdb](master) $ curl -X GET http://localhost:8080/pdb/query/v4/event-counts -d 'distinct_start_time=2015-01-01' -d 'distinct_end_time=2016-10-10' -d 'distinct_resources=true' -d 'summarize_by=resource' -d 'query=["=", "latest_report?", true]'
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1"/>
<title>Error 500 </title>
</head>
<body>
<h2>HTTP ERROR: 500</h2>
<p>Problem accessing /pdb/query/v4/event-counts. Reason:
<pre>    org.postgresql.util.PSQLException: ERROR: column certnames.latest_report does not exist
  Hint: Perhaps you meant to reference the column "certnames.latest_report_id".
  Position: 2905</pre></p>
<hr /><i><small>Powered by Jetty://</small></i>
</body>
</html>



 Comments   
Comment by Beth Cornils [ 2016/03/15 ]

Ryan Senior and Wyatt Alt any way we can bump this? And if so what would I hurt by doing so?

Comment by Wyatt Alt [ 2016/03/15 ]

Beth Cornils Ryan Senior I'm +1 on bumping. I just looked into it for a few minutes and I can see the issue. I don't think it'll be a hard fix.

The problem is right here:
https://github.com/puppetlabs/puppetdb/blob/master/src/puppetlabs/puppetdb/query.clj#L625

That code is not covered by tests, and the latest_report column of certnames has since been renamed to latest_report_id. Additionally the latest_reports table does not exist. I think changing that line to

latest_events.report_id %s (SELECT certnames.latest_report_id FROM certnames)

and making another change to include the report_id column in the latest_events CTE will fix it. After that we'll need to test it against a large database to verify an improvement. If the optimizer is still getting tripped up on the CTE at that point we can do a nasty workaround by parsing the query here: https://github.com/puppetlabs/puppetdb/blob/master/src/puppetlabs/puppetdb/query/events.clj#L52

and baking the latest report restriction into the CTE itself.

Beth Cornils What's your timeframe for this?

Generated at Wed Jun 19 18:50:08 PDT 2019 using JIRA 7.7.1#77002-sha1:e75ca93d5574d9409c0630b81c894d9065296414.