hsearch 0.4.0

BM25 full-text search for PostgreSQL: a Tantivy-backed `bm25` index
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
use core::ffi::c_char;
use pgrx::datum::{FromDatum, IntoDatum};
use pgrx::guc::{GucContext, GucFlags, GucRegistry, GucSetting};
use pgrx::prelude::*;
use std::ffi::CStr;

mod am;
mod blockstore;
mod options;
mod scoreboard;
mod store;
mod tokenizer;

use tokenizer::NgramConfig;

pgrx::pg_module_magic!();

pub static MAX_MATCHES: GucSetting<i32> = GucSetting::<i32>::new(1000);

pub fn max_matches() -> usize {
    MAX_MATCHES.get().max(1) as usize
}

macro_rules! pg_finfo_v1 {
    ($finfo:ident) => {
        #[unsafe(no_mangle)]
        pub extern "C" fn $finfo() -> &'static pg_sys::Pg_finfo_record {
            const RECORD: pg_sys::Pg_finfo_record = pg_sys::Pg_finfo_record { api_version: 1 };
            &RECORD
        }
    };
}

pg_finfo_v1!(pg_finfo_hsearch_ngram_in);
pg_finfo_v1!(pg_finfo_hsearch_ngram_out);
pg_finfo_v1!(pg_finfo_hsearch_ngram_typmod_in);
pg_finfo_v1!(pg_finfo_hsearch_ngram_typmod_out);
pg_finfo_v1!(pg_finfo_hsearch_ngram_match);
pg_finfo_v1!(pg_finfo_hsearch_score);
pg_finfo_v1!(pg_finfo_hsearch_bm25_handler);

#[pg_guard]
pub extern "C-unwind" fn _PG_init() {
    GucRegistry::define_int_guc(
        c"hsearch.max_matches",
        c"Max BM25 candidate matches collected per &&& scan key.",
        c"Top-K by BM25 score. Lower = faster searches; higher = better recall for filter-style \
          queries. ORDER BY hyper.score(...) + LIMIT autocomplete is unaffected (top-K contains \
          the top-N).",
        &MAX_MATCHES,
        1,
        i32::MAX,
        GucContext::Userset,
        GucFlags::default(),
    );
    unsafe {
        options::init_reloptions();
        scoreboard::install_hooks();
        store::install_cache_callbacks();
    }
}

unsafe fn raw_arg(fcinfo: pg_sys::FunctionCallInfo, n: usize) -> pg_sys::Datum {
    let nargs = (*fcinfo).nargs as usize;
    let args = (*fcinfo).args.as_slice(nargs);
    args[n].value
}

unsafe fn arg_is_null(fcinfo: pg_sys::FunctionCallInfo, n: usize) -> bool {
    let nargs = (*fcinfo).nargs as usize;
    let args = (*fcinfo).args.as_slice(nargs);
    args[n].isnull
}

unsafe fn make_cstring(s: &str) -> pg_sys::Datum {
    let bytes = s.as_bytes();
    let out = pg_sys::palloc(bytes.len() + 1) as *mut c_char;
    std::ptr::copy_nonoverlapping(bytes.as_ptr() as *const c_char, out, bytes.len());
    *out.add(bytes.len()) = 0;
    pg_sys::Datum::from(out as usize)
}

#[pg_guard]
#[unsafe(no_mangle)]
pub unsafe extern "C-unwind" fn hsearch_ngram_in(
    fcinfo: pg_sys::FunctionCallInfo,
) -> pg_sys::Datum {
    let cstr = raw_arg(fcinfo, 0).cast_mut_ptr::<c_char>();
    let s = CStr::from_ptr(cstr).to_string_lossy().into_owned();
    s.into_datum().unwrap()
}

#[pg_guard]
#[unsafe(no_mangle)]
pub unsafe extern "C-unwind" fn hsearch_ngram_out(
    fcinfo: pg_sys::FunctionCallInfo,
) -> pg_sys::Datum {
    let s = String::from_datum(raw_arg(fcinfo, 0), false).unwrap_or_default();
    make_cstring(&s)
}

