-- Copyright 2026 Manuel Reyes Bravo
-- SPDX-License-Identifier: Apache-2.0

-- pg_plan_guard 1.1.10 -> 1.1.11
--
-- 1. CREATE EXTENSION refuses PostgreSQL 18 and earlier. Every function here reads plan advice
--    through pg_plan_advice, which arrived in PostgreSQL 19. Until 1.1.10 the extension
--    installed cleanly on 18 and failed at the first capture() ("pg_plan_guard requires
--    pg_plan_advice (PostgreSQL 19+)"); the same refusal now comes at install time, the way
--    pg_recall_guard refuses to install without tsm_system_rows. A fresh install runs this
--    script too, so the check covers it.
--
-- 2. sync_stash() calls pg_stash_advice in the schema that extension was installed in.
--    It called pg_create_advice_stash() and pg_set_stashed_advice() unqualified, through the
--    caller's search_path, and sync_stash() needs a superuser (it sets compute_query_id): a
--    superuser whose path listed a schema another role could write ran that role's overload.
--    `make check-audit` (PG-S2) plants one and checks it is not called, with a control that
--    calls it. Without pg_stash_advice in the database, each baseline's action reads
--    'failed: pg_stash_advice is not installed in this database' (until 1.1.10: the error of
--    the unqualified call), and its query_id is still cached, as before.

\echo Use "ALTER EXTENSION pg_plan_guard UPDATE TO '1.1.11'" to load this file. \quit

DO $$
BEGIN
    IF pg_catalog.current_setting('server_version_num')::int < 190000 THEN
        RAISE EXCEPTION 'pg_plan_guard requires PostgreSQL 19 or later, this is %',
            pg_catalog.current_setting('server_version')
            USING HINT = 'It reads plan advice through pg_plan_advice, new in PostgreSQL 19.';
    END IF;
END
$$;

CREATE OR REPLACE FUNCTION plan_guard.sync_stash(stash_name text)
RETURNS TABLE (name text, query_id bigint, action text)
LANGUAGE plpgsql
AS $$
DECLARE
    r     record;
    v_qid bigint;
    v_nsp text;
BEGIN
    -- pg_stash_advice is relocatable: its functions are called in the schema it lives in,
    -- never through the caller's search_path (1.1.11).
    SELECT n.nspname INTO v_nsp
      FROM pg_catalog.pg_extension e
      JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
     WHERE e.extname = 'pg_stash_advice';

    IF v_nsp IS NOT NULL THEN
        BEGIN
            EXECUTE pg_catalog.format('SELECT %I.pg_create_advice_stash($1)', v_nsp) USING stash_name;
        EXCEPTION WHEN OTHERS THEN
            NULL;  -- already exists, or not creatable here; set_stashed_advice will tell us
        END;
    END IF;

    FOR r IN SELECT b.id, b.name, b.query_sql, b.advice, b.captured_by,
                    coalesce(b.search_path, pg_catalog.current_setting('search_path')) AS search_path
             FROM plan_guard.baselines b ORDER BY b.name
    LOOP
        BEGIN
            v_qid := plan_guard._query_id_under(r.search_path, r.query_sql,
                                                plan_guard._author_or_refuse(r.name, r.captured_by));
        EXCEPTION WHEN OTHERS THEN
            name := r.name; query_id := NULL; action := 'no query_id: ' || SQLERRM;
            RETURN NEXT;
            CONTINUE;
        END;

        IF v_qid IS NULL THEN
            name := r.name; query_id := NULL; action := 'no query_id';
            RETURN NEXT;
            CONTINUE;
        END IF;

        UPDATE plan_guard.baselines SET query_id = v_qid WHERE id = r.id;

        IF v_nsp IS NULL THEN
            action := 'failed: pg_stash_advice is not installed in this database';
        ELSE
            BEGIN
                EXECUTE pg_catalog.format('SELECT %I.pg_set_stashed_advice($1, $2, $3)', v_nsp)
                    USING stash_name, v_qid, r.advice;
                action := 'stashed';
            EXCEPTION WHEN OTHERS THEN
                action := 'failed: ' || SQLERRM;
            END;
        END IF;

        name := r.name; query_id := v_qid;
        RETURN NEXT;
    END LOOP;
END;
$$;