Inserts and Updates now require an up-to-date schema cache

[steve-chavez]

Problem

Start postgREST:

$ postgrest-with-postgresql-16 -f test/spec/fixtures/load.sql postgrest-run

Add a new column:

alter table test.projects add column new text;

Then:

http POST localhost:3000/projects <<JSON
{"id": 343, "new": "foo"}
JSON

HTTP/1.1 400 Bad Request
{
    "code": "PGRST204",
    "details": null,
    "hint": null,
    "message": "Column 'new' of relation 'projects' does not exist"
}

http POST "localhost:3000/projects?columns=id,new" <<JSON
{"id": 343, "new": "foo"}
JSON

HTTP/1.1 400 Bad Request
{
    "code": "PGRST204",
    "details": null,
    "hint": null,
    "message": "Column 'new' of relation 'projects' does not exist"
}

http PATCH "localhost:3000/projects?id=eq.1" <<JSON
{"new": "foo"}
JSON

HTTP/1.1 400 Bad Request
{
    "code": "PGRST204",
    "details": null,
    "hint": null,
    "message": "Column 'new' of relation 'projects' does not exist"
}

And this happens when using columns or not.

Seems this was introduced on #2839. The problem is that it's not documented on https://postgrest.org/en/v12/references/schema_cache.html.

Solution

Update the docs. While at it, make the error message more clear. Right now it seems it comes from PostgreSQL.

Notes

Maybe it's a good thing to behave like this now since we'll reject the request at the plan level and save pool connections. This whenever the user mistypes a column.

[steve-chavez]

On https://postgrest.org/en/v12/references/schema_cache.html#schema-cache

Some PostgREST features need metadata from the database schema.

Now it's almost like every feature requires the schema cache.. the only thing that doesn't require it are GETs.. but we might as well just say we require it for everything.

I'll delete the "Feature - Required Metadata" table and the "some" claim.

[wolfgangwalther]

Seems this was introduced on #2839.

Yes, I remember this was on purpose.

Now it's almost like every feature requires the schema cache.. the only thing that doesn't require it are GETs.. but we might as well just say we require it for everything.

Agree - that's the direction we should be taking!