query_requests is a system view. You have to first pull the underlying
system tables for this view:
Code: Select all
dbadmin=> SELECT query_string
dbadmin-> FROM v_internal.vs_system_views
dbadmin-> WHERE view_schema = 'v_monitor'
dbadmin-> AND view_name = 'query_requests';
query_string
------------------------------------------------------------------------------------------------------------------------
create view "v_monitor"."query_requests" as select ri.node_name,
ri.user_name,
ri.session_id,
ri.request_id,
ri.transaction_id,
ri.statement_id,
ri.request_type,
replace(replace(ri.request, E'\n', ' '), E'\t', ' ') as request,
ri.label as request_label,
ri.search_path,
round(ra.memory_mb, 2) as memory_acquired_mb,
rc.success,
de.error_count,
ri.time as start_timestamp,
rc.time as end_timestamp,
datediff('millisecond', ri.time, rc.time) as request_duration_ms,
rc.time IS NULL as is_executing
from
v_internal.dc_requests_issued ri
LEFT OUTER JOIN v_internal.dc_requests_completed rc USING (node_name, session_id, request_id)
LEFT OUTER JOIN (select node_name,
session_id,
request_id,
count(*) as error_count
from v_internal.dc_errors
where error_level >= 20
group by 1,2,3) de USING (node_name, session_id, request_id)
LEFT OUTER JOIN (select node_name,
transaction_id,
statement_id,
max(memory_kb)/1024::float as memory_mb
from v_internal.dc_resource_acquisitions
where result = 'Granted'
group by 1,2,3) ra
USING (node_name, transaction_id, statement_id)
order by is_executing desc, rc.time desc
;
(1 row)
This view involves:
- v_internal.dc_requests_issued
- v_internal.dc_requests_completed
- v_internal.dc_errors
- v_internal.dc_resource_acquisitions
If you try to find the oldest row for each data collector component, you'll see a lot of variance. That's because by default, data collector uses a disk based policy. Meaning, once the disk based retention limit is reached, it will purge those rows from the data collector component.
Since this view involves 4 system tables, you will need to set a retention policy for each of those to make up the query_requests view. However, you can set a
time based collection policy for each component.
My recommendation would be to put together a more simple view; perhaps with just dc_requests_issued and dc_requests_completed so that you don't need to increase the retention policy for components such as dc_errors or dc_resource_acquisitions.
You can search v_internal.vs_system_tables for other user related system tables:
Code: Select all
SELECT table_schema,
table_name,
table_description,
is_view
FROM v_internal.vs_system_tables
WHERE table_description ILIKE '%user%'
ORDER BY table_schema,
table_name;