moonshotai/kimi-k3 · d2_pgvector · rep 1

env | grep -i supabase | sed 's/\(KEY=.\{20\}\).*/\1.../' ; echo '---'; which psql; psql --version 2>/dev/null; which python3; python3 -c "import psycopg2" 2>&1 | head -1

SUPABASE_DB_PASSWORD=<redacted:SUPABASE_DB_PASSWORD>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=eyJhbGciOiJIUzI1NiIs...
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=eyJhbGciOiJIUzI1NiIs...
---
/usr/bin/psql
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)
/usr/bin/python3
Traceback (most recent call last):

psql "$SUPABASE_DB_URL" -c "\d public.documents" -c "SELECT id, title, embedding_json FROM public.documents ORDER BY id;"

                 Table "public.documents"
     Column     |  Type   | Collation | Nullable | Default 
----------------+---------+-----------+----------+---------
 id             | integer |           | not null | 
 title          | text    |           | not null | 
 embedding_json | jsonb   |           | not null | 
Indexes:
    "documents_pkey" PRIMARY KEY, btree (id)

 id |  title  |  embedding_json  
----+---------+------------------
  1 | alpha   | [10, 0.5, 0, 0]
  2 | bravo   | [0.9, 0.3, 0, 0]
  3 | delta   | [20, 60, 0, 0]
  4 | echo    | [0, 1, 0, 0]
  5 | foxtrot | [-5, 0.1, 0, 0]
  6 | golf    | [0.1, 0.1, 3, 4]
(6 rows)

cat > /tmp/setup.sql <<'EOF'
-- 1) pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- 2) vector(4) column populated from embedding_json
ALTER TABLE public.documents ADD COLUMN IF NOT EXISTS embedding vector(4);
UPDATE public.documents SET embedding = embedding_json::text::vector(4) WHERE embedding IS NULL;

-- 3) HNSW index using cosine distance
CREATE INDEX IF NOT EXISTS documents_embedding_hnsw_idx
  ON public.documents USING hnsw (embedding vector_cosine_ops);

-- 4) match_documents function (security definer so anon can read through it)
CREATE OR REPLACE FUNCTION public.match_documents(query_embedding vector(4), match_count int)
RETURNS TABLE (id int, title text, distance float8)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $$
  SELECT d.id, d.title, (d.embedding <=> query_embedding)::float8 AS distance
  FROM public.documents AS d
  ORDER BY d.embedding <=> query_embedding
  LIMIT match_count;
$$;

GRANT EXECUTE ON FUNCTION public.match_documents(vector(4), int) TO anon, authenticated, service_role;

-- ask PostgREST to reload its schema cache
NOTIFY pgrst, 'reload schema';
EOF
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -f /tmp/setup.sql

CREATE EXTENSION
ALTER TABLE
UPDATE 6
CREATE INDEX

psql:/tmp/setup.sql:24: ERROR:  operator does not exist: public.vector <=> public.vector
LINE 8:   SELECT d.id, d.title, (d.embedding <=> query_embedding)::f...
                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

cat > /tmp/fix.sql <<'EOF'
CREATE OR REPLACE FUNCTION public.match_documents(query_embedding vector(4), match_count int)
RETURNS TABLE (id int, title text, distance float8)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $$
  SELECT d.id, d.title, (d.embedding OPERATOR(vector.<=>) query_embedding)::float8 AS distance
  FROM public.documents AS d
  ORDER BY d.embedding OPERATOR(vector.<=>) query_embedding
  LIMIT match_count;
$$;

GRANT EXECUTE ON FUNCTION public.match_documents(vector(4), int) TO anon, authenticated, service_role;

NOTIFY pgrst, 'reload schema';
EOF
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -f /tmp/fix.sql

psql:/tmp/fix.sql:12: ERROR:  schema "vector" does not exist
LINE 8:   SELECT d.id, d.title, (d.embedding OPERATOR(vector.<=>) qu...
                                             ^

psql "$SUPABASE_DB_URL" -c "SELECT e.extname, n.nspname AS schema FROM pg_extension e JOIN pg_namespace n ON n.oid = e.extnamespace WHERE e.extname='vector';" -c "SELECT oprname, n.nspname FROM pg_operator o JOIN pg_namespace n ON n.oid=o.oprnamespace WHERE oprname='<=>';"

 extname | schema 
---------+--------
 vector  | public
(1 row)

 oprname | nspname 
---------+---------
 <=>     | public
 <=>     | public
 <=>     | public
(3 rows)

cat > /tmp/fix2.sql <<'EOF'
CREATE OR REPLACE FUNCTION public.match_documents(query_embedding vector(4), match_count int)
RETURNS TABLE (id int, title text, distance float8)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $$
  SELECT d.id, d.title, (d.embedding OPERATOR(public.<=>) query_embedding)::float8 AS distance
  FROM public.documents AS d
  ORDER BY d.embedding OPERATOR(public.<=>) query_embedding
  LIMIT match_count;
$$;

GRANT EXECUTE ON FUNCTION public.match_documents(vector(4), int) TO anon, authenticated, service_role;

NOTIFY pgrst, 'reload schema';
EOF
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -f /tmp/fix2.sql

CREATE FUNCTION
GRANT
NOTIFY

psql "$SUPABASE_DB_URL" -c "SELECT id, title, round(distance::numeric, 6) FROM match_documents('[1,0,0,0]'::vector(4), 6);"

 id |  title  |  round   
----+---------+----------
  1 | alpha   | 0.001248
  2 | bravo   | 0.051317
  3 | delta   | 0.683772
  6 | golf    | 0.980008
  4 | echo    | 1.000000
  5 | foxtrot | 1.999800
(6 rows)

sleep 2
curl -s -w "\nHTTP %{http_code}\n" -X POST "$SUPABASE_URL/rest/v1/rpc/match_documents" \
  -H "apikey: $SUPABASE_ANON_KEY" \
  -H "Authorization: Bearer $SUPABASE_ANON_KEY" \
  -H "Content-Type: application/json" \
  -d '{"query_embedding": [1, 0, 0, 0], "match_count": 3}'

[{"id":1,"title":"alpha","distance":0.00124766112215546}, 
 {"id":2,"title":"bravo","distance":0.0513167145152487}, 
 {"id":3,"title":"delta","distance":0.683772233983162}]
HTTP 200

curl -s -X POST "$SUPABASE_URL/rest/v1/rpc/match_documents" \
  -H "apikey: $SUPABASE_ANON_KEY" \
  -H "Authorization: Bearer $SUPABASE_ANON_KEY" \
  -H "Content-Type: application/json" \
  -d '{"query_embedding": [0, 0, 0.6, 0.8], "match_count": 3}'
echo
psql "$SUPABASE_DB_URL" -c "SELECT indexdef FROM pg_indexes WHERE tablename='documents' AND indexname='documents_embedding_hnsw_idx';" -c "SELECT proname, prosecdef AS security_definer FROM pg_proc WHERE proname='match_documents';"

[{"id":6,"title":"golf","distance":0.000399769304185948}, 
 {"id":1,"title":"alpha","distance":1}, 
 {"id":2,"title":"bravo","distance":1}]
                                                indexdef                                                
--------------------------------------------------------------------------------------------------------
 CREATE INDEX documents_embedding_hnsw_idx ON public.documents USING hnsw (embedding vector_cosine_ops)
(1 row)

     proname     | security_definer 
-----------------+------------------
 match_documents | t
(1 row)