Partition tables not created...

SebastienPetit

Environment
ArcGIS Monitor 2023.2
Windows Server 2019 Standard
RAM 24 GB
PostgreSQL 14

Problem description:

ArcGIS Monitor stopped writing collected metrics into PostgreSQL database and accordingly nothing shows up in monitor web application. After further analysis, we found that ArcGIS Monitor is trying to insert metrics data but the partition tables are missing. For example, monitor is trying to insert metrics data for 12 Sep 2023, unfortunately, monitor does not find metrics_data_2023_09_12 partition table. It keeps trying to insert records till the heap memory is full and it throws heap limit Allocation failed.

After creating the partition table manually, monitor started to write metrics data successfully and it shows up in the Monitor web application. Accordingly, the root cause and the effect is probably that the partition tables are not being created.

 

RanaDas

Hi @essamadelali ,

Your observations was helpful for us. We made a little bit changes in creating the partition tables with the help of our DBA @MOHAMMEDKHAJAANSARI . Instead of creating tables one by one we created a function in which we pass the date ranges and the tables get created when a single query is fired. Other folks can utilize it if they using PosgreSQL.

Function Code:

DECLARE
current_partition_date DATE := start_date;
BEGIN
-- Create the main table if it doesn't exist

-- Loop to create partitions
WHILE current_partition_date <= end_date LOOP
-- Generate the partition name based on the current date
-- You might want to use a specific naming convention for your partitions
-- For example: 'partition_20240118'
EXECUTE 'CREATE TABLE IF NOT EXISTS metrics_data_' || to_char(current_partition_date, 'YYYY_MM_DD') || ' PARTITION OF metrics_data FOR VALUES FROM (''' || current_partition_date || ''') TO (''' || current_partition_date + 1 || ''')';

-- Increment the current date for the next iteration
current_partition_date := current_partition_date + 1;
END LOOP;
END;

 

Query:

SELECT public.create_partitioned_table_2d(
'2024-01-01',
'2024-12-31'
)

 

Note: create create_partitioned_table_2d is the name of the function and the dates specified below are the start date and end date respectively(creates partition for a year).