Release 5.2.5 - potential upgrade issue with PostgreSQL backend

[alk]

Hi,

TL/DR: if you are using PostgreSQL - either wait for 5.2.6, or check that you don't have duplicate records in idata_* tables before the upgrade (scripts bellow).

in 5.2.5 we modified DB schema and added PK on the idata_* tables (which hold history of all collected metrics). It was done to resolve issues with active-active replication. Previously these tables had no PK for performance reasons (in really old verions of the postgres).

However, we got couple of reports, that schema upgrade failed due to duplicates. While it's not realy clear why there are duplicates, if breaks upgrade process. This will be automated in 5.2.6, but if you want to upgrade now, or already mid-process, do the following:

1. Check if you have any duplicates. If there are none - you are good to go, and can safely proceed with the upgrade.

DO $$
DECLARE
    node_record RECORD;
    tbl_name TEXT;
    dup_count INTEGER;
    total_duplicates INTEGER := 0;
BEGIN
    FOR node_record IN SELECT id FROM nodes
    LOOP
        tbl_name := 'idata_' || node_record.id;

        IF EXISTS (
            SELECT 1
            FROM information_schema.tables
            WHERE table_schema = 'public'
            AND table_name = tbl_name
        ) THEN
            RAISE NOTICE 'Processing table %', tbl_name;

            EXECUTE format('
                WITH ranked AS (
                    SELECT ctid,
                           ROW_NUMBER() OVER (PARTITION BY item_id, idata_timestamp ORDER BY ctid) as rn
                    FROM public.%I
                )
                SELECT COUNT(*)
                FROM ranked
                WHERE rn > 1
            ', tbl_name) INTO dup_count;

            IF dup_count > 0 THEN
                RAISE NOTICE 'Table % has % duplicate rows', tbl_name, dup_count;
                total_duplicates := total_duplicates + dup_count;
            END IF;
        END IF;
    END LOOP;

    RAISE NOTICE 'Total duplicate rows found: %', total_duplicates;
END $$;

2. If you have duplicates, you need to remove them before proceeding with the upgrade.

DO $$
DECLARE
    node_record RECORD;
    tbl_name TEXT;
    deleted_count INTEGER;
    total_deleted INTEGER := 0;
BEGIN
    FOR node_record IN SELECT id FROM nodes
    LOOP
        tbl_name := 'idata_' || node_record.id;

        IF EXISTS (
            SELECT 1
            FROM information_schema.tables
            WHERE table_schema = 'public'
            AND table_name = tbl_name
        ) THEN
            RAISE NOTICE 'Processing table %', tbl_name;

            EXECUTE format('
                WITH duplicates AS (
                    SELECT ctid,
                           ROW_NUMBER() OVER (
                               PARTITION BY item_id, idata_timestamp
                               ORDER BY ctid
                           ) as rn
                    FROM public.%I
                )
                DELETE FROM public.%I
                WHERE ctid IN (
                    SELECT ctid
                    FROM duplicates
                    WHERE rn > 1
                )', tbl_name, tbl_name);

            GET DIAGNOSTICS deleted_count = ROW_COUNT;

            IF deleted_count > 0 THEN
                RAISE NOTICE 'Deleted % duplicate rows from table %', deleted_count, tbl_name;
                total_deleted := total_deleted + deleted_count;
            ELSE
                RAISE NOTICE 'Table % has no duplicates', tbl_name;
            END IF;
        END IF;
    END LOOP;

    RAISE NOTICE 'Total duplicate rows deleted: %', total_deleted;
END $$;