-- only needed if script rerun without psql restart
drop table fill_dates;
drop table tmp_contract;
set ENABLE_NESTLOOP to on;
set ENABLE_MERGEJOIN to on;
set ENABLE_HASHJOIN to on;

VACUUM ANALYZE;

SELECT DISTINCT d.date INTO TEMP fill_dates FROM daily d, futures f
WHERE d.inst_id = f.inst_id
AND f.fut_base_id = 3209
AND f.inst_id <= 3517
AND d.date >= '2002-01-08'
AND d.data_source_id = 36869
AND d.trade_venue_id = 3347
ORDER BY d.date DESC LIMIT 300;

SELECT date, open, high, low, close, volume,
       open_interest, update_time, user_id
 INTO TEMP tmp_contract
 FROM daily
 WHERE inst_id = 3517::bigint
 AND data_source_id = 36869::bigint
 AND trade_venue_id = 3347::bigint
 AND date >= (SELECT MIN(date) FROM fill_dates);

create unique index tmp_contract_date on tmp_contract (date);
vacuum analyze tmp_contract;

DELETE FROM fill_dates
 WHERE EXISTS (SELECT tc.date
                FROM tmp_contract tc
                WHERE tc.date = fill_dates.date);

set ENABLE_NESTLOOP to on;
set ENABLE_MERGEJOIN to off;
set ENABLE_HASHJOIN to off;

-- query triggers error

INSERT INTO tmp_contract
 SELECT d.date, d.open + tc.close - d2.close AS open,
        d.high + tc.close - d2.close AS high,
        d.low + tc.close - d2.close AS low,
        d.close + tc.close - d2.close AS close,
        d.volume, d.open_interest, d.update_time, d.user_id
  FROM daily d, daily d2, tmp_contract tc, fill_dates fd
  WHERE d.date = fd.date
   AND d.inst_id = 3516::bigint
   AND d.data_source_id = 36869::bigint
   AND d.trade_venue_id = 3347::bigint
   AND d2.inst_id = 3516::bigint
   AND d2.data_source_id = 36869::bigint
   AND d2.trade_venue_id = 3347::bigint
   AND d2.date =  (SELECT MIN(d3.date)
                    FROM daily d3, tmp_contract tc2
                    WHERE d3.date = tc2.date
                     AND d3.data_source_id = 36869::bigint
                     AND d3.trade_venue_id = 3347::bigint
                     AND d3.inst_id = 3516::bigint
                     AND d3.date > d.date)
    AND tc.date = d2.date;