Many-To-Many join fails when a view in another exposed schema has the same name as a table in the target schema

[psteinroe]

Environment

• Operating system: macos + supabase cloud
• Supabase CLI version: 1.178.2

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.

{
  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.

Reproduction: https://github.com/psteinroe/postgrest-repro

Schema:

create table public.one (
    id serial primary key,
    name text not null
);

create table public.two (
    id serial primary key,
    name text not null
);

create table public.one_two (
    one_id int references one(id),
    two_id int references two(id),
    primary key (one_id, two_id)
);

create schema api;

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;

create view api.one with (security_invoker) as
select id, name
from public.one;

create view api.two with (security_invoker) as
select id, name
from public.two;

create view api.one_two with (security_invoker) as
select one_id, two_id
from public.one_two;

The query (using supabase-js):

const { data, error } = await s.from("one").select("name,list:two(name)");

[laurenceisla]

To Reproduce in PostgREST

Both schemas need to be in db-schemas:

db-schemas = "api,public"

Then, the request would be:

curl 'localhost:3000/one?select=name,list:two(name)'

Issue

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.

Extra info: Part of the SQL query

```sql SELECT "api"."one"."name", COALESCE("one_list_1"."one_list_1", '[]') AS "list" FROM "api"."one" LEFT JOIN LATERAL (SELECT json_agg("one_list_1")::jsonb AS "one_list_1" FROM (SELECT "api"."two"."name" FROM "api"."two", "public"."one_two" WHERE "api"."one_two"."two_id" = "api"."two"."id" AND "api"."one_two"."one_id" = "api"."one"."id") AS "one_list_1") AS "one_list_1" ON TRUE) ```

Workaround

For now, you can disambiguate the many-to-many relationships using spread embeds. So, the request would be:

curl 'localhost:3000/one?select=name,list:one_two(...two(name))'

Or, I'm assuming it's like this in supabase-js:

const { data, error } = await s.from("one").select("name,list:one_two(...two(name))");

[psteinroe]

Thanks for the quick response!

Our use case is that the api schema will serve as our public api, and changing all queries in our app that target the public schema is not an option.

Let me know if I can support here further.

[wolfgangwalther]

Our use case is that the api schema will serve as our public api,

Why is public then part of db-schemas?

You should only add the schema to be exposed as the api there. db-schemas=api would be enough.

[wolfgangwalther]

So, there's definitely something wrong with a) the embedding detection or b) with building the query. I believe that it's both

Agreed.

[psteinroe]

Why is public then part of db-schemas?

because we are using the public schema as our "internal" api used by our apps, and the api schema for the public api used by our customers.

[wolfgangwalther]

because we are using the public schema as our "internal" api used by our apps, and the api schema for the public api used by our customers.

Ah, interesting. You could also spin up two postgrest instances, one for each. This way you would be able to avoid dealing with all the Profile headers etc., too.

But ultimately, this is a bug, yes.