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

set -euo pipefail
command -v psql
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -Atc "select current_database(), current_user; select count(*) from public.documents; select id,title,embedding_json from public.documents order by id;"

/usr/bin/psql
postgres|postgres
6
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]

set -euo pipefail
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;
CREATE SCHEMA IF NOT EXISTS extensions;
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_cosine_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 float8)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = pg_catalog, public, extensions
AS $function$
  SELECT d.id,
         d.title,
         (d.embedding <=> query_embedding)::float8 AS distance
  FROM public.documents AS d
  WHERE d.embedding IS NOT NULL
  ORDER BY d.embedding <=> query_embedding, d.id
  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, authenticated, service_role;
COMMIT;

NOTIFY pgrst, 'reload schema';
SQL

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -P pager=off -c "SELECT * FROM public.match_documents('[1,0,0,0]'::extensions.vector(4), 6);"
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -P pager=off -c "SELECT indexname,indexdef FROM pg_indexes WHERE schemaname='public' AND tablename='documents';"

BEGIN
CREATE SCHEMA
CREATE EXTENSION
ALTER TABLE
UPDATE 6
ALTER TABLE
CREATE INDEX
CREATE FUNCTION
REVOKE
GRANT
COMMIT
NOTIFY
 id |  title  |      distance       
----+---------+---------------------
  1 | alpha   | 0.00124766112215546
  2 | bravo   |  0.0513167145152487
  3 | delta   |   0.683772233983162
  6 | golf    |    0.98000799508818
  4 | echo    |                   1
  5 | foxtrot |    1.99980005540512
(6 rows)

              indexname              |                                                   indexdef                                                    
-------------------------------------+---------------------------------------------------------------------------------------------------------------
 documents_pkey                      | CREATE UNIQUE INDEX documents_pkey ON public.documents USING btree (id)
 documents_embedding_hnsw_cosine_idx | CREATE INDEX documents_embedding_hnsw_cosine_idx ON public.documents USING hnsw (embedding vector_cosine_ops)
(2 rows)

NOTICE:  schema "extensions" already exists, skipping

set -euo pipefail
url="${SUPABASE_URL%/}/rest/v1/rpc/match_documents"
body='{"query_embedding":"[1,0,0,0]","match_count":3}'
for i in 1 2 3 4 5; do
  response=$(curl -sS -w '\n%{http_code}' -X POST "$url" \
    -H "apikey: $SUPABASE_ANON_KEY" \
    -H "Authorization: Bearer $SUPABASE_ANON_KEY" \
    -H 'Content-Type: application/json' \
    -d "$body")
  code=${response##*$'\n'}
  data=${response%$'\n'*}
  if [ "$code" = 200 ]; then
    printf 'HTTP %s\n%s\n' "$code" "$data"
    exit 0
  fi
  printf 'Attempt %s: HTTP %s: %s\n' "$i" "$code" "$data" >&2
  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}]