function extensions.moddatetime() does not exist for Migrations

[besteman]

Bug report

Describe the bug

I am getting an error that function extensions.moddatetime() does not exist

To Reproduce

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

1. Create an updated function with the following:

    create extension if not exists moddatetime schema extensions;
    create trigger handle_updated_at before update on bom
    for each row execute procedure moddatetime (updated_at);
   

2. run: supabase db commit updated_at
3. Run: supabase stop or remove containers or start a new local dev setup
4. See error: Error: Error resetting database: ERROR: function extensions.moddatetime() does not exist

Expected behavior

Exepted migrations to be run successfully and updated_at migration to work

System information

• OS: macOS 12.1 M1
• Version of supabase-cli: 0.16.1

P.S

Should update the issue templete since the Nodejs CLI is Deprecated

- Version of Node.js: [e.g. 10.10.0]

[besteman]

I think I have fixed this locally for me:

CREATE EXTENSION IF NOT EXISTS moddatetime
	SCHEMA "extensions";

I had to manually add this to the top of migration file. The migration file did not include this when auto-generated.

[soedirgo]

I think this is similar to https://github.com/supabase/cli/issues/61.

The problem with including CREATE EXTENSION in a migration is you won't be able to push the migration to the remote db, since the postgres role used for accessing the remote db can't do CREATE EXTENSION.

This needs to be fixed from the Supabase side, which we're currently working on (to quote myself here, what you can do from the dashboard you should be able to do with the postgres role).

For now though, you can write it in supabase/extensions.sql instead, which is only run on the local database. You do need to manually keep it in sync with the remote db's extensions though.

[lughino]

This problem still exists! I updated supabase following a patch SQL Editor revoke superuser access and now I get this error Failed to run sql query: function moddatetime() does not exist

[sweatybridge]

Hi @lughino , there appears to be a problem with the patch not updating search path of the temp role. You can fix it manually by running the following command in SQL Editor.

alter role postgres_temporary_object_holder SET search_path TO "$user",public,extensions;

We will be updating the patch script upstream but it won't be automatically applied to any projects.

Alternatively, you can prefix the function with its schema to avoid issues with search path, ie. extensions.moddatetime().

[lughino]

@sweatybridge Thanks that have fixed the issue. However, I found other issues with that patch, like not having anymore autocomplete of table names in the SQL editor, but this is for another issue to open. Thanks for your help

[mosnicholas]

I'm seeing this with the github CI & the vector extension.

Applying migration 20230209001317_embeddings.sql...
Error: ERROR: extension "vector" is not available (SQLSTATE 0A000)
At statement 0: create extension if not exists "vector" with schema "public" version '0.4.0'

Is there a good workaround?

[soedirgo]

Can you create a new issue @mosnicholas? And also include your db.major_version in supabase/config.toml in the issue description?

[MaximusMcCann]

This appears to be happening again :| ERROR: function extensions.moddatetime() does not exist (SQLSTATE 42883)

[MaximusMcCann]

For the next person. If you pull your migrations and limit to certain schemas --schema public,auth,storage you must add the extensions schema... Fun

[erskingardner]

I'm also getting this error trying to pull my database migrations. ERROR: function public.moddatetime() does not exist (SQLSTATE 42883)

[hu0p]

I'm also encountering this error, but with a separate function, jsonb_matches_schema. It seems like the extensions schema is not being generated for a fresh (local) project based on a pre-existing remote project with either supabase db pull (no arguments provided) or supabase db pull --schema public,auth,storage,extensions as @MaximusMcCann noted (if I understand them correctly).

[MaximusMcCann]

ya, jk. this is broken, even when adding in the extensions

[MaximusMcCann]

CREATE EXTENSION IF NOT EXISTS moddatetime
	SCHEMA "extensions";

^^ In the earliest seed file, go "find" moddatetime, and then above the first occurrence add the above to create the extension

[hu0p]

Hmm, if you meant earliest migration file, yep, doing the equivalent for our case (jsonb_matches_schema) worked! Thank you! That's interesting. I wonder why the migration tool doesn't include that line. This command seems to have some odd corner cases. Are certain schema imports done implicitly by other commands? We couldn't figure out how some of the default extensions were being enabled either. It makes it very difficult to troubleshoot.

[MaximusMcCann]

This bug is still happening. fwiw

[santiarr]

encountering this with pgvector:

ERROR: type public.vector does not exist (SQLSTATE 42704)

[santiarr]

ERROR: type public.vector does not exist

fixed by adding this to the top of my migration:

SET search_path TO public, extensions; SELECT pg_catalog.set_config('search_path', '', false); CREATE EXTENSION IF NOT EXISTS vector SCHEMA public;