--postgresql HashAggregate memory problem test case for PostgreSQL 9.3.1, 9.3.2
--reported as bug ID #8687

create unlogged table table1 (key1 integer, text1 text, text2 text, val1 integer, val2 integer);

create unlogged table table2 (val3 integer, val4 integer);

create unlogged table table3 (text1 text, val3 integer);


create or replace function load_test_tables() returns boolean as $$
declare
  k1 integer;
begin
  perform setseed(0.12345);
  --10M rows
  for k1 in 1..10000000 loop
    insert into table1 values(k1, random()::text, random()::text, (1000*random())::integer, (1000*random())::integer);
  end loop;
  for k1 in 1..1000 loop
    insert into table2 values(k1, k1+10);
  end loop;
  return true;
end;
$$ language plpgsql;

truncate table table1;
truncate table table2;

--this will take several minutes...
select load_test_tables();

create unique index idx_table2_val3 on table2(val3);

vacuum analyze table1;
vacuum analyze table2;

--might need to adjust this upward to get HashAggregate plan
set work_mem='300MB';

--monitor background processes using top
--virtual process memory will go from about 400MB to over 6GB roughly adding 100MB per second.
--my VM is 4GM memory plus 4GB swap and OS will kill the postgres process when out of memory.

truncate table table3;
insert into table3 
--explain
select distinct t1.key1, t2.val3
from table1 t1, table2 t2
where upper(t1.text2) like '0.1%'
and t2.val3 between t1.val1 and t1.val2+10;

/*
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 HashAggregate  (cost=375535.78..431091.34 rows=5555556 width=8)
   ->  Nested Loop  (cost=0.15..347758.00 rows=5555556 width=8)
         ->  Seq Scan on table1 t1  (cost=0.00..173625.00 rows=50000 width=12)
               Filter: (upper(text2) ~~ '0.1%'::text)
         ->  Index Only Scan using idx_table2_val3 on table2 t2  (cost=0.15..2.37 rows=111 width=4)
               Index Cond: ((val3 >= t1.val1) AND (val3 <= (t1.val2 + 10)))
*/