Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Sign up
Appearance settings

Can not vacuum index which used jsquery in plpgsql #34

Closed
@rezonx3m

Description

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 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

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions

        AltStyle によって変換されたページ (->オリジナル) /