58 Zeilen
1.8 KiB
SQL
58 Zeilen
1.8 KiB
SQL
-- Migration: Add June 2025 partition for license_heartbeats table
|
|
-- This migration adds the missing partition for the current month (June 2025)
|
|
|
|
-- Check if the partition already exists before creating it
|
|
DO $$
|
|
BEGIN
|
|
-- Check if the June 2025 partition exists
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_tables
|
|
WHERE tablename = 'license_heartbeats_2025_06'
|
|
) THEN
|
|
-- Create the June 2025 partition
|
|
EXECUTE 'CREATE TABLE license_heartbeats_2025_06 PARTITION OF license_heartbeats
|
|
FOR VALUES FROM (''2025-06-01'') TO (''2025-07-01'')';
|
|
|
|
RAISE NOTICE 'Created partition license_heartbeats_2025_06';
|
|
ELSE
|
|
RAISE NOTICE 'Partition license_heartbeats_2025_06 already exists';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Also create partitions for the next few months to avoid future issues
|
|
DO $$
|
|
DECLARE
|
|
partition_name text;
|
|
start_date date;
|
|
end_date date;
|
|
i integer;
|
|
BEGIN
|
|
-- Create partitions for the next 6 months
|
|
FOR i IN 0..6 LOOP
|
|
start_date := date_trunc('month', CURRENT_DATE + (i || ' months')::interval);
|
|
end_date := start_date + interval '1 month';
|
|
partition_name := 'license_heartbeats_' || to_char(start_date, 'YYYY_MM');
|
|
|
|
-- Check if partition already exists
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_tables
|
|
WHERE tablename = partition_name
|
|
) THEN
|
|
EXECUTE format('CREATE TABLE %I PARTITION OF license_heartbeats FOR VALUES FROM (%L) TO (%L)',
|
|
partition_name, start_date, end_date);
|
|
|
|
RAISE NOTICE 'Created partition %', partition_name;
|
|
END IF;
|
|
END LOOP;
|
|
END $$;
|
|
|
|
-- Verify the partitions were created
|
|
SELECT
|
|
schemaname,
|
|
tablename,
|
|
tableowner
|
|
FROM pg_tables
|
|
WHERE tablename LIKE 'license_heartbeats_%'
|
|
ORDER BY tablename; |