-- test/sql/collation_guard.sql
--
-- pg_tre refuses nondeterministic collations.  Under one (ICU with
-- strength level1: 'a' = 'A' = 'á'), = and LIKE match strings that share
-- no trigrams, so a trigram index would silently miss rows.  Every way of
-- picking the index collation is covered: the column's COLLATE, an
-- explicit CREATE INDEX ... COLLATE, and ALTER COLUMN changing it (which
-- rebuilds the index, so the check runs again).
--
-- Needs ICU; the collation is created here so the test is self-contained.
CREATE EXTENSION IF NOT EXISTS pg_tre;
psql:test/sql/collation_guard.sql:12: NOTICE:  extension "pg_tre" already exists, skipping
CREATE EXTENSION
\set VERBOSITY terse
SET client_min_messages = warning;
SET
DROP TABLE IF EXISTS cg;
DROP TABLE
DROP COLLATION IF EXISTS cg_ci;
DROP COLLATION
CREATE COLLATION cg_ci (provider = icu, locale = 'und-u-ks-level1',
                        deterministic = false);
CREATE COLLATION
-- (1) Column collation: refused.
CREATE TABLE cg (s text COLLATE cg_ci);
CREATE TABLE
INSERT INTO cg VALUES ('Café'), ('cafe');
INSERT 0 2
CREATE INDEX cg_bad ON cg USING tre (s);
psql:test/sql/collation_guard.sql:25: ERROR:  pg_tre does not support nondeterministic collations
-- (2) Index collation overrides the column: COLLATE "C" is accepted ...
CREATE INDEX cg_c ON cg USING tre (s COLLATE "C");
CREATE INDEX
SELECT count(*) AS indexes FROM pg_indexes WHERE tablename = 'cg';
 indexes 
---------
       1
(1 row)

DROP INDEX cg_c;
DROP INDEX
-- ... and an explicit nondeterministic index collation is refused.
DROP TABLE cg;
DROP TABLE
CREATE TABLE cg (s text);
CREATE TABLE
INSERT INTO cg VALUES ('Café'), ('cafe');
INSERT 0 2
CREATE INDEX cg_bad ON cg USING tre (s COLLATE cg_ci);
psql:test/sql/collation_guard.sql:36: ERROR:  pg_tre does not support nondeterministic collations
-- (3) ALTER COLUMN to a nondeterministic collation rebuilds the index, so
--     it is refused too -- and the table is left as it was.
CREATE INDEX cg_ok ON cg USING tre (s);
CREATE INDEX
ALTER TABLE cg ALTER COLUMN s TYPE text COLLATE cg_ci;
psql:test/sql/collation_guard.sql:41: ERROR:  pg_tre does not support nondeterministic collations
SELECT collation_name FROM information_schema.columns
 WHERE table_name = 'cg' AND column_name = 's';
 collation_name 
----------------
 
(1 row)

-- (4) A deterministic ICU collation is fine, and the index still answers.
DROP INDEX cg_ok;
DROP INDEX
ALTER TABLE cg ALTER COLUMN s TYPE text COLLATE "und-x-icu";
ALTER TABLE
CREATE INDEX cg_icu ON cg USING tre (s);
CREATE INDEX
SET enable_seqscan = off;
SET
SELECT s FROM cg WHERE s %~~ tre_pattern('Café', 0) ORDER BY s;
  s   
------
 Café
(1 row)

RESET enable_seqscan;
RESET
DROP TABLE cg;
DROP TABLE
DROP COLLATION cg_ci;
DROP COLLATION