-- Per-table options of a sift_table (sift.table_set_option and its companions): the value is checked as the setting of the same name is, the table's option
-- beats the setting whatever its source, and the rows follow the table through RENAME, SET SCHEMA, rewrites and DROP.
CREATE EXTENSION pg_sift;
SET client_min_messages = warning;
CREATE ROLE opt_owner LOGIN;
CREATE ROLE opt_reader LOGIN;
CREATE ROLE opt_other LOGIN;
GRANT ALL ON SCHEMA public TO PUBLIC;
RESET client_min_messages;
SET ROLE opt_owner;
CREATE TABLE ot (id int, v text) USING sift_table;
CREATE TABLE ot2 (id int, v text) USING sift_table;
CREATE TABLE heap_t (id int);
GRANT SELECT ON ot TO opt_reader;
RESET ROLE;
-- nothing is set: every option reads the setting
SELECT count(*) AS options FROM sift.table_options('ot');
 options 
---------
      16
(1 row)

SELECT name, value, source, default_value FROM sift.table_options('ot') WHERE name IN ('write_buffer_limit', 'compression', 'hot_update') ORDER BY name;
        name        | value | source  | default_value 
--------------------+-------+---------+---------------
 compression        |       | default | 
 hot_update         | on    | default | on
 write_buffer_limit | 4MB   | default | 4096kB
(3 rows)

SELECT sift.table_get_option('ot', 'write_buffer_limit');
 table_get_option 
------------------
 4MB
(1 row)

-- every function names its table argument tbl
SELECT sift.table_get_option(tbl => 'ot', name => 'hot_update');
 table_get_option 
------------------
 on
(1 row)

SELECT count(*) AS options_named FROM sift.table_options(tbl => 'ot');
 options_named 
---------------
            16
(1 row)

-- set, read back in the canonical text of the setting, reset
SET ROLE opt_owner;
SELECT sift.table_set_option('ot', 'write_buffer_limit', '2048');
 table_set_option 
------------------
 
(1 row)

SELECT sift.table_get_option('ot', 'write_buffer_limit');
 table_get_option 
------------------
 2MB
(1 row)

SELECT sift.table_set_option('ot', 'compression', 'zstd:3');
 table_set_option 
------------------
 
(1 row)

SELECT sift.table_set_option('ot', 'hot_update', 'off');
 table_set_option 
------------------
 
(1 row)

SELECT sift.table_set_option('ot', 'hot_update', 'false');
 table_set_option 
------------------
 
(1 row)

SELECT name, value, source FROM sift.table_options('ot') WHERE source = 'table' ORDER BY name;
        name        | value  | source 
--------------------+--------+--------
 compression        | zstd:3 | table
 hot_update         | off    | table
 write_buffer_limit | 2MB    | table
(3 rows)

SELECT name, value FROM sift.table_options('ot2') WHERE name = 'write_buffer_limit';
        name        | value 
--------------------+-------
 write_buffer_limit | 4MB
(1 row)

SELECT sift.table_reset_option('ot', 'compression');
 table_reset_option 
--------------------
 
(1 row)

SELECT sift.table_reset_option('ot', 'compression');
 table_reset_option 
--------------------
 
(1 row)

SELECT sift.table_set_option(tbl => 'ot2', name => 'wide_rows', value => 'off');
 table_set_option 
------------------
 
(1 row)

SELECT sift.table_reset_option(tbl => 'ot2', name => 'wide_rows');
 table_reset_option 
--------------------
 
(1 row)

SELECT name, value, source FROM sift.table_options('ot') WHERE source = 'table' ORDER BY name;
        name        | value | source 
--------------------+-------+--------
 hot_update         | off   | table
 write_buffer_limit | 2MB   | table
(2 rows)

