Topics

Supabase•4w ago

New columns aren't propagating to schema cache (PGRST204)

Insert calls to my table is failing when a new column is included. - i added column in dashboard - the ts type def generated does include that column - made an insert call using service role including a field for that column - that call fails with PGRST204 - tried to reload schema cache NOTIFY pgrst, 'reload schema'; - hit the supabase /rest/v1/ endpoint directly and got the same error - opted out of cache when initiating supabase sdk; no luck either

30 Replies

garyaustin•4w ago

Is this more than one column? More than one table? Check the case of the column name is an exact match with the UI column name. Make sure there is not a space at the end. Restart the instance. Do you have more than one instance with the same tables?

KLooOP•4w ago

@garyaustin thx for speedy reply as always yes triple checked the column name. exact match wdym by restart instance? we're using hosted

garyaustin•4w ago

On the general settings tab you can restart the instance (reboot). Check the API Gateway log to make sure the request is getting there.

KLooOP•4w ago

- POST call is hitting the API gateway - can't sustain the downtime right now. will need to schedule a maintenance period

garyaustin•4w ago

Can you post the entire message. I'm skeptical a restart will do more than the notify would. But you should not need to do the notify anyways if you just add a column. It is mainly if you change a column name already in use.

KLooOP•4w ago

message.txt

garyaustin•4w ago

I meant the error message back at the client. I don't see the 204 error detail in the log you posted, but it is very verbose. You also did not say if more than one table or another column also does this.

KLooOP•4w ago

another eng reported as well but not as detailed so i suspect more than one table. getting you that error message now error message

{
    "code": "PGRST204",
    "details": null,
    "hint": null,
    "message": "Could not find the 'contact_uses_imessage' column of 'contacts' in the schema cache"
}

{
    "code": "PGRST204",
    "details": null,
    "hint": null,
    "message": "Could not find the 'contact_uses_imessage' column of 'contacts' in the schema cache"
}

garyaustin•4w ago

Also should probably plan on a support request. We have seen this 2 or 3 times. With the normal debug we can do not finding anything, but if I recall the OPs in those cases just never responded to last questions like they resolved it or moved on. Searched on pgrst204.

KLooOP•4w ago

yeah i saw those. already opened support ticket

garyaustin•4w ago

So the column name is not reserved and not in double sets of quotes like one of the other cases... No space at end here (not sure on actual column name) and not casing issues Unless your column had an upper case in the DB.

KLooOP•4w ago

here's the curl for that error message. used service key here

curl 'https://redacted.supabase.co/rest/v1/contacts' \
  -H 'accept: */*' \
  -H 'accept-language: en-US,en;q=0.9' \
  -H 'apikey: [redacted]' \
  -H 'authorization: Bearer [redacted]' \
  -H 'content-profile: public' \
  -H 'content-type: application/json' \
  -H 'origin: http://localhost:3001' \
  -H 'prefer;' \
  -H 'priority: u=1, i' \
  -H 'referer: http://localhost:3001/' \
  -H 'sec-ch-ua: "Not;A=Brand";v="99", "Google Chrome";v="139", "Chromium";v="139"' \
  -H 'sec-ch-ua-mobile: ?0' \
  -H 'sec-ch-ua-platform: "macOS"' \
  -H 'sec-fetch-dest: empty' \
  -H 'sec-fetch-mode: cors' \
  -H 'sec-fetch-site: cross-site' \
  -H 'user-agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36' \
  -H 'x-client-info: supabase-js-web/2.55.0' \
  --data-raw '{"contact_first_name":"Test","contact_last_name":"User","contact_primary_email":"test.user.1757512158443@example.com","contact_uses_imessage":false,"company_id":"Os0t1Fe64qTs4dlxfol5","contact_custom_variables":{}}'

