Question

Errors with Setting Default Time Filters

  • 12 July 2022
  • 1 reply
  • 114 views

I have an application where I want users to be able to filter their data by time on a dashboard, and I want to be able to set a default filter time of “in the past 365 days”. I’m also hoping that filtering the data will also speed up the query time since less data is being pulled in. Right now when I set the default filter in the dashboard to be the “past 365 days, ” the auto-generated SQL is

SELECT    (CAST(EXTRACT(HOUR FROM open_event_fdw.open_timestamp ) AS INT)) AS "open_event_fdw.open_hour_of_day",            (CAST(COUNT(DISTINCT CASE WHEN (DATE(open_event_fdw.open_timestamp )) is not null THEN ( open_event_fdw.message_id || '-' ||  open_event_fdw.send_id )  ELSE NULL END)  AS FLOAT)) / NULLIF(COUNT(DISTINCT ( delivery_event_fdw.message_id || '-' || delivery_event_fdw.message_timestamp ) ), 0) AS "open_event_fdw.unique_open_rate",    COUNT(DISTINCT CASE WHEN (DATE(open_event_fdw.open_timestamp )) is not null THEN ( open_event_fdw.message_id || '-' ||  open_event_fdw.send_id )  ELSE NULL END) AS "open_event_fdw.unique_open_count"FROM public.submit_event_fdw  AS submit_event_fdwINNER JOIN public.send_event_fdw  AS send_event_fdw ON submit_event_fdw.send_id = send_event_fdw.send_id      AND submit_event_fdw.client_id = send_event_fdw.client_id      AND submit_event_fdw.cured_id = send_event_fdw.cured_idLEFT JOIN public.delivery_event_fdw  AS delivery_event_fdw ON submit_event_fdw.send_id = delivery_event_fdw.send_id          AND submit_event_fdw.client_id = delivery_event_fdw.client_id          AND submit_event_fdw.cured_id = delivery_event_fdw.cured_idLEFT JOIN public.open_event_fdw  AS open_event_fdw ON submit_event_fdw.send_id = open_event_fdw.send_id          AND submit_event_fdw.client_id = open_event_fdw.client_id          AND submit_event_fdw.cured_id = open_event_fdw.cured_idWHERE ((( send_event_fdw.message_timestamp  ) >= ((SELECT (DATE_TRUNC('day', CURRENT_TIMESTAMP) + (-364 || ' day')::INTERVAL))) AND ( send_event_fdw.message_timestamp  ) < ((SELECT ((DATE_TRUNC('day', CURRENT_TIMESTAMP) + (-364 || ' day')::INTERVAL) + (365 || ' day')::INTERVAL))))) AND (submit_event_fdw.client_id ) = 'socratic'GROUP BY    1HAVING COUNT(DISTINCT CASE WHEN (DATE(open_event_fdw.open_timestamp )) is not null THEN ( open_event_fdw.message_id || '-' ||  open_event_fdw.send_id )  ELSE NULL END) > 0ORDER BY    1FETCH NEXT 25 ROWS ONLY

Which has nested SELECT’s, which causes the query to take a very long time, and I receive an Error:

Ensure the database connection is working and the modeled syntax is appropriate to the connections SQL dialect.ERROR: exceeded the maximum size allowed for the total set of cursor data: 8000MB. Where: remote SQL command: 

When I set a filter with a default specified date range, such as from 7/11/2021 to 7/11/2022, I do not receive the same error, and the data seems to load reasonably fast. However, I don’t want the default range to be a specific date because we would have to reset the date periodically for the default to be reasonable for our customers (making the range for the current last year).To try to fix this issue, I tried to set a custom filter, ${submit_event_fdw.submitted_timestamp_date} >= add_years(-1, now()) .
This autogenerates the following SQL, which appears fine (there are no nested SELECT statements).

SELECT    (CAST(EXTRACT(HOUR FROM open_event_fdw.open_timestamp ) AS INT)) AS "open_event_fdw.open_hour_of_day",            (CAST(COUNT(DISTINCT CASE WHEN (DATE(open_event_fdw.open_timestamp )) is not null THEN ( open_event_fdw.message_id || '-' ||  open_event_fdw.send_id )  ELSE NULL END)  AS FLOAT)) / NULLIF(COUNT(DISTINCT ( delivery_event_fdw.message_id || '-' || delivery_event_fdw.message_timestamp ) ), 0) AS "open_event_fdw.unique_open_rate",    COUNT(DISTINCT CASE WHEN (DATE(open_event_fdw.open_timestamp )) is not null THEN ( open_event_fdw.message_id || '-' ||  open_event_fdw.send_id )  ELSE NULL END) AS "open_event_fdw.unique_open_count"FROM public.submit_event_fdw  AS submit_event_fdwLEFT JOIN public.delivery_event_fdw  AS delivery_event_fdw ON submit_event_fdw.send_id = delivery_event_fdw.send_id          AND submit_event_fdw.client_id = delivery_event_fdw.client_id          AND submit_event_fdw.cured_id = delivery_event_fdw.cured_idLEFT JOIN public.open_event_fdw  AS open_event_fdw ON submit_event_fdw.send_id = open_event_fdw.send_id          AND submit_event_fdw.client_id = open_event_fdw.client_id          AND submit_event_fdw.cured_id = open_event_fdw.cured_idWHERE (submit_event_fdw.client_id ) = 'socratic' AND ((DATE_TRUNC('day', submit_event_fdw.submitted_timestamp )) >= (CURRENT_TIMESTAMP + (-1 || ' year')::INTERVAL))GROUP BY    1HAVING COUNT(DISTINCT CASE WHEN (DATE(open_event_fdw.open_timestamp )) is not null THEN ( open_event_fdw.message_id || '-' ||  open_event_fdw.send_id )  ELSE NULL END) > 0ORDER BY    1FETCH NEXT 25 ROWS ONLY

However, this also comes up with an Error:

Ensure the database connection is working and the modeled syntax is appropriate to the connections SQL dialect.ERROR: exceeded the maximum size allowed for the total set of cursor data: 8000MB. Where: remote SQL command: 

Other times I have also been receiving the error:

Ensure the database connection is working and the modeled syntax is appropriate to the connections SQL dialect.ERROR: opening multiple cursors from within the same client connection is not allowed.

Is there something else that could be causing these errors or is there a way to dynamically set the default time for a time range filter?


1 reply

What is the database type, @thomas-cured ?

Reply