Thread: maximum size limit for a query string?

maximum size limit for a query string?

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

Re: maximum size limit for a query string?

From
Vibhor Kumar
Date:
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


Re: maximum size limit for a query string?

From
Pavel Stehule
Date:
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>:
>
> 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
>

Re: maximum size limit for a query string?

From
andreas@a-kretschmer.de
Date:
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


Re: maximum size limit for a query string?

From
Pavel Stehule
Date:
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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: maximum size limit for a query string?

From
Andreas Kretschmer
Date:
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°

Re: maximum size limit for a query string?

From
Vibhor Kumar
Date:
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


Re: maximum size limit for a query string?

From
Josh Kupershmidt
Date:
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]. 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

Re: maximum size limit for a query string?

From
"David Johnston"
Date:
> -----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.