maximum size limit for a query string?

Zitat von AI Rumman <rummandba@gmail.com>:

Is there any maximum size limit for a query string in Postgresql 9.0.1?
If yes, what is it ?.

Not sure, but maybe 16 MByte, see
http://www.phpbuilder.com/board/archive/index.php/t-10250064.html

On Jun 7, 2011, at 4:09 PM, AI Rumman wrote:

Is there any maximum size limit for a query string in Postgresql 9.0.1?
If yes, what is it ?.

track_activity_query_size parameter.
http://www.postgresql.org/docs/9.0/static/runtime-config-statistics.html

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhorkumar.wordpress.com

Hello

no, it means some different.

we tested a SQL about 20MB with success.

The maximum of varlena is 1GB - so it is necessary to be possible send
a query longer 1GB. But you need a free RAM 3-5x larger then query
size.

Regards

Pavel Stehule

2011/6/7 Vibhor Kumar <vibhor.kumar@enterprisedb.com>:

2011/6/7 <andreas@a-kretschmer.de>:

Zitat von AI Rumman <rummandba@gmail.com>:

Is there any maximum size limit for a query string in Postgresql 9.0.1?
If yes, what is it ?.

Not sure, but maybe 16 MByte, see
http://www.phpbuilder.com/board/archive/index.php/t-10250064.html

isn't it limit for PHP?

Pavel

Pavel Stehule <pavel.stehule@gmail.com> wrote:

2011/6/7 <andreas@a-kretschmer.de>:

Zitat von AI Rumman <rummandba@gmail.com>:

Is there any maximum size limit for a query string in Postgresql 9.0.1?
If yes, what is it ?.

Not sure, but maybe 16 MByte, see
http://www.phpbuilder.com/board/archive/index.php/t-10250064.html

isn't it limit for PHP?

Maybe...

Yeah, i think, 16 MByte isn't the real limit, yes. And i've seen
queries larger than that limit, but i can't find the link, sorry.
(maybe depesz.com, dunno, can't remember, i'm sorry)

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

On Jun 7, 2011, at 5:18 PM, Pavel Stehule wrote:

Hello

no, it means some different.

we tested a SQL about 20MB with success.

The maximum of varlena is 1GB - so it is necessary to be possible send
a query longer 1GB. But you need a free RAM 3-5x larger then query
size.

Thanks. my bad :(. I understood this question wrong.

Regards

Pavel Stehule

2011/6/7 Vibhor Kumar <vibhor.kumar@enterprisedb.com>:

On Jun 7, 2011, at 4:09 PM, AI Rumman wrote:

Is there any maximum size limit for a query string in Postgresql 9.0.1?
If yes, what is it ?.

track_activity_query_size parameter.
http://www.postgresql.org/docs/9.0/static/runtime-config-statistics.html

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhorkumar.wordpress.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhorkumar.wordpress.com

On Tue, Jun 7, 2011 at 2:38 PM, Andreas Kretschmer
<akretschmer@spamfence.net> wrote:

Yeah, i think, 16 MByte isn't the real limit, yes. And i've seen
queries larger than that limit, but i can't find the link, sorry.
(maybe depesz.com, dunno, can't remember, i'm sorry)

The thread linked seems to mainly be talking about MySQL, which has a
max_allowed_packet limit of something like 16 MB by default, but can
be adjusted upwards[1]http://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html. For Postgres, we can have COPY statements sent
over the network, limited only by your disk space, since these are
sent (and parsed, I think) buffer-by-buffer. If you do
pg_dump --table=big_table | pg_restore
you should see a giant COPY statement constructed; these can obviously
be arbitrarily large.

As for SQL statements that have to be constructed in-memory on the
client, and parsed/planned on the server all-at-once, I don't know of
any limits, other than maybe running out of memory somewhere.

Josh

--
[1]: http://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html

-----Original Message-----
Maybe...

Yeah, i think, 16 MByte isn't the real limit, yes. And i've seen queries

larger

than that limit, but i can't find the link, sorry.
(maybe depesz.com, dunno, can't remember, i'm sorry)

What kind of use-case would generate that large of a query?

Also, are we talking simply about the query as passed to PostgreSQL or the
fully re-written query? If your query is that long you better already be
using views or it is likely to be impossible to read. Further, unless you
have way too many columns the query planner and rewriter are more likely to
choke on the query than any kind of simple IO or memory constraint.

Oh, and if you need 16MB because you are using "one table to rule them all"
with 500 self-joins then a database crash is really doing you favor...

I guess I could see a query of the form:

INSERT INTO table (a) VALUES (16mb of content);

In fact, that is probably more of what is being asked...as opposed to 16MB
select query...anyway...

David J.