Thread: The length of the sql query

The length of the sql query

From
Emi Lu
Date:
Hello,

Just curious to know whether postgresql has any length constraint about 
where part, such as


Query =
[
select col1, col2, ... coln
from table 1, table2,
where

constraint1 + constraint2 +................................constraintN
]

Is there any length arrange for the Query str such as 500M, 1G, etc? Or 
the query can be as long as it is.

Thanks a lot!



Re: The length of the sql query

From
Emi Lu
Date:
Hello,

It appears in MySql 3.23 the limit is 16 MB.  In 4.0 and later, it is 1 GB

http://dev.mysql.com/doc/refman/4.1/en/packet-too-large.html


Could someone tell me where I can find PostgreSQL doc about the query 
length please
Tks a lot!

> Hello,
>
> Just curious to know whether postgresql has any length constraint 
> about where part, such as
>
>
> Query =
> [
> select col1, col2, ... coln
> from table 1, table2,
> where
>
> constraint1 + constraint2 +................................constraintN
> ]
>
> Is there any length arrange for the Query str such as 500M, 1G, etc? 
> Or the query can be as long as it is.
>
> Thanks a lot!
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings



Re: The length of the sql query

From
Tom Lane
Date:
Emi Lu <emilu@encs.concordia.ca> writes:
> Could someone tell me where I can find PostgreSQL doc about the query 
> length please

The theoretical limit is 1Gb (because palloc won't allow creation of a
longer string than that).  The practical limit is probably a great deal
less, especially if you don't have a 64-bit machine with gobs of memory,
because the query text itself is hardly the major consumer of memory
for any real-world query.

Your question is really entirely meaningless when you haven't specified
exactly what sort of query you're thinking of or what kind of platform
you intend to try to run it on.

The 1Gb upper limit for MySQL is a pretty academic number too, for
exactly the same reasons.  Have you tried putting a 1Gb query string
into MySQL?
        regards, tom lane