unsafe fn cstring_array(d: pg_sys::Datum) -> Vec<String> {
    let arr = pg_sys::pg_detoast_datum(d.cast_mut_ptr::<pg_sys::varlena>()) as *mut pg_sys::ArrayType;
    let mut elems: *mut pg_sys::Datum = std::ptr::null_mut();
    let mut nulls: *mut bool = std::ptr::null_mut();
    let mut n: i32 = 0;
    pg_sys::deconstruct_array(
        arr,
        pg_sys::CSTRINGOID,
        -2,
        false,
        b'c' as c_char,
        &mut elems,
        &mut nulls,
        &mut n,
    );
    let slice = std::slice::from_raw_parts(elems, n as usize);
    slice
        .iter()
        .map(|dd| {
            CStr::from_ptr(dd.cast_mut_ptr::<c_char>())
                .to_string_lossy()
                .into_owned()
        })
        .collect()
}

#[pg_guard]
#[unsafe(no_mangle)]
pub unsafe extern "C-unwind" fn hsearch_ngram_typmod_in(
    fcinfo: pg_sys::FunctionCallInfo,
) -> pg_sys::Datum {
    let parts = cstring_array(raw_arg(fcinfo, 0));
    match tokenizer::pack_typmod(&parts) {
        Ok(packed) => {
            if tokenizer::unpack_typmod(packed) != NgramConfig::default() {
                error!("hyper.ngram only supports (2,5,'ascii_folding=true') in this version");
            }
            packed.into_datum().unwrap()
        }
        Err(e) => error!("hyper.ngram type modifier: {e}"),
    }
}

#[pg_guard]
#[unsafe(no_mangle)]
pub unsafe extern "C-unwind" fn hsearch_ngram_typmod_out(
    fcinfo: pg_sys::FunctionCallInfo,
) -> pg_sys::Datum {
    let tm = i32::from_datum(raw_arg(fcinfo, 0), false).unwrap_or(-1);
    let cfg = tokenizer::unpack_typmod(tm);
    let s = format!(
        "({},{},'ascii_folding={}')",
        cfg.min_gram, cfg.max_gram, cfg.ascii_folding
    );
    make_cstring(&s)
}

#[pg_guard]
#[unsafe(no_mangle)]
pub unsafe extern "C-unwind" fn hsearch_ngram_match(
    fcinfo: pg_sys::FunctionCallInfo,
) -> pg_sys::Datum {
    if arg_is_null(fcinfo, 0) || arg_is_null(fcinfo, 1) {
        return false.into_datum().unwrap();
    }
    let hay = String::from_datum(raw_arg(fcinfo, 0), false).unwrap_or_default();
    let needle = String::from_datum(raw_arg(fcinfo, 1), false).unwrap_or_default();
    let matched = tokenizer::ngram_match(&hay, &needle, &NgramConfig::default());
    matched.into_datum().unwrap()
}

#[pg_guard]
#[unsafe(no_mangle)]
pub unsafe extern "C-unwind" fn hsearch_score(fcinfo: pg_sys::FunctionCallInfo) -> pg_sys::Datum {
    if arg_is_null(fcinfo, 0) {
        return 0.0f64.into_datum().unwrap();
    }
    let key = String::from_datum(raw_arg(fcinfo, 0), false).unwrap_or_default();
    let score = scoreboard::lookup(&key).unwrap_or(0.0) as f64;
    score.into_datum().unwrap()
}