curl 'https://redacted.supabase.co/rest/v1/contacts' \
  -H 'accept: */*' \
  -H 'accept-language: en-US,en;q=0.9' \
  -H 'apikey: [redacted]' \
  -H 'authorization: Bearer [redacted]' \
  -H 'content-profile: public' \
  -H 'content-type: application/json' \
  -H 'origin: http://localhost:3001' \
  -H 'prefer;' \
  -H 'priority: u=1, i' \
  -H 'referer: http://localhost:3001/' \
  -H 'sec-ch-ua: "Not;A=Brand";v="99", "Google Chrome";v="139", "Chromium";v="139"' \
  -H 'sec-ch-ua-mobile: ?0' \
  -H 'sec-ch-ua-platform: "macOS"' \
  -H 'sec-fetch-dest: empty' \
  -H 'sec-fetch-mode: cors' \
  -H 'sec-fetch-site: cross-site' \
  -H 'user-agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36' \
  -H 'x-client-info: supabase-js-web/2.55.0' \
  --data-raw '{"contact_first_name":"Test","contact_last_name":"User","contact_primary_email":"test.user.1757512158443@example.com","contact_uses_imessage":false,"company_id":"Os0t1Fe64qTs4dlxfol5","contact_custom_variables":{}}'

actual column name is contact_uses_imessage new tables use snake case & no capitalized column names

garyaustin•4w ago

Check the definition of the table in the UI (bottom right) to make sure name is not in quotes with a space. It is hard to tell in the UI if so. Usually only comes up if created in the UI. Otherwise restart/support are all I've got.

KLooOP•4w ago

No description

KLooOP•4w ago

no quotes

garyaustin•4w ago

Yeah, all looks good.

KLooOP•4w ago

so i'm out of luck until support? will schedule a restart soon given this is a postgrest issue, would support be able to ssh into instance and restart that service only?

garyaustin•4w ago

No idea.

KLooOP•4w ago

thank you anyways

garyaustin•4w ago

tried to reload schema cache NOTIFY pgrst, 'reload schema'; When you say tried to.... do you mean that ran without error and had no affect or you got an error running it?

KLooOP•4w ago

yes ran successfully. no impact on error

garyaustin•4w ago

You might run it again and check the PostgREST and Postgres logs for error. The notify itself would not get any error the process had. Seems like the cache is not being reset by that or the UI when it makes a column change.

KLooOP•4w ago

oooo this is interesting postgrest:

{
  "event_message": "Failed listening for notifications on the \"pgrst\" channel. ERROR:  could not access status of transaction 533513714 DETAIL:  Could not open file \"pg_xact/01FC\": No such file or directory.",
  "id": "c343dde8-65f8-4020-b629-da5433ac16ea",
  "metadata": [
    {
      "_AUDIT_LOGINUID": null,
      "_AUDIT_SESSION": null,
      "_LINE_BREAK": null,
      "host": "db-dvsokczsrpugowdlcfgr",
      "metadata": [],
      "project": null,
      "source_type": null
    }
  ],
  "timestamp": 1757514835511924
}

{
  "event_message": "Failed listening for notifications on the \"pgrst\" channel. ERROR:  could not access status of transaction 533513714 DETAIL:  Could not open file \"pg_xact/01FC\": No such file or directory.",
  "id": "c343dde8-65f8-4020-b629-da5433ac16ea",
  "metadata": [
    {
      "_AUDIT_LOGINUID": null,
      "_AUDIT_SESSION": null,
      "_LINE_BREAK": null,
      "host": "db-dvsokczsrpugowdlcfgr",
      "metadata": [],
      "project": null,
      "source_type": null
    }
  ],
  "timestamp": 1757514835511924
}

garyaustin•4w ago

Make sure you add that to your support request. I suspect a restart will mask whatever caused this.

KLooOP•4w ago

noted. i'm on this

garyaustin•4w ago

Can you post your ticket number here. I reached out to a dev as we have seen something like this a couple of times with no clear answer.

KLooOP•4w ago

SU-244507 thank you so much!

garyaustin•4w ago

Seems like restart...

No description

garyaustin•4w ago

Note it is Postgres that has the notify bug and that has to restart not PostgREST.

KLooOP•4w ago

ah ok thank you

Supabase gives you the tools, documentation, and community that makes managing databases, authentication, and backend infrastructure a lot less overwhelming.

41KMembers

View on Discord

Did you find this page helpful?