Skip site navigation (1) Skip section navigation (2)

Re: Very long SQL strings

From: Chris Browne <cbbrowne(at)acm(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Very long SQL strings
Date: 2007-06-21 19:52:24
Message-ID: 608xad6q6f.fsf@dba2.int.libertyrms.com (view raw or flat)
Thread:
Lists: pgsql-performance
tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) writes:
> PS: for the record, there is a hard limit at 1GB of query text, owing
> to restrictions built into palloc.  But I think you'd hit other
> memory limits or performance bottlenecks before that one.

It would be much funnier to set a hard limit of 640K of query text.
The reasoning should be obvious :-).

I once ran into the situation where Slony-I generated a query that
made the parser blow out (some sort of memory problem / running out of
stack space somewhere thing); it was just short of 640K long, and so
we figured that evidently it was wrong to conclude that "640K ought to
be enough for anybody."

Neil Conway was an observer; he was speculating that, with some
(possibly nontrivial) change to the parser, we should have been able
to cope with it.

The query consisted mostly of a NOT IN clause where the list had some
atrocious number of entries in it (all integers).

(Aside: I wound up writing a "query compressor" (now in 1.2) which
would read that list and, if it was at all large, try to squeeze any
sets of consecutive integers into sets of "NOT BETWEEN" clauses.
Usually, the lists, of XIDs, were more or less consecutive, and
frequently, in the cases where the query got to MBs in size, there
would be sets of hundreds or even thousands of consecutive integers
such that we'd be left with a tiny query after this...)
-- 
select 'cbbrowne' || '@' || 'linuxfinances.info';
http://linuxfinances.info/info/linux.html
As of next Monday, MACLISP will no longer support list structure.
Please downgrade your programs.

In response to

Responses

pgsql-performance by date

Next:From: Larry RosenmanDate: 2007-06-21 19:59:53
Subject: Re: Database-wide VACUUM ANALYZE
Previous:From: Steven FlattDate: 2007-06-21 19:36:00
Subject: Re: Database-wide VACUUM ANALYZE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group