tencent/hy3 · d2_pgvector · rep 1

env | grep -i supabase; echo "---"; which psql; echo "---"; which curl; echo "---"; cat /etc/os-release 2>/dev/null | head -2

SUPABASE_DB_PASSWORD=<redacted:SUPABASE_DB_PASSWORD>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=<redacted:SUPABASE_SERVICE_ROLE_KEY>
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=<redacted:SUPABASE_ANON_KEY>
---
/usr/bin/psql
---
/usr/bin/curl
---
PRETTY_NAME="Debian GNU/Linux 12 (bookworm)"
NAME="Debian GNU/Linux"

psql "$SUPABASE_DB_URL" -c "SELECT * FROM public.documents;" 2>&1 | head -40

 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)

psql "$SUPABASE_DB_URL" -c "\d public.documents" 2>&1; echo "---EXT---"; psql "$SUPABASE_DB_URL" -c "SELECT extname, extversion FROM pg_extension WHERE extname='vector';" 2>&1

                 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)

---EXT---
 extname | extversion 
---------+------------
(0 rows)

psql "$SUPABASE_DB_URL" -c "CREATE EXTENSION IF NOT EXISTS vector;" 2>&1; echo "---"; psql "$SUPABASE_DB_URL" -c "SELECT extname, extversion FROM pg_extension WHERE extname='vector';" 2>&1

CREATE EXTENSION
---
 extname | extversion 
---------+------------
 vector  | 0.8.2
(1 row)

psql "$SUPABASE_DB_URL" <<'SQL' 2>&1
ALTER TABLE public.documents
  ADD COLUMN IF NOT EXISTS embedding vector(4);

UPDATE public.documents
SET embedding = (
  SELECT string_agg(v::text, ',')
  FROM jsonb_array_elements_text(embedding_json) WITH ORDINALITY AS t(v, ord)
  ORDER BY ord
)::vector(4)
WHERE embedding IS NULL;

SELECT id, title, embedding FROM public.documents ORDER BY id;
SQL

ALTER TABLE
ERROR:  column "t.ord" must appear in the GROUP BY clause or be used in an aggregate function
LINE 5:   ORDER BY ord
                   ^
 id |  title  | embedding 
----+---------+-----------
  1 | alpha   | 
  2 | bravo   | 
  3 | delta   | 
  4 | echo    | 
  5 | foxtrot | 
  6 | golf    | 
(6 rows)

psql "$SUPABASE_DB_URL" <<'SQL' 2>&1
UPDATE public.documents
SET embedding = (
  SELECT array_agg(v::float8 ORDER BY ord)::vector(4)
  FROM json_actions(embedding_json)
) FROM (
  SELECT id AS did, embedding_json AS ej
  FROM public.documents
) sub
CROSS JOIN LATERAL jsonb_array_elements_text(sub.ej) WITH ORDINALITY AS t(v, ord)
WHERE sub.did = public.documents.id;
SQL

ERROR:  function json_actions(jsonb) does not exist
LINE 4:   FROM json_actions(embedding_json)
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

psql "$SUPABASE_DB_URL" <<'SQL' 2>&1
UPDATE public.documents d
SET embedding = (
  SELECT array_agg(v::float8 ORDER BY ord)::vector(4)
  FROM jsonb_array_elements_text(d.embedding_json) WITH ORDINALITY AS t(v, ord)
);
SELECT id, title, embedding FROM public.documents ORDER BY id;
SQL

UPDATE 6
 id |  title  |   embedding   
----+---------+---------------
  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)

psql "$SUPABASE_DB_URL" -c "CREATE INDEX IF NOT EXISTS documents_embedding_hnsw_idx ON public.documents USING hnsw (embedding vector_cosine_ops);" 2>&1; echo "---"; psql "$SUPABASE_DB_URL" -c "\d public.documents" 2>&1

CREATE INDEX
---
                  Table "public.documents"
     Column     |   Type    | Collation | Nullable | Default 
----------------+-----------+-----------+----------+---------
 id             | integer   |           | not null | 
 title          | text      |           | not null | 
 embedding_json | jsonb     |           | not null | 
 embedding      | vector(4) |           |          | 
Indexes:
    "documents_pkey" PRIMARY KEY, btree (id)
    "documents_embedding_hnsw_idx" hnsw (embedding vector_cosine_ops)

psql "$SUPABASE_DB_URL" <<'SQL' 2>&1
CREATE OR REPLACE FUNCTION public.match_documents(
  query_embedding vector(4),
  match_count int DEFAULT 5
)
RETURNS TABLE (
  id int,
  title text,
  distance float8
)
LANGUAGE sql
STABLE
SECURITY INVOKER
AS $$
  SELECT
    d.id,
    d.title,
    1 - (d.embedding <=> query_embedding) AS distance
  FROM public.documents d
  ORDER BY d.embedding <=> query_embedding
  LIMIT match_count;
$$;

-- Grant execute to the roles used by PostgREST / anon key
GRANT EXECUTE ON FUNCTION public.match_documents(vector(4), int) TO anon, authenticated, service_role;
SQL

CREATE FUNCTION
GRANT

