cgalo5758
fe19ee415c
- Pin Dockerfile to Go 1.23 to match go.mod - Record README front-door audit findings
57 lines
2.9 KiB
SQL
57 lines
2.9 KiB
SQL
-- SPDX-License-Identifier: AGPL-3.0-only OR LicenseRef-Commercial
|
|
-- SPDX-FileCopyrightText: 2025-2026 Christian Galo
|
|
|
|
-- Pins every row clock the demo snapshot holds so two captures of the same
|
|
-- state are identical (docs/testing.md, "make screens"). Each listed column
|
|
-- is set to 2026-01-01 09:00 UTC plus one second per distinct original
|
|
-- instant, in the column's own order (dense rank): distinct instants stay
|
|
-- distinct, each table keeps the chronology its rows were written in, and
|
|
-- ties stay ties. The two rows one conferral act writes (the incumbent's end
|
|
-- and the successor's typed row) share one effective_at, which the Tier
|
|
-- changes fold relies on; a per-row offset would split them and the sheet
|
|
-- would show every supersession twice. Order inside a tie is the ledger
|
|
-- query's own tiebreak (the transition id), not this script's business.
|
|
-- Runs with session_replication_role = replica so updated_at triggers stay
|
|
-- quiet. A table without a primary key gets the base instant alone.
|
|
DO $$
|
|
DECLARE
|
|
r record;
|
|
pkexpr text;
|
|
rowexpr text;
|
|
BEGIN
|
|
FOR r IN
|
|
SELECT table_schema, table_name, column_name
|
|
FROM information_schema.columns
|
|
WHERE table_schema NOT IN ('pg_catalog', 'information_schema', 'goose')
|
|
AND data_type LIKE 'timestamp%'
|
|
AND column_name IN ('created_at', 'updated_at', 'joined_at', 'effective_at', 'activated_at', 'valid_from', 'last_login_at', 'observed_at')
|
|
LOOP
|
|
-- The primary key as one comparable text value, once unqualified (for
|
|
-- the ranking subquery) and once qualified (for the row it updates).
|
|
SELECT 'concat_ws('','', ' || string_agg(format('%I::text', a.attname), ', ' ORDER BY x.ord) || ')',
|
|
'concat_ws('','', ' || string_agg(format('t.%I::text', a.attname), ', ' ORDER BY x.ord) || ')'
|
|
INTO pkexpr, rowexpr
|
|
FROM pg_index i
|
|
JOIN LATERAL unnest(i.indkey) WITH ORDINALITY AS x(attnum, ord) ON true
|
|
JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = x.attnum
|
|
WHERE i.indrelid = format('%I.%I', r.table_schema, r.table_name)::regclass
|
|
AND i.indisprimary
|
|
GROUP BY i.indexrelid;
|
|
|
|
IF pkexpr IS NULL THEN
|
|
EXECUTE format('UPDATE %I.%I SET %I = %L WHERE %I IS NOT NULL',
|
|
r.table_schema, r.table_name, r.column_name, '2026-01-01 09:00:00+00', r.column_name);
|
|
ELSE
|
|
-- The ranking subquery reads the table as it stood before this
|
|
-- statement, so the offsets follow the column's original order.
|
|
EXECUTE format(
|
|
'UPDATE %I.%I AS t SET %I = %L::timestamptz + ((ranked.rn - 1) * interval ''1 second'') '
|
|
'FROM (SELECT %s AS pk, dense_rank() OVER (ORDER BY %I) AS rn FROM %I.%I WHERE %I IS NOT NULL) AS ranked '
|
|
'WHERE %s = ranked.pk AND t.%I IS NOT NULL',
|
|
r.table_schema, r.table_name, r.column_name, '2026-01-01 09:00:00+00',
|
|
pkexpr, r.column_name, r.table_schema, r.table_name, r.column_name,
|
|
rowexpr, r.column_name);
|
|
END IF;
|
|
END LOOP;
|
|
END $$;
|