PostgREST/PostgreSQL 无法放大字符串缓冲区消息 [英] PostgREST / PostgreSQL Cannot enlarge string buffer message

问题描述

我在运行的 postgREST API 上遇到了无法放大字符串缓冲区的消息.我猜有些表太大而无法与 API 一起使用.

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.

我正在使用来自 https://hub.docker.com 的 docker postgrest/postgrest 容器/r/postgrest/postgrest 版本为 PostgREST 5.1.0.

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?

使用 API 的表大小是否有任何限制.到目前为止，我在文档中找不到任何信息.

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

============ 更新

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

postgres 日志给了我以下 SQL 查询:

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

我使用以下 postgres 版本:

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"

推荐答案

除非重新编译 PostgreSQL 是不可能升级限制的(定义了 此处).

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.

使用 PostgREST，您可以进行垂直过滤(只需选择您需要的列)或 分页 减少您在一个请求中获得的行数.

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.

这篇关于PostgREST/PostgreSQL 无法放大字符串缓冲区消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！