-- The limits a user can reach say what to do: SQLSTATE, message, detail and hint of each, with the test-only setting sift.test_limits lowering -- the limit so that a few rows reach it (src/core/limits.h). scripts/check-errors.sh proves that no 54000, 0A000 or 55000 lacks a hint or a detail. CREATE EXTENSION pg_sift; CREATE EXTENSION pg_sift_test; CREATE FUNCTION expect(q text) RETURNS text LANGUAGE plpgsql AS $$ DECLARE st text; m text; d text; h text; BEGIN EXECUTE q; RETURN 'no error'; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS st = RETURNED_SQLSTATE, m = MESSAGE_TEXT, d = PG_EXCEPTION_DETAIL, h = PG_EXCEPTION_HINT; RETURN concat_ws(E'\n ', st || ' ' || m, 'detail: ' || nullif(d, ''), 'hint: ' || nullif(h, '')); END $$; CREATE TABLE one (id int, v text); INSERT INTO one VALUES (1, repeat('x', 200)); CREATE TABLE many (id int, v text); INSERT INTO many SELECT i, repeat(chr(96 + i), 60) FROM generate_series(1, 5) i; -- a string over the limit, and the strings of one unit over it SET sift.test_limits = 'string_bytes=100'; SELECT expect('CREATE INDEX ON one USING sift_col (id, v)'); expect -------------------------------------------------------------------------------------- 54000 a value of 200 bytes does not fit a sift_col column (at most 100 bytes) + hint: Leave the column out of the index, or index left(column, N) or a hash of it. (1 row) SELECT expect('CREATE INDEX ON many USING sift_col (id, v)'); expect -------------------------------------------------------------------------------------- 54000 the strings of one unit of a sift_col column exceed 100 bytes + hint: Leave the column out of the index, or index left(column, N) or a hash of it. (1 row) RESET sift.test_limits; -- an index that is not on a heap table CREATE TABLE tbl (id int, v text) USING sift_table; SELECT expect('CREATE INDEX ON tbl USING sift_col (id)'); expect ---------------------------------------------------------------------------------------- 0A000 sift_col indexes require a heap table + hint: Index a heap table, or copy the rows into one with CREATE TABLE ... AS SELECT. (1 row) -- more leaves than an index holds: 128 declared paths CREATE TABLE docs (id int, payload jsonb); SELECT expect(format('CREATE INDEX ON docs USING sift_col (id, payload) WITH (paths = %L)', (SELECT string_agg(format('payload.k%s int4', i), ', ') FROM generate_series(1, 128) i))); expect ----------------------------------------------------------------------------------------------------------------- 54000 a sift_col index holds at most 128 columns + detail: Column "payload" brings the index to 129 columns and paths. + hint: Leave out the columns the queries do not read, or create several indexes: the planner picks among them. (1 row) -- a jsonb path spec over its size SELECT expect(format('CREATE INDEX ON many USING sift_col (id) WITH (paths = %L)', repeat('a int4,', 20000) || 'b int4')); expect ---------------------------------------------------------------------------- 54000 sift shred paths: the spec has more than 65536 bytes + hint: Declare fewer paths, or a wildcard path for the keys of an object. (1 row) -- a row of a sift_table over the write buffer SET sift.table_write_buffer_limit = 2048; SET sift.table_wide_rows = off; SELECT expect($$INSERT INTO tbl VALUES (1, repeat('z', 3000000))$$); expect ------------------------------------------------------------------------------------------------------------------------------------------------------------- 54000 row of sift_table "tbl" is too large: 3000024 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. (1 row) RESET sift.table_wide_rows; RESET sift.table_write_buffer_limit; -- sift.explain_why takes one statement; a score outside a sift scan says where it exists SELECT expect($$SELECT * FROM sift.explain_why('select 1; select 2')$$); expect ------------------------------------------------------------------------------------ 0A000 sift.explain_why takes one SELECT statement or the EXECUTE of a prepared one+ hint: Pass the text of a single SELECT, without a second statement. (1 row) SELECT expect('SELECT sift.score(ctid) FROM many'); expect ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0A000 sift.score() must be computed by a sift scan of the same relation + detail: The score exists only inside the sift scan that matched the row: it is not computed above a join, in a WHERE clause, for another table than the one scanned, or while sift.enable_scan is off.+ hint: Call it on the ctid of a table with a sift index, in the query level that scans the table, with a @@> condition on the indexed column. (1 row) DROP TABLE one, many, tbl, docs; DROP FUNCTION expect(text); DROP EXTENSION pg_sift_test; DROP EXTENSION pg_sift;