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?