Re: BUG #2428: ERROR: out of memory, running INSERT SELECT statement

On May 11, 2006, at 4:42 AM, Simon Riggs wrote:

> On Wed, 2006-05-10 at 09:34 -0700, Casey Duncan wrote:
>
>> The script actually fails before the function is even defined anyhow,
>> on this statement:
>>
>> INSERT INTO ss
>> (ss_id, name, ll_id, shared_ss_id, time_added,
>> shared_creator_id)
>> SELECT nextval('ss_id_seq'), s.name, lts.ll_id, s.ss_id,
>> lts.time_added, s.ll_id
>> FROM ss AS s, ll_to_ss AS lts
>> WHERE lts.ll_id != s.ll_id;
>
> As your database is defined, this SQL statement will return
> approximately 4 trillion rows, by my calculation. As you say, it
> returns
> no rows at all when the database is empty.

*slaps forehead* I totally missed the "!=" in the where clause, Doh!
Thanks for hitting me with a clue-stick.

> If it hadn't failed on OOM it would have failed on disk space,
> assuming
> you didn't have a requirement for a 100 Tb table. So fixing this
> problem
> at the server end isn't something that is likely to happen soon/ever.
>
>> From here, your SQL looks like it has an error-of-intention.
>
> [This is exactly the type of statement that statement_cost_limit patch
> would have rejected early with an appropriate message.]

That would be nice, as it is it currently fails in very nasty way.
"ERROR: Query too stupid" might be better for this one ;^)

Thanks much.

-Casey