FK points to VIEW instead of TABLE in OpenAPI output

[laurenceisla]

Environment

• PostgreSQL version: 14.6
• PostgREST version: v10.1.1.20221215
• Operating system: Manjaro

Description of the issue

As it was mentioned in #2601, there is a regression where the OpenAPI output points a relationship to a View instead of a Table when the latter is the original destination of said relationship. The issue happens only when the view's name goes before the table name alphabetically. The reproducible example explains this better.

This issue started since version 9.0.1.20220630, more specifically in commit 115dae7. It may be due to the sort operation that is done in that commit that causes the view and table relationships to "mix" in the schema cache.

Steps to reproduce

This is an example adapted from #2601.

Create two tables with a relationship between them:

create table api.places (
  id bigint primary key,
  name text
);

create table api.projects(
  id bigint primary key,
  place_id bigint references api.places,
  name text
);

The OpenAPI output for the place_id column in the projects definition is:

"place_id": {
  "format": "bigint",
  "type": "integer",
  "description": "Note:\nThis is a Foreign Key to `places.id`.<fk table='places' column='id'/>"
}

After creating the view with a name that goes alphabetically before places:

create view api.my_view as
  select api.places.id, api.projects.name as proj_name, api.places.name as pla_name
  from api.projects join api.places on projects.place_id = places.id;

The output changes (when it shouldn't) to:

"place_id": {
  "description": "Note:\nThis is a Foreign Key to `my_view.id`.<fk table='my_view' column='id'/>",
  "format": "bigint",
  "type": "integer"
}

If the view's name is alphabetically after places, e.g. your_view then the output doesn't change.

[steve-chavez]

Does the bug only affect OpenAPI, or resource embedding as well? Maybe the generated query also changes from using places.id to my_view.id?

[laurenceisla]

Does the bug only affect OpenAPI, or resource embedding as well? Maybe the generated query also changes from using places.id to my_view.id?

If it's about the query that generates the Schema Cache then yes, it looks like it only affects the OpenAPI output. Resource embedding is more precise when looking for the relationship, e.g. requesting projects?select=place_id(*) or projects?select=places(*) correctly references the table and gives a different result than doing projects?select=my_view(*).