Applying local migration - ERROR: extension "supabase-dbdev" is not available (SQLSTATE 0A000)

[JanKups]

I am unable to apply a local migration.

ERROR: extension "supabase-dbdev" is not available (SQLSTATE 0A000)

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

1. Update DB on supabase.com, install dbdev, install (in my case) supabase_rbac
2. Pull DB on local machine supabase db pull
3. Reset DB supabase db reset
4. See error

Expected behavior

Migration applied, incl extension.

Additional context

I tried to run all the install commands locally first (from here) but this did not help.

Migration file example:

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

CREATE EXTENSION IF NOT EXISTS "pg_tle" WITH SCHEMA "pgtle";

CREATE EXTENSION IF NOT EXISTS "supabase-dbdev" WITH SCHEMA "public";

CREATE EXTENSION IF NOT EXISTS "pgsodium" WITH SCHEMA "pgsodium";

CREATE SCHEMA IF NOT EXISTS "supabase_migrations";

ALTER SCHEMA "supabase_migrations" OWNER TO "postgres";

CREATE EXTENSION IF NOT EXISTS "http" WITH SCHEMA "extensions";

CREATE EXTENSION IF NOT EXISTS "pg_graphql" WITH SCHEMA "graphql";

CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "extensions";

CREATE EXTENSION IF NOT EXISTS "pgcrypto" WITH SCHEMA "extensions";

CREATE EXTENSION IF NOT EXISTS "pgjwt" WITH SCHEMA "extensions";

CREATE EXTENSION IF NOT EXISTS "pointsource-supabase_rbac" WITH SCHEMA "public";

CREATE EXTENSION IF NOT EXISTS "supabase_vault" WITH SCHEMA "vault";

CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA "extensions";

What is the way to move from remote to local, incl the extensions?

[imor]

The migration file doesn't contain correct sql to install "supabase-dbdev". You can manually replace the following line:

CREATE EXTENSION IF NOT EXISTS "supabase-dbdev" WITH SCHEMA "public";

With this:

/*---------------------
---- install dbdev ----
----------------------
Requires:
  - pg_tle: https://github.com/aws/pg_tle
  - pgsql-http: https://github.com/pramsey/pgsql-http
*/
create extension if not exists http with schema extensions;
create extension if not exists pg_tle;
drop extension if exists "supabase-dbdev";
select pgtle.uninstall_extension_if_exists('supabase-dbdev');
select
    pgtle.install_extension(
        'supabase-dbdev',
        resp.contents ->> 'version',
        'PostgreSQL package manager',
        resp.contents ->> 'sql'
    )
