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

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

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Casey Duncan <casey(at)pandora(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2428: ERROR: out of memory, running INSERT SELECT
Date: 2006-05-11 11:42:09
Message-ID: 1147347729.3465.61.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-bugs
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


In response to

Responses

pgsql-bugs by date

Next:From: Thomas ReissDate: 2006-05-11 15:48:35
Subject: BUG #2432: PID file not created if $PGDATA is different from Datadir
Previous:From: bhavaniDate: 2006-05-11 05:29:02
Subject: BUG #2431: Error:SELECT query has no destination for result data

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