\set ECHO none
\pset format unaligned

-- Exercises the ProcessUtility CTAS lineage hook: when a
-- CREATE TABLE AS / CREATE MATERIALIZED VIEW / SELECT INTO inner
-- SELECT projects a provsql column verbatim from a tracked source,
-- the hook populates the new relation's table_info (with the
-- inherited TID/BID kind and BID block-key alignment when the key
-- columns survive the projection) and ancestor set (transitive
-- union of source ancestors), and installs the provenance_guard
-- trigger so subsequent INSERT / UPDATE OF provsql still flip the
-- table to OPAQUE.
--
-- Output is shaped to be OID-stable: we compare ancestor lists
-- against the source relation's OID rather than printing raw OIDs.

CREATE TABLE ci_src_a(x int, y int);
INSERT INTO ci_src_a VALUES (1, 10), (2, 20), (3, 30);
SELECT add_provenance('ci_src_a');

CREATE TABLE ci_src_b(x int, lbl text);
INSERT INTO ci_src_b VALUES (1, 'b1'), (2, 'b2');
SELECT add_provenance('ci_src_b');

CREATE TABLE ci_bid(k int, v int);
INSERT INTO ci_bid VALUES (1, 100), (1, 101), (2, 200);
SELECT repair_key('ci_bid', 'k');

-- ---------------------------------------------------------------
-- (1) Single-source TID CTAS that projects the source's provsql:
--     inherits kind=TID, ancestors={ci_src_a}.
-- ---------------------------------------------------------------
CREATE TABLE ci_t1 AS SELECT x, provsql FROM ci_src_a;
SELECT (get_table_info('ci_t1'::regclass::oid)).kind  AS t1_kind,
       get_ancestors('ci_t1'::regclass::oid)
         = ARRAY['ci_src_a'::regclass::oid]           AS t1_ancestors_correct;

-- ---------------------------------------------------------------
-- (2) Multi-source TID CTAS (join over two disjoint TID sources)
--     projecting one source's provsql: classifier promotes to TID
--     (every source TID, ancestor sets {ci_src_a} and {ci_src_b}
--     pairwise disjoint).  The CTAS hook fires and records
--     kind=TID, ancestors=union({ci_src_a, ci_src_b}).
-- ---------------------------------------------------------------
CREATE TABLE ci_t2 AS
  SELECT a.x, a.provsql FROM ci_src_a a, ci_src_b b WHERE a.x = b.x;
SELECT (get_table_info('ci_t2'::regclass::oid)).kind  AS t2_kind,
       (SELECT array_agg(o ORDER BY o)
          FROM unnest(get_ancestors('ci_t2'::regclass::oid)) o)
       = (SELECT array_agg(o ORDER BY o)
            FROM unnest(ARRAY['ci_src_a'::regclass::oid,
                              'ci_src_b'::regclass::oid]) o)
                                                       AS t2_ancestors_correct;

-- ---------------------------------------------------------------
-- (3) CTAS without projecting provsql: the rewriting still gives the
--     new relation the provsql column of its rows, so the hook records
--     the same lineage, and a row inserted later gets a token of its
--     own from the guard.
-- ---------------------------------------------------------------
CREATE TABLE ci_t3 AS SELECT x, y FROM ci_src_a;
SELECT (get_table_info('ci_t3'::regclass::oid)).kind  AS t3_kind,
       get_ancestors('ci_t3'::regclass::oid)
         = ARRAY['ci_src_a'::regclass::oid]           AS t3_ancestors_correct;
INSERT INTO ci_t3 VALUES (4, 40);
SET provsql.active = off;
SELECT count(*) FILTER (WHERE provsql IS NULL) AS t3_rows_without_token
  FROM ci_t3;
RESET provsql.active;

-- ---------------------------------------------------------------
-- (4) BID source: target list keeps the block-key column k.
--     Inherits kind=BID with the new attno of k as block_key.
--     Original k is attno 1 in ci_bid; in the new ci_t4 it lands
--     at attno 1 too (we project k first), so block_key = {1}.
-- ---------------------------------------------------------------
CREATE TABLE ci_t4 AS SELECT k, provsql FROM ci_bid;
SELECT (get_table_info('ci_t4'::regclass::oid)).kind       AS t4_kind,
       (get_table_info('ci_t4'::regclass::oid)).block_key  AS t4_block_key,
       get_ancestors('ci_t4'::regclass::oid)
         = ARRAY['ci_bid'::regclass::oid]                  AS t4_ancestors_correct;

