-- gucprobe.sql : run in the "regression" database left by make installcheck.  For every
-- column of a user table whose type is a built-in base type, an array of one, or a domain over
-- either, hash json_array(col RETURNING jsonb)::text over all rows, once with the defaults and
-- once per setting below.  A type whose hash changes has a JSON output that depends on that
-- setting.  Run after make installcheck, with a locale that formats money
-- differently from C:
--   psql -X -d regression -v loc=de_DE.utf8 -f gucprobe.sql.txt
\set VERBOSITY terse
\pset tuples_only on
\pset format unaligned
SET TimeZone = 'UTC'; SET DateStyle = 'ISO, MDY'; SET IntervalStyle = postgres;
SET extra_float_digits = 1; SET bytea_output = hex;
SET lc_monetary = 'C'; SET lc_numeric = 'C';
DROP TABLE IF EXISTS probe_res, probe_cols CASCADE;
DROP FUNCTION IF EXISTS probe_gen();
CREATE TABLE probe_res (variant text, typ text, tbl text, col text, h text);
-- The columns, resolved once: domain -> base, array -> element, element domain -> base.
CREATE TABLE probe_cols AS
WITH cols AS (
  SELECT c.oid::regclass::text AS tbl, a.attname::text AS col,
         CASE WHEN t.typtype = 'd' THEN t.typbasetype ELSE t.oid END AS b1
    FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
    JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped
    JOIN pg_type t ON t.oid = a.atttypid
   WHERE c.relkind IN ('r', 'm') AND n.nspname !~ '^pg_'
     AND n.nspname <> 'information_schema'
     AND c.relname NOT IN ('probe_res', 'probe_cols')),
el AS (
  SELECT cols.*, CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN t.typelem ELSE b1 END AS e1
    FROM cols JOIN pg_type t ON t.oid = cols.b1),
base AS (
  SELECT el.*, CASE WHEN t.typtype = 'd' THEN t.typbasetype ELSE t.oid END AS e2
    FROM el JOIN pg_type t ON t.oid = el.e1)
SELECT base.tbl, base.col, pg_catalog.format_type(base.e2, NULL) AS typ
  FROM base JOIN pg_type t ON t.oid = base.e2
 WHERE base.e2 < 16384 AND t.typtype = 'b';
-- One INSERT per column, for the settings now in effect; probe.variant names them.
CREATE FUNCTION probe_gen() RETURNS SETOF text LANGUAGE sql AS $f$
  SELECT format('INSERT INTO probe_res SELECT %L, %L, %L, %L, '
                'md5(coalesce(string_agg(json_array(%I RETURNING jsonb)::text, '
                '%L ORDER BY ctid), %L)) FROM %s',
                current_setting('probe.variant'), typ, tbl, col, col, E'\n', '', tbl)
    FROM probe_cols ORDER BY tbl, col
$f$;
SELECT set_config('probe.variant', 'base', false);
SELECT probe_gen() \gexec
SET extra_float_digits = 0;
SELECT set_config('probe.variant', 'extra_float_digits=0', false);
SELECT probe_gen() \gexec
SET extra_float_digits = -15;
SELECT set_config('probe.variant', 'extra_float_digits=-15', false);
SELECT probe_gen() \gexec
SET extra_float_digits = 3;
SELECT set_config('probe.variant', 'extra_float_digits=3', false);
SELECT probe_gen() \gexec
SET extra_float_digits = 1;
SET bytea_output = escape;
SELECT set_config('probe.variant', 'bytea_output=escape', false);
SELECT probe_gen() \gexec
SET bytea_output = hex;
SET DateStyle = 'SQL, DMY';
SELECT set_config('probe.variant', 'DateStyle=SQL,DMY', false);
SELECT probe_gen() \gexec
SET DateStyle = 'Postgres, MDY';
SELECT set_config('probe.variant', 'DateStyle=Postgres', false);
SELECT probe_gen() \gexec
SET DateStyle = 'German';
SELECT set_config('probe.variant', 'DateStyle=German', false);
SELECT probe_gen() \gexec
SET DateStyle = 'ISO, MDY';
SET IntervalStyle = postgres_verbose;
SELECT set_config('probe.variant', 'IntervalStyle=postgres_verbose', false);
SELECT probe_gen() \gexec
SET IntervalStyle = sql_standard;
SELECT set_config('probe.variant', 'IntervalStyle=sql_standard', false);
SELECT probe_gen() \gexec
SET IntervalStyle = iso_8601;
SELECT set_config('probe.variant', 'IntervalStyle=iso_8601', false);
SELECT probe_gen() \gexec
SET IntervalStyle = postgres;
SET TimeZone = 'Asia/Kathmandu';
SELECT set_config('probe.variant', 'TimeZone=Asia/Kathmandu', false);
SELECT probe_gen() \gexec
SET TimeZone = 'America/Santiago';
SELECT set_config('probe.variant', 'TimeZone=America/Santiago', false);
SELECT probe_gen() \gexec
SET TimeZone = 'UTC';
SET lc_monetary = :'loc';
SELECT set_config('probe.variant', 'lc_monetary=' || :'loc' || '', false);
SELECT probe_gen() \gexec
SET lc_monetary = 'C';
SET lc_numeric = :'loc';
SELECT set_config('probe.variant', 'lc_numeric=' || :'loc' || '', false);
SELECT probe_gen() \gexec
SET lc_numeric = 'C';
-- Types covered, with the number of columns and the output function's volatility.
SELECT 'COVER ' || r.typ || ' cols=' || count(*) || ' out=' || p.proname::text || ':' || p.provolatile::text
  FROM probe_res r JOIN pg_type t ON t.oid = r.typ::regtype JOIN pg_proc p ON p.oid = t.typoutput
 WHERE r.variant = 'base' GROUP BY r.typ, p.proname, p.provolatile ORDER BY 1;
-- Per type: the settings under which its JSON changed, on how many columns.
SELECT 'CHANGES ' || r.typ || ' out=' || p.proname::text || ':' || p.provolatile::text || ' | ' ||
       string_agg(r.variant || ' (' || r.n::text || ')', ', ' ORDER BY r.variant)
  FROM (SELECT v.variant, v.typ, count(*) AS n FROM probe_res v JOIN probe_res b
          ON b.variant = 'base' AND b.tbl = v.tbl AND b.col = v.col
         WHERE v.variant <> 'base' AND v.h IS DISTINCT FROM b.h GROUP BY 1, 2) r
  JOIN pg_type t ON t.oid = r.typ::regtype JOIN pg_proc p ON p.oid = t.typoutput
 GROUP BY r.typ, p.proname, p.provolatile ORDER BY 1;
-- The columns behind each change.
SELECT 'COLS ' || v.typ || ' | ' || v.variant || ' | ' || string_agg(v.tbl || '.' || v.col, ' ' ORDER BY v.tbl, v.col)
  FROM probe_res v JOIN probe_res b ON b.variant = 'base' AND b.tbl = v.tbl AND b.col = v.col
 WHERE v.variant <> 'base' AND v.h IS DISTINCT FROM b.h GROUP BY v.typ, v.variant ORDER BY 1;
-- Columns hashed per setting (equal counts: no INSERT failed).
SELECT 'ROWS ' || variant || ' ' || count(*) FROM probe_res GROUP BY variant ORDER BY 1;
SELECT 'PROBECOLS ' || count(*) FROM probe_cols;