Can not vacuum index which used jsquery in plpgsql

[rezonx3m]

create database test; \c test create extension jsquery; create table test(j jsonb); insert into test values ('{"id":1,"test":"test"}');

CREATE OR REPLACE FUNCTION has_test(j jsonb) RETURNS boolean LANGUAGE plpgsql IMMUTABLE AS $$ DECLARE ret boolean; BEGIN SELECT $1 @@ '$.test = test' into ret; return ret; END; $$;

CREATE INDEX test_index_plpgsql -- does't work in vacuum ON test USING btree (has_test(j));

CREATE INDEX test_index_clear -- works fine ON test ((j->>'id')) WHERE j @@ '$.test = test';

vacuumdb -fzv --dbname=test vacuumdb: vacuuming database "test" INFO: vacuuming "public.test" INFO: "test": found 0 removable, 1 nonremovable row versions in 1 pages DETAIL: 0 dead row versions cannot be removed yet. CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s. vacuumdb: vacuuming of database "test" failed: ERROR: operator does not exist: jsonb @@ unknown LINE 1: SELECT $1 @@ '$.test = test' ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT $1 @@ '$.test = test' CONTEXT: PL/pgSQL function public.has_test(jsonb) line 5 at SQL statement

ii postgresql-11 11.0-1.pgdg16.04+2 amd64
Package: postgresql-11 Status: install ok installed Priority: optional Section: database Installed-Size: 43753 Maintainer: Debian PostgreSQL Maintainers [email protected] Architecture: amd64 Version: 11.0-1.pgdg16.04+2 Provides: postgresql-contrib-11 Homepage: http://www.postgresql.org/ Postgresql-Catversion: 201809051

ii postgresql-11-jsquery 1.1.1-1.pgdg16.04+1 amd64
apt-cache show postgresql-11-jsquery Package: postgresql-11-jsquery Source: jsquery Version: 1.1.1-1.pgdg16.04+1 Architecture: amd64 Maintainer: Debian PostgreSQL Maintainers [email protected] Installed-Size: 290 Depends: postgresql-11, libc6 (>= 2.14) Homepage: https://github.com/postgrespro/jsquery Priority: optional Section: database Filename: pool/main/j/jsquery/postgresql-11-jsquery_1.1.1-1.pgdg16.04+1_amd64.deb Size: 127560 SHA256: b6fdd08d91b83ca5ababe56af3c58dc2891a940102f838313d9f2bbcb8647974 SHA1: 88cab2e08e988ceb1811a82b3aec4c9f6838af44 MD5sum: 586f57fe09dd34c397ee17d2e17d2a95

ii postgresql-common 195.pgdg16.04+1 all

for pg9 - no problem

[devig]

The same problem Error in query: ERROR: operator is not unique: jsonb @@ unknown LINE 2: SELECT * FROM Planet WHERE wiki @@ 'features(fuel = "1" AND ... HINT: Could not choose a best candidate operator. You might need to add explicit type casts.

[sokolcati]

Hi! Thank you for reporting the vacuum issue. Regarding the ERROR: operator is not unique (not in plpgsql): Please note that the jsquery module creates its own version of the @@ operator, which is also present in postgresql and other extensions. Therefore, if you are interested in using the @@ operator, you need to clarify which operator you should use, for example:

CREATE INDEX test_index_clear
ON test
((j->>'id'))
WHERE j @@ '$.test = test'::jsquery;

(you need to add "::jsquery" after the expression with the operator)