from http(
    (
        'GET',
        'https://api.database.dev/rest/v1/'
        || 'package_versions?select=sql,version'
        || '&package_name=eq.supabase-dbdev'
        || '&order=version.desc'
        || '&limit=1',
        array[
            (
                'apiKey',
                'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJp'
                || 'c3MiOiJzdXBhYmFzZSIsInJlZiI6InhtdXB0cHBsZnZpaWZyY'
                || 'ndtbXR2Iiwicm9sZSI6ImFub24iLCJpYXQiOjE2ODAxMDczNzI'
                || 'sImV4cCI6MTk5NTY4MzM3Mn0.z2CN0mvO2No8wSi46Gw59DFGCTJ'
                || 'rzM0AQKsu_5k134s'
            )::http_header
        ],
        null,
        null
    )
) x,
lateral (
    select
        ((row_to_json(x) -> 'content') #>> '{}')::json -> 0
) resp(contents);
create extension "supabase-dbdev";
select dbdev.install('supabase-dbdev');
drop extension if exists "supabase-dbdev";
create extension "supabase-dbdev";

This should install supabase-dbdev correctly in local db. supabase_rbac will also need to be fixed by replacing:

CREATE EXTENSION IF NOT EXISTS "pointsource-supabase_rbac" WITH SCHEMA "public";

with:

select dbdev.install('pointsource-supabase_rbac');
create extension "pointsource-supabase_rbac"
    version '0.0.2';

Do similar substitutions for any other TLE extension.

Keeping this issue open to investigate how to get supabase db pull to generate the correct migration file.

[JanKups]

@imor Thanks for your help. Do I need to install "pg_tle" and "pgsql-http" separately on the local machine?

After the proposed changes I am met with ERROR: type "http_header" does not exist (SQLSTATE 42704)

[imor]

Looks like http extension is not installed properly. Which line exactly do you see this error on?

[JanKups]

@imor

I can confirm I have not manually installed "pg_tle" and "pgsql-http". I am still pulling the db and running the migration. I might be missing a step (like installing pg_tle).

Full error:

ERROR: type "http_header" does not exist (SQLSTATE 42704)                
At statement 14: select                                                  
    pgtle.install_extension(                                             
        'supabase-dbdev',                                                
        resp.contents ->> 'version',                                     
        'PostgreSQL package manager',                                    
        resp.contents ->> 'sql'                                          
    )                                                                    
from http(                                                               
    (                                                                    
        'GET',                                                           
        'https://api.database.dev/rest/v1/'                              
        || 'package_versions?select=sql,version'                         
        || '&package_name=eq.supabase-dbdev'                             
        || '&order=version.desc'                                         
        || '&limit=1',                                                   
        array[                                                           
            (                                                            
                'apiKey',                                                
                'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJp'              
                || 'c3MiOiJzdXBhYmFzZSIsInJlZiI6InhtdXB0cHBsZnZpaWZyY'   
                || 'ndtbXR2Iiwicm9sZSI6ImFub24iLCJpYXQiOjE2ODAxMDczNzI'  
                || 'sImV4cCI6MTk5NTY4MzM3Mn0.z2CN0mvO2No8wSi46Gw59DFGCTJ'
                || 'rzM0AQKsu_5k134s'                                    
            )::http_header                                               
        ],                                                               
        null,                                                            
        null                                                             
    )                                                                    
) x,                                                                     
lateral (                                                                
    select                                                               
        ((row_to_json(x) -> 'content') #>> '{}')::json -> 0              
) resp(contents)  

[imor]

What happens if you use the following migration file:

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

/*---------------------
---- install dbdev ----
----------------------
Requires:
  - pg_tle: https://github.com/aws/pg_tle
  - pgsql-http: https://github.com/pramsey/pgsql-http
*/
create extension if not exists http with schema extensions;
create extension if not exists pg_tle;
drop extension if exists "supabase-dbdev";
select pgtle.uninstall_extension_if_exists('supabase-dbdev');
select
    pgtle.install_extension(
        'supabase-dbdev',
        resp.contents ->> 'version',
        'PostgreSQL package manager',
        resp.contents ->> 'sql'
    )
from http(
    (
        'GET',
        'https://api.database.dev/rest/v1/'
        || 'package_versions?select=sql,version'
        || '&package_name=eq.supabase-dbdev'
        || '&order=version.desc'
        || '&limit=1',
        array[
            (
                'apiKey',
                'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJp'
                || 'c3MiOiJzdXBhYmFzZSIsInJlZiI6InhtdXB0cHBsZnZpaWZyY'
                || 'ndtbXR2Iiwicm9sZSI6ImFub24iLCJpYXQiOjE2ODAxMDczNzI'
                || 'sImV4cCI6MTk5NTY4MzM3Mn0.z2CN0mvO2No8wSi46Gw59DFGCTJ'
                || 'rzM0AQKsu_5k134s'
            )::http_header
        ],
        null,
        null
    )
) x,
lateral (
    select
        ((row_to_json(x) -> 'content') #>> '{}')::json -> 0
) resp(contents);
create extension "supabase-dbdev";
select dbdev.install('supabase-dbdev');
drop extension if exists "supabase-dbdev";
create extension "supabase-dbdev";

--CREATE EXTENSION IF NOT EXISTS "pg_tle" WITH SCHEMA "pgtle";

--CREATE EXTENSION IF NOT EXISTS "supabase-dbdev" WITH SCHEMA "public";

CREATE EXTENSION IF NOT EXISTS "pgsodium" WITH SCHEMA "pgsodium";

CREATE SCHEMA IF NOT EXISTS "supabase_migrations";

ALTER SCHEMA "supabase_migrations" OWNER TO "postgres";

--CREATE EXTENSION IF NOT EXISTS "http" WITH SCHEMA "extensions";

CREATE EXTENSION IF NOT EXISTS "pg_graphql" WITH SCHEMA "graphql";

CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "extensions";

CREATE EXTENSION IF NOT EXISTS "pgcrypto" WITH SCHEMA "extensions";

CREATE EXTENSION IF NOT EXISTS "pgjwt" WITH SCHEMA "extensions";

--CREATE EXTENSION IF NOT EXISTS "pointsource-supabase_rbac" WITH SCHEMA "public";
select dbdev.install('pointsource-supabase_rbac');
create extension "pointsource-supabase_rbac"
    version '0.0.2';

CREATE EXTENSION IF NOT EXISTS "supabase_vault" WITH SCHEMA "vault";

CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA "extensions";

Also what is the output of the following sql:

select extname, extversion
from pg_extension
where extname in ('http', 'pg_tle', 'supabase-dbdev');

I want to see the extension versions installed in the db.

[JanKups]

@imor Thanks again!

extname	extversion
supabase-dbdev	0.0.4
http	1.5
pg_tle	1.0.4

Unfortunately the error persists.

ERROR: type "http_header" does not exist (SQLSTATE 42704)                
At statement 14: select                                                  
    pgtle.install_extension(                                             
        'supabase-dbdev',                                                
        resp.contents ->> 'version',                                     
        'PostgreSQL package manager',                                    
        resp.contents ->> 'sql'                                          
    )                                                                    
from http(  

It's a new project so I am able to start over from scratch. Would starting out local, and pushing up be an idea?

[imor]

Weird, I can install supabase-dbdev with this exact same http version.

Would starting out local, and pushing up be an idea?

Try creating a project locally after updating the public.ecr.aws/supabase/postgres docker image to the latest.

Also can you check what this prints: select * from pg_type where typname = 'http_header';

[JanKups]

@imor I managed to successfully run db reset. Thanks!

I have created a new project, and locally performed the following steps:

• supabase init
• supabase start
• Applied query in SQL Editor:

/*---------------------
---- install dbdev ----
----------------------
Requires:
  - pg_tle: https://github.com/aws/pg_tle
  - pgsql-http: https://github.com/pramsey/pgsql-http
*/
create extension if not exists http with schema extensions;
create extension if not exists pg_tle;
drop extension if exists "supabase-dbdev";
select pgtle.uninstall_extension_if_exists('supabase-dbdev');
select
    pgtle.install_extension(
        'supabase-dbdev',
        resp.contents ->> 'version',
        'PostgreSQL package manager',
        resp.contents ->> 'sql'
    )
from http(
    (
        'GET',
        'https://api.database.dev/rest/v1/'
        || 'package_versions?select=sql,version'
        || '&package_name=eq.supabase-dbdev'
        || '&order=version.desc'
        || '&limit=1',
        array[
            (
                'apiKey',
                'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJp'
                || 'c3MiOiJzdXBhYmFzZSIsInJlZiI6InhtdXB0cHBsZnZpaWZyY'
                || 'ndtbXR2Iiwicm9sZSI6ImFub24iLCJpYXQiOjE2ODAxMDczNzI'
                || 'sImV4cCI6MTk5NTY4MzM3Mn0.z2CN0mvO2No8wSi46Gw59DFGCTJ'
                || 'rzM0AQKsu_5k134s'
            )::http_header
        ],
        null,
        null
    )
) x,
lateral (
    select
        ((row_to_json(x) -> 'content') #>> '{}')::json -> 0
) resp(contents);
create extension "supabase-dbdev";
select dbdev.install('supabase-dbdev');
drop extension if exists "supabase-dbdev";
create extension "supabase-dbdev";

• select dbdev.install('pointsource-supabase_rbac');
• create extension "pointsource-supabase_rbac";
• supabase migration new user_roles
• supabase db diff --schema public
• Copied diff result into migration
• supabase db reset
• Error persists: Applying migration 20240206082517_user_roles.sql... ERROR: extension "pointsource-supabase_rbac" is not available (SQLSTATE 0A000) At statement 0: create extension if not exists "pointsource-supabase_rbac" with schema "public" version '0.0.2'

Updated migration file, based on your previous answers:

/*---------------------
---- install dbdev ----
----------------------
Requires:
  - pg_tle: https://github.com/aws/pg_tle
  - pgsql-http: https://github.com/pramsey/pgsql-http
*/
create extension if not exists http with schema extensions;
create extension if not exists pg_tle;
drop extension if exists "supabase-dbdev";
select pgtle.uninstall_extension_if_exists('supabase-dbdev');
select
    pgtle.install_extension(
        'supabase-dbdev',
        resp.contents ->> 'version',
        'PostgreSQL package manager',
        resp.contents ->> 'sql'
    )
from http(
    (
        'GET',
        'https://api.database.dev/rest/v1/'
        || 'package_versions?select=sql,version'
        || '&package_name=eq.supabase-dbdev'
        || '&order=version.desc'
        || '&limit=1',
        array[
            (
                'apiKey',
                'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJp'
                || 'c3MiOiJzdXBhYmFzZSIsInJlZiI6InhtdXB0cHBsZnZpaWZyY'
                || 'ndtbXR2Iiwicm9sZSI6ImFub24iLCJpYXQiOjE2ODAxMDczNzI'
                || 'sImV4cCI6MTk5NTY4MzM3Mn0.z2CN0mvO2No8wSi46Gw59DFGCTJ'
                || 'rzM0AQKsu_5k134s'
            )::http_header
        ],
        null,
        null
    )
) x,
lateral (
    select
        ((row_to_json(x) -> 'content') #>> '{}')::json -> 0
) resp(contents);
create extension "supabase-dbdev";
select dbdev.install('supabase-dbdev');
drop extension if exists "supabase-dbdev";
create extension "supabase-dbdev";

select dbdev.install('pointsource-supabase_rbac');

create extension if not exists "pointsource-supabase_rbac" with schema "public" version '0.0.2';

create extension if not exists "supabase-dbdev" with schema "public" version '0.0.4';

grant delete on table "public"."group_users" to "anon";

grant insert on table "public"."group_users" to "anon";

grant references on table "public"."group_users" to "anon";

grant select on table "public"."group_users" to "anon";

grant trigger on table "public"."group_users" to "anon";

grant truncate on table "public"."group_users" to "anon";

grant update on table "public"."group_users" to "anon";

grant delete on table "public"."group_users" to "authenticated";

grant insert on table "public"."group_users" to "authenticated";

grant references on table "public"."group_users" to "authenticated";

grant select on table "public"."group_users" to "authenticated";

grant trigger on table "public"."group_users" to "authenticated";

grant truncate on table "public"."group_users" to "authenticated";

grant update on table "public"."group_users" to "authenticated";

grant delete on table "public"."group_users" to "service_role";

grant insert on table "public"."group_users" to "service_role";

grant references on table "public"."group_users" to "service_role";

grant select on table "public"."group_users" to "service_role";

grant trigger on table "public"."group_users" to "service_role";

grant truncate on table "public"."group_users" to "service_role";

grant update on table "public"."group_users" to "service_role";

grant delete on table "public"."groups" to "anon";

grant insert on table "public"."groups" to "anon";

grant references on table "public"."groups" to "anon";

grant select on table "public"."groups" to "anon";

grant trigger on table "public"."groups" to "anon";

grant truncate on table "public"."groups" to "anon";

grant update on table "public"."groups" to "anon";

grant delete on table "public"."groups" to "authenticated";

grant insert on table "public"."groups" to "authenticated";

grant references on table "public"."groups" to "authenticated";

grant select on table "public"."groups" to "authenticated";

grant trigger on table "public"."groups" to "authenticated";

grant truncate on table "public"."groups" to "authenticated";

grant update on table "public"."groups" to "authenticated";

grant delete on table "public"."groups" to "service_role";

grant insert on table "public"."groups" to "service_role";

grant references on table "public"."groups" to "service_role";

grant select on table "public"."groups" to "service_role";

grant trigger on table "public"."groups" to "service_role";

grant truncate on table "public"."groups" to "service_role";

grant update on table "public"."groups" to "service_role";

drop trigger if exists on_change_update_user_metadata on public.group_users;
CREATE TRIGGER on_change_update_user_metadata AFTER INSERT OR DELETE OR UPDATE ON public.group_users FOR EACH ROW EXECUTE FUNCTION update_user_roles();

drop trigger if exists on_insert_set_group_owner on public.groups;
CREATE TRIGGER on_insert_set_group_owner AFTER INSERT ON public.groups FOR EACH ROW EXECUTE FUNCTION set_group_owner();

drop trigger if exists on_delete_user on public.user_roles;
CREATE TRIGGER on_delete_user INSTEAD OF DELETE ON public.user_roles FOR EACH ROW EXECUTE FUNCTION delete_group_users();

Before first step I updated with brew install supabase/tap/supabase Supabase rest: public.ecr.aws/supabase/postgrest:v12.0.1

[imor]

Great, closing this ticket as the issue is resolved.