PostgREST
Return ignored rows when `resolution=ignore-duplicates` is combined with `return=representation`
Environment
- PostgreSQL version: 15.12
- PostgREST version: v12.2.8
- Operating system: Linux
Description of issue
When making a POST request using the Prefer header with value return=representation,resolution=ignore-duplicates, an empty array is sent back instead of the existing entry. This is a bug in my opinion.
To be more precise, an empty array is returned. When also using header key Accept and value application/vnd.pgrst.object+json, the error message {"code":"PGRST116","details":"The result contains 0 rows","hint":null,"message":"JSON object requested, multiple (or no) rows returned"} is returned.
Please provide a reproducer.
My assumption is, that no rows are actually inserted, all are already there and the duplicates ignored.
PostgreSQL's ON CONFLICT DO NOTHING clause will then not return the rows, so this is not a bug, but expected.
Note that we could potentially change the behavior in the future, once https://www.postgresql.org/message-id/flat/2b5db2e6-8ece-44d0-9890-f256fdca9f7e%40proxel.se is merged into PostgreSQL and available. But that will probably still be 1-2 years.
I'm closing this, because I strongly believe that it behaves as designed (by PostgreSQL). If you can come up with a case, where rows are not ignored, but in fact created, and still not returned - please re-open.
My assumption is, that no rows are actually inserted, all are already there and the duplicates ignored.
Indeed, but I still want the existing rows to be returned. Those rows contain data that my request doesn't have yet (in my case the primary key), which I need to have. If Postgres doesn't return those rows, PostgREST can still obtain them, right?
Consider this to be a feature request then.
PostgREST can still obtain them, right?
I don't think this is easily possible, no.
There are some challenges around running a SELECT and an INSERT on the same table in the same statement, because the SELECT wouldn't see the rows that were inserted.
Even considering this as a new feature, we'd still be waiting for the above mentioned PostgreSQL feature.
There are some challenges around running a SELECT and an INSERT on the same table in the same statement, because the SELECT wouldn't see the rows that were inserted.
Being completely ignorant on the internal workings of PostgREST, couldn't it be split up in two statements? One for inserting and collecting newly inserted rows? And the second one for ignored (because duplicate) rows?
If this is very contradicting with how PostgREST works, I can understand if you don't want to plan this feature. In any case, thanks for considering and for making this awesome product!
Being completely ignorant on the internal workings of PostgREST, couldn't it be split up in two statements? One for inserting and collecting newly inserted rows? And the second one for ignored (because duplicate) rows?
Unfortunately not. The whole response is built by PostgreSQL itself, not PostgREST. With two top-level statements in one transaction, we would suddenly need to handle merging the response in haskell code - which would have huge implications.