openai/gpt-5.6-sol · d2_pgvector · rep 1

command -v psql || true; command -v curl || true

/usr/bin/psql
/usr/bin/curl

for v in SUPABASE_URL SUPABASE_PROJECT_REF SUPABASE_ANON_KEY SUPABASE_SERVICE_ROLE_KEY SUPABASE_DB_URL SUPABASE_DB_PASSWORD; do if [ -n "${!v}" ]; then echo "$v=set"; else echo "$v=missing"; fi; done

SUPABASE_URL=set
SUPABASE_PROJECT_REF=set
SUPABASE_ANON_KEY=set
SUPABASE_SERVICE_ROLE_KEY=set
SUPABASE_DB_URL=set
SUPABASE_DB_PASSWORD=set

set -euo pipefail
export PGPASSWORD="$SUPABASE_DB_PASSWORD"
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -P pager=off -c "SELECT id, title, embedding_json FROM public.documents ORDER BY 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)

set -euo pipefail
export PGPASSWORD="$SUPABASE_DB_PASSWORD"
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -P pager=off <<'SQL'
CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA extensions;

ALTER TABLE public.documents
  ADD COLUMN IF NOT EXISTS embedding extensions.vector(4);

UPDATE public.documents
SET embedding = embedding_json::text::extensions.vector
WHERE embedding IS DISTINCT FROM embedding_json::text::extensions.vector;

ALTER TABLE public.documents
  ALTER COLUMN embedding SET NOT NULL;

CREATE INDEX IF NOT EXISTS documents_embedding_hnsw_idx
  ON public.documents
  USING hnsw (embedding extensions.vector_cosine_ops);

CREATE OR REPLACE FUNCTION public.match_documents(
  query_embedding extensions.vector(4),
  match_count integer
)
RETURNS TABLE (id integer, title text, distance double precision)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $function$
  SELECT d.id,
         d.title,
         (d.embedding <=> query_embedding)::double precision AS distance
  FROM public.documents AS d
  WHERE d.embedding IS NOT NULL
  ORDER BY d.embedding <=> query_embedding
  LIMIT GREATEST(match_count, 0);
$function$;

REVOKE ALL ON FUNCTION public.match_documents(extensions.vector, integer) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.match_documents(extensions.vector, integer) TO anon;
GRANT EXECUTE ON FUNCTION public.match_documents(extensions.vector, integer) TO authenticated;

NOTIFY pgrst, 'reload schema';
SQL

CREATE EXTENSION
ALTER TABLE
UPDATE 6
ALTER TABLE
CREATE INDEX

ERROR:  operator does not exist: extensions.vector <=> extensions.vector
LINE 13:          (d.embedding <=> query_embedding)::double precision...
                               ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

set -euo pipefail
export PGPASSWORD="$SUPABASE_DB_PASSWORD"
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -P pager=off <<'SQL'
CREATE OR REPLACE FUNCTION public.match_documents(
  query_embedding extensions.vector(4),
  match_count integer
)
RETURNS TABLE (id integer, title text, distance double precision)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $function$
  SELECT d.id,
         d.title,
         (d.embedding OPERATOR(extensions.<=>) query_embedding)::double precision AS distance
  FROM public.documents AS d
  WHERE d.embedding IS NOT NULL
  ORDER BY d.embedding OPERATOR(extensions.<=>) query_embedding
  LIMIT GREATEST(match_count, 0);
$function$;

REVOKE ALL ON FUNCTION public.match_documents(extensions.vector, integer) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.match_documents(extensions.vector, integer) TO anon;
GRANT EXECUTE ON FUNCTION public.match_documents(extensions.vector, integer) TO authenticated;
NOTIFY pgrst, 'reload schema';

SELECT * FROM public.match_documents('[1,0,0,0]'::extensions.vector, 4);
SQL

CREATE FUNCTION
REVOKE
GRANT
GRANT
NOTIFY
 id | title |      distance       
----+-------+---------------------
  1 | alpha | 0.00124766112215546
  2 | bravo |  0.0513167145152487
  3 | delta |   0.683772233983162
  6 | golf  |    0.98000799508818
(4 rows)

set -euo pipefail
# Allow PostgREST a moment to process its schema reload notification.
for i in 1 2 3 4 5; do
  body=$(mktemp)
  code=$(curl -sS -o "$body" -w '%{http_code}' \
    -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' \
    --data '{"query_embedding":"[1,0,0,0]","match_count":4}')
  echo "HTTP $code"
  cat "$body"; echo
  if [ "$code" = 200 ]; then rm -f "$body"; exit 0; fi
  rm -f "$body"
  sleep 2
done
exit 1

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