Re: Query got Killed with CTE.

On 17 October 2017 at 21:18, Prabhat Sahu <prabhat(dot)sahu(at)enterprisedb(dot)com>
wrote:

> Hi,
>
> While quering with CTE against PG HEAD , i found that query got killed
> with this below error logs
> -- Machine Configuration: (d1.xlarge) CUPs : 8 , RAM : 16GB , SIze : 640GB
>
> postgres=# with x as (select 5 c1 from generate_series(1,1000000) x)
> select * from x x1 join x x2 using(c1);
> Killed
> 2017-10-17 14:12:33.558 BST [949] LOG: could not send data to client:
> Broken pipe
> 2017-10-17 14:12:33.558 BST [949] STATEMENT: with x as (select 5 c1 from
> generate_series(1,1000000) x) select * from x x1 join x x2 using(c1);
> 2017-10-17 14:12:33.559 BST [949] FATAL: connection to client lost
> 2017-10-17 14:12:33.559 BST [949] STATEMENT: with x as (select 5 c1 from
> generate_series(1,1000000) x) select * from x x1 join x x2 using(c1);
>
>
You produced a huge cross-product by the looks, and psql ran of of RAM
buffering the result. The OOM killer fired (check 'dmesg' to confirm) and
killed psql. The server noticed psql going away, and reported the fact.

None of this is surprising. What's the problem here?

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services