You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
A Postgrest many-to-many query fails if a table in the "primary" schema has the same name as a view in another schema also exposed by postgrest.
{code: "PGRST201",details: [{cardinality: "many-to-many",embedding: "one with two",relationship: "one_two using one_two_one_id_fkey(one_id) and one_two_two_id_fkey(two_id)",},{cardinality: "many-to-many",embedding: "one with two",relationship: "one_two using one_two_one_id_fkey(one_id) and one_two_two_id_fkey(two_id)",}],hint: "Try changing 'two' to one of the following: 'two!one_two', 'two!one_two'. Find the desired relationship in the 'details' key.",message: "Could not embed because more than one relationship was found for 'one' and 'two'",}
My expectation is that postgrest prefers the table in the primary schema. Note that explicitly setting the db schema on the supabase client also does not change anything.
createtablepublic.one (
id serialprimary key,
name textnot null
);
createtablepublic.two (
id serialprimary key,
name textnot null
);
createtablepublic.one_two (
one_id intreferences one(id),
two_id intreferences two(id),
primary key (one_id, two_id)
);
createschemaapi;
grant usage on schema api to postgres, anon, authenticated, service_role;
alter default privileges in schema api grant all on tables to postgres, anon, authenticated, service_role;
alter default privileges in schema api grant all on functions to postgres, anon, authenticated, service_role;
alter default privileges in schema api grant all on sequences to postgres, anon, authenticated, service_role;
createviewapi.one with (security_invoker) asselect id, name
frompublic.one;
createviewapi.two with (security_invoker) asselect id, name
frompublic.two;
createviewapi.one_two with (security_invoker) asselect one_id, two_id
frompublic.one_two;
My expectation is that postgrest prefers the table in the primary schema.
I would expect so too. I had my doubts since PostgREST allows embedding through views and thought that maybe this is detecting public.one-two and api.one-two as possible intermediate embeds. But then I tried dropping the api.one_two view, this would mean that public.one-two is the only candidate to embed between api.one and api.two. As expected it recognized it, but it returned a SQL error:
{
"code": "42P01",
"details": null,
"hint": "There is an entry for table \"one_two\", but it cannot be referenced from this part of the query.",
"message": "invalid reference to FROM-clause entry for table \"one_two\""
}
So, there's definitely something wrong with a) the embedding detection or b) with building the query. I believe that it's both so I'm tagging this one as a bug for now.
Environment
Description of issue
A Postgrest many-to-many query fails if a table in the "primary" schema has the same name as a view in another schema also exposed by postgrest.
My expectation is that postgrest prefers the table in the primary schema. Note that explicitly setting the db schema on the supabase client also does not change anything.
Reproduction: https://github.com/psteinroe/postgrest-repro
Schema:
The query (using supabase-js):
The text was updated successfully, but these errors were encountered: