32bit message length = 1GB query?

Question

I'm reading a document about the postgres wire protocol and it describes the structure of a data packet as follows:

It then goes on to say

a 32 bit message length follows - this means you can’t send a query that’s larger than 1 GB

Could someone explain how a 32bit message length means a query length that can't be greater than 1GB?

Is it because a 32bit integer can have a maximum numeric value of ~2 billion which means 2 billion characters which is something like 1GB? As you can see, I'm pretty unclear how this is calculated.

Answer 1

I don't think that's right, and the explanation certainly isn't.

2^32 bytes is 4GiB, or if you use only the positive half of a signed integer, 2GiB (2^31). 1GiB is 2^30, i.e. only using 30 bits of a 32-bit integer.

PostgreSQL's maximum field size is 1GiB, but that's due to details of how variable-length fields are represented.

I'm not at all convinced that this means queries are limited to 1 GiB. In particular, with separate Parse/Bind/Execute, the parameters are processed separately, and while each parameter is so limited, I don't see that the whole query should be.

On 32-bit machines, PostgreSQL used to get only 2GiB of address space, or 3 GiB on some OSes. A few Linux variants allowed 4 GiB, but that was uncommon. Given that it must read the query into memory, parse it, process string or bytea escapes, etc, it is typical that multiple copies of the input data will be made in memory. This rapidly exhausts the available address space even if the host has more RAM than that, and causes an out-of-memory error. So in practice on a 32-bit machine the field size limit was much lower than 1 GiB, and so was the maximum query size limit.

The reason the field size limit is 1 GiB not 2, or 4, is the format of struct varlena (see src/include/c.h). One bit of the first byte of the size field is reserved to indicate whether it's a 1-byte or 4-byte size header. This lets PostgreSQL store the vastly most common small variable length fields more compactly. The second bit indicates whether it's stored compressed or uncompressed. See "Bit layouts for varlena headers" in src/include/postgres.h for the gory details.

None of this applies to the query size, and AFAIK PostgreSQL never stores the query text as a struct varlena Datum. So the real limit could well be 2GB, but a close reading of the protocol code would be required to confirm that.

src/backend/libpq/pqformat.c shows that sizes are represented as signed 32-bit integers (via struct StringInfoData), so that limits any message to 2^31 bits, 2 GiB. I don't immediately see what'd limit it to 1 GiB, if such a limitation exists.

Nonetheless, I strongly recommend never emitting protocol messages bigger than 1 GiB, since that's the documented limit.