Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Can not vacuum index which used jsquery in plpgsql #34

Open
rezonx3m opened this issue Nov 27, 2018 · 0 comments
Open

Can not vacuum index which used jsquery in plpgsql #34

rezonx3m opened this issue Nov 27, 2018 · 0 comments

Comments

@rezonx3m
Copy link

@rezonx3m rezonx3m commented Nov 27, 2018

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant
You can’t perform that action at this time.