Connection closed by PostgREST before sending complete response in case of response is Large(200MB)

[codeayra]

Environment

• PostgreSQL version::postgres-v12.12
• PostgREST version: postgrest/postgrest:v9.0.0
• Operating system: Unix

Description of issue

When trying to fetch data for a database view which has around 311000 rows postgREST server closes connection before sending the whole response.
With Postman: I get connection aborted
With Cur: l I get some data rows then gets error 'closed, but still some data to read'.
With Apache HttpClient : "org.apache.http.ConnectionClosedException: Premature end of chunk coded message body: closing chunk expected , (after reading some bytes)

I have used postgRest default configuration.

The strange part is when I run curl from the machine where postgREST server is running I gets the data, but when these machine are different and connected via network, I get this error.

(Expected behavior vs actual behavior)
I should get whole 200 MB of data vs I am getting Connection closed error after sometime.(partial data gets received)

(Steps to reproduce: Include a minimal SQL definition plus how you make the request to PostgREST and the response body)

curl --location --request GET http://ip:port/view_name'
--header 'Accept-Profile: schema_name'
--header 'Accept: text/csv' \

[steve-chavez]

The strange part is when I run curl from the machine where postgREST server is running I gets the data, but when these machine are different and connected via network, I get this error.

Have you checked your server logs when this happens? Perhaps it's an OOM(out of memory) error.

One way to do this could be to daemonize postgrest and then do journalctl -u postgrest.service. Otherwise dmesg | grep postgrest could do.

[codeayra]

I have checked postgREST logs and no error on those logs, postgREST is running as kubernetes pod and initially we were getting OOM, but we increased the memory of pod(its 2Gig now) and now we are not getting any OOM and pod is running fine not at all crashing and on logs also no error is printing and I am able to get the data for other views after this call gets failed.

"The strange part is when I run curl from the machine where postgREST server is running I gets the data " (this scenario is tested on windows machine not on kubernetes) while the actual problem I am facing when postgREST is running on Kubernetes.

[steve-chavez]

When trying to fetch data for a database view which has around 311000 rows postgREST server closes connection before sending the whole response.

Hm, do you really need to download all the data in one request? Using Limits and Pagination should avoid this issue.

You can also set a hard limit for all requests https://postgrest.org/en/stable/configuration.html#db-max-rows

[codeayra]

Actually its having 300k rows and if I go with Limits and pagination I need to apply sort and then only I can apply pagination and limit, that is taking lots of time.(I have tried that as well)
So I need to download the whole data at once. I don't see any logs on postgREST logs even enabling 'info'.

Is there anyway to increase the postgREST web server connection timeout?

[steve-chavez]

Is there anyway to increase the postgREST web server connection timeout?

There is no timeout for how long a response takes from our side.

I run curl from the machine where postgREST server is running I gets the data, but when these machine are different and connected via network

Proxies(nginx, cloudflare, etc) can also apply timeouts, since postgREST responds correctly locally then this is likely a problem with proxies.

[codeayra]

I have checked proxy logs as well and it prints that connection closed by upstream. Even I tried hitting postgREST without proxy, its same behavior.

[codeayra]

some more question..

1. Does postgREST takes all memory given to Kubernetes pod? or any extra configuration required to increase the memory used by postgREST container?

2. Does postgREST caches the view/table data in memory after it returned response? If Yes can we disable that cache? What I see when I first get this data my postgREST mem usage from 247 Mib to 1.04 Gib and remains at that level even after returning reponse and then if I change reponse fromat from csv to json it again increase from 1.04 GiB to 2.33 Gib and remains at that level.

[codeayra]

One more strange behavior I see, when I send Request Header accept='application/json' the posgrest pod's max mem usage goes to 500Mb and the it goes to OOM and pod gets restarts, but when I ask for accept='text/csv' the postgrest pod's max mem usage goes to 1.8 GB (and it remains to this level only even after sending response back)
Why is it so??