Jira (PDB-4937) Add a default statement timeout to user queries

[Austin Blatt (Jira)]

Austin Blatt created an issue

PuppetDB / PDB-4937 Add a default statement timeout to user queries Issue Type: Improvement Assignee: Austin Blatt Created: 2020/10/21 10:50 AM Fix Versions: PDB 7.0.0 Labels: platform_7 Priority: Normal Reporter: Austin Blatt Remaining questions What is the default statement timeout for a query? 1min, 5min, 10min? Add Comment

This message was sent by Atlassian Jira (v8.5.2#805002-sha1:a66f935)

[Austin Blatt (Jira)]

Austin Blatt updated an issue

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: Austin Blatt Story Points: 5

Add Comment

[Austin Blatt (Jira)]

Austin Blatt updated an issue

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: Austin Blatt Fix Version/s: PDB 6.14.0

Add Comment

[Austin Blatt (Jira)]

Austin Blatt updated an issue

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: Austin Blatt Acceptance Criteria: * Query parameter to increase/decrease statement timeout for that query * Config/env parameter to increase/decrease statement timeout for _all_ queries * Disabled by a default value of 0 in PuppetDB 6.Y.0

Add Comment

[Austin Blatt (Jira)]

Austin Blatt assigned an issue to Unassigned

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: Austin Blatt Assignee: Austin Blatt

Add Comment

[Austin Blatt (Jira)]

Austin Blatt assigned an issue to Rob Browning

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: Austin Blatt Assignee: Rob Browning

Add Comment

[Austin Blatt (Jira)]

Austin Blatt updated an issue

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: Austin Blatt Fix Version/s: PDB 6.14.0 Fix Version/s: PDB 7.0.0

Add Comment

[Rob Browning (Jira)]

Rob Browning commented on PDB-4937

Re: Add a default statement timeout to user queries We're setting this work aside until/unless we come up with a more sophisticated approach.  That is, with streaming responses, we can't return a custom status (say 503) to indicate a timeout once we've started streaming, and we might also want to provide a finer distinction than just 503. In order to pursue this further, we might want to investigate chunked transfer encoding "trailing headers", where we could report a timeout.  Alternately, we could change our output format in order to allow us to send an indication of the timeout in the result stream itself that the client could then detect and handle. The rest of the work went fairly smoothly, so were we able to come up with a palatable solution to this question, then we should be able to resume the effort.

Add Comment

[Mihai Buzgau (Jira)]

Mihai Buzgau updated an issue

PuppetDB / PDB-4937

Add a default statement timeout to user queries

Change By: Mihai Buzgau Labels: platform_7

Add Comment

[Zachary Kent (Jira)]

Zachary Kent updated an issue

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: Zachary Kent Epic Link: PDB-4969

Add Comment

[David McTavish (Jira)]

David McTavish updated an issue

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: David McTavish Story Points: 5

Add Comment

This message was sent by Atlassian Jira (v8.20.2#820002-sha1:829506d)

[David McTavish (Jira)]

David McTavish updated an issue

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: David McTavish Priority: Normal Low

Add Comment

[Alvin Rodis (Jira)]

Alvin Rodis updated an issue

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: Alvin Rodis Labels: jira_escalated

Add Comment

This message was sent by Atlassian Jira (v8.20.11#820011-sha1:0629dd8)

[Charlie Sharpsteen (Jira)]

Charlie Sharpsteen updated an issue

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: Charlie Sharpsteen Zendesk Ticket Count: 1 Zendesk Ticket IDs: 49787

Add Comment

[Charlie Sharpsteen (Jira)]

Charlie Sharpsteen commented on PDB-4937

Re: Add a default statement timeout to user queries Re-opening this one. We've got a ticket where a user is reporting queries that persist for 1 day or longer and end up blocking other PuppetDB activities by holding locks. The root cause of this behavior is likely some other issue, but a default timeout for queries is a first line of defense that would help to blunt the impact.

Add Comment

[Cas Donoghue (Jira)]

Cas Donoghue commented on PDB-4937

Re: Add a default statement timeout to user queries

Then engineering work for this is pretty straight forward. We need to work with product (David Piekny ) and CS to come up with reasonable defaults and nail down which stream we target the work for.

Add Comment

[Austin Blatt (Jira)]

Austin Blatt updated an issue

PuppetDB / PDB-4937

Add a default statement timeout to user queries

Change By: Austin Blatt Team: HA Skeletor

Add Comment

[Austin Blatt (Jira)]

Austin Blatt assigned an issue to Unassigned

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: Austin Blatt Assignee: Rob Browning

Add Comment

[Austin Blatt (Jira)]

Austin Blatt updated an issue

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: Austin Blatt Priority: Low Normal

Add Comment

[Austin Blatt (Jira)]

Austin Blatt updated an issue

PuppetDB / PDB-4937 Add a default statement timeout to user queries Change By: Austin Blatt

Remaining questions * What is the default statement timeout for a query? 1min, 5min, 10min? JDBC does _not_ respect Thread interrupts so we could implement this by tracking running jdbc queries and calling JDBC's Statement.cancel() method. Alternatively, and likely preferably, we can use statement timeouts to abort a query after a set time. Either by using setQueryTimeout (or setQueryTimeoutMs), and allow pgjdbc to handle things from there (see https://github.com/pgjdbc/pgjdbc/blob/5c78edb1412467636f996416ad37118f651db86e/pgjdbc/src/test/java/org/postgresql/test/jdbc2/StatementTest.java#L737-L762 for an example). Or, we can set explicit statement timeouts in the SQL if the above does not work {code} SET statement_timeout TO 1 I puppetdb-> ; SET I puppetdb=> select pg_sleep(2); ERROR:  canceling statement due to statement timeout {code}

Add Comment

[Austin Blatt (Jira)]

Austin Blatt updated an issue

PuppetDB / PDB-4937 Add a default statement timeout to user queries Change By: Austin Blatt

Remaining questions * What is the default statement timeout for a query? 1min, 5min, 10min? JDBC does _not_ respect Thread interrupts so we could implement this by tracking running jdbc queries and calling JDBC's Statement.cancel() method. Alternatively, and likely preferably, we can use statement timeouts to abort a query after a set time. Either by using setQueryTimeout (or setQueryTimeoutMs), and allow pgjdbc to handle things from there (see https://github.com/pgjdbc/pgjdbc/blob/5c78edb1412467636f996416ad37118f651db86e/pgjdbc/src/test/java/org/postgresql/test/jdbc2/StatementTest.java#L737-L762 for an example). Or, we can set explicit statement timeouts in the SQL if the above does not work {code}

puppetdb-> SET statement_timeout TO 1 I puppetdb-> ; SET

I puppetdb=> select pg_sleep(2); ERROR:  canceling statement due to statement timeout {code}

Add Comment

[Rob Browning (Jira)]

Rob Browning assigned an issue to Rob Browning

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: Rob Browning Sprint: Skeletor 03/15/2023 Story Points: 1 Assignee: Rob Browning

Add Comment

[Cas Donoghue (Jira)]

Cas Donoghue updated an issue

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: Cas Donoghue Sprint: Skeletor 03/15/2023 , Skeletor 03/29/2023

Add Comment

[Cas Donoghue (Jira)]

Cas Donoghue updated an issue

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: Cas Donoghue Sprint: Skeletor 03/15/2023, Skeletor 03/29/2023 , Skeletor 04/12/2023

Add Comment

[Nirupama Mantha (Jira)]

Nirupama Mantha updated an issue

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: Nirupama Mantha Labels: jira_escalated

Add Comment

[Nirupama Mantha (Jira)]

[Austin Blatt (Jira)]

Austin Blatt updated an issue

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: Austin Blatt Fix Version/s: PDB 8.0.0

Add Comment

[Cas Donoghue (Jira)]

Cas Donoghue updated an issue

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: Cas Donoghue Sprint: Skeletor 03/15/2023, Skeletor 03/29/2023, Skeletor 04/12/2023 , Skeletor 04/26/2024

Add Comment

[Rob Browning (Jira)]

Rob Browning updated an issue

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: Rob Browning Release Notes: Enhancement Release Notes Summary: PuppetDB now supports query timeouts for queries to the `query/` endpoint via an [optional query parameter].  A [default] and a [maximum] can also be specified in the configuration.  The current default is ten minutes.

Add Comment

[Rob Browning (Jira)]

Rob Browning updated an issue

PuppetDB / PDB-4937 Add a default statement timeout to user queries

Change By: Rob Browning Epic Link: PDB PE - 4969 35436

Add Comment

[Rob Browning (Jira)]

Rob Browning commented on PDB-4937

Re: Add a default statement timeout to user queries In the end we implemented timeouts both on the pdb and pg sides, though the pg side timeouts aren't as precise because pg's statement_timeouts appear to apply to each underlying fetch batch for a top-level select, rather than the select as a whole. After some discussion, including CS, we also settled on a default statement timeout of 10m, which we expect to be long enough to avoid any significant disruption of existing environments (that aren't already likely in related trouble). Ideally, it might be even lower, something we can revisit later. Assuming everything works right, that timeout should also substantially mitigate (or outright avoid) the causes of a number of serious escalations we've seen in the past, since no queries will be allowed to remain open for (sometimes) hours, unintentionally.

Add Comment