-- The reloption connect of a child sift_col index: 'fk -> [schema.]parent(key)'. CREATE INDEX, REINDEX and ALTER INDEX resolve it: the child index
-- depends on the sift_col index of the parent table that tracks the key, and that index keeps the key journal. The text is the record (pg_dump writes it).
CREATE EXTENSION pg_sift;
CREATE EXTENSION pg_sift_test;
SET max_parallel_workers_per_gather = 0;
CREATE TABLE dim_a (id int PRIMARY KEY, category_id int NOT NULL, label text, pad text) WITH (autovacuum_enabled = false);
INSERT INTO dim_a SELECT i, i % 10, 'l' || i, 'x' FROM generate_series(1, 500) i;
CREATE TABLE events (id int PRIMARY KEY, dim_a_id int NOT NULL, value_num int NOT NULL, label text) WITH (autovacuum_enabled = false);
INSERT INTO events SELECT i, 1 + i % 500, i, 'e' FROM generate_series(1, 2000) i;
CREATE FUNCTION edges(child regclass) RETURNS text LANGUAGE sql AS
$$ SELECT coalesce(string_agg(refobjid::regclass::text, ' ' ORDER BY refobjid::regclass::text), 'none') FROM pg_depend
   WHERE classid = 'pg_class'::regclass AND objid = child AND refclassid = 'pg_class'::regclass AND deptype = 'n' $$;
CREATE FUNCTION journal_of(idx regclass) RETURNS boolean LANGUAGE sql AS $$ SELECT lineage <> 0 FROM sift._test_conn_gen(idx) $$;
-- what is wrong with the text, and with the names
CREATE INDEX bad ON events USING sift_col (id, dim_a_id) WITH (connect = 'dim_a_id dim_a(id)');
ERROR:  invalid connect option "dim_a_id dim_a(id)": expected '->' after the column of this index that holds the parent key
CREATE INDEX bad ON events USING sift_col (id, dim_a_id) WITH (connect = 'dim_a_id -> dim_a(id, x)');
ERROR:  invalid connect option "dim_a_id -> dim_a(id, x)": expected ')' after the key column: the key is one column
CREATE INDEX bad ON events USING sift_col (id, dim_a_id) WITH (connect = 'dim_a_id -> dim_a(id) category_id');
ERROR:  invalid connect option "dim_a_id -> dim_a(id) category_id": unexpected text after the connection
CREATE INDEX bad ON events USING sift_col (id, dim_a_id) WITH (connect = 'dim_a_id -> dim_a(id) carries category_id');
ERROR:  invalid connect option "dim_a_id -> dim_a(id) carries category_id": unexpected text after the connection
-- the parent table has no sift_col index yet
CREATE INDEX bad ON events USING sift_col (id, dim_a_id) WITH (connect = 'dim_a_id -> dim_a(id)');
ERROR:  connect: table "dim_a" has no sift_col index that tracks "id"
HINT:  CREATE INDEX .. ON dim_a USING sift_col (id, ...) with track_changes naming id (the default for a primary key).
CREATE INDEX dim_a_col ON dim_a USING sift_col (id, category_id, label);
NOTICE:  sift_col index "dim_a_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('dim_a_col');
CREATE INDEX bad ON events USING sift_col (id, dim_a_id) WITH (connect = 'value_num -> dim_a(id)');
ERROR:  connect: "value_num" is not a column of this index
HINT:  Name the column of the index that holds the key of the parent table.
CREATE INDEX bad ON events USING sift_col (id, dim_a_id) WITH (connect = 'label -> dim_a(id)');
ERROR:  connect: "label" is not a column of this index
HINT:  Name the column of the index that holds the key of the parent table.
CREATE INDEX bad ON events USING sift_col (id, dim_a_id) WITH (connect = 'dim_a_id -> nowhere(id)');
ERROR:  relation "public.nowhere" does not exist
CREATE INDEX bad ON events USING sift_col (id, dim_a_id) WITH (connect = 'dim_a_id -> dim_a(nokey)');
ERROR:  connect: table "dim_a" has no column "nokey"
CREATE INDEX bad ON events USING sift_col (id, dim_a_id) WITH (connect = 'dim_a_id -> events(id)');
ERROR:  connect: the parent table is the table of the index
HINT:  Name the table that this table joins.
SELECT edges('dim_a_col') AS parent_edges, journal_of('dim_a_col') AS journal;
 parent_edges | journal 
--------------+---------
 none         | f
(1 row)

