DELETE ignores top-level filtering of resource embeddings

[rzetterberg]

Environment

• PostgreSQL version: 17.6
• PostgREST version: 13.0.6
• Operating system: NixOS

Description of issue

For details, please see this RME.

My RME has the following DDL:

The data of the RME consists of:

• 2 directors
  • John
  • Albert
• 5 films
  • 3 directed by John
  • 2 directed by Albert

Imagine that I want to delete all films directed by directors with the name John, I would then use the following query parameters:

• select=*,director:directors!inner(*)
• director.name=eq.John

To double check that the resource embeddings top-level filtering have been specified correctly, I first start by sending a GET with the query parameters above:

GET http://localhost:8080/films?select=*,director:directors!inner(*)&director.name=eq.John

That works, as expected, it only returns 3 of the 5 films, and they all belong to John:

[
  {"id":1,"name":"Movie about dogs","director_id":1,"director":{"id": 1, "name": "John"}},
  {"id":2,"name":"Movie about cats","director_id":1,"director":{"id": 1, "name": "John"}},
  {"id":3,"name":"Movie about cows","director_id":1,"director":{"id": 1, "name": "John"}}
]

Looking at the PostgreSQL log, I see that the following query was generated by PostgREST:

WITH pgrst_source AS (
  SELECT "api"."films".*
       , row_to_json("films_director_1".*)::jsonb AS "director"
    FROM "api"."films"
         INNER JOIN LATERAL (
           SELECT "directors_1".*
             FROM "api"."directors" AS "directors_1"
            WHERE "directors_1"."name" = $1
              AND "directors_1"."id" = "api"."films"."director_id"
            LIMIT $2
           OFFSET $3
         ) AS "films_director_1"
      ON TRUE
   LIMIT $4
  OFFSET $5
)

-- Parameters: $1 = 'John', $2 = '100', $3 = '0', $4 = '100', $5 = '0'

However, when I send a DELETE request with the same query parameters:

DELETE http://localhost:8080/films?select=*,director:directors!inner(*)&director.name=eq.John

All films are deleted and PostgREST generates the following query:

WITH pgrst_source AS (
     DELETE FROM "api"."films"
  RETURNING 1
)

I've encountered this problem in both v12 and v13, where the documentation says:

Deletions also support Return Representation, Resource Embedding and Vertical Filtering.

Let me know if you need any more information, if I can help in some way, or if you are having problems getting the RME working.

Also, I found out about this problem when pg-safeupdate refused to delete all rows in my project, so no data has been harmed in making this bug report 😉

[laurenceisla]

DELETE ignores top-level filtering of resource embeddings

Imagine that I want to delete all films directed by directors with the name John, I would then use the following query parameters:

* `select=*,director:directors!inner(*)`

* `director.name=eq.John` 

I think this is by design: the select parameter is not involved in the mutation itself (INSERT,DELETE or UPDATE), it's used after the mutation is done alongside the Return Representation. Doing a DELETE using the embedded resources is more akin to the relational insert in #818, the only way to delete by a referenced table is using the FK columns (director_id in this table).

So, the returned representation for a DELETE is of the deleted resource(s) (all the table rows in this case) and the resource embedding/filtering is done after that fact.

I've encountered this problem in both v12 and v13, where the documentation says:

Deletions also support Return Representation, Resource Embedding and Vertical Filtering.

I see, this can be interpreted in the way mentioned here. Perhaps the docs need to clarify that resource embedding and vertical filtering work with the Return Representation, not by themselves on the mutation. I'm adding the docs label for this reason, for now.

[rzetterberg]

Thanks for the explanation!

When the documentation says:

To delete rows in a table, use the DELETE verb plus Horizontal Filtering"

And links to horizontal filtering under the "Read" chapter. It tells me that filtering works the same way for GET and DELETE requests. The filter is applied to how rows are selected/marked for deletion.

When I then read this in the documentation:

Deletions also support Return Representation, Resource Embedding and Vertical Filtering.

It tells me that everything in the chapter "Resource Embedding" is supported (including top-level filtering) and since I already know that filtering works the same way for GET and DELETE requests, I assume that top-level filtering on resource embeddings will be applied to how rows are selected/marked for deletion.

It doesn't tell me anywhere that I should assume a different behavior and that some of the filters are applied to how rows are selected/marked for deletion and some filters have no effect.

I'm not saying that my assumptions are correct. I'm just saying that, this is how I, a person with limited PostgREST experience, interpreted that part of the documentation.

Regardless if the documentation gets revised or if top-level filtering on resource embeddings gets implemented on DELETE request. I think that if I'm using an unsupported filter (such as director.name=eq.John), it should give me an 400 Bad Request response. I don't think it should just ignore the unsupported filter and do a DELETE FROM "api"."films".

[laurenceisla]

I'm not saying that my assumptions are correct. I'm just saying that, this is how I, a person with limited PostgREST experience, interpreted that part of the documentation.

Yes, no worries, this is a valid interpretation at first glance, which can be avoided by improving the docs. So the feedback is appreciated here!

I think that if I'm using an unsupported filter (such as director.name=eq.John), it should give me an 400 Bad Request response.

This may be impossible to do, since it would be valid if the user wants to filter the returned rows after the mutation is done. Like saying: "I want to delete the whole films table and return the rows of the films with directors named John for some reason", which is allowed.

[rzetterberg]

Would it be helpful if I provide a PR with an update to the delete chapter docs?

This may be impossible to do, since it would be valid if the user wants to filter the returned rows after the mutation is done. Like saying: "I want to delete the whole films table and return the rows of the films with directors named John for some reason", which is allowed.

That's true, I didn't think about that. I was so focused on "my" problem I forgot about the other use cases 😅

[laurenceisla]

Would it be helpful if I provide a PR with an update to the delete chapter docs?

Yes, thank you! Please go ahead.