1

Postgres version is 11.2, dockerized, the default image with pg_activity installed as an extra.

OS is Ubuntu server 18.04. Base dockerfile is debian:stretch-slim

Call is done via the PostgreSQL JDBC Driver, org.postgresql:postgresql:42.2.5

I just now encountered a simple SUM() query giving me this:

Cannot enlarge string buffer containing 0 bytes by 1342170831 more bytes.

The query is something like:

select sum(size) from table;

The "size" field being bigint.

I wouldn't expect such a query to give me this problem. What's this indicative of? Are certain "size" fields beyond all proportion? I'd think the biggest not have more than 12 digits.

This has me confused. What should I check for?

Also, is there a more efficient way to perform this query, as to not trigger that 1 big stringbuffer enlarge?

EDIT:

Result of SELECT MAX(size), MIN(size) FROM table;

     max      | min 
--------------+-----
 230780611421 |   0
(1 row)

So yeah, 12 digits max, as I expected.

Amount of items in the table: 4123633

Quick calculation: 1342170831 / 4123633 = 325.48

I won't be able to isolate for a test case right away. Nor will I be able to quickly upgrade the install.

EDIT2: It does not happen when doing the query via PSQL. Casting to numeric does not make a difference, both work via psql.

The result is 75559012258007 btw.

EDIT3: I can't output the entire table, so here's just the field:

               Column               |            Type             | Collation | Nullable |                   Default                    
------------------------------------+-----------------------------+-----------+----------+----------------------------------------------
 size                               | bigint                      |           | not null | 

And yes, it is a table, not a view.

  • 2
    That sure sounds like a bug. Can you please edit your question and add the complete create table statement for the table in question? Can you create a self-contained test case that reproduces this error? How many rows does the table contain? Did you try to update to the latest minor release (which is 11.5)? Which operating system are you using? Which SQL client are you using to run this (maybe the error is from your SQL client, not from Postgres). In case you are not using psql, does this also happen when you run the query in psql`? – a_horse_with_no_name Oct 31 '19 at 10:12
  • Also, give us the output of SELECT MAX(size), MIN(size) FROM table; (I hope that its real name isn't table! If it is, try renaming it. Give us the output of SELECT COUNT(size) FROM table; and SELECT COUNT(*) FROM table; Do you have any way of partitioning this table - i.e. by other fields? From the CREATE TABLE requested by @a_horse_with_no_name, could you do your SUM(size) on various different other fields (`WHERE field_x = 'foo') and see does that narrow down the issue. I have used BIGINT to calculate the total amount of trading in S&P 500 over 5yrs, $12,000 trillion - no probs! – Vérace Oct 31 '19 at 11:32
  • Can you give us an EXPLAIN VERBOSE output of your query? – jjanes Oct 31 '19 at 11:55
  • This has me confused too. Could it be that the "table" is really a view? – Laurenz Albe Oct 31 '19 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.