-- (5) BID source: target list DROPS the block-key column.  The
--     classifier's BID-projection check (the n_meta == 1 branch
--     resolves outer Vars through subqueries back to base columns)
--     now reports OPAQUE for this case, so the CTAS hook skips
--     and no metadata is recorded for the new relation.  The
--     resulting ci_t5 is untracked -- safer than the previous
--     "demote to TID" behaviour, which advertised independence
--     the rows did not actually have under any preserved column.
-- ---------------------------------------------------------------
CREATE TABLE ci_t5 AS SELECT v, provsql FROM ci_bid;
SELECT get_table_info('ci_t5'::regclass::oid) IS NULL  AS t5_no_metadata,
       get_ancestors('ci_t5'::regclass::oid)  IS NULL  AS t5_no_ancestry;

-- ---------------------------------------------------------------
-- (6) SELECT INTO: PG's parser transforms this to CreateTableAsStmt;
--     the hook fires identically.
-- ---------------------------------------------------------------
SELECT x, provsql INTO ci_t6 FROM ci_src_a;
SELECT (get_table_info('ci_t6'::regclass::oid)).kind  AS t6_kind,
       get_ancestors('ci_t6'::regclass::oid)
         = ARRAY['ci_src_a'::regclass::oid]           AS t6_ancestors_correct;

-- ---------------------------------------------------------------
-- (7) CREATE MATERIALIZED VIEW: same statement node (CreateTableAsStmt
--     with objtype=OBJECT_MATVIEW), hook fires identically.
-- ---------------------------------------------------------------
CREATE MATERIALIZED VIEW ci_mv1 AS SELECT x, provsql FROM ci_src_a;
SELECT (get_table_info('ci_mv1'::regclass::oid)).kind  AS mv_kind,
       get_ancestors('ci_mv1'::regclass::oid)
         = ARRAY['ci_src_a'::regclass::oid]            AS mv_ancestors_correct;
DROP MATERIALIZED VIEW ci_mv1;

-- ---------------------------------------------------------------
-- (7b) A materialized view that does not project provsql: it gets
--     the provenance of its rows as a column, the source's tokens,
--     kept by REFRESH (CONCURRENTLY too), with the same lineage.  An
--     aggregate is stored as its value (it was the token's bits read
--     as a bigint).
-- ---------------------------------------------------------------
CREATE MATERIALIZED VIEW ci_mv2 AS SELECT x, y FROM ci_src_a;
SELECT (get_table_info('ci_mv2'::regclass::oid)).kind  AS mv2_kind,
       get_ancestors('ci_mv2'::regclass::oid)
         = ARRAY['ci_src_a'::regclass::oid]            AS mv2_ancestors_correct;
SET provsql.active = off;
SELECT count(*) AS mv2_rows,
       count(*) FILTER (WHERE m.provsql = s.provsql) AS mv2_source_tokens
  FROM ci_mv2 m JOIN ci_src_a s USING (x);
RESET provsql.active;
INSERT INTO ci_src_a VALUES (4, 40);
REFRESH MATERIALIZED VIEW ci_mv2;
CREATE UNIQUE INDEX ON ci_mv2 (x);
DELETE FROM ci_src_a WHERE x = 4;
REFRESH MATERIALIZED VIEW CONCURRENTLY ci_mv2;
SET provsql.active = off;
SELECT count(*) AS mv2_rows_refreshed,
       count(*) FILTER (WHERE m.provsql = s.provsql) AS mv2_source_tokens
  FROM ci_mv2 m JOIN ci_src_a s USING (x);
RESET provsql.active;
CREATE MATERIALIZED VIEW ci_mv3 AS
  SELECT x, count(*) AS n FROM ci_src_a GROUP BY x;
SET provsql.active = off;
SELECT x, n FROM ci_mv3 ORDER BY x;
RESET provsql.active;
DROP MATERIALIZED VIEW ci_mv2, ci_mv3;

-- (A recursive query cannot fill a materialized view: tested with the
-- recursive queries, in recursive.sql, which PostgreSQL 15+ runs.)

