For reference, the error is something like this (abbreviated):

execute 3: with recursive pks_fks as ( -- pk + fk referencing col select conrelid as resorigtbl, unnest(conkey) as resorigcol from pg_constraint where contype IN ('p', 'f') union -- fk referenced col select confrelid, unnest(confkey) from pg_constraint where contype='f' ), views as ( select c.oid as view_id, n.nspname as view_schema, c.relname as view_name, r.ev_action as view_definition from pg_class c join pg_namespace n on n.oid = c.relnamespace join pg_rewrite r on r.ev_class = c.oid where c.relkind in ('v', 'm') and n.nspname = ANY($1 || $2) ), ...........

can you show the definition of the function

get_cosmos_userid

@Denzo

Hi @User, thanks for taking the time to help me out. The function body of

get_cosmos_userid()

is this:

sql
create or replace function public.get_cosmos_userid(email text, region text default 'us', lang text default 'en', currency text default 'usd')
returns jsonb
language sql
as $$
  select content::json->'user'->'userId' as userId
  from 
    http_post(
      'https://example.tld',
      '{ 
        "email": "' || get_cosmos_userid.email || '",
        "region": "' || coalesce(get_cosmos_userid.region, 'us') || '",
        "language": "' || coalesce(get_cosmos_userid.lang, 'en') || '",
        "currency": "' || coalesce(get_cosmos_userid.currency, 'usd') || '",
        "domain": "example.tld" 
      }',
      'application/json'
    );
$$

oh i see. i thought it would be related to the function being defined with security invoker and not having the rights to do what it needs, but the function is only doing an http_post...

I'm continuing work on this Monday, I suppose there's a few things I can try with regards to elimination which I'll try, e.g. use a dummy function that just directly returns some data, and using the

http_post()

call directly within the trigger function to see whether the issue is simply something with regards to permissions and/or scope.

exactly, the error is so cryptic! that's why i was suggesting a path like the one you described in your last reply

You probably need to add

set search_path to public, extensions

to your function calling http_post as it is in the extensions schema and the session user might not have that path from a trigger depending on your settings. I'm not sure just doing extensions.http_post is enough as it is a shell for http() and not sure it sets it's own search path or not. But your error is not what I would expect for this versus a not found error.

in

get_cosmos_userid

, instead of referencing the parameters with e.g.

get_cosmos_userid.email

, could you try using just the parameter name (e.g. just

email

instead of

get_cosmos_userid.email

) or positional value (e.g.

$1

to reference the first parameter aka

email

,

$2

for

region

...)? not certain that this will help, but just in case

@User @User thanks for the pointers, I'll have a look into it today and I'll let you know if it fixes that 🙂

That error looks like a block of code…..odd

either way, thanks for helping me out, I really appreciate it! hope to return that favour to others here once I'm a bit more familiar with Supabase/Postgres 🙂

@User That error comes from PostgREST, in particular one of its instrospection queries: https://github.com/PostgREST/postgrest/blob/main/src/PostgREST/DbStructure.hs#L755-L905

@User Calling the

get_cosmos_userid

method directly works fine from RPC, I haven't tried that with the trigger function itself though (is that even possible?)

@User @User it was my understanding this was a trigger function off of some table operation, NOT AN RPC called function. If so the only PostgREST involvement should be in the actual API call to do insert/update/select or what ever you were doing on the table the trigger went of off. So the error would be coming back from PostgreSQL from the trigger function back to PostgREST and then I guess causing PostgREST to "mishandle" the error if Steve thinks that message is generated there.

Yeah it was a trigger whenever a new user was created, it does a

http_post

call to some endpoint to fetch a user id from our old database to link new users to our old API

Hmm.. really weird because the query in the error message is executed at startup and when there's a schema change(due to a postgres event trigger that captures DDL statements), not when an insert/update is done..

@User just a heads up if you are triggering off auth.users insert when a new user is created. Your http function needs to respond in a "few" seconds (I don't remember the number now). If you don't respond from the trigger in that time frame, gotrue will timeout the user creation and you will get user error. May not be a problem for the case where you already have captured the users, but not a good thing for possible new users.