POSTGRES Vérifier les séquences d'un schéma

Souvent on a des "inserts" dans une base qui plantent à cause de la contrainte d'unicité d'une table. Plus particulièrement quand on a un id que dépends d'une séquence.

On utilise pour cela la fonction suivante :

CREATE OR REPLACE FUNCTION check_sequences(p_schema text)
RETURNS TABLE(
    table_name text,
    column_name text,
    sequence_name text,
    seq_value bigint,
    max_value bigint,
    status text
)
LANGUAGE plpgsql
AS $$
DECLARE
    rec record;
    dyn_sql text;
    max_val bigint;
    last_val bigint;
BEGIN
    FOR rec IN
        SELECT
            t.relname AS table_name,
            a.attname AS column_name,
            s.relname AS sequence_name,
            n.nspname AS seq_schema
        FROM pg_class t
        JOIN pg_namespace n2 ON n2.oid = t.relnamespace
        JOIN pg_attribute a ON a.attrelid = t.oid
        JOIN pg_attrdef ad ON ad.adrelid = t.oid AND ad.adnum = a.attnum
        JOIN pg_depend d ON d.refobjid = t.oid AND d.refobjsubid = a.attnum
        JOIN pg_class s ON s.oid = d.objid AND s.relkind = 'S'
        JOIN pg_namespace n ON n.oid = s.relnamespace
        WHERE t.relkind = 'r'
          AND n2.nspname = p_schema
    LOOP
        -- Lire last_value de la séquence
        EXECUTE format('SELECT last_value FROM %I.%I', rec.seq_schema, rec.sequence_name)
            INTO last_val;

        -- Lire MAX(colonne) de la table
        dyn_sql := format(
            'SELECT COALESCE(MAX(%I), 0) FROM %I.%I',
            rec.column_name, p_schema, rec.table_name
        );
        EXECUTE dyn_sql INTO max_val;

        -- Assigner aux OUT
        table_name := rec.table_name;
        column_name := rec.column_name;
        sequence_name := rec.sequence_name;
        seq_value := last_val;
        max_value := max_val;
        status := CASE WHEN last_val < max_val THEN 'NOK' ELSE 'OK' END;

        RETURN NEXT;
    END LOOP;
END;
$$;

C'est copilot qui est arrivé à pondre ça après 4 ou 5 essais infructueux. On appelle la fonction avec :

SELECT * FROM check_sequences('monschema');

Le résultat est quelque chose comme :

user_role	id	user_role_id_seq	1	2	NOK
user_list	id	prd_user_list_id_seq	382	382	OK
user_alert	id	user_alert_id_seq	7	7	OK
application	id	prd_application_id_seq	93	93	OK
status_value	id	prd_status_value_id_seq	10	11	NOK

Tous les NOK (not OK) indiquent une séquence pas à jours. On fait les mises à jour:

ALTER SEQUENCE spoc_rec.user_role_id_seq
	RESTART 2;

ALTER SEQUENCE prd_status_value_id_seq
	RESTART 11;

Tout revient dans l'ordre:

user_role	id	user_role_id_seq	2	2	OK
user_spoc	id	prd_user_spoc_id_seq	382	382	OK
user_alert	id	user_alert_id_seq	7	7	OK
application	id	prd_application_id_seq	93	93	OK
status_value	id	prd_status_value_id_seq	11	11	OK

On pourait envisager un script qui fasse les mises à jours automatiquement... à voir.