Skip to content

Commit dce55f1

Browse files
ildusildus
committed
Add update triggers support
1 parent d4874e3 commit dce55f1

File tree

9 files changed

+382
-8
lines changed

9 files changed

+382
-8
lines changed

‎README.md‎

Lines changed: 24 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,20 +1,40 @@
11
tsvector2
22
==========
33

4-
Extended tsvector type for PostgreSQL 10+. It was implemented to provide better
4+
Extended `tsvector` type for PostgreSQL 10+. It was implemented to provide better
55
compression and to remove 1MB size limitation of original tsvector type.
66

77
It could be used as transparent replacement of original tsvector and supports
8-
all its functions and index types. Functions that contain `tsvector` in their
8+
all its functions, operators and index types. Functions that contain `tsvector` in their
99
names have been changed to `tsvector2`. Full list of these functions specified
1010
below.
1111

12-
tsvector2 specific functions
13-
-----------------------------
12+
Refer to PostgreSQL [documentation](https://www.postgresql.org/docs/current/datatype-textsearch.html)
13+
to get details about `tsvector`. They can be also applied to `tsvector2`.
14+
15+
`tsvector2` specific functions
16+
------------------------------
1417

1518
* `to_tsvector2` (from `text`, `json`, `jsonb` types)
1619
* `array_to_tsvector2`
1720
* `tsvector2_to_array`
1821
* `tsvector2_stat` (should be used instead of `ts_stat`)
1922
* `jsonb_to_tsvector2`
2023
* `json_to_tsvector2`
24+
* `tsvector2_update_trigger`
25+
* `tsvector2_update_trigger_column`
26+
27+
Notice that `jsonb_to_tsvector2` and `json_to_tsvector2` work different on
28+
PostgreSQL 10 and 11. Same applies to `tsvector` functions.
29+
30+
Common functions that could be safely used on both types
31+
--------------------------------------------------------
32+
33+
* `strip`
34+
* `unnest`
35+
* `length`
36+
* `setweight`
37+
* `ts_rank`
38+
* `ts_rank_cd`
39+
* `ts_delete`
40+
* `ts_filter`

‎expected/functions.out‎

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -220,4 +220,68 @@ SELECT ts_filter('base hidden rebel spaceship strike'::tsvector2, '{a}');
220220

221221
SELECT ts_filter('base hidden rebel spaceship strike'::tsvector2, '{a,b,NULL}');
222222
ERROR: weight array may not contain nulls
223+
CREATE TABLE t1(a tsvector2, t text);
224+
CREATE TRIGGER tsvectorupdate
225+
BEFORE UPDATE OR INSERT ON t1
226+
FOR EACH ROW EXECUTE PROCEDURE tsvector2_update_trigger(a, 'pg_catalog.english', t);
227+
SELECT * FROM t1;
228+
a | t
229+
---+---
230+
(0 rows)
231+
232+
INSERT INTO t1 (t) VALUES ('345 qwerty');
233+
SELECT * FROM t1;
234+
a | t
235+
--------------------+------------
236+
'345':1 'qwerti':2 | 345 qwerty
237+
(1 row)
238+
239+
UPDATE t1 SET t = null WHERE t = '345 qwerty';
240+
SELECT * FROM t1;
241+
a | t
242+
---+---
243+
|
244+
(1 row)
245+
246+
INSERT INTO t1 (t) VALUES ('345 qwerty');
247+
SELECT * FROM t1;
248+
a | t
249+
--------------------+------------
250+
|
251+
'345':1 'qwerti':2 | 345 qwerty
252+
(2 rows)
253+
254+
DROP TABLE t1 CASCADE;
255+
CREATE TABLE t2(a tsvector2, t text, c regconfig);
256+
CREATE TRIGGER tsvectorupdate
257+
BEFORE UPDATE OR INSERT ON t2
258+
FOR EACH ROW EXECUTE PROCEDURE tsvector2_update_trigger_column(a, c, t);
259+
SELECT * FROM t2;
260+
a | t | c
261+
---+---+---
262+
(0 rows)
263+
264+
INSERT INTO t2 (t, c) VALUES ('345 qwerty', 'pg_catalog.english');
265+
SELECT * FROM t2;
266+
a | t | c
267+
--------------------+------------+---------
268+
'345':1 'qwerti':2 | 345 qwerty | english
269+
(1 row)
270+
271+
UPDATE t2 SET t = null WHERE t = '345 qwerty';
272+
SELECT * FROM t2;
273+
a | t | c
274+
---+---+---------
275+
| | english
276+
(1 row)
277+
278+
INSERT INTO t2 (t, c) VALUES ('345 qwerty', 'pg_catalog.english');
279+
SELECT * FROM t2;
280+
a | t | c
281+
--------------------+------------+---------
282+
| | english
283+
'345':1 'qwerti':2 | 345 qwerty | english
284+
(2 rows)
285+
286+
DROP TABLE t2 CASCADE;
223287
drop extension tsvector2 cascade;

‎expected/search.out‎

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -347,6 +347,54 @@ SELECT * FROM tsvector2_stat('SELECT a FROM test_tsvector2', 'AB') ORDER BY ndoc
347347
------+------+--------
348348
(0 rows)
349349

350+
-- test finding items in GIN's pending list
351+
create temp table pendtest (ts tsvector2);
352+
create index pendtest_idx on pendtest using gin(ts);
353+
insert into pendtest values (to_tsvector2('Lore ipsam'));
354+
insert into pendtest values (to_tsvector2('Lore ipsum'));
355+
select * from pendtest where 'ipsu:*'::tsquery @@ ts;
356+
ts
357+
--------------------
358+
'ipsum':2 'lore':1
359+
(1 row)
360+
361+
select * from pendtest where 'ipsa:*'::tsquery @@ ts;
362+
ts
363+
--------------------
364+
'ipsam':2 'lore':1
365+
(1 row)
366+
367+
select * from pendtest where 'ips:*'::tsquery @@ ts;
368+
ts
369+
--------------------
370+
'ipsam':2 'lore':1
371+
'ipsum':2 'lore':1
372+
(2 rows)
373+
374+
select * from pendtest where 'ipt:*'::tsquery @@ ts;
375+
ts
376+
----
377+
(0 rows)
378+
379+
select * from pendtest where 'ipi:*'::tsquery @@ ts;
380+
ts
381+
----
382+
(0 rows)
383+
384+
--check OP_PHRASE on index
385+
create temp table phrase_index_test(fts tsvector2);
386+
insert into phrase_index_test values ('A fat cat has just eaten a rat.');
387+
insert into phrase_index_test values (to_tsvector2('english', 'A fat cat has just eaten a rat.'));
388+
create index phrase_index_test_idx on phrase_index_test using gin(fts);
389+
set enable_seqscan = off;
390+
select * from phrase_index_test where fts @@ phraseto_tsquery('english', 'fat cat');
391+
fts
392+
-----------------------------------
393+
'cat':3 'eaten':6 'fat':2 'rat':8
394+
(1 row)
395+
350396
RESET enable_seqscan;
351397
DROP TABLE test_tsvector2 CASCADE;
398+
DROP TABLE pendtest CASCADE;
399+
DROP TABLE phrase_index_test CASCADE;
352400
DROP EXTENSION tsvector2 CASCADE;

‎init.sql‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -331,6 +331,17 @@ CREATE FUNCTION ts_rank(tsvector2, tsquery)
331331
AS 'MODULE_PATHNAME', 'tsvector2_rank_tt'
332332
LANGUAGE C STRICT IMMUTABLE;
333333

334+
-- tsvector2_update_trigger
335+
CREATE FUNCTION tsvector2_update_trigger()
336+
RETURNS trigger
337+
AS 'MODULE_PATHNAME', 'tsvector2_update_trigger_byid'
338+
LANGUAGE C;
339+
340+
CREATE FUNCTION tsvector2_update_trigger_column()
341+
RETURNS trigger
342+
AS 'MODULE_PATHNAME', 'tsvector2_update_trigger_bycolumn'
343+
LANGUAGE C;
344+
334345
-- operator families for various types of indexes
335346
CREATE OPERATOR FAMILY tsvector2_ops USING btree;
336347
CREATE OPERATOR FAMILY tsvector2_ops USING gist;

‎sql/functions.sql‎

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -45,4 +45,34 @@ SELECT ts_filter('base:7A empir:17 evil:15 first:11 galact:16 hidden:6A rebel:1A
4545
SELECT ts_filter('base hidden rebel spaceship strike'::tsvector2, '{a}');
4646
SELECT ts_filter('base hidden rebel spaceship strike'::tsvector2, '{a,b,NULL}');
4747

48+
CREATE TABLE t1(a tsvector2, t text);
49+
50+
CREATE TRIGGER tsvectorupdate
51+
BEFORE UPDATE OR INSERT ON t1
52+
FOR EACH ROW EXECUTE PROCEDURE tsvector2_update_trigger(a, 'pg_catalog.english', t);
53+
54+
SELECT * FROM t1;
55+
INSERT INTO t1 (t) VALUES ('345 qwerty');
56+
SELECT * FROM t1;
57+
UPDATE t1 SET t = null WHERE t = '345 qwerty';
58+
SELECT * FROM t1;
59+
INSERT INTO t1 (t) VALUES ('345 qwerty');
60+
SELECT * FROM t1;
61+
62+
DROP TABLE t1 CASCADE;
63+
64+
CREATE TABLE t2(a tsvector2, t text, c regconfig);
65+
CREATE TRIGGER tsvectorupdate
66+
BEFORE UPDATE OR INSERT ON t2
67+
FOR EACH ROW EXECUTE PROCEDURE tsvector2_update_trigger_column(a, c, t);
68+
69+
SELECT * FROM t2;
70+
INSERT INTO t2 (t, c) VALUES ('345 qwerty', 'pg_catalog.english');
71+
SELECT * FROM t2;
72+
UPDATE t2 SET t = null WHERE t = '345 qwerty';
73+
SELECT * FROM t2;
74+
INSERT INTO t2 (t, c) VALUES ('345 qwerty', 'pg_catalog.english');
75+
SELECT * FROM t2;
76+
77+
DROP TABLE t2 CASCADE;
4878
drop extension tsvector2 cascade;

‎sql/search.sql‎

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -82,6 +82,27 @@ SELECT count(*) FROM test_tsvector2 WHERE a @@ '!no_such_lexeme';
8282
SELECT * FROM tsvector2_stat('SELECT a FROM test_tsvector2') ORDER BY ndoc DESC, nentry DESC, word LIMIT 10;
8383
SELECT * FROM tsvector2_stat('SELECT a FROM test_tsvector2', 'AB') ORDER BY ndoc DESC, nentry DESC, word;
8484

85+
-- test finding items in GIN's pending list
86+
create temp table pendtest (ts tsvector2);
87+
create index pendtest_idx on pendtest using gin(ts);
88+
insert into pendtest values (to_tsvector2('Lore ipsam'));
89+
insert into pendtest values (to_tsvector2('Lore ipsum'));
90+
select * from pendtest where 'ipsu:*'::tsquery @@ ts;
91+
select * from pendtest where 'ipsa:*'::tsquery @@ ts;
92+
select * from pendtest where 'ips:*'::tsquery @@ ts;
93+
select * from pendtest where 'ipt:*'::tsquery @@ ts;
94+
select * from pendtest where 'ipi:*'::tsquery @@ ts;
95+
96+
--check OP_PHRASE on index
97+
create temp table phrase_index_test(fts tsvector2);
98+
insert into phrase_index_test values ('A fat cat has just eaten a rat.');
99+
insert into phrase_index_test values (to_tsvector2('english', 'A fat cat has just eaten a rat.'));
100+
create index phrase_index_test_idx on phrase_index_test using gin(fts);
101+
set enable_seqscan = off;
102+
select * from phrase_index_test where fts @@ phraseto_tsquery('english', 'fat cat');
103+
85104
RESET enable_seqscan;
86105
DROP TABLE test_tsvector2 CASCADE;
106+
DROP TABLE pendtest CASCADE;
107+
DROP TABLE phrase_index_test CASCADE;
87108
DROP EXTENSION tsvector2 CASCADE;

‎src/tsvector2.h‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -161,6 +161,7 @@ extern int tsvector2_getoffset(TSVector2 vec, int idx, WordEntry2 **we);
161161
extern char *tsvector2_addlexeme(TSVector2 tsv, int idx, int *dataoff,
162162
char *lexeme, int lexeme_len, WordEntryPos *pos, int npos);
163163
extern int32 ts2_compare_string(char *a, int lena, char *b, int lenb, bool prefix);
164+
extern TSVector2 make_tsvector2(void *prs1);
164165

165166
/* Returns lexeme and its entry by given index from TSVector2 */
166167
inline static char *
@@ -196,4 +197,5 @@ typedef enum JsonToIndex
196197
#else
197198
#define init_tsvector_parser_compat(x,y) init_tsvector_parser(x,y)
198199
#endif
200+
199201
#endif

‎src/tsvector2_conv.c‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -10,9 +10,9 @@
1010
#include "postgres.h"
1111

1212
#include "tsearch/ts_cache.h"
13-
#include "tsearch/ts_utils.h"
1413
#include "utils/builtins.h"
1514
#include "utils/jsonapi.h"
15+
#include "tsearch/ts_utils.h"
1616

1717
#include "tsvector2.h"
1818

@@ -22,7 +22,6 @@ typedef struct TSVectorBuildState
2222
Oid cfgId;
2323
} TSVectorBuildState;
2424

25-
static TSVector2 make_tsvector2(ParsedText *prs);
2625
static void add_to_tsvector2(void *_state, char *elem_value, int elem_len);
2726

2827
/*
@@ -363,14 +362,15 @@ add_to_tsvector2(void *_state, char *elem_value, int elem_len)
363362
*
364363
* Note: frees prs->words and subsidiary data.
365364
*/
366-
static TSVector2
367-
make_tsvector2(ParsedText *prs)
365+
TSVector2
366+
make_tsvector2(void *prs1)
368367
{
369368
int i,
370369
lenstr = 0,
371370
totallen,
372371
stroff = 0;
373372
TSVector2 in;
373+
ParsedText *prs = (ParsedText *) prs1;
374374

375375
/* Merge duplicate words */
376376
if (prs->curwords > 0)

0 commit comments

Comments
 (0)