50

The app we are building might execute quite a big insert queries. Is there limit that my postgres query can have only a certain number of characters?

Kannan Ramamoorthy
  • 647
  • 2
  • 7
  • 8
  • 2
    I would just say that if you're even approaching any sort of limit of that nature, you would need to reconsider your design. I have found that, usually, any queries that are coming close are normally generated by some sort of framework (involving extreme string concatenation) and they tend to lose contact with reality. – Vérace Mar 06 '16 at 09:14

1 Answers1

61

For current PostgreSQL version (up to 9.5), queries are received by a backend in a Stringinfo buffer, which is limited to MaxAllocSize, defined as:

#define MaxAllocSize   ((Size) 0x3fffffff) /* 1 gigabyte - 1 */

(see http://doxygen.postgresql.org/memutils_8h.html)

So a query is limited to 1 gigabyte (2^30) in size, minus 1 byte for a terminating null byte.

Should a client try to send a larger query, an error looking like this would come back:

ERROR: out of memory
DETAIL: Cannot enlarge string buffer containing 0 bytes by N more bytes.

where N is the size of the query.

Be aware that a query just below 1GB might require large amounts of memory to be parsed, planned or executed, in addition to that 1GB buffer.

If you need to push a large series of literals into a query, consider the alternative of creating a temporary table, COPY rows into it and have the main query refer to that temporary table.

Daniel Vérité
  • 31,182
  • 3
  • 72
  • 80
  • @Quandary: I don't see what change you're referring to. stringinfo.h currently says "StringInfo provides an extensible string data type (currently limited to a length of 1GB)" – Daniel Vérité Jun 16 '20 at 15:12
  • I was refering to https://stackoverflow.com/questions/4936731/maximum-length-of-an-sql-query - but i see this post ist from 2011 - if the 1GB is now, then it appears to have been shortened, not extended. My fault for not looking at the date. PS: MaxAllocSize is now defined in https://github.com/postgres/postgres/blob/master/src/common/stringinfo.c and there it notes: /* It's possible we could use a different value for this in frontend code */ – Quandary Jun 17 '20 at 06:39
  • @Quandary: interesting, I wouldn't have thought it ever was 2GB and I'm still skeptical. I'll do some research and update the answer. – Daniel Vérité Jun 17 '20 at 08:34
  • I'm not sure about that, might just be bogus info from SO in the first place. – Quandary Jun 17 '20 at 10:25