extension_sql!(
    r#"
CREATE SCHEMA IF NOT EXISTS hyper;

CREATE TYPE hyper.ngram;

CREATE FUNCTION hyper.ngram_in(cstring) RETURNS hyper.ngram
    AS 'MODULE_PATHNAME', 'hsearch_ngram_in' LANGUAGE c IMMUTABLE STRICT;
CREATE FUNCTION hyper.ngram_out(hyper.ngram) RETURNS cstring
    AS 'MODULE_PATHNAME', 'hsearch_ngram_out' LANGUAGE c IMMUTABLE STRICT;
CREATE FUNCTION hyper.ngram_typmod_in(cstring[]) RETURNS integer
    AS 'MODULE_PATHNAME', 'hsearch_ngram_typmod_in' LANGUAGE c IMMUTABLE STRICT;
CREATE FUNCTION hyper.ngram_typmod_out(integer) RETURNS cstring
    AS 'MODULE_PATHNAME', 'hsearch_ngram_typmod_out' LANGUAGE c IMMUTABLE STRICT;

CREATE TYPE hyper.ngram (
    INPUT = hyper.ngram_in,
    OUTPUT = hyper.ngram_out,
    TYPMOD_IN = hyper.ngram_typmod_in,
    TYPMOD_OUT = hyper.ngram_typmod_out,
    INTERNALLENGTH = VARIABLE,
    STORAGE = extended,
    CATEGORY = 'S'
);

CREATE CAST (text AS hyper.ngram) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (character varying AS hyper.ngram) WITHOUT FUNCTION AS IMPLICIT;

CREATE FUNCTION hyper.ngram_match(text, text) RETURNS boolean
    AS 'MODULE_PATHNAME', 'hsearch_ngram_match' LANGUAGE c IMMUTABLE STRICT PARALLEL SAFE COST 100000;

CREATE OPERATOR public.&&& (
    LEFTARG = text,
    RIGHTARG = text,
    FUNCTION = hyper.ngram_match,
    RESTRICT = contsel,
    JOIN = contjoinsel
);

CREATE FUNCTION hyper.score(text) RETURNS double precision
    AS 'MODULE_PATHNAME', 'hsearch_score' LANGUAGE c STABLE PARALLEL UNSAFE;

CREATE FUNCTION hyper.bm25_handler(internal) RETURNS index_am_handler
    AS 'MODULE_PATHNAME', 'hsearch_bm25_handler' LANGUAGE c;

CREATE ACCESS METHOD bm25 TYPE INDEX HANDLER hyper.bm25_handler;

CREATE OPERATOR FAMILY hyper.bm25_ops USING bm25;
ALTER OPERATOR FAMILY hyper.bm25_ops USING bm25
    ADD OPERATOR 1 public.&&& (text, text);

CREATE OPERATOR CLASS hyper.text_bm25_ops DEFAULT FOR TYPE text
    USING bm25 FAMILY hyper.bm25_ops AS STORAGE text;
CREATE OPERATOR CLASS hyper.varchar_bm25_ops DEFAULT FOR TYPE character varying
    USING bm25 FAMILY hyper.bm25_ops AS STORAGE character varying;
CREATE OPERATOR CLASS hyper.ngram_bm25_ops DEFAULT FOR TYPE hyper.ngram
    USING bm25 FAMILY hyper.bm25_ops AS STORAGE hyper.ngram;

CREATE FUNCTION hyper.reindex_all() RETURNS integer
    LANGUAGE plpgsql AS $$
DECLARE
    r record;
    n integer := 0;
BEGIN
    FOR r IN
        SELECT c.oid::regclass AS idx
        FROM pg_class c
        JOIN pg_am a ON a.oid = c.relam
        WHERE a.amname = 'bm25' AND c.relkind = 'i'
    LOOP
        EXECUTE 'REINDEX INDEX ' || r.idx::text;
        n := n + 1;
    END LOOP;
    RETURN n;
END;
$$;
"#,
    name = "hsearch_sql",
    finalize
);

#[cfg(any(test, feature = "pg_test"))]
#[pgrx::pg_schema]
mod tests {
    use pgrx::prelude::*;

