PostgresError: operator does not exist: vectors.vector <=> vectors.vector

[djjudas21]

The bug

I've been struggling with facial recognition since I switched from the bundled PostgreSQL to a CloudNativePG deployment by following this guide. Since then every time I try and do something like faces the UI gives me this error:

The problem looks related to #7431 but none of the steps there (mostly the GRANTs) fixed my problem.

This is my CloudNativePG definition. Looks like it is loading appropriate versions of pgvecto.rs, but I really have no idea about that side of things.

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: immich-db
  namespace: immich
spec:
  # At the time of writing, immich is only compatible with pgvecto.rs <0.4. Latest postgres image with that version is 16.5.
  imageName: ghcr.io/tensorchord/cloudnative-pgvecto.rs:16.5-v0.3.0
  # A bug was reported in v0.3.0, which does not allow a replicated setup:
  # https://github.com/immich-app/immich-charts/issues/149#issuecomment-2559012759
  instances: 1

  postgresql:
    shared_preload_libraries:
      - "vectors.so"

  # The "postgres" superuser is needed only for import and it can be deactivated after migration.
  enableSuperuserAccess: true
  # superuserSecret: immich-superuser

  managed:
    roles:
      - name: immich
        superuser: true
        login: true
        passwordSecret:
          name: immich-postgres-user

  bootstrap:
    initdb:
      database: immich
      owner: immich
      secret:
        name: immich-postgres-user
      postInitSQL:
        - CREATE EXTENSION IF NOT EXISTS "vectors";
        - CREATE EXTENSION IF NOT EXISTS "cube" CASCADE;
        - CREATE EXTENSION IF NOT EXISTS "earthdistance" CASCADE;

  storage:
    size: 8Gi
    storageClass: ceph-block

The OS that Immich Server is running on

Kubernetes

Version of Immich Server

v1.25.6

Version of Immich Mobile App

v1.25.6

Platform with the issue

• Server
• Web
• Mobile

Your docker-compose.yml content

-

Your .env content

-

Reproduction steps

...

Relevant log output

