Thread: maximum size limit for a query string?
Is there any maximum size limit for a query string in Postgresql 9.0.1?
If yes, what is it ?.
If yes, what is it ?.
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>: > > 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 >
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
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 >
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]. 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.