/*
 * -------------------------------------------
 *  NOTE: This test behaves differenly on 9.5
 * -------------------------------------------
 */
SET search_path = 'public';
CREATE EXTENSION pg_pathman;
CREATE SCHEMA rowmarks;
CREATE TABLE rowmarks.first(id int NOT NULL);
CREATE TABLE rowmarks.second(id int NOT NULL);
INSERT INTO rowmarks.first SELECT generate_series(1, 10);
INSERT INTO rowmarks.second SELECT generate_series(1, 10);
SELECT create_hash_partitions('rowmarks.first', 'id', 5);
 create_hash_partitions 
------------------------
                      5
(1 row)

VACUUM ANALYZE;
/* Not partitioned */
SELECT * FROM rowmarks.second ORDER BY id FOR UPDATE;
 id 
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

/* Simple case (plan) */
EXPLAIN (COSTS OFF)
SELECT * FROM rowmarks.first ORDER BY id FOR UPDATE;
              QUERY PLAN               
---------------------------------------
 LockRows
   ->  Sort
         Sort Key: first_0.id
         ->  Append
               ->  Seq Scan on first_0
               ->  Seq Scan on first_1
               ->  Seq Scan on first_2
               ->  Seq Scan on first_3
               ->  Seq Scan on first_4
(9 rows)

/* Simple case (execution) */
SELECT * FROM rowmarks.first ORDER BY id FOR UPDATE;
 id 
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

SELECT FROM rowmarks.first ORDER BY id FOR UPDATE;
--
(10 rows)

SELECT tableoid > 0 FROM rowmarks.first ORDER BY id FOR UPDATE;
 ?column? 
----------
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
(10 rows)

/* A little harder (plan) */
EXPLAIN (COSTS OFF)
SELECT * FROM rowmarks.first
WHERE id = (SELECT id FROM rowmarks.first
			ORDER BY id
			OFFSET 10 LIMIT 1
			FOR UPDATE)
FOR SHARE;
                     QUERY PLAN                      
-----------------------------------------------------
 LockRows
   InitPlan 1 (returns $1)
     ->  Limit
           ->  LockRows
                 ->  Sort
                       Sort Key: first_0.id
                       ->  Append
                             ->  Seq Scan on first_0
                             ->  Seq Scan on first_1
                             ->  Seq Scan on first_2
                             ->  Seq Scan on first_3
                             ->  Seq Scan on first_4
   ->  Custom Scan (RuntimeAppend)
         Prune by: (first.id = $1)
         ->  Seq Scan on first_0 first
               Filter: (id = $1)
         ->  Seq Scan on first_1 first
               Filter: (id = $1)
         ->  Seq Scan on first_2 first
               Filter: (id = $1)
         ->  Seq Scan on first_3 first
               Filter: (id = $1)
         ->  Seq Scan on first_4 first
               Filter: (id = $1)
(24 rows)

/* A little harder (execution) */
SELECT * FROM rowmarks.first
WHERE id = (SELECT id FROM rowmarks.first
			ORDER BY id
			OFFSET 5 LIMIT 1
			FOR UPDATE)
FOR SHARE;
 id 
----
  6
(1 row)

/* Two tables (plan) */
EXPLAIN (COSTS OFF)
SELECT * FROM rowmarks.first
WHERE id = (SELECT id FROM rowmarks.second
			ORDER BY id
			OFFSET 5 LIMIT 1
			FOR UPDATE)
FOR SHARE;
                  QUERY PLAN                  
----------------------------------------------
 LockRows
   InitPlan 1 (returns $1)
     ->  Limit
           ->  LockRows
                 ->  Sort
                       Sort Key: second.id
                       ->  Seq Scan on second
   ->  Custom Scan (RuntimeAppend)
         Prune by: (first.id = $1)
         ->  Seq Scan on first_0 first
               Filter: (id = $1)
         ->  Seq Scan on first_1 first
               Filter: (id = $1)
         ->  Seq Scan on first_2 first
               Filter: (id = $1)
         ->  Seq Scan on first_3 first
               Filter: (id = $1)
         ->  Seq Scan on first_4 first
               Filter: (id = $1)
(19 rows)

/* Two tables (execution) */
SELECT * FROM rowmarks.first
WHERE id = (SELECT id FROM rowmarks.second
			ORDER BY id
			OFFSET 5 LIMIT 1
			FOR UPDATE)
FOR SHARE;
 id 
----
  6
(1 row)

/* JOIN (plan) */
EXPLAIN (COSTS OFF)
SELECT * FROM rowmarks.first
JOIN rowmarks.second USING(id)
ORDER BY id
FOR UPDATE;
                    QUERY PLAN                     
---------------------------------------------------
 LockRows
   ->  Sort
         Sort Key: first_0.id
         ->  Hash Join
               Hash Cond: (first_0.id = second.id)
               ->  Append
                     ->  Seq Scan on first_0
                     ->  Seq Scan on first_1
                     ->  Seq Scan on first_2
                     ->  Seq Scan on first_3
                     ->  Seq Scan on first_4
               ->  Hash
                     ->  Seq Scan on second
(13 rows)

/* JOIN (execution) */
SELECT * FROM rowmarks.first
JOIN rowmarks.second USING(id)
ORDER BY id
FOR UPDATE;
 id 
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

