About Database error

[dpSky]

Translation:

I am running Hindsight using Docker Compose and have started the database container separately.

Images used:

pgvector/pgvector:pg17
ghcr.io/vectorize-io/hindsight:latest

Environment variables:
`HINDSIGHT_API_VECTOR_EXTENSION=pgvector
HINDSIGHT_API_TEXT_SEARCH_EXTENSION=native

PostgreSQL

HINDSIGHT_API_DATABASE_URL=postgresql://u_hindsight:p_hindsight@db:5432/d_hindsight
HINDSIGHT_API_DATABASE_SCHEMA=public
HINDSIGHT_API_MIGRATION_DATABASE_URL=postgresql://u_hindsight:p_hindsight@db:5432/d_hindsight`

However, the database throws the following error:
[7579] ERROR: function schemas_with_pending_work() does not exist at character 15 [7579] HINT: No function matches the given name and argument types. You might need to add explicit type casts. [7579] STATEMENT: SELECT * FROM schemas_with_pending_work()

I have explicitly set HINDSIGHT_API_DATABASE_SCHEMA=public, but why does the API server query schemas_with_pending_work() in the default schema?

Because of this issue, many pending tasks have accumulated and cannot be processed automatically.

Regarding the HINDSIGHT_API_MIGRATION_DATABASE_URL parameter: when executing hindsight-admin run-db-migration, it initializes the default schema instead of public.

I would like help understanding whether this is a configuration problem on my side or an issue in the software, and how I should fix it.

[sueun-dev]

yep, I can reproduce the exact DB log on 0.6.0.

I ran the worker poller against a fresh pgvector/pgvector:pg17 DB with HINDSIGHT_API_DATABASE_SCHEMA=public and just a dummy public.async_operations table. On ghcr.io/vectorize-io/hindsight:0.6.0, _scan_active_schemas() logs:

ERROR:  function schemas_with_pending_work() does not exist
STATEMENT:  SELECT * FROM schemas_with_pending_work()

Same test on 0.6.1 doesn't log that. The poller caches {'schemas_with_pending_work': False} and falls back to the normal per-schema scan.

So this looks like the old worker path, not your public schema setting. Repull/recreate the app container, or pin ghcr.io/vectorize-io/hindsight:0.6.1 instead of a cached latest. You don't need to create that function for a normal single-schema setup, it's just an optional optimization.