PGRST105 Filters must include all and only primary key columns with 'eq' operators

[fredvos]

Environment

• PostgreSQL version: Docker postgres:latest (PostgreSQL 15.1 (Debian 15.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit)
• PostgREST version: Dockewr postgrest:latest (10.1.1)
• Operating system: Linux (Synology NAS)

Description of issue

With a table with a single column as primary key I expect to be able to create or replace records using PUT statements, but I always get a message saying PGRST105 Filters must include all and only primary key columns with 'eq' operators.

Suppose PostgREST has a base URL like http://host:port and I create a table named test : create table test(id TEXT NOT NULL PRIMARY KEY, bla TEXT);

After inserting ('een', 'fiets') (using psql) request http://host:port/test returns '[{"id":"een","bla":"fiets"}]' as expected.

I can insert a new record via PostgREST with:

curl "http://host:port/test" -X POST -H "Content-Type: application/json" -d '{"id":"twee","bla":"auto"}'

Now the previous query returns '[{"id":"een","bla":"fiets"}, {"id":"twee","bla":"auto"}]'

So POSTS work as a means to add records.

Selecting a specific record works too:

curl "http://host:port/test?id=eq.twee"
[{"id":"twee","bla":"auto"}]

Adding a new record with PUT fails:

curl "http://host:port/test?id=eq.drie" -X PUT -H "Content-Type: application/json" -d '{"id":"drie","bla":"trein"}'

{"code":"PGRST105","details":null,"hint":null,"message":"Filters must include all and only primary key columns with 'eq' operators"}

The same result when I want to replace a record with an existing id. For inserts I can use POSTs to replace the PUTs, but updates fail. Am I doing something wrong? I cannot find any references to this PGRST105 error from people experiencing the same issue.

I'm a beginner with PostgREST. I like it very much! I guess I'm doing something wrong.

Thanks, Fred

[steve-chavez]

Hello,

Looks like you might be running into a stale schema cache, try reloading it. See https://postgrest.org/en/stable/schema_cache.html#schema-cache-reloading

[fredvos]

Thank you, Steve
After a NOTIFY pgrst, 'reload schema'; in psql it works as expected.

[pascal-schetelat]

Hi,

I have an issue that is related to this one, but I doesn't seem to be related to schema cache, I hope you could help me diagnose it.
I'm a long time user of postgREST and I cannot understand the issue at play here. I'm using postgrest/postgrest:v11.1.0

Let's say I have a table order with 2 primary keys, customer_id and item_id, 2 others non nullable columns (create_at and a region and a bunch of other nullable columns). PostgREST has been restarted since the table has been created.

• If I insert a row in the table using this role with pgsql, it works.

INSERT INTO gorenove_services_data.output_service_simulation_dpe_v4 (customer_id, item_id, created_at, region)
VALUES ('abc', 'def', '2023-08-02 14:30:00','38');

• if I do GET /order?customer_id=eq.abc&item_id=eq.def with the right token, it works and returns the row defined earlier

But I try to use PUT /order?customer_id=eq.abc&item_id=eq.hij

I get
{'code': 'PGRST105', 'details': None, 'hint': None, 'message': "Filters must include all and only primary key columns with 'eq' operators"}

This might be related, but :

• this table is in a secondary schema selected using Accept-Profile : {schema_name}. select is not an issue.
• this table also happen to have RLS in order to deal with write permission.
  A user with role customer will be able to insert rows in the table, but only select, delete or update rows where customer_id checks with the JWT property customer_id. The RLS is working fine an only retuns orders made by this customer.

Do you have any idea ? I don't know haskell but I tried looking for the condition that could raise the InvalidFilters error but I couldn't get what is going on.

Thanks in advance,

Pascal Schetelat

[steve-chavez]

Let's say I have a table order with 2 primary keys, customer_id and item_id
But I try to use PUT /order?customer_id=eq.abc&item_id=eq.hij
{'code': 'PGRST105', 'details': None, 'hint': None, 'message': "Filters must include all and only primary key columns with 'eq' operators"}

@pascal-schetelat That might be a bug since you're including the PK columns in the filters. Can you open an issue with a reproducible example?

As a workaround, you could use PATCH or POST with Prefer: resolution=merge-duplicates.

[pascal-schetelat]

Thank you for your help.
I already tried PATCH and POST with this header to no avail.
I'll try to file an issue that you can reproduce.

[pascal-schetelat]

Ok, sorry, I forgot to switch from 'Content-Profile' to 'Accept-Profile'

#2890