-- ---------------------------------------------------------------
-- (8) CTAS WITH NO DATA: structure-only, inner SELECT not executed.
--     The hook still fires (the new relation has a provsql column
--     by virtue of the TLE shape), so the metadata is recorded and
--     subsequent INSERTs will exercise provenance_guard.
-- ---------------------------------------------------------------
CREATE TABLE ci_t8 AS SELECT x, provsql FROM ci_src_a WITH NO DATA;
SELECT (get_table_info('ci_t8'::regclass::oid)).kind  AS t8_kind,
       get_ancestors('ci_t8'::regclass::oid)
         = ARRAY['ci_src_a'::regclass::oid]           AS t8_ancestors_correct;

-- ---------------------------------------------------------------
-- (9) provenance_guard installation: confirm the trigger exists on
--     a hook-tracked CTAS, and that supplying a user UUID on INSERT
--     flips the table to OPAQUE (the same guard semantics as
--     add_provenance'd tables).
-- ---------------------------------------------------------------
SELECT tgname FROM pg_trigger
 WHERE tgrelid = 'ci_t1'::regclass
   AND NOT tgisinternal
 ORDER BY tgname;
INSERT INTO ci_t1(x, provsql) VALUES (4, public.uuid_generate_v4());
SELECT (get_table_info('ci_t1'::regclass::oid)).kind  AS t1_kind_after_user_insert;

-- ---------------------------------------------------------------
-- (10) DROP TABLE fires cleanup_table_info, ancestry goes with it.
-- ---------------------------------------------------------------
SELECT 'ci_t4'::regclass::oid AS ci_t4_oid \gset
DROP TABLE ci_t4;
SELECT get_table_info(:ci_t4_oid) IS NULL AS t4_cleaned_up,
       get_ancestors(:ci_t4_oid) IS NULL  AS t4_ancestry_cleaned_up;

DROP TABLE ci_t1;
DROP TABLE ci_t2;
DROP TABLE ci_t3;
DROP TABLE ci_t5;
-- A materialized view over an aggregate whose value is no number (array_agg,
-- string_agg) stores the value with the type its column is declared with: no
-- cast takes the agg_token there, and the token's bytes were stored in the
-- integer[] column (reading it crashed the backend).  Read as text, converted.
CREATE TABLE ci_ug(user_id int, group_id int);
INSERT INTO ci_ug VALUES (1,1), (1,2), (1,3), (2,4);
SELECT add_provenance('ci_ug');
CREATE MATERIALIZED VIEW ci_mv3 AS
  SELECT user_id, array_agg(group_id ORDER BY group_id) AS groups,
         string_agg(group_id::text, ',' ORDER BY group_id) AS s
  FROM ci_ug GROUP BY user_id;
SELECT format_type(atttypid, atttypmod) FROM pg_attribute
  WHERE attrelid = 'ci_mv3'::regclass AND attname = 'groups';
-- read without the rewriting: the tokens of the rows differ from run to run
SET provsql.active = off;
SELECT user_id, groups, s FROM ci_mv3 ORDER BY user_id;
SELECT user_id FROM ci_mv3 WHERE groups @> ARRAY[1,2,3];
RESET provsql.active;
REFRESH MATERIALIZED VIEW ci_mv3;
SET provsql.active = off;
SELECT user_id, groups FROM ci_mv3 ORDER BY user_id;
RESET provsql.active;
DROP MATERIALIZED VIEW ci_mv3;
-- The same for an INSERT ... SELECT into a tracked table's integer[] column.
CREATE TABLE ci_ug_into(user_id int, groups int[]);
SELECT add_provenance('ci_ug_into');
INSERT INTO ci_ug_into(user_id, groups)
  SELECT user_id, array_agg(group_id ORDER BY group_id) FROM ci_ug GROUP BY user_id;
SELECT remove_provenance('ci_ug_into');
SELECT user_id, groups FROM ci_ug_into ORDER BY user_id;
DROP TABLE ci_ug_into;
SELECT remove_provenance('ci_ug');
DROP TABLE ci_ug;

DROP TABLE ci_t6;
DROP TABLE ci_t8;
DROP TABLE ci_src_a;
DROP TABLE ci_src_b;
DROP TABLE ci_bid;