-- a parent in another schema is named with its schema; an unqualified name is in the schema of the child table
CREATE SCHEMA other;
CREATE TABLE other.dim_o (id int PRIMARY KEY, category_id int NOT NULL);
CREATE INDEX dim_o_col ON other.dim_o USING sift_col (id, category_id);
NOTICE:  sift_col index "dim_o_col": 2 automatic choices (key_columns, track_changes)
DETAIL:  key_columns = category_id; track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('dim_o_col');
CREATE INDEX bad ON events USING sift_col (id, dim_a_id) WITH (connect = 'dim_a_id -> dim_o(id)');
ERROR:  relation "public.dim_o" does not exist
CREATE INDEX events_o ON events USING sift_col (id, dim_a_id) WITH (connect = 'dim_a_id -> other.dim_o(id)');
NOTICE:  sift_col index "events_o": 2 automatic choices (key_columns, track_changes)
DETAIL:  key_columns = dim_a_id; track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('events_o');
SELECT edges('events_o') AS other_edges;
   other_edges   
-----------------
 other.dim_o_col
(1 row)

DROP INDEX events_o;
DROP SCHEMA other CASCADE;
NOTICE:  drop cascades to table other.dim_o
-- the connection: a dependency, the journal, the text in the options
CREATE INDEX events_col ON events USING sift_col (id, dim_a_id, value_num) WITH (connect = 'dim_a_id -> public.dim_a(id)');
NOTICE:  sift_col index "events_col": 2 automatic choices (key_columns, track_changes)
DETAIL:  key_columns = dim_a_id; track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('events_col');
SELECT edges('events_col') AS child_edges, journal_of('dim_a_col') AS journal;
 child_edges | journal 
-------------+---------
 dim_a_col   | t
(1 row)

SELECT o AS option FROM pg_class c, unnest(c.reloptions) o WHERE c.relname = 'events_col' AND o LIKE 'connect=%';
                option                
--------------------------------------
 connect=dim_a_id -> public.dim_a(id)
(1 row)

SELECT regexp_replace(pg_get_indexdef('events_col'::regclass), '^.*WITH', 'WITH') AS definition;
                                                         definition                                                          
-----------------------------------------------------------------------------------------------------------------------------
 WITH (connect='dim_a_id -> public.dim_a(id)', key_columns='auto: dim_a_id', track_changes=id, point_tid_columns='auto: id')
(1 row)

SELECT child, parent, declaration, lineage IS NOT NULL AS journal, records, stamp FROM sift.col_connections;
   child    |  parent   |         declaration          | journal | records | stamp 
------------+-----------+------------------------------+---------+---------+-------
 events_col | dim_a_col | dim_a_id -> public.dim_a(id) | t       |       0 | ok
(1 row)

SELECT lineage IS NULL AS no_journal, stamp FROM sift.col_connection_state('events_col');
 no_journal |   stamp    
------------+------------
 t          | no journal
(1 row)

-- REINDEX, and ALTER INDEX of another option, keep one dependency
REINDEX INDEX events_col;
NOTICE:  sift_col index "events_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('events_col');
ALTER INDEX events_col SET (superunit = 8);
SELECT count(*) AS edges FROM pg_depend WHERE classid = 'pg_class'::regclass AND objid = 'events_col'::regclass AND refobjid = 'dim_a_col'::regclass;
 edges 
-------
     1
(1 row)

-- off removes the connection, the text brings it back, RESET removes it again
ALTER INDEX events_col SET (connect = 'off');
SELECT edges('events_col') AS after_off;
 after_off 
-----------
 none
(1 row)

ALTER INDEX events_col SET (connect = 'dim_a_id -> dim_a(id)');
SELECT edges('events_col') AS after_set;
 after_set 
-----------
 dim_a_col
(1 row)

ALTER INDEX events_col SET (connect = 'dim_a_id -> dim_a(nokey)');
ERROR:  connect: table "dim_a" has no column "nokey"
ALTER INDEX events_col RESET (connect);
SELECT edges('events_col') AS after_reset;
 after_reset 
-------------
 none
(1 row)

ALTER INDEX events_col SET (connect = 'dim_a_id -> dim_a(id)');
-- the parent index and table cannot go under the child index; CASCADE takes the child index with them
DROP INDEX dim_a_col;
ERROR:  cannot drop index dim_a_col because other objects depend on it
DETAIL:  index events_col depends on index dim_a_col
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
DROP TABLE dim_a;
ERROR:  cannot drop table dim_a because other objects depend on it
DETAIL:  index events_col depends on index dim_a_col
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
DROP TABLE dim_a CASCADE;
NOTICE:  drop cascades to index events_col
SELECT count(*) AS child_indexes FROM pg_class WHERE relname = 'events_col';
 child_indexes 
---------------
             0
(1 row)

DROP TABLE events;
DROP FUNCTION edges(regclass);
DROP FUNCTION journal_of(regclass);
DROP EXTENSION pg_sift_test;
DROP EXTENSION pg_sift;