vector type not found in the database - pgvecto_rs.psycopg register_vector about pgvecto.rs

[alfer-edb]

UPDATE:

I found something odd, if I run the query above as postgres user i get:

ann1=# SELECT
            typname AS name, oid, typarray AS array_oid,
            oid::regtype::text AS regtype, typdelim AS delimiter
        FROM pg_type t
        WHERE t.oid = to_regtype('vector')
        ORDER BY t.oid;
  name  |  oid  | array_oid | regtype | delimiter
--------+-------+-----------+---------+-----------
 vector | 24755 |     24793 | vector  | ,

While as ann1 user:

ann1=> SELECT
            typname AS name, oid, typarray AS array_oid,
            oid::regtype::text AS regtype, typdelim AS delimiter
        FROM pg_type t
        WHERE t.oid = to_regtype('vector')
        ORDER BY t.oid;
 name | oid | array_oid | regtype | delimiter
------+-----+-----------+---------+-----------
(0 rows)

However, if I replace the function to_regtype('vector') with oid 24755 I get:

ann1=> SELECT
            typname AS name, oid, typarray AS array_oid,
            oid::regtype::text AS regtype, typdelim AS delimiter
        FROM pg_type t
        WHERE t.oid = 24755
        ORDER BY t.oid;
  name  |  oid  | array_oid |    regtype     | delimiter
--------+-------+-----------+----------------+-----------
 vector | 24755 |     24793 | vectors.vector | ,
(1 row)

For some reasons the function to_regtype is returning null. The user ann1 has been granted all privileges.

[VoVAllen]

Looks like some kind of permission issue. Have you tried grant permission to the user for the vectors schema and change the search path to include the schema?

[cutecutecat]

Can you show me the result of:

SHOW search_path;

And try this:

SET search_path = "$user", public, vectors';

in your session?

[alfer-edb]

Here is the output:

postgres@ip-172-16-0-56:~$ psql -d ann1 -U ann1
psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
Type "help" for help.

ann1=> SHOW search_path;
       search_path
--------------------------
 "$user", public, vectors
(1 row)

ann1=> SET search_path = "$user", public, vectors;
SET
ann1=> SELECT
            typname AS name, oid, typarray AS array_oid,
            oid::regtype::text AS regtype, typdelim AS delimiter
        FROM pg_type t
        WHERE t.oid = to_regtype('vector')
        ORDER BY t.oid;
 name | oid | array_oid | regtype | delimiter
------+-----+-----------+---------+-----------
(0 rows)

ann1=> SELECT
            typname AS name, oid, typarray AS array_oid,
            oid::regtype::text AS regtype, typdelim AS delimiter
        FROM pg_type t
        WHERE t.oid = 24755
        ORDER BY t.oid;
  name  |  oid  | array_oid |    regtype     | delimiter
--------+-------+-----------+----------------+-----------
 vector | 24755 |     24793 | vectors.vector | ,
(1 row)

This is with pg16.2, I'll try with pg15 soon to see if the issue persists.

[alfer-edb]

@VoVAllen I checked and in fact my non-privileged user did not have permissions to use the extension. I just ran GRANT ALL ON SCHEMA vectors to ann1 as postgres user and the issue is solved :)

Closing the issue, thank you everyone.