Project

General

Profile

Actions

Bug #7656

closed

My queries don't show timeline ones

Added by Pavel Kácha about 1 year ago. Updated 3 months ago.

Status:
Closed
Priority:
Normal
Category:
Development - Core
Target version:
Start date:
05/19/2023
Due date:
% Done:

0%

Estimated time:
To be discussed:
Yes

Description

Seems timeline queries are not shown in "My queries", question is whether they are counted into search query quota.


Related issues

Related to Mentat - Bug #7710: API requests return 500 when their SQL query is cancelledClosedJakub Maloštik03/04/2024

Actions
Actions #1

Updated by Rajmund Hruška about 1 year ago

  • Status changed from New to In Progress
  • Assignee set to Rajmund Hruška

The view 'my queries' shows the queries which contain substring _mentatq(...)_, for example

SELECT * FROM events AS "_mentatq(4_cormhs)_" WHERE "detecttime" >= '2023-05-19T07:00:00'::timestamp AND "detecttime" <= '2023-05-26T07:00:00'::timestamp ORDER BY "detecttime" DESC LIMIT 100

The timeline queries look like this:

WITH
timeline AS (SELECT * FROM (SELECT '2023-04-01T00:00:00'::timestamp AS bucket UNION SELECT generate_series('2023-04-01T00:00:00'::timestamp, '2023-04-07T00:00:00'::timestamp - INTERVAL '1 microsecond', '0 days 3600.000000 seconds'::interval) AS bucket) AS t ORDER BY bucket),
raw AS (SELECT GREATEST('2023-04-01T00:00:00'::timestamp, '2023-04-01T00:00:00'::timestamp + '0 days 3600.000000 seconds'::interval * (width_bucket(detecttime, (SELECT array_agg(bucket) FROM generate_series('2023-04-01T00:00:00'::timestamp, '2023-04-07T00:00:00'::timestamp - INTERVAL '1 microsecond', '0 days 3600.000000 seconds'::interval) AS bucket)) - 1)) AS bucket, COUNT(*) AS count FROM "events" WHERE "detecttime" >= '2023-04-01T00:00:00'::timestamp AND "detecttime" < '2023-04-07T00:00:00'::timestamp GROUP BY bucket)
(SELECT timeline.bucket, COALESCE(count, 0) AS count FROM "timeline" LEFT JOIN "raw" ON timeline.bucket = raw.bucket ORDER BY bucket ASC) UNION ALL SELECT NULL, SUM(count)::bigint FROM "raw";

I don't really know where to put _mentatq(...)_. Radko Krkoš could you please help me?

Actions #2

Updated by Rajmund Hruška about 1 year ago

Rajmund Hruška wrote in #note-1:

The view 'my queries' shows the queries which contain substring _mentatq(...)_, for example

[...]

The timeline queries look like this:
[...]

I don't really know where to put _mentatq(...)_. Radko Krkoš could you please help me?

Nevermind, I was missing the double quotes.

Actions #3

Updated by Rajmund Hruška about 1 year ago

With timeline queries it's a bit more complicated. When the user presses 'Search' button, there is only one query. But as soon as the first tab is computed - #events - the user can click on the other tabs, each one producing its own query. Now, the user doesn't know which query belongs to which tab.

Also, I am not sure what should happen after the query is canceled. Should the tab stay empty? And then when the tab is clicked on again, should it execute the query again?

Actions #4

Updated by Rajmund Hruška about 1 year ago

  • To be discussed changed from No to Yes
Actions #5

Updated by Pavel Kácha about 1 year ago

Rajmund Hruška wrote in #note-3:

With timeline queries it's a bit more complicated. When the user presses 'Search' button, there is only one query. But as soon as the first tab is computed - #events - the user can click on the other tabs, each one producing its own query. Now, the user doesn't know which query belongs to which tab.

Also, I am not sure what should happen after the query is canceled. Should the tab stay empty? And then when the tab is clicked on again, should it execute the query again?

Oy vey. I'd say the tab should not stay empty, but show some info that the query has been killed. Maybe that's the argument to ditch HTTP error and use the same mechanism (flash banner?) even for events? And yes, the only sane thing is for query to start again when clicked. (User can always modify the query parameters before clicking.)

Actions #6

Updated by Rajmund Hruška about 1 year ago

To identify the user query, 'AS mentatq(ID_xxxxx)' is appended to the query. The queries for the given user are then searched by using regular expressions. But, the query from pg_stat_activity is truncated [1]. So if the query is too long, the query won't be found, as that appendix is added to the end of the query.

So, we can either change the limit of the query length by making a migration or we can come up with a way of having the identifier at the beginning of the query.

[1] https://stackoverflow.com/questions/1135266/queries-in-pg-stat-activity-are-truncated

Actions #7

Updated by Rajmund Hruška 12 months ago

  • Assignee changed from Rajmund Hruška to Jakub Maloštik
Actions #8

Updated by Pavel Kácha 12 months ago

Jakub, could you please look into it? We need to:

  • widen the pg_stat_activity result to actually get the id (migration?) as mentioned in #7656#note-6
  • within timeline tab somehow inform the user that his query has been killed as mentioned in #7656#note-5. No need for fancy stuff, as it will probably need a later rehaul with new Boostrap (#7554)
Actions #9

Updated by Pavel Kácha 12 months ago

From meeting 2023-06-29:

After widening the pg_stat_activity result size, the PostgreSQL needs to be restarted. We'll leave this on admin, but they need to be informed - so we need to add the bullet into "Upgrading" docs and preferably also into release notes of new stable version.

Actions #10

Updated by Jakub Maloštik 10 months ago

  • Status changed from In Progress to Resolved
Actions #11

Updated by Rajmund Hruška 10 months ago

  • Status changed from Resolved to In Review
  • Target version changed from Backlog to 2.12
Actions #12

Updated by Rajmund Hruška 6 months ago

  • Status changed from In Review to In Progress

After cancelling the query, the tab in the timeline says An error occurred while retrieving data: 500 Internal Server Error. I think there should be a meaningful error message.

Actions #13

Updated by Jakub Maloštik 4 months ago

  • Related to Bug #7710: API requests return 500 when their SQL query is cancelled added
Actions #14

Updated by Jakub Maloštik 4 months ago

  • Status changed from In Progress to Resolved
Actions #15

Updated by Rajmund Hruška 3 months ago

  • Status changed from Resolved to In Review
Actions #16

Updated by Rajmund Hruška 3 months ago

  • Status changed from In Review to Closed
Actions

Also available in: Atom PDF