-- a value the setting refuses is refused, with the reason of the setting
SELECT sift.table_set_option('ot', 'write_buffer_limit', 'abc');
ERROR:  invalid value for parameter "sift.table_write_buffer_limit": "abc"
CONTEXT:  while checking the option "write_buffer_limit" of a sift_table
SELECT sift.table_set_option('ot', 'write_buffer_limit', '1MB');
ERROR:  1024 kB is outside the valid range for parameter "sift.table_write_buffer_limit" (2048 kB .. 8192 kB)
CONTEXT:  while checking the option "write_buffer_limit" of a sift_table
SELECT sift.table_set_option('ot', 'compression', 'bogus!');
ERROR:  invalid compression option "bogus!": unknown compression: expected auto, none, lz4[:N], zstd[:N] or for, delta, rle, dict, alp joined by '+'
CONTEXT:  while checking the option "compression" of a sift_table
SELECT sift.table_set_option('ot', 'hot_update', 'maybe');
ERROR:  parameter "sift.table_hot_update" requires a Boolean value
CONTEXT:  while checking the option "hot_update" of a sift_table
SELECT sift.table_set_option('ot', 'nope', '1');
ERROR:  "nope" is not a per-table option of a sift_table
HINT:  The options are: write_buffer_limit, wide_rows, inline_promote, async_promote, backpressure, hot_update, bulk_groups, dict_lookup, key_table, compression, dead_rewrite_pct, auto_defrag, auto_defrag_free_pct, auto_defrag_budget, auto_defrag_coalesce, auto_defrag_indexes.
SELECT sift.table_set_option('ot', 'auto_defrag_delay', '1s');
ERROR:  "auto_defrag_delay" is not a per-table option of a sift_table
HINT:  The options are: write_buffer_limit, wide_rows, inline_promote, async_promote, backpressure, hot_update, bulk_groups, dict_lookup, key_table, compression, dead_rewrite_pct, auto_defrag, auto_defrag_free_pct, auto_defrag_budget, auto_defrag_coalesce, auto_defrag_indexes.
SELECT sift.table_set_option('heap_t', 'hot_update', 'on');
ERROR:  "heap_t" is not a sift_table
SELECT sift.table_set_option('ot', 'hot_update', NULL);
ERROR:  the value of an option must not be NULL
HINT:  sift.table_reset_option takes the option off the table.
SELECT sift.table_get_option('ot', 'nope');
ERROR:  "nope" is not a per-table option of a sift_table
HINT:  The options are: write_buffer_limit, wide_rows, inline_promote, async_promote, backpressure, hot_update, bulk_groups, dict_lookup, key_table, compression, dead_rewrite_pct, auto_defrag, auto_defrag_free_pct, auto_defrag_budget, auto_defrag_coalesce, auto_defrag_indexes.
RESET ROLE;
-- who may set and who may read
SET ROLE opt_other;
SELECT sift.table_set_option('ot', 'hot_update', 'on');
ERROR:  must be owner of table ot
SELECT sift.table_reset_option('ot');
ERROR:  must be owner of table ot
SELECT sift.table_get_option('ot', 'hot_update');
ERROR:  permission denied for table ot
SELECT count(*) AS other_sees FROM sift.table_options();
 other_sees 
------------
          0
(1 row)

RESET ROLE;
SET ROLE opt_reader;
SELECT sift.table_get_option('ot', 'hot_update');
 table_get_option 
------------------
 off
(1 row)

SELECT sift.table_set_option('ot', 'hot_update', 'on');
ERROR:  must be owner of table ot
SELECT DISTINCT rel FROM sift.table_options();
 rel 
-----
 ot
(1 row)

RESET ROLE;
-- the owner needs no privilege on the table that holds the rows, and nobody reads it
SET ROLE opt_owner;
SELECT count(*) FROM sift.table_option_values;
ERROR:  permission denied for table table_option_values
RESET ROLE;
-- precedence: the option of the table beats the setting, whatever its source; reset takes the table back to the setting
SET sift.table_write_buffer_limit = '8MB';
SELECT name, value, source FROM sift.table_options('ot2') WHERE name = 'write_buffer_limit';
        name        | value | source  
--------------------+-------+---------
 write_buffer_limit | 8MB   | setting
(1 row)

SELECT sift.table_get_option('ot', 'write_buffer_limit');
 table_get_option 
------------------
 2MB
(1 row)

SELECT sift.table_set_option('ot2', 'write_buffer_limit', '4MB');
 table_set_option 
------------------
 
(1 row)

SELECT sift.table_get_option('ot2', 'write_buffer_limit');
 table_get_option 
------------------
 4MB
(1 row)

SELECT name, value, source FROM sift.table_options('ot2') WHERE name = 'write_buffer_limit';
        name        | value | source 
--------------------+-------+--------
 write_buffer_limit | 4MB   | table
(1 row)

SELECT sift.table_reset_option('ot2', 'write_buffer_limit');
 table_reset_option 
--------------------
 
(1 row)

SELECT sift.table_get_option('ot2', 'write_buffer_limit');
 table_get_option 
------------------
 8MB
(1 row)

RESET sift.table_write_buffer_limit;
-- the option acts: a row larger than the write buffer is refused by the table that turns wide rows off, with the setting on
SELECT sift.table_set_option('ot', 'wide_rows', 'off');
 table_set_option 
------------------
 
(1 row)

INSERT INTO ot VALUES (1, repeat('a', 5000000));
ERROR:  row of sift_table "ot" is too large: 5000024 bytes
DETAIL:  A row has to fit one write buffer of 2048 kB.
HINT:  Set sift.table_wide_rows = on to accept it (up to 1 GB), raise sift.table_write_buffer_limit (up to 8192 kB), or keep the document outside the row.
INSERT INTO ot2 VALUES (1, repeat('a', 5000000));
SELECT sift.table_reset_option('ot', 'wide_rows');
 table_reset_option 
