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 hastest(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 pkg-postgresql-public@lists.alioth.debian.org 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 team+postgresql@tracker.debian.org 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)

[sokolcati]

Regarding the VACUUM problem

vacuumdb performs SELECT pg_catalog.set_config('search_path', '', false); before executing VACUUM It's OK for an index without a function wrapper. In the case of plpgsql functions, their body is recompiled. We get an error similar to an attempt to call a function with the public scheme missing from the search_path. An example of getting such an error without using jsquery:

CREATE SCHEMA other_schema;
SELECT pg_catalog.set_config('search_path', '"$user", public, other_schema', false);
CREATE TABLE other_schema.other_table(a int);
CREATE TABLE test_table(b int);

CREATE OR REPLACE FUNCTION use_other_table(a int) RETURNS boolean
LANGUAGE plpgsql IMMUTABLE
AS $$
DECLARE
ret boolean;
BEGIN
    SELECT count(*) FROM other_table;
    SELECT $1 > 0 into ret;
    return ret;
END;

$$;

SET search_path TO " ";
SELECT public.use_other_table(15);
-------------
psql:other_test.sql:18: ERROR:  relation "other_table" does not exist

So getting the error described in the issue is a feature of the plpgsql functions, and not a problem with the jsquery module.

How to avoid this error: explicitly specify the schemas for jsquery: ::public.jsquery.

[sokolcati]

I consider the issue resolved and close it. If you have any questions regarding the provided solution to this problem or about other errors, please feel free to open new issues.

[rezonx3m]

thx!