ERROR: Could not query the database for the schema cache. Retrying.

[mrkhoiit]

Environment

• PostgreSQL version: PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit)
• PostgREST version: i use docker and use image: postgrest/postgrest , now version: v11.2.1
• Operating system: I run PostgREST on google cloud run service

Description of issue

I run PostgREST on google cloud run service . Some time i recived error : Could not query the database for the schema cache. Retrying.

{
  "code": "PGRST002",
  "details": null,
  "hint": null,
  "message": "Could not query the database for the schema cache. Retrying."
}

I don't know fixed this. Or This problem because PostgREST on google cloud run service . How fixed this. Thanks !

[steve-chavez]

@mrkhoiit Please share the logs of the postgrest service. There should be more details there.

[mrkhoiit]

@steve-chavez. Thank you for response. This is log from google clound run ( This is Serverless) . Seem log don't show more infomation. I don't know PostgREST can work on serverless or how i can config for this work ?

2023-10-22 21:15:39.514 ICT
GET5031.03 KB28 msChrome 118 https://postgrest-api-4ripfpd3za-uc.a.run.app/DgdKBjbuZlT2SRofCk_w?updated_at=gt.2023-10-22T04:08:32.952Z 
[Open in Logs Explorer](https://console.cloud.google.com/logs/query;query=resource.type%20%3D%20%22cloud_run_revision%22%0Aresource.labels.service_name%20%3D%20%22postgrest-api%22%0Aresource.labels.location%20%3D%20%22us-central1%22%0A%20severity%3E%3DDEFAULT;timeRange=2023-10-22T14:15:39.514343Z%2F2023-10-22T14:15:39.514343Z--PT1H;pinnedLogId=2023-10-22T14:15:39.514343Z%2F65352e8b00085072dbddd54e?authuser=1&project=starion-stagging)
{
httpRequest: {
latency: "0.028767171s"
protocol: "HTTP/1.1"
referer: "https://starion-stagging.com/"
remoteIp: "2402:800:61ca:384a:29b9:56f6:5442:188d"
requestMethod: "GET"
requestSize: "1461"
requestUrl: "https://postgrest-api-4ripfpd3za-uc.a.run.app/DgdKBjbuZlT2SRofCk_w?updated_at=gt.2023-10-22T04:08:32.952Z"
responseSize: "1053"
serverIp: "2001:4860:4802:32::35"
status: 503
userAgent: "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36"
}
insertId: "65352e8b00085072dbddd54e"
labels: {
instanceId: "0037d6d5d3fd9c532266254098c0e52ee852f59c3ac2dc08a669cd000ac7b1b8b59532d7ac0320c2b66d62b4bfbf8f6ee93a162e3e595eb36ac5f8ac16e011b79193"
}
logName: "projects/starion-stagging/logs/run.googleapis.com%2Frequests"
receiveTimestamp: "2023-10-22T14:15:39.631722923Z"
resource: {
labels: {
location: "us-central1"
revision_name: "postgrest-api-00003-kq5"
project_id: "starion-stagging"
service_name: "postgrest-api"
configuration_name: "postgrest-api"
}
type: "cloud_run_revision"
}
severity: "ERROR"
spanId: "7310273835298461876"
timestamp: "2023-10-22T14:15:39.514343Z"
trace: "projects/starion-stagging/traces/141be439574e47e76fee7d4ce010df23"
}

[steve-chavez]

@mrkhoiit There should be output like shown here:

https://postgrest.org/en/stable/references/admin.html#logging

We'd need stderr to debug.

[mrkhoiit]

@steve-chavez this is log :

2023-10-23 15:08:27.736 ICT
23/Oct/2023:08:08:27 +0000: Starting PostgREST 11.2.1...
2023-10-23 15:08:27.736 ICT
23/Oct/2023:08:08:27 +0000: Attempting to connect to the database...
2023-10-23 15:08:32.537 ICT
23/Oct/2023:08:08:32 +0000: Connection successful
2023-10-23 15:08:33.637 ICT
23/Oct/2023:08:08:33 +0000: Config reloaded
2023-10-23 15:08:42.887 ICT
GET20056.37 MB14.7 sStarion/1 CFNetwork/1404.0.5 Darwin/22.3.0 https://postgrest-api-4ripfpd3za-uc.a.run.app/iYpSFwEol6nHHGjuxUol?updated_at=gt.1970-01-01T00:00:09.000Z 
2023-10-23 15:08:43.320 ICT
23/Oct/2023:08:08:43 +0000: Schema cache loaded
2023-10-23 15:08:59.162 ICT
GET200775 B172 msokhttp/5.0.0-SNAPSHOT https://postgrest-api-4ripfpd3za-uc.a.run.app/GdkoGYS3G9orOofxqo_I?updated_at=gt.2023-10-23T07:10:45.866Z 
2023-10-23 15:09:45.508 ICT
OPTIONS200959 B3 msChrome 117 https://postgrest-api-4ripfpd3za-uc.a.run.app/XdNMgnXeDHnMQ_INmTMo?updated_at=gt.1970-01-01T00:00:09.000Z 
2023-10-23 15:09:45.509 ICT
GET2001.02 KB515 msChrome 117 https://postgrest-api-4ripfpd3za-uc.a.run.app/iOvpHHXgYrFGCceOdhPG?updated_at=gt.1970-01-01T00:00:09.000Z 
2023-10-23 15:09:45.724 ICT
GET4041.02 KB419 msChrome 117 https://postgrest-api-4ripfpd3za-uc.a.run.app/XdNMgnXeDHnMQ_INmTMo?updated_at=gt.1970-01-01T00:00:09.000Z 
2023-10-23 15:09:46.736 ICT
23/Oct/2023:08:09:46 +0000: Starting PostgREST 11.2.1...
2023-10-23 15:09:46.736 ICT
23/Oct/2023:08:09:46 +0000: Attempting to connect to the database...
2023-10-23 15:09:47.355 ICT
OPTIONS200950 B2 msChrome 117 https://postgrest-api-4ripfpd3za-uc.a.run.app/XdNMgnXeDHnMQ_INmTMo?is_deleted=eq.false 
2023-10-23 15:09:47.355 ICT
OPTIONS200950 B2 msChrome 117 https://postgrest-api-4ripfpd3za-uc.a.run.app/XdNMgnXeDHnMQ_INmTMo?is_deleted=eq.false&order=created_at.asc&limit=35&offset=0 
2023-10-23 15:09:47.556 ICT
GET2061.02 KB540 msChrome 117 https://postgrest-api-4ripfpd3za-uc.a.run.app/XdNMgnXeDHnMQ_INmTMo?is_deleted=eq.false 
2023-10-23 15:09:47.557 ICT
GET2061.05 KB512 msChrome 117 https://postgrest-api-4ripfpd3za-uc.a.run.app/XdNMgnXeDHnMQ_INmTMo?is_deleted=eq.false&order=created_at.asc&limit=35&offset=0 
2023-10-23 15:09:47.579 ICT
23/Oct/2023:08:09:46 +0000: Connection successful
2023-10-23 15:09:47.855 ICT
23/Oct/2023:08:09:47 +0000: Config reloaded
2023-10-23 15:09:50.064 ICT
23/Oct/2023:08:09:50 +0000: Schema cache loaded
2023-10-23 15:10:07.049 ICT
GET2061.02 KB203 msChrome 117 https://postgrest-api-4ripfpd3za-uc.a.run.app/iOvpHHXgYrFGCceOdhPG?is_deleted=eq.false 
2023-10-23 15:10:07.050 ICT
GET2061.05 KB211 msChrome 117 https://postgrest-api-4ripfpd3za-uc.a.run.app/iOvpHHXgYrFGCceOdhPG?is_deleted=eq.false&order=created_at.asc&limit=35&offset=0 
2023-10-23 15:10:08.458 ICT
GET2001.02 KB170 msChrome 117 https://postgrest-api-4ripfpd3za-uc.a.run.app/iOvpHHXgYrFGCceOdhPG?updated_at=gt.1970-01-01T00:00:09.000Z 
2023-10-23 15:10:56.196 ICT
OPTIONS200942 B2 msChrome 117 https://postgrest-api-4ripfpd3za-uc.a.run.app/iOvpHHXgYrFGCceOdhPG?updated_at=gt.1970-01-01T00:00:09.000Z 
2023-10-23 15:10:56.438 ICT
GET2001 KB533 msChrome 117 https://postgrest-api-4ripfpd3za-uc.a.run.app/iOvpHHXgYrFGCceOdhPG?updated_at=gt.1970-01-01T00:00:09.000Z 
2023-10-23 15:11:00.413 ICT
GET2061.05 KB214 msChrome 117 https://postgrest-api-4ripfpd3za-uc.a.run.app/iOvpHHXgYrFGCceOdhPG?is_deleted=eq.false&order=created_at.asc&limit=35&offset=0 
2023-10-23 15:11:00.414 ICT
GET2061.02 KB501 msChrome 117 https://postgrest-api-4ripfpd3za-uc.a.run.app/iOvpHHXgYrFGCceOdhPG?is_deleted=eq.false 
2023-10-23 15:11:27.037 ICT
23/Oct/2023:08:11:27 +0000: Starting PostgREST 11.2.1...
2023-10-23 15:11:27.037 ICT
23/Oct/2023:08:11:27 +0000: Attempting to connect to the database...
2023-10-23 15:11:27.477 ICT
OPTIONS200959 B3 msChrome 117 https://postgrest-api-4ripfpd3za-uc.a.run.app/iOvpHHXgYrFGCceOdhPG?updated_at=gt.2023-10-23T08:10:17.835Z 

Because it runs on serverless, PosgREST must reconnect after a period of inactivity.

[steve-chavez]

Because it runs on serverless, PosgREST must reconnect after a period of inactivity.

Ah, thanks for mentioning that. I've just noticed this is the same discussion as #2918

So I don't think we can run on serverless mode for now but it might be possible later.