/*
 * -------------------------------------------
 *  NOTE: This test behaves differenly on 9.5
 * -------------------------------------------
 */
\set VERBOSITY terse
SET search_path = 'public';
CREATE SCHEMA pathman;
CREATE EXTENSION pg_pathman SCHEMA pathman;
CREATE SCHEMA test;
SET enable_indexscan = ON;
SET enable_seqscan = OFF;
/* Temporary table for JOINs */
CREATE TABLE test.tmp (id INTEGER NOT NULL, value INTEGER NOT NULL);
INSERT INTO test.tmp VALUES (1, 1), (2, 2);
/* Partition table by RANGE */
CREATE TABLE test.range_rel (
	id		SERIAL PRIMARY KEY,
	dt		TIMESTAMP NOT NULL,
	value	INTEGER);
INSERT INTO test.range_rel (dt, value) SELECT g, extract(day from g)
FROM generate_series('2010-01-01'::date, '2010-12-31'::date, '1 day') AS g;
SELECT pathman.create_range_partitions('test.range_rel', 'dt',
									   '2010-01-01'::date, '1 month'::interval,
									   12);
 create_range_partitions 
-------------------------
                      12
(1 row)

/*
 * Test UPDATE and DELETE
 */
/* have partitions for this 'dt' */
EXPLAIN (COSTS OFF) UPDATE test.range_rel SET value = 111 WHERE dt = '2010-06-15';
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Update on range_rel_6
   ->  Seq Scan on range_rel_6
         Filter: (dt = 'Tue Jun 15 00:00:00 2010'::timestamp without time zone)
(3 rows)

BEGIN;
UPDATE test.range_rel SET value = 111 WHERE dt = '2010-06-15';
SELECT * FROM test.range_rel WHERE dt = '2010-06-15';
 id  |            dt            | value 
-----+--------------------------+-------
 166 | Tue Jun 15 00:00:00 2010 |   111
(1 row)

ROLLBACK;
/* have partitions for this 'dt' */
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel WHERE dt = '2010-06-15';
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Delete on range_rel_6
   ->  Seq Scan on range_rel_6
         Filter: (dt = 'Tue Jun 15 00:00:00 2010'::timestamp without time zone)
(3 rows)

BEGIN;
DELETE FROM test.range_rel WHERE dt = '2010-06-15';
SELECT * FROM test.range_rel WHERE dt = '2010-06-15';
 id | dt | value 
----+----+-------
(0 rows)

ROLLBACK;
/* no partitions for this 'dt' */
EXPLAIN (COSTS OFF) UPDATE test.range_rel SET value = 222 WHERE dt = '1990-01-01';
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Update on range_rel
   ->  Seq Scan on range_rel
         Filter: (dt = 'Mon Jan 01 00:00:00 1990'::timestamp without time zone)
(3 rows)

BEGIN;
UPDATE test.range_rel SET value = 111 WHERE dt = '1990-01-01';
SELECT * FROM test.range_rel WHERE dt = '1990-01-01';
 id | dt | value 
----+----+-------
(0 rows)

ROLLBACK;
/* no partitions for this 'dt' */
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel WHERE dt < '1990-01-01';
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Delete on range_rel
   ->  Seq Scan on range_rel
         Filter: (dt < 'Mon Jan 01 00:00:00 1990'::timestamp without time zone)
(3 rows)

BEGIN;
DELETE FROM test.range_rel WHERE dt < '1990-01-01';
SELECT * FROM test.range_rel WHERE dt < '1990-01-01';
 id | dt | value 
----+----+-------
(0 rows)

ROLLBACK;
/* UPDATE + FROM, partitioned table */
EXPLAIN (COSTS OFF)
UPDATE test.range_rel r SET value = t.value
FROM test.tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Update on range_rel_1 r
   ->  Hash Join
         Hash Cond: (t.id = r.id)
         ->  Seq Scan on tmp t
         ->  Hash
               ->  Index Scan using range_rel_1_pkey on range_rel_1 r
                     Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
(7 rows)

BEGIN;
UPDATE test.range_rel r SET value = t.value
FROM test.tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;
ROLLBACK;
/* UPDATE + FROM, single table */
EXPLAIN (COSTS OFF)
UPDATE test.tmp t SET value = r.value
FROM test.range_rel r WHERE r.dt = '2010-01-01' AND r.id = t.id;
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Update on tmp t
   ->  Hash Join
         Hash Cond: (t.id = r.id)
         ->  Seq Scan on tmp t
         ->  Hash
               ->  Append
                     ->  Index Scan using range_rel_1_pkey on range_rel_1 r
                           Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
