[SOLVED] PostgREST / PostgreSQL Cannot enlarge string buffer message

PostgREST / PostgreSQL Cannot enlarge string buffer message

I run into a Cannot enlarge string buffer message on my running postgREST API. I guess some tables are too large to work successful with the API.

I am using the docker postgrest/postgrest container from https://hub.docker.com/r/postgrest/postgrest with the version PostgREST 5.1.0.

Everything is working as expected but if the tables size getting too large, I get following error message.

hint    null
details "Cannot enlarge string buffer containing 1073741822 bytes by 1 more bytes."
code    "54000"
message "out of memory"

I can't determine the threshold when it's working or not. Is there a possibility to enlarge the string buffer in some config file or is this hardcoded?

Are there any limits from the table size working with the API. So far I couldn’t find any information in the docu.

=========== Update

The postgres logs give me following SQL query:

WITH pg_source AS (
  SELECT "public"."n_osm_bawue_line".* 
  FROM "public"."n_osm_bawue_line"
) 
SELECT null AS total_result_set, 
       pg_catalog.count(_postgrest_t) AS page_total, 
       array[]::text[] AS header, 
       coalesce(json_agg(_postgrest_t), '[]')::character varying AS body 
FROM ( 
  SELECT * 
  FROM pg_source
) _postgrest_t

I use following postgres version:

"PostgreSQL 11.1 (Debian 11.1-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit"

Solution

Unless you recompile PostgreSQL is not possible to upgrade the limit(defined here).

My suggestion would be to try to reduce the size of the payload(are you sure you need all the data?) or get all of the payload in multiple requests.

With PostgREST you can do vertical filtering(just select the columns that you need) or paginate to reduce the number of rows you get in one request.