[regression] many-to-many relationships (FK-only, no or different PK)

[enote-kane]

Environment

• PostgreSQL version: 15.2
• PostgREST version: postgrest/postgrest:v10.2.0
• Operating system: Linux

Description of issue

Since v10.0.0, many-to-many relationships are not discovered properly. Last known working version: v9.0.1 (couldn't test the releases between due to #2410 ).

Schema:

CREATE ROLE data_admin nologin;

CREATE SCHEMA api;
GRANT USAGE ON SCHEMA api TO data_admin;

CREATE TABLE api.test_a (id text PRIMARY KEY);
CREATE TABLE api.test_b (id text PRIMARY KEY);
CREATE TABLE api.test_mapping (id_a text NOT NULL REFERENCES api.test_a(id), id_b text NOT NULL REFERENCES api.test_b(id));

INSERT INTO api.test_a VALUES ('value for A');
INSERT INTO api.test_b VALUES ('value for B');
INSERT INTO api.test_mapping VALUES ('value for A', 'value for B');

GRANT SELECT ON ALL TABLES IN SCHEMA api TO data_admin;

Docker compose service:

  my-admin-api:
    image: "postgrest/postgrest:v10.2.0"
    networks:
      - local-net
    depends_on:
      - my-database
    environment:
      PGRST_DB_URI: "postgresql://test:test@my-database:5432/mydb"
      PGRST_DB_SCHEMA: "api,public"
      PGRST_DB_EXTRA_SEARCH_PATH: "public"
      PGRST_DB_ANON_ROLE: "data_admin"
      PGRST_DB_POOL: "5"
      PGRST_DB_POOL_TIMEOUT: "15"
      PGRST_LOG_LEVEL: "info"
      PGRST_SERVER_HOST: "*"
      PGRST_SERVER_PORT: "3345"

API call:

curl -s 'http://<container-ip>:3345/test_a?select=b:test_b(*)' | jq '.'

Expected response:

[
  {
    "b": [
      {
        "id": "value for B"
      }
    ]
  }
]

Actual response:

{
  "code": "PGRST200",
  "details": "Searched for a foreign key relationship between 'test_a' and 'test_b' in the schema 'api', but no matches were found.",
  "hint": "Perhaps you meant 'test_mapping' instead of 'test_b'.",
  "message": "Could not find a relationship between 'test_a' and 'test_b' in the schema cache"
}

Was this change done intentionally for v10? If so, please close this issue, since the current documentation states the composite key requirement.

[laurenceisla]

Yes, this change was included in version 10.0.0 as mentioned in the CHANGELOG:

postgrest/CHANGELOG.md

Lines 196 to 197 in ce378e6

	- #2070, Restrict generated many-to-many relationships - @steve-chavez
	+ A primary key that contains the foreign key columns is now needed for generating many-to-many relationships.

This means that for a many-to-many relationship to be detected, the intermediate table must contain the foreign keys as primary keys. In your example, adding this constraint will make the embedding work as you expect:

alter table api.test_mapping add constraint test_mapping_pk primary key (id_a, id_b);

[enote-kane]

OK, I get that. I was just surprised that after upgrading, some functionality was broken that other services of ours relied on.

Adjusting our DB schemas now...

[laurenceisla]

Ah, there's also a list of Breaking Changes in the docs that may help you with the migration.