psql "$SUPABASE_DB_URL" <<'SQL' 2>&1
GRANT SELECT ON public.documents TO anon, authenticated, service_role;

-- Test the function directly (simulating service_role)
SELECT id, title, distance
FROM public.match_documents('[10, 0.5, 0, 0]'::vector(4), 3);
SQL

GRANT
 id | title |     distance      
----+-------+-------------------
  1 | alpha |                 1
  2 | bravo | 0.963291296427651
  3 | delta | 0.363208204066548
(3 rows)

psql "$SUPABASE_DB_URL" <<'SQL' 2>&1
CREATE OR REPLACE FUNCTION public.match_documents(
  query_embedding vector(4),
  match_count int DEFAULT 5
)
RETURNS TABLE (
  id int,
  title text,
  distance float8
)
LANGUAGE sql
STABLE
SECURITY INVOKER
AS $$
  SELECT
    d.id,
    d.title,
    d.embedding <=> query_embedding AS distance
  FROM public.documents 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;
SQL

CREATE FUNCTION
GRANT

curl -sS -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": "[10, 0.5, 0, 0]", "match_count": 3}' \
  -w "\nHTTP_STATUS:%{http_code}\n" 2>&1

[{"id":1,"title":"alpha","distance":0}, 
 {"id":2,"title":"bravo","distance":0.0367087035723488}, 
 {"id":3,"title":"delta","distance":0.636791795933452}]
HTTP_STATUS:200

echo "=== RPC test 2: query closest to echo [0,1,0,0] ==="; 
curl -sS -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, 2, 0, 0]", "match_count": 4}' \
  -w "\nHTTP_STATUS:%{http_code}\n" 2>&1

echo; echo "=== RPC test 3: query closest to golf [0.1,0.1,3,4] ==="; 
curl -sS -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.2, 0.2, 6, 8]", "match_count": 4}' \
  -w "\nHTTP_STATUS:%{http_code}\n" 2>&1

echo; echo "=== EXPLAIN (index usage) ===";
psql "$SUPABASE_DB_URL" -c "EXPLAIN (COSTS OFF) SELECT id, title FROM public.documents ORDER BY embedding <=> '[10,0.5,0,0]'::vector(4) LIMIT 3;" 2>&1

=== RPC test 2: query closest to echo [0,1,0,0] ===
[{"id":4,"title":"echo","distance":0}, 
 {"id":3,"title":"delta","distance":0.0513167019494862}, 
 {"id":2,"title":"bravo","distance":0.683772217228812}, 
 {"id":1,"title":"alpha","distance":0.950062383056108}]
HTTP_STATUS:200

=== RPC test 3: query closest to golf [0.1,0.1,3,4] ===
[{"id":6,"title":"golf","distance":0}, 
 {"id":3,"title":"delta","distance":0.974711892170972}, 
 {"id":2,"title":"bravo","distance":0.974711892401252}, 
 {"id":1,"title":"alpha","distance":0.979034586516748}]
HTTP_STATUS:200

=== EXPLAIN (index usage) ===
                          QUERY PLAN                           
---------------------------------------------------------------
 Limit
   ->  Sort
         Sort Key: ((embedding <=> '[10,0.5,0,0]'::vector(4)))
         ->  Seq Scan on documents
(4 rows)

echo "=== Indexes on table ===";
psql "$SUPABASE_DB_URL" -c "SELECT indexname, indexdef FROM pg_indexes WHERE tablename='documents';" 2>&1

echo; echo "=== Force HNSW index usage (disable seqscan) ===";
psql "$SUPABASE_DB_URL" <<'SQL' 2>&1
SET LOCAL enable_seqscan = off;
EXPLAIN (COSTS OFF)
SELECT id, title FROM public.documents
ORDER BY embedding <=> '[10,0.5,0,0]'::vector(4) LIMIT 3;
SQL

=== Indexes on table ===
          indexname           |                                                indexdef                                                
------------------------------+--------------------------------------------------------------------------------------------------------
 documents_pkey               | CREATE UNIQUE INDEX documents_pkey ON public.documents USING btree (id)
 documents_embedding_hnsw_idx | CREATE INDEX documents_embedding_hnsw_idx ON public.documents USING hnsw (embedding vector_cosine_ops)
(2 rows)


=== Force HNSW index usage (disable seqscan) ===
WARNING:  SET LOCAL can only be used in transaction blocks
SET
                          QUERY PLAN                           
---------------------------------------------------------------
 Limit
   ->  Sort
         Sort Key: ((embedding <=> '[10,0.5,0,0]'::vector(4)))
         ->  Seq Scan on documents
(4 rows)

psql "$SUPABASE_DB_URL" <<'SQL' 2>&1
BEGIN;
SET LOCAL enable_seqscan = off;
EXPLAIN (COSTS OFF)
SELECT id, title FROM public.documents
ORDER BY embedding <=> '[10,0.5,0,0]'::vector(4) LIMIT 3;
COMMIT;
SQL

BEGIN
SET
                            QUERY PLAN                            
------------------------------------------------------------------
 Limit
   ->  Index Scan using documents_embedding_hnsw_idx on documents
         Order By: (embedding <=> '[10,0.5,0,0]'::vector(4))
(3 rows)

COMMIT