--------------------
 
(1 row)

INSERT INTO ot VALUES (2, repeat('b', 5000000));
SELECT 'ot' AS t, id, length(v) FROM ot UNION ALL SELECT 'ot2', id, length(v) FROM ot2 ORDER BY 1, 2;
  t  | id | length  
-----+----+---------
 ot  |  2 | 5000000
 ot2 |  1 | 5000000
(2 rows)

-- the options go with the table through every rewrite and rename
SELECT sift.table_set_option('ot', 'hot_update', 'off');
 table_set_option 
------------------
 
(1 row)

SELECT sift.table_set_option('ot', 'backpressure', '60');
 table_set_option 
------------------
 
(1 row)

TRUNCATE ot;
VACUUM FULL ot;
CREATE INDEX ot_id ON ot (id);
CLUSTER ot USING ot_id;
REINDEX TABLE ot;
SELECT name, value FROM sift.table_options('ot') WHERE source = 'table' ORDER BY name;
        name        | value 
--------------------+-------
 backpressure       | 60
 hot_update         | off
 write_buffer_limit | 2MB
(3 rows)

ALTER TABLE ot RENAME TO ot_renamed;
CREATE SCHEMA other_schema;
ALTER TABLE ot_renamed SET SCHEMA other_schema;
SELECT rel, name, value FROM sift.table_options('other_schema.ot_renamed') WHERE source = 'table' ORDER BY name;
           rel           |        name        | value 
-------------------------+--------------------+-------
 other_schema.ot_renamed | backpressure       | 60
 other_schema.ot_renamed | hot_update         | off
 other_schema.ot_renamed | write_buffer_limit | 2MB
(3 rows)

SELECT sift.table_get_option('other_schema.ot_renamed', 'hot_update');
 table_get_option 
------------------
 off
(1 row)

-- the heap and the options stay apart: a table moved off the access method keeps no visible options, and comes back with them
ALTER TABLE other_schema.ot_renamed SET ACCESS METHOD heap;
SELECT count(*) AS visible FROM sift.table_options('other_schema.ot_renamed');
 visible 
---------
       0
(1 row)

SELECT sift.table_get_option('other_schema.ot_renamed', 'hot_update');
ERROR:  "ot_renamed" is not a sift_table
ALTER TABLE other_schema.ot_renamed SET ACCESS METHOD sift_table;
SELECT name, value FROM sift.table_options('other_schema.ot_renamed') WHERE source = 'table' ORDER BY name;
        name        | value 
--------------------+-------
 backpressure       | 60
 hot_update         | off
 write_buffer_limit | 2MB
(3 rows)

-- a row written straight to the table of the options (what a restore does) acts like the function, and a table that is not there is skipped
INSERT INTO sift.table_option_values VALUES ('ot2'::regclass, 'hot_update', 'off');
SELECT sift.table_get_option('ot2', 'hot_update');
 table_get_option 
------------------
 off
(1 row)

COPY sift.table_option_values (rel, name, value) FROM stdin;
SELECT count(*) AS rows_for_missing_table FROM sift.table_option_values WHERE rel::oid = 0;
 rows_for_missing_table 
------------------------
                      0
(1 row)

SELECT sift.table_reset_option('ot2');
 table_reset_option 
--------------------
 
(1 row)

-- DROP leaves no row: the table, its schema, an owner's objects
SELECT count(*) AS rows_before FROM sift.table_option_values;
 rows_before 
-------------
           3
(1 row)

DROP TABLE other_schema.ot_renamed;
SELECT count(*) AS rows_after_drop FROM sift.table_option_values;
 rows_after_drop 
-----------------
               0
(1 row)

SELECT sift.table_set_option('ot2', 'hot_update', 'off');
 table_set_option 
------------------
 
(1 row)

CREATE TABLE other_schema.in_schema (id int) USING sift_table;
SELECT sift.table_set_option('other_schema.in_schema', 'hot_update', 'off');
 table_set_option 
------------------
 
(1 row)

DROP SCHEMA other_schema CASCADE;
NOTICE:  drop cascades to table other_schema.in_schema
SELECT rel, name FROM sift.table_option_values ORDER BY 1, 2;
 rel |    name    
-----+------------
 ot2 | hot_update
(1 row)

DROP OWNED BY opt_owner;
SELECT count(*) AS rows_after_drop_owned FROM sift.table_option_values;
 rows_after_drop_owned 
-----------------------
                     0
(1 row)

DROP EXTENSION pg_sift;
DROP ROLE opt_owner;
DROP ROLE opt_reader;
DROP ROLE opt_other;