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

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.

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.]

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com