mirror of
https://github.com/supabase/supabase.git
synced 2026-10-06 09:55:06 +03:00
Ali WaseemandJordi Enric
01d12e83c1
The 47 BigQuery-era logs queries across these 20 pages error on the
ClickHouse-backed logs engine ("Backend error! Retry your query."). This
converts them per the rules in `apps/studio/lib/ai/clickhouse-logs.ts`
and repoints every Logs Explorer link at the SQL Editor with the query
source set to **Logs**, since the Logs Explorer is being retired. Also
fixes two stale PostgreSQL 12 links in the tables guide.
Each of the 14 prefilled links was verified to decode back to exactly
the SQL shown on its page. One caveat for review:
`response.headers.proxy_status` in `postgrest-error-codes.mdx` is
unverified — it isn't in the published field reference, and the test
project had no `edge_logs` traffic to confirm against.
Fixes DOCS-1331
<!-- This is an auto-generated comment: release notes by coderabbit.ai
-->
## Summary by CodeRabbit
- **Documentation**
- Updated database, storage, API, and Edge Function logging guides to
use the SQL Editor and current Logs interface.
- Replaced legacy Log Explorer and BigQuery examples with current query
syntax and structured log fields.
- Refreshed troubleshooting queries for error diagnosis, filtering,
aggregation, and performance analysis.
- Improved examples with clearer source filters, status handling,
request details, joins, and result limits.
- Updated PostgreSQL documentation links and clarified how API error
codes appear in responses.
<!-- end of auto-generated comment: release notes by coderabbit.ai -->
---------
Co-authored-by: Jordi Enric <jordi.err@gmail.com>
261 lines
16 KiB
Plaintext
261 lines
16 KiB
Plaintext
---
|
|
id: 'postgrest-error-codes'
|
|
title: 'Error Codes'
|
|
description: 'PostgREST Error Codes'
|
|
subtitle: 'Identify PostgREST errors and resolve them'
|
|
sidebar_label: 'Debugging'
|
|
---
|
|
|
|
<Admonition type="note">
|
|
|
|
The docs reflect the error codes and information in [PostgREST's official
|
|
docs](https://docs.postgrest.org/en/stable/).
|
|
|
|
</Admonition>
|
|
|
|
## PostgREST error codes
|
|
|
|
Error codes from the Data API are returned as JSON objects
|
|
|
|
```json
|
|
{
|
|
"code": "42703",
|
|
"details": null,
|
|
"hint": "Perhaps you meant to reference the column some_table.fake_col",
|
|
"message": "column some_table.fake_col does not exist"
|
|
}
|
|
```
|
|
|
|
Here is the full list of error codes and their descriptions:
|
|
|
|
## Database level errors
|
|
|
|
To understand the errors reference the [Postgres Error Docs](https://www.postgresql.org/docs/current/errcodes-appendix.html).
|
|
|
|
Here's the text formatted as a proper markdown table:
|
|
|
|
| Postgres error code(s) | HTTP status | Error description |
|
|
| ---------------------- | ------------------------------ | ------------------------------- |
|
|
| 08\* | 503 | connection error |
|
|
| 09\* | 500 | triggered action exception |
|
|
| 0L\* | 403 | invalid grantor |
|
|
| 0P\* | 403 | invalid role specification |
|
|
| 23503 | 409 | foreign key violation |
|
|
| 23505 | 409 | uniqueness violation |
|
|
| 25006 | 405 | read only SQL transaction |
|
|
| 25\* | 500 | invalid transaction state |
|
|
| 28\* | 403 | invalid auth specification |
|
|
| 2D\* | 500 | invalid transaction termination |
|
|
| 38\* | 500 | external routine exception |
|
|
| 39\* | 500 | external routine invocation |
|
|
| 3B\* | 500 | savepoint exception |
|
|
| 40\* | 500 | transaction rollback |
|
|
| 53400 | 500 | config limit exceeded |
|
|
| 53\* | 503 | insufficient resources |
|
|
| 54\* | 500 | too complex |
|
|
| 55\* | 500 | obj not in prerequisite state |
|
|
| 57\* | 500 | operator intervention |
|
|
| 58\* | 500 | system error |
|
|
| F0\* | 500 | config file error |
|
|
| HV\* | 500 | foreign data wrapper error |
|
|
| P0001 | 400 | default code for "raise" |
|
|
| P0\* | 500 | PL/pgSQL error |
|
|
| XX\* | 500 | internal error |
|
|
| 42883 | 404 | undefined function |
|
|
| 42P01 | 404 | undefined table |
|
|
| 42P17 | 500 | infinite recursion |
|
|
| 42501 | if authenticated 403, else 401 | insufficient privileges |
|
|
| other | 400 | |
|
|
|
|
## API level errors
|
|
|
|
### Connection errors
|
|
|
|
Errors that prevent that data API from interacting with Postgres.
|
|
|
|
| Code | HTTP status | Description |
|
|
| -------- | ----------- | --------------------------------------------------------------------------------------------------------------------- |
|
|
| PGRST000 | 503 | Could not connect with the database due to an incorrect connection string or due to the Postgres service not running. |
|
|
| PGRST001 | 503 | Could not connect with the database due to an internal error. |
|
|
| PGRST002 | 503 | Could not connect with the database when building the schema cache |
|
|
| PGRST003 | 504 | The request timed out waiting for a connection from PostgREST's internal pool |
|
|
|
|
### API requests
|
|
|
|
Errors with data structures or request formatting
|
|
|
|
| Code | HTTP status | Description |
|
|
| -------- | ----------- | --------------------------------------------------------------------------------------------------------------------------- |
|
|
| PGRST100 | 400 | Parsing error in the query string parameter. |
|
|
| PGRST101 | 405 | For database functions, only `GET` and `POST` verbs are allowed. Any other verb will throw this error. |
|
|
| PGRST102 | 400 | An invalid request body was sent(e.g. an empty body or malformed JSON). |
|
|
| PGRST103 | 416 | An invalid range was specified for limits. |
|
|
| PGRST105 | 405 | An invalid `UPDATE`/`UPSERT` request was done |
|
|
| PGRST106 | 406 | The schema specified when switching schemas is not exposed to the API. |
|
|
| PGRST107 | 415 | The `Content-Type` sent in the request is invalid. |
|
|
| PGRST108 | 400 | The filter is applied to an embedded resource that is not specified in the `select` part of the query string. |
|
|
| PGRST111 | 500 | An invalid `response.headers` was set. |
|
|
| PGRST112 | 500 | The status code must be a positive integer. |
|
|
| PGRST114 | 400 | For an `UPSERT` using `PUT` when limits and offsets are used. |
|
|
| PGRST115 | 400 | For an `UPSERT` using `PUT` when the primary key in the query string and the body are different. |
|
|
| PGRST116 | 406 | More than 1 or no items where returned when requesting a singular response. |
|
|
| PGRST117 | 405 | The HTTP verb used in the request in not supported. |
|
|
| PGRST118 | 400 | Could not order the result using the related table because there is no many-to-one or one-to-one relationship between them. |
|
|
| PGRST120 | 400 | An embedded resource can only be filtered using the `is.null` or `not.is.null` operators. |
|
|
| PGRST121 | 500 | API can't parse the JSON objects in RAISE `PGRST` error. |
|
|
| PGRST122 | 400 | Invalid preferences found in `Prefer` header with `Prefer: handling=strict`. |
|
|
| PGRST123 | 400 | Aggregate functions are disabled. |
|
|
| PGRST124 | 400 | `max-affected` preference is violated. |
|
|
| PGRST125 | 404 | Invalid path is specified in request URL. |
|
|
| PGRST126 | 404 | Open API config is disabled but API root path is accessed. |
|
|
| PGRST127 | 400 | The feature specified in the `details` field is not implemented. |
|
|
| PGRST128 | 400 | `max-affected` preference is violated with `RPC` call. |
|
|
|
|
### Schema cache errors
|
|
|
|
The API is unable to identify relationships or objects within the query requests.
|
|
|
|
| Code | HTTP status | Description |
|
|
| -------- | ----------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
|
|
| PGRST200 | 400 | Caused by stale foreign key relationships, otherwise any of the embedding resources or the relationship itself may not exist in the database. |
|
|
| PGRST201 | 300 | An ambiguous embedding request was made. |
|
|
| PGRST202 | 404 | Caused by a stale function signature, otherwise the function may not exist in the database. |
|
|
| PGRST203 | 300 | Caused by requesting overloaded functions with the same argument names but different types, or by using a `POST` verb to request overloaded functions with a `JSON` or `JSONB` type unnamed parameter. The solution is to rename the function or add/modify the names of the arguments. |
|
|
| PGRST204 | 400 | Caused when the column specified in the columns query parameter is not found. |
|
|
| PGRST205 | 404 | Caused when the table specified in the URI is not found. |
|
|
|
|
### Authentication errors
|
|
|
|
The request lacks the proper credentials to request data
|
|
|
|
| Code | HTTP status | Description |
|
|
| -------- | ----------- | ------------------------------------------------------------------------------------------------ |
|
|
| PGRST300 | 500 | PostgREST does not have an active JWT secret to validate requests |
|
|
| PGRST301 | 401 | Provided JWT couldn't be decoded or it is invalid. |
|
|
| PGRST302 | 401 | Attempted to do a request without the header `Auth: Bearer` when the anonymous role is disabled. |
|
|
| PGRST303 | 401 | JWT claims validation or parsing failed. |
|
|
|
|
### Internal errors
|
|
|
|
Data API error unspecified
|
|
|
|
| Code | HTTP status | Description |
|
|
| -------- | ----------- | --------------------------------------------------------------------------- |
|
|
| PGRSTX00 | 500 | Internal errors related to the library used for connecting to the database. |
|
|
|
|
## Viewing errors in the logs
|
|
|
|
One can filter for API errors in the [SQL Editor](/dashboard/project/_/sql/new?skip=true&source=logs) with the query source set to **Logs**. Below are useful queries for filtering and analyzing API errors:
|
|
|
|
### Find all API errors that occurred at the database level
|
|
|
|
```sql
|
|
select
|
|
timestamp,
|
|
event_message,
|
|
log_attributes['parsed.error_severity'] as error_severity,
|
|
log_attributes['parsed.user_name'] as user_name,
|
|
log_attributes['parsed.query'] as query,
|
|
log_attributes['parsed.detail'] as detail,
|
|
log_attributes['parsed.hint'] as hint,
|
|
log_attributes['parsed.sql_state_code'] as sql_state_code,
|
|
log_attributes['parsed.backend_type'] as backend_type
|
|
from logs
|
|
where
|
|
source = 'postgres_logs'
|
|
and log_attributes['parsed.error_severity'] in ('ERROR', 'FATAL', 'PANIC')
|
|
and log_attributes['parsed.user_name'] = 'authenticator' -- the authenticator role represents the database API
|
|
order by timestamp desc
|
|
limit 100;
|
|
```
|
|
|
|
### Find specific database error from the data API
|
|
|
|
```sql
|
|
select
|
|
timestamp,
|
|
event_message,
|
|
log_attributes['parsed.error_severity'] as error_severity,
|
|
log_attributes['parsed.user_name'] as user_name,
|
|
log_attributes['parsed.query'] as query,
|
|
log_attributes['parsed.detail'] as detail,
|
|
log_attributes['parsed.hint'] as hint,
|
|
log_attributes['parsed.sql_state_code'] as sql_state_code,
|
|
log_attributes['parsed.backend_type'] as backend_type
|
|
from logs
|
|
where
|
|
source = 'postgres_logs'
|
|
and log_attributes['parsed.sql_state_code'] = '42501'
|
|
and log_attributes['parsed.user_name'] = 'authenticator' -- the authenticator role represents the database API
|
|
order by timestamp desc
|
|
limit 100;
|
|
```
|
|
|
|
<Admonition type="note">
|
|
|
|
The codes in the table above are returned in the response body, not recorded in the logs. Use the queries below to find the failing requests, then read the `code` from the response your client received.
|
|
|
|
</Admonition>
|
|
|
|
### Find API errors at the gateway
|
|
|
|
`sb_error_code` is the error code the API gateway recorded for a request, such as `UNAUTHORIZED_MISSING_API_KEY`. It is empty when the request reached PostgREST and failed there.
|
|
|
|
```sql
|
|
select
|
|
timestamp,
|
|
log_attributes['response.status_code'] as status_code,
|
|
log_attributes['response.headers.sb_error_code'] as gateway_error_code,
|
|
log_attributes['request.path'] as path,
|
|
event_message
|
|
from logs
|
|
where
|
|
source = 'edge_logs'
|
|
and toInt32OrZero(log_attributes['response.status_code']) >= 300
|
|
and match(log_attributes['request.path'], '^/rest/v1/')
|
|
order by timestamp desc
|
|
limit 100;
|
|
```
|
|
|
|
### Count errors per path by hour:
|
|
|
|
```sql
|
|
select
|
|
toStartOfHour(timestamp) as hour,
|
|
count() as error_count,
|
|
log_attributes['request.path'] as path
|
|
from logs
|
|
where
|
|
source = 'edge_logs'
|
|
and toInt32OrZero(log_attributes['response.status_code']) >= 300
|
|
and match(log_attributes['request.path'], '^/rest/v1/')
|
|
group by hour, path
|
|
order by hour desc
|
|
limit 100;
|
|
```
|
|
|
|
### Find data API request from specific authenticated user
|
|
|
|
```sql
|
|
select
|
|
timestamp,
|
|
event_message,
|
|
log_attributes['request.headers.cf_connecting_ip'] as requesters_ip,
|
|
log_attributes['request.url'] as request_url,
|
|
log_attributes['request.method'] as request_method,
|
|
log_attributes['request.sb.jwt.authorization.payload.subject'] as user_id,
|
|
log_attributes['request.sb.jwt.apikey.payload.role'] as apikey_role,
|
|
log_attributes['request.sb.jwt.authorization.payload.role'] as authorization_token_role,
|
|
log_attributes['request.headers.user_agent'] as user_agent,
|
|
log_attributes['request.cf.city'] as city,
|
|
log_attributes['request.cf.country'] as country,
|
|
log_attributes['request.cf.postalCode'] as postalCode
|
|
from logs
|
|
where
|
|
source = 'edge_logs'
|
|
and match(log_attributes['request.path'], '^/rest/v1/')
|
|
and log_attributes['request.sb.jwt.authorization.payload.subject'] = 'SOME_USER_ID' -- <---ADD USER_ID from auth.users table
|
|
order by timestamp desc
|
|
limit 100;
|
|
```
|