https://supabase.com/ logo
#sql
Title
# sql
d

Denzo

01/21/2022, 9:50 AM
For reference, the error is something like this (abbreviated):
Copy code
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) ), ...........
z

ziad

01/21/2022, 5:46 PM
can you show the definition of the function
get_cosmos_userid
@Denzo
d

Denzo

01/21/2022, 7:53 PM
Hi @User, thanks for taking the time to help me out. The function body of
get_cosmos_userid()
is this:
Copy code
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'
    );
$$
z

ziad

01/21/2022, 8:11 PM
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...
your description of "arcane" for that error message is on point.
postgres errors are often easy to read and understand, but not this one
barring the ability to interpret the error, ii see you already tried to go by elimination. can we push that process further?
d

Denzo

01/21/2022, 9:53 PM
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.
If it helps I can post the full error, which is much much bigger, so I'll need to pastebin that
though I doubt you'll learn much from it
z

ziad

01/22/2022, 1:53 AM
exactly, the error is so cryptic! that's why i was suggesting a path like the one you described in your last reply
feel free to post your progress here, if only to have a rubber duck
g

garyaustin

01/23/2022, 3:40 PM
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.
j

jason-lynx

01/24/2022, 6:39 AM
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
d

Denzo

01/24/2022, 8:42 AM
@User @User thanks for the pointers, I'll have a look into it today and I'll let you know if it fixes that 🙂
this is the full error by the way
Seems like adding
security definer set search_path to public, extensions
did the trick, although the latter might not have been necessary
although it was not recommended to me to add it since it will be publicly accessible, although in this case it doesn't pose a problem or security risk
g

garyaustin

01/24/2022, 2:28 PM
That error looks like a block of code…..odd
d

Denzo

01/24/2022, 2:37 PM
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 🙂
s

Steve

01/25/2022, 10:30 PM
@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
I've never seen the query fail in that way, can you come with a reproducible test case?
First, how do you get the error, does it come when calling supabase-js rpc?
d

Denzo

01/25/2022, 10:47 PM
@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?)
anyway I'll look into setting up a repro tomorrow, if it is indeed an upstream bug it would be nice to get it fixed so others don't need to go through the same debugging hell as i have
g

garyaustin

01/25/2022, 11:06 PM
@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.
d

Denzo

01/26/2022, 9:57 AM
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
so calling the method for fetching that id worked fine on its own through SQL and RPC, but it went bust whenever it was called by the trigger function
s

Steve

01/26/2022, 10:17 PM
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..
g

garyaustin

01/26/2022, 11:20 PM
@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.