(8 rows)

BEGIN;
UPDATE test.tmp t SET value = r.value
FROM test.range_rel r WHERE r.dt = '2010-01-01' AND r.id = t.id;
ROLLBACK;
/* DELETE + USING, partitioned table */
EXPLAIN (COSTS OFF)
DELETE FROM test.range_rel r USING test.tmp t
WHERE r.dt = '2010-01-02' AND r.id = t.id;
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Delete on range_rel_1 r
   ->  Hash Join
         Hash Cond: (t.id = r.id)
         ->  Seq Scan on tmp t
         ->  Hash
               ->  Index Scan using range_rel_1_pkey on range_rel_1 r
                     Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
(7 rows)

BEGIN;
DELETE FROM test.range_rel r USING test.tmp t
WHERE r.dt = '2010-01-02' AND r.id = t.id;
ROLLBACK;
/* DELETE + USING, single table */
EXPLAIN (COSTS OFF)
DELETE FROM test.tmp t USING test.range_rel r
WHERE r.dt = '2010-01-02' AND r.id = t.id;
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Delete on tmp t
   ->  Hash Join
         Hash Cond: (t.id = r.id)
         ->  Seq Scan on tmp t
         ->  Hash
               ->  Append
                     ->  Index Scan using range_rel_1_pkey on range_rel_1 r
                           Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
(8 rows)

BEGIN;
DELETE FROM test.tmp t USING test.range_rel r
WHERE r.dt = '2010-01-02' AND r.id = t.id;
ROLLBACK;
/* Test special rule for CTE; SELECT (PostgreSQL 9.5) */
EXPLAIN (COSTS OFF)
WITH q AS (SELECT * FROM test.range_rel r
		   WHERE r.dt = '2010-01-02')
DELETE FROM test.tmp USING q;
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Delete on tmp
   CTE q
     ->  Append
           ->  Seq Scan on range_rel_1 r
                 Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
   ->  Nested Loop
         ->  CTE Scan on q
         ->  Materialize
               ->  Seq Scan on tmp
(9 rows)

BEGIN;
WITH q AS (SELECT * FROM test.range_rel r
		   WHERE r.dt = '2010-01-02')
DELETE FROM test.tmp USING q;
ROLLBACK;
/* Test special rule for CTE; DELETE (PostgreSQL 9.5) */
EXPLAIN (COSTS OFF)
WITH q AS (DELETE FROM test.range_rel r
		   WHERE r.dt = '2010-01-02'
		   RETURNING *)
DELETE FROM test.tmp USING q;
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Delete on tmp
   CTE q
     ->  Delete on range_rel_1 r
           ->  Seq Scan on range_rel_1 r
                 Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
   ->  Nested Loop
         ->  CTE Scan on q
         ->  Materialize
               ->  Seq Scan on tmp
(9 rows)

BEGIN;
WITH q AS (DELETE FROM test.range_rel r
		   WHERE r.dt = '2010-01-02'
		   RETURNING *)
DELETE FROM test.tmp USING q;
ROLLBACK;
/* Test special rule for CTE; DELETE + USING (PostgreSQL 9.5) */
EXPLAIN (COSTS OFF)
WITH q AS (DELETE FROM test.tmp t
		   USING test.range_rel r
		   WHERE r.dt = '2010-01-02' AND r.id = t.id
		   RETURNING *)
DELETE FROM test.tmp USING q;
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Delete on tmp
   CTE q
     ->  Delete on tmp t
           ->  Hash Join
                 Hash Cond: (t.id = r.id)
                 ->  Seq Scan on tmp t
                 ->  Hash
                       ->  Append
                             ->  Index Scan using range_rel_1_pkey on range_rel_1 r
                                   Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
   ->  Nested Loop
         ->  CTE Scan on q
         ->  Materialize
               ->  Seq Scan on tmp
(14 rows)

BEGIN;
WITH q AS (DELETE FROM test.tmp t
		   USING test.range_rel r
		   WHERE r.dt = '2010-01-02' AND r.id = t.id
		   RETURNING *)
DELETE FROM test.tmp USING q;
ROLLBACK;
DROP SCHEMA test CASCADE;
NOTICE:  drop cascades to 15 other objects
DROP EXTENSION pg_pathman CASCADE;
DROP SCHEMA pathman CASCADE;