[Nest] 17  - 01/28/2025, 10:23:10 PM    WARN [Api:ServerService~fdc4cger] Unknown keys found: {
  "machineLearning": {
    "classification": {
      "enabled": true
    }
  }
}
[Nest] 17  - 01/28/2025, 10:23:11 PM     LOG [Api:EventRepository] Websocket Connect:    NRPnAWE9H41KGuwyAAAB
[Nest] 17  - 01/28/2025, 10:24:40 PM     LOG [Api:EventRepository] Websocket Disconnect: NRPnAWE9H41KGuwyAAAB
[Nest] 17  - 01/28/2025, 10:24:40 PM    WARN [Api:SystemConfigService~d9jwp3ed] Unknown keys found: {
  "machineLearning": {
    "classification": {
      "enabled": true
    }
  }
}
[Nest] 17  - 01/28/2025, 10:24:41 PM    WARN [Api:ServerService~vh39v8bx] Unknown keys found: {
  "machineLearning": {
    "classification": {
      "enabled": true
    }
  }
}
[Nest] 17  - 01/28/2025, 10:24:41 PM     LOG [Api:EventRepository] Websocket Connect:    0PnXqc83xvlIuUaFAAAD
[Nest] 17  - 01/28/2025, 10:24:51 PM    WARN [Api:PersonService~t142c6hs] Unknown keys found: {
  "machineLearning": {
    "classification": {
      "enabled": true
    }
  }
}
Query failed : {
  durationMs: 3.0820090007036924,
  error: PostgresError: operator does not exist: vectors.vector <=> vectors.vector
      at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26)
      at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:474:6)
      at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
      at Socket.emit (node:events:524:28)
      at addChunk (node:internal/streams/readable:561:12)
      at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
      at Readable.push (node:internal/streams/readable:392:5)
      at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
      at TCP.callbackTrampoline (node:internal/async_hooks:130:17) {
    severity_local: 'ERROR',
    severity: 'ERROR',
    code: '42883',
    hint: 'No operator matches the given name and argument types. You might need to add explicit type casts.',
    position: '495',
    file: 'parse_oper.c',
    line: '647',
    routine: 'op_error'
  },
  sql: 'select "person".* from "person" inner join "asset_faces" on "asset_faces"."personId" = "person"."id" inner join "assets" on "asset_faces"."assetId" = "assets"."id" and "assets"."isArchived" = $1 and "assets"."deletedAt" is null where "person"."ownerId" = $2 group by "person"."id" having ("person"."name" != $3 or count("asset_faces"."assetId") >= $4) order by "person"."isHidden" asc, (select "face_search"."embedding" from "face_search" where "face_search"."faceId" = "person"."faceAssetId") <=> (select "face_search"."embedding" from "face_search" where "face_search"."faceId" = $5) limit $6 offset $7',
  params: [
    false,
    'a6319eb8-d7a9-4c76-abb6-8ed094ed0fe1',
    '',
    3,
    '872ca6d1-bbf8-494c-ba05-0cded1048b80',
    501,
    0
  ]
}
[Nest] 17  - 01/28/2025, 10:24:51 PM   ERROR [Api:ErrorInterceptor~t142c6hs] Unknown error: PostgresError: operator does not exist: vectors.vector <=> vectors.vector
PostgresError: operator does not exist: vectors.vector <=> vectors.vector
    at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26)
    at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:474:6)
    at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
    at Socket.emit (node:events:524:28)
    at addChunk (node:internal/streams/readable:561:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
    at Readable.push (node:internal/streams/readable:392:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:130:17)
[Nest] 17  - 01/28/2025, 10:26:50 PM     LOG [Api:EventRepository] Websocket Disconnect: 0PnXqc83xvlIuUaFAAAD
[Nest] 17  - 01/28/2025, 10:26:50 PM    WARN [Api:SystemConfigService~i8in8vbr] Unknown keys found: {
  "machineLearning": {
    "classification": {
      "enabled": true
    }
  }
}
[Nest] 17  - 01/28/2025, 10:26:50 PM    WARN [Api:ServerService~kldl2vxm] Unknown keys found: {
  "machineLearning": {
    "classification": {
      "enabled": true
    }
  }
}
[Nest] 17  - 01/28/2025, 10:26:55 PM    WARN [Api:PersonService~wvhfxc31] Unknown keys found: {
  "machineLearning": {
    "classification": {
      "enabled": true
    }
  }
}
Query failed : {
  durationMs: 2.74082800000906,
  error: PostgresError: operator does not exist: vectors.vector <=> vectors.vector
      at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26)
      at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:474:6)
      at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
      at Socket.emit (node:events:524:28)
      at addChunk (node:internal/streams/readable:561:12)
      at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
      at Readable.push (node:internal/streams/readable:392:5)
      at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
      at TCP.callbackTrampoline (node:internal/async_hooks:130:17) {
    severity_local: 'ERROR',
    severity: 'ERROR',
    code: '42883',
    hint: 'No operator matches the given name and argument types. You might need to add explicit type casts.',
    position: '495',
    file: 'parse_oper.c',
    line: '647',
    routine: 'op_error'
  },
  sql: 'select "person".* from "person" inner join "asset_faces" on "asset_faces"."personId" = "person"."id" inner join "assets" on "asset_faces"."assetId" = "assets"."id" and "assets"."isArchived" = $1 and "assets"."deletedAt" is null where "person"."ownerId" = $2 group by "person"."id" having ("person"."name" != $3 or count("asset_faces"."assetId") >= $4) order by "person"."isHidden" asc, (select "face_search"."embedding" from "face_search" where "face_search"."faceId" = "person"."faceAssetId") <=> (select "face_search"."embedding" from "face_search" where "face_search"."faceId" = $5) limit $6 offset $7',
  params: [
    false,
    'a6319eb8-d7a9-4c76-abb6-8ed094ed0fe1',
    '',
    3,
    '872ca6d1-bbf8-494c-ba05-0cded1048b80',
    501,
    0
  ]
}
[Nest] 17  - 01/28/2025, 10:26:55 PM   ERROR [Api:ErrorInterceptor~wvhfxc31] Unknown error: PostgresError: operator does not exist: vectors.vector <=> vectors.vector
PostgresError: operator does not exist: vectors.vector <=> vectors.vector
    at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26)
    at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:474:6)
    at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
    at Socket.emit (node:events:524:28)
    at addChunk (node:internal/streams/readable:561:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
    at Readable.push (node:internal/streams/readable:392:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:130:17)

Additional information

No response

[Banh-Canh]

#9060 (comment)

Hey, applying this fixed it for me.