/* ONLY (plan) */
EXPLAIN (COSTS OFF)
SELECT * FROM ONLY rowmarks.first FOR SHARE;
       QUERY PLAN        
-------------------------
 LockRows
   ->  Seq Scan on first
(2 rows)

/* ONLY (execution) */
SELECT * FROM ONLY rowmarks.first FOR SHARE;
 id 
----
(0 rows)

/* Check updates (plan) */
SET enable_hashjoin = f;	/* Hash Semi Join on 10 vs Hash Join on 9.6 */
SET enable_mergejoin = f;	/* Merge Semi Join on 10 vs Merge Join on 9.6 */
EXPLAIN (COSTS OFF)
UPDATE rowmarks.second SET id = 2
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1);
              QUERY PLAN               
---------------------------------------
 Update on second
   ->  Nested Loop Semi Join
         ->  Seq Scan on second
               Filter: (id = 1)
         ->  Append
               ->  Seq Scan on first_0
                     Filter: (id = 1)
(7 rows)

EXPLAIN (COSTS OFF)
UPDATE rowmarks.second SET id = 2
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id < 1);
                  QUERY PLAN                   
-----------------------------------------------
 Update on second
   ->  Nested Loop Semi Join
         Join Filter: (second.id = first_0.id)
         ->  Seq Scan on second
         ->  Materialize
               ->  Append
                     ->  Seq Scan on first_0
                           Filter: (id < 1)
                     ->  Seq Scan on first_1
                           Filter: (id < 1)
                     ->  Seq Scan on first_2
                           Filter: (id < 1)
                     ->  Seq Scan on first_3
                           Filter: (id < 1)
                     ->  Seq Scan on first_4
                           Filter: (id < 1)
(16 rows)

EXPLAIN (COSTS OFF)
UPDATE rowmarks.second SET id = 2
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1 OR id = 2);
                  QUERY PLAN                   
-----------------------------------------------
 Update on second
   ->  Nested Loop Semi Join
         Join Filter: (second.id = first_0.id)
         ->  Seq Scan on second
         ->  Materialize
               ->  Append
                     ->  Seq Scan on first_0
                           Filter: (id = 1)
                     ->  Seq Scan on first_1
                           Filter: (id = 2)
(10 rows)

EXPLAIN (COSTS OFF)
UPDATE rowmarks.second SET id = 2
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1)
RETURNING *, tableoid::regclass;
              QUERY PLAN               
---------------------------------------
 Update on second
   ->  Nested Loop Semi Join
         ->  Seq Scan on second
               Filter: (id = 1)
         ->  Append
               ->  Seq Scan on first_0
                     Filter: (id = 1)
(7 rows)

SET enable_hashjoin = t;
SET enable_mergejoin = t;
/* Check updates (execution) */
UPDATE rowmarks.second SET id = 1
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1 OR id = 2)
RETURNING *, tableoid::regclass;
 id |    tableoid     
----+-----------------
  1 | rowmarks.second
  1 | rowmarks.second
(2 rows)

/* Check deletes (plan) */
SET enable_hashjoin = f;	/* Hash Semi Join on 10 vs Hash Join on 9.6 */
SET enable_mergejoin = f;	/* Merge Semi Join on 10 vs Merge Join on 9.6 */
EXPLAIN (COSTS OFF)
DELETE FROM rowmarks.second
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1);
              QUERY PLAN               
---------------------------------------
 Delete on second
   ->  Nested Loop Semi Join
         ->  Seq Scan on second
               Filter: (id = 1)
         ->  Append
               ->  Seq Scan on first_0
                     Filter: (id = 1)
(7 rows)

EXPLAIN (COSTS OFF)
DELETE FROM rowmarks.second
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id < 1);
                  QUERY PLAN                   
-----------------------------------------------
 Delete on second
   ->  Nested Loop Semi Join
         Join Filter: (second.id = first_0.id)
         ->  Seq Scan on second
         ->  Materialize
               ->  Append
                     ->  Seq Scan on first_0
                           Filter: (id < 1)
                     ->  Seq Scan on first_1
                           Filter: (id < 1)
                     ->  Seq Scan on first_2
                           Filter: (id < 1)
                     ->  Seq Scan on first_3
                           Filter: (id < 1)
                     ->  Seq Scan on first_4
                           Filter: (id < 1)
(16 rows)

EXPLAIN (COSTS OFF)
DELETE FROM rowmarks.second
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1 OR id = 2);
                  QUERY PLAN                   
-----------------------------------------------
 Delete on second
   ->  Nested Loop Semi Join
         Join Filter: (second.id = first_0.id)
         ->  Seq Scan on second
         ->  Materialize
               ->  Append
                     ->  Seq Scan on first_0
                           Filter: (id = 1)
                     ->  Seq Scan on first_1
                           Filter: (id = 2)
(10 rows)

SET enable_hashjoin = t;
SET enable_mergejoin = t;
DROP SCHEMA rowmarks CASCADE;
NOTICE:  drop cascades to 7 other objects
DETAIL:  drop cascades to table rowmarks.first
drop cascades to table rowmarks.second
drop cascades to table rowmarks.first_0
drop cascades to table rowmarks.first_1
drop cascades to table rowmarks.first_2
drop cascades to table rowmarks.first_3
drop cascades to table rowmarks.first_4
DROP EXTENSION pg_pathman;