    fn seed_items() {
        Spi::run(
            "CREATE TABLE items (
                _id varchar(24) PRIMARY KEY,
                name text,
                summary text,
                category_path text
             )",
        )
        .unwrap();
        Spi::run(
            "INSERT INTO items (_id, name, summary, category_path) VALUES
                ('000000000000000000000001', 'Kavos aparatas DeLonghi', 'puikus espresso', 'virtuve technika'),
                ('000000000000000000000002', 'Dviratis kalnu', 'lengvas aliuminis', 'sportas dviraciai'),
                ('000000000000000000000003', 'Ąžuolinis stalas', 'masyvus baldas', 'baldai stalai'),
                ('000000000000000000000004', 'Kavinukas turkiškas', 'varinis', 'virtuve indai')",
        )
        .unwrap();
        Spi::run(
            "CREATE INDEX search_idx ON items USING bm25 (
                _id,
                (name::hyper.ngram(2,5,'ascii_folding=true')),
                (summary::hyper.ngram(2,5,'ascii_folding=true')),
                (category_path::hyper.ngram(2,5,'ascii_folding=true'))
             ) WITH (key_field='_id')",
        )
        .unwrap();
        // Tiny test tables make the planner prefer a seq scan; force the bm25 index
        // path (what runs in production on a large table, and what populates scores).
        Spi::run("SET LOCAL enable_seqscan = off").unwrap();
    }

    #[pg_test]
    fn schema_and_am_exist() {
        let n = Spi::get_one::<i64>("SELECT count(*) FROM pg_namespace WHERE nspname='hyper'")
            .unwrap()
            .unwrap();
        assert_eq!(n, 1);
        let am = Spi::get_one::<i64>("SELECT count(*) FROM pg_am WHERE amname='bm25'")
            .unwrap()
            .unwrap();
        assert_eq!(am, 1);
    }

    #[pg_test]
    fn ngram_cast_parses() {
        Spi::run("SELECT 'kava'::hyper.ngram(2,5,'ascii_folding=true')").unwrap();
    }

    #[pg_test]
    fn operator_uses_index_and_matches() {
        seed_items();
        let count = Spi::get_one::<i64>("SELECT count(*) FROM items WHERE name &&& 'kavos'")
            .unwrap()
            .unwrap();
        assert!(count >= 1, "expected kavos matches, got {count}");
        let plan = Spi::get_one::<String>(
            "EXPLAIN (FORMAT TEXT) SELECT _id FROM items WHERE name &&& 'kavos'",
        )
        .unwrap()
        .unwrap_or_default();
        assert!(
            plan.contains("bm25") || plan.to_lowercase().contains("bitmap"),
            "plan did not use the bm25 index: {plan}"
        );
    }

    #[pg_test]
    fn score_orders_results() {
        seed_items();
        let id = Spi::get_one::<String>(
            "SELECT _id FROM items WHERE name &&& 'kavos' ORDER BY hyper.score(_id) DESC LIMIT 1",
        )
        .unwrap()
        .unwrap();
        assert!(id == "000000000000000000000001" || id == "000000000000000000000004");
    }

    #[pg_test]
    fn accent_folding_matches() {
        seed_items();
        let count = Spi::get_one::<i64>("SELECT count(*) FROM items WHERE name &&& 'azuol'")
            .unwrap()
            .unwrap();
        assert_eq!(count, 1, "ascii-folded query should match Ąžuolinis");
    }

    #[pg_test]
    fn autocomplete_shape() {
        seed_items();
        let got = Spi::get_one::<i64>(
            "WITH scored_ids AS (
                 SELECT _id, hyper.score(_id) AS score
                 FROM items
                 WHERE (name &&& 'kav' OR summary &&& 'kav' OR category_path &&& 'kav')
                 ORDER BY score DESC
             )
             SELECT count(*) FROM scored_ids s JOIN items i ON i._id = s._id",
        )
        .unwrap()
        .unwrap();
        assert!(got >= 2, "expected kav* autocomplete hits, got {got}");
    }

    #[pg_test]
    fn admin_shape_conjunction() {
        seed_items();
        let n = Spi::get_one::<i64>(
            "SELECT count(*) FROM (
                 SELECT _id, hyper.score(_id) AS score
                 FROM items
                 WHERE (name &&& 'kavos') AND (summary &&& 'espresso')
                 ORDER BY score DESC LIMIT 10 OFFSET 0
             ) q",
        )
        .unwrap()
        .unwrap();
        assert_eq!(n, 1, "conjunction should match exactly the espresso row");
    }

    #[pg_test]
    fn delete_then_search_excludes_row() {
        seed_items();
        Spi::run("DELETE FROM items WHERE _id='000000000000000000000001'").unwrap();
        let still = Spi::get_one::<bool>(
            "SELECT EXISTS(SELECT 1 FROM items WHERE name &&& 'aparatas'
                           AND _id='000000000000000000000001')",
        )
        .unwrap()
        .unwrap_or(false);
        assert!(!still, "deleted row must not be returned by search");
    }

    #[pg_test]
    fn short_word_two_chars() {
        seed_items();
        let count = Spi::get_one::<i64>("SELECT count(*) FROM items WHERE name &&& 'ka'")
            .unwrap()
            .unwrap();
        assert!(count >= 2, "two-char ngram query should match, got {count}");
    }

    #[pg_test]
    fn search_and_scoring_survive_caught_subxact_error() {
        seed_items();
        Spi::run("DO $$ BEGIN PERFORM 1/0; EXCEPTION WHEN OTHERS THEN NULL; END $$;").unwrap();
        let id = Spi::get_one::<String>(
            "SELECT _id FROM items WHERE name &&& 'kavos' ORDER BY hyper.score(_id) DESC LIMIT 1",
        )
        .unwrap()
        .unwrap();
        assert!(
            id == "000000000000000000000001" || id == "000000000000000000000004",
            "search must keep scoring correctly after a caught error, got {id}"
        );
    }

    #[pg_test(error = "key_field 'nope' does not name a column of the bm25 index")]
    fn key_field_typo_rejected() {
        Spi::run("CREATE TABLE t1 (_id varchar(24) PRIMARY KEY, name text)").unwrap();
        Spi::run(
            "CREATE INDEX ON t1 USING bm25 (_id, (name::hyper.ngram(2,5,'ascii_folding=true')))
             WITH (key_field='nope')",
        )
        .unwrap();
    }

    #[pg_test(error = "bm25 index requires WITH (key_field='<column>')")]
    fn key_field_required() {
        Spi::run("CREATE TABLE t2 (_id varchar(24) PRIMARY KEY, name text)").unwrap();
        Spi::run("CREATE INDEX ON t2 USING bm25 (_id, (name::hyper.ngram(2,5,'ascii_folding=true')))")
            .unwrap();
    }

    #[pg_test(error = "bm25 indexes on unlogged tables are not supported")]
    fn unlogged_rejected() {
        Spi::run("CREATE UNLOGGED TABLE t3 (_id varchar(24) PRIMARY KEY, name text)").unwrap();
        Spi::run(
            "CREATE INDEX ON t3 USING bm25 (_id, (name::hyper.ngram(2,5,'ascii_folding=true')))
             WITH (key_field='_id')",
        )
        .unwrap();
    }

    #[pg_test(error = "hyper.ngram only supports (2,5,'ascii_folding=true') in this version")]
    fn non_default_ngram_config_rejected() {
        Spi::run("SELECT 'x'::hyper.ngram(3,8)").unwrap();
    }

    #[pg_test]
    fn default_ngram_config_accepted() {
        Spi::run("SELECT 'x'::hyper.ngram(2,5)").unwrap();
        Spi::run("SELECT 'x'::hyper.ngram(2,5,'ascii_folding=true')").unwrap();
    }

    #[pg_test]
    fn savepoint_rollback_discards_pending_inserts() {
        seed_items();
        Spi::run(
            "DO $$ BEGIN
                INSERT INTO items (_id, name, summary, category_path)
                VALUES ('00000000000000000000dead', 'Nikon fotoaparatas', 'x', 'y');
                RAISE EXCEPTION 'boom';
            EXCEPTION WHEN OTHERS THEN NULL; END $$;",
        )
        .unwrap();
        let n = Spi::get_one::<i64>("SELECT count(*) FROM items WHERE name &&& 'nikon'")
            .unwrap()
            .unwrap();
        assert_eq!(n, 0, "rolled-back insert must not match");
    }

    #[pg_test]
    fn reindex_all_runs() {
        seed_items();
        let n = Spi::get_one::<i32>("SELECT hyper.reindex_all()").unwrap().unwrap();
        assert!(n >= 1);
        let count = Spi::get_one::<i64>("SELECT count(*) FROM items WHERE name &&& 'kavos'")
            .unwrap()
            .unwrap();
        assert!(count >= 1, "search must work after reindex");
    }

    fn seed_catalog() {
        Spi::run(
            "CREATE TABLE items (
                _id varchar(24) PRIMARY KEY,
                name text,
                summary text,
                category_path text
             )",
        )
        .unwrap();
        Spi::run(
            "INSERT INTO items (_id, name, summary, category_path) VALUES
                ('000000000000000000000001', 'Telefonas Samsung Galaxy', 'islankstomas ekranas', 'elektronika telefonai'),
                ('000000000000000000000002', 'Balionas gimtadieniui', 'helio balionas', 'dekoracijos sventems'),
                ('000000000000000000000003', 'Azuolinis stalas', 'masyvus virtuves stalas', 'baldai stalai'),
                ('000000000000000000000004', 'Nacionalinis kostiumas', 'tradicinis lietuviskas', 'apranga kostiumai'),
                ('000000000000000000000005', 'Stotele lauko suoliukas', 'patogus baldas', 'baldai lauko'),
                ('000000000000000000000006', 'Mikrofonas studijinis', 'kondensatorinis', 'technika garsas'),
                ('000000000000000000000007', 'Dviratis kalnu', 'aliuminis remas', 'sportas dviraciai'),
                ('000000000000000000000008', 'Automobilio padangos', 'vasarines', 'auto padangos'),
                ('000000000000000000000009', 'Sony PlayStation 5 konsole', 'zaidimu konsole', 'elektronika zaidimu konsoles'),
                ('000000000000000000000010', 'Canon EOS R6 fotoaparatas', 'profesionali kamera', 'fototechnika fotoaparatai')",
        )
        .unwrap();
        Spi::run(
            "CREATE INDEX search_idx ON items USING bm25 (
                _id,
                (name::hyper.ngram(2,5,'ascii_folding=true')),
                (summary::hyper.ngram(2,5,'ascii_folding=true')),
                (category_path::hyper.ngram(2,5,'ascii_folding=true'))
             ) WITH (key_field='_id')",
        )
        .unwrap();
    }

    const CANON_WHERE: &str =
        "(name &&& 'canon' OR summary &&& 'canon' OR category_path &&& 'canon')";
    const PLAY_WHERE: &str =
        "(name &&& 'playstation' OR summary &&& 'playstation' OR category_path &&& 'playstation')";

    #[pg_test]
    fn repro_score_populated_default_plan() {
        seed_catalog();
        let max_s = Spi::get_one::<f64>(&format!(
            "SELECT COALESCE(max(hyper.score(_id)),0) FROM items WHERE {CANON_WHERE}"
        ))
        .unwrap()
        .unwrap_or(0.0);
        assert!(
            max_s > 0.0,
            "default plan must populate BM25 scores; got max score {max_s} (0 => index not used, scoreboard empty)"
        );
    }

    #[pg_test]
    fn repro_canon_ranks_first_default_plan() {
        seed_catalog();
        let top = Spi::get_one::<String>(&format!(
            "SELECT _id FROM items WHERE {CANON_WHERE} ORDER BY hyper.score(_id) DESC, _id LIMIT 1"
        ))
        .unwrap()
        .unwrap();
        assert_eq!(
            top, "000000000000000000000010",
            "Canon item must rank #1 for query 'canon' under the default plan"
        );
    }

    #[pg_test]
    fn canon_ranks_first_with_index() {
        seed_catalog();
        Spi::run("SET enable_seqscan = off").unwrap();
        let top = Spi::get_one::<String>(&format!(
            "SELECT _id FROM items WHERE {CANON_WHERE} ORDER BY hyper.score(_id) DESC, _id LIMIT 1"
        ))
        .unwrap()
        .unwrap();
        assert_eq!(top, "000000000000000000000010", "Canon must rank #1 when the index is used");
    }

    #[pg_test]
    fn playstation_ranks_first_with_index() {
        seed_catalog();
        Spi::run("SET enable_seqscan = off").unwrap();
        let top = Spi::get_one::<String>(&format!(
            "SELECT _id FROM items WHERE {PLAY_WHERE} ORDER BY hyper.score(_id) DESC, _id LIMIT 1"
        ))
        .unwrap()
        .unwrap();
        assert_eq!(top, "000000000000000000000009", "PlayStation must rank #1 when the index is used");
    }

    #[pg_test]
    fn repro_match_breadth_canon() {
        seed_catalog();
        Spi::run("SET enable_seqscan = off").unwrap();
        let n = Spi::get_one::<i64>(&format!("SELECT count(*) FROM items WHERE {CANON_WHERE}"))
            .unwrap()
            .unwrap();
        assert_eq!(n, 1, "query 'canon' should match only the Canon row, got {n}");
    }

    #[pg_test]
    fn two_char_query_returns_only_substring_matches() {
        seed_catalog();
        let where_ka =
            "(name &&& 'ka' OR summary &&& 'ka' OR category_path &&& 'ka')";
        let bad = Spi::get_one::<i64>(&format!(
            "SELECT count(*) FROM items WHERE {where_ka}
             AND lower(name||' '||summary||' '||category_path) NOT LIKE '%ka%'"
        ))
        .unwrap()
        .unwrap();
        assert_eq!(bad, 0, "every 'ka' match must actually contain the substring 'ka'");

        let balionas_matches = Spi::get_one::<bool>(&format!(
            "SELECT EXISTS(SELECT 1 FROM items WHERE {where_ka}
             AND _id='000000000000000000000002')"
        ))
        .unwrap()
        .unwrap_or(false);
        assert!(!balionas_matches, "'Balionas …' has no 'ka' and must be excluded");

        let n = Spi::get_one::<i64>(&format!("SELECT count(*) FROM items WHERE {where_ka}"))
            .unwrap()
            .unwrap();
        assert!(n >= 4, "expected the real 'ka' substring rows, got {n}");
    }

    #[pg_test]
    fn ka_matches_exactly_the_substring_rows() {
        seed_catalog();
        let where_ka = "(name &&& 'ka' OR summary &&& 'ka' OR category_path &&& 'ka')";
        let via_op = Spi::get_one::<String>(&format!(
            "SELECT COALESCE(string_agg(_id, ',' ORDER BY _id),'') FROM items WHERE {where_ka}"
        ))
        .unwrap()
        .unwrap();
        let via_like = Spi::get_one::<String>(
            "SELECT COALESCE(string_agg(_id, ',' ORDER BY _id),'') FROM items
             WHERE lower(name||' '||summary||' '||category_path) LIKE '%ka%'",
        )
        .unwrap()
        .unwrap();
        let n = via_op.split(',').filter(|s| !s.is_empty()).count();
        assert_eq!(
            via_op, via_like,
            "&&& 'ka' must match exactly the rows whose text contains 'ka' (got n={n})"
        );
        assert_eq!(n, 7, "expected 7 'ka' rows in the seed catalog, got {n}: {via_op}");
    }
}

#[cfg(test)]
pub mod pg_test {
    pub fn setup(_options: Vec<&str>) {}

    pub fn postgresql_conf_options() -> Vec<&'static str> {
        vec!["shared_preload_libraries = 'hsearch'"]
    }
}