Re: [HACKERS] Problem with complex query

Hello!
  Tom, I want to remind you that you looked into my database and found the
problem was that central.shop_id was int4 but shops.shop_id int2. After
making all fields identical most of the problem was fixed.  I just rerun the query now - and it worked!

On Sun, 9 May 1999, Tom Lane wrote:

> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> >> SELECT DISTINCT p.subsec_id
> >> FROM central cn, shops sh, districts d, positions p
> >> WHERE cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id
> >> AND   d.city_id = %d AND cn.pos_id = p.pos_id
> >> AND   cn.date_i >= current_date - '7 days'::timespan
> >> 
> >> While running postgres slowly eats all swap space (30 Meg) and aborts:
> 
> > Not sure how to comment on this.  Is 6.5beta any better?
> 
> Probably not :-(.  My guess is that the expression "current_date -
> '7 days'::timespan" is being re-evaluated at each tuple, and since
> we don't yet have intra-statement space recovery, the palloc'd space
> just grows and grows.  Oleg, can you try evaluating that expression
> on the application side and sending over a constant instead?
> 
> I think being able to recover palloc'd space after every few tuples
> will have to be a top priority for 6.6; we've seen too many complaints
> that trace back to this sort of thing.
> 
>             regards, tom lane
> 

Oleg.
----    Oleg Broytmann     http://members.xoom.com/phd2/     phd2@earthling.net          Programmers don't die, they
justGOSUB without RETURN.