path: root/src/test/regress/expected/tidscan.out

-- tests for tidscans
CREATE TABLE tidscan(id integer);
-- only insert a few rows, we don't want to spill onto a second table page
INSERT INTO tidscan VALUES (1), (2), (3);
-- show ctids
SELECT ctid, * FROM tidscan;
 ctid  | id 
-------+----
 (0,1) |  1
 (0,2) |  2
 (0,3) |  3
(3 rows)

-- ctid equality - implemented as tidscan
EXPLAIN (COSTS OFF)
SELECT ctid, * FROM tidscan WHERE ctid = '(0,1)';
            QUERY PLAN             
-----------------------------------
 Tid Scan on tidscan
   TID Cond: (ctid = '(0,1)'::tid)
(2 rows)

SELECT ctid, * FROM tidscan WHERE ctid = '(0,1)';
 ctid  | id 
-------+----
 (0,1) |  1
(1 row)

EXPLAIN (COSTS OFF)
SELECT ctid, * FROM tidscan WHERE '(0,1)' = ctid;
            QUERY PLAN             
-----------------------------------
 Tid Scan on tidscan
   TID Cond: ('(0,1)'::tid = ctid)
(2 rows)

SELECT ctid, * FROM tidscan WHERE '(0,1)' = ctid;
 ctid  | id 
-------+----
 (0,1) |  1
(1 row)

-- OR'd clauses
EXPLAIN (COSTS OFF)
SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
                          QUERY PLAN                          
--------------------------------------------------------------
 Tid Scan on tidscan
   TID Cond: ((ctid = '(0,2)'::tid) OR ('(0,1)'::tid = ctid))
(2 rows)

SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
 ctid  | id 
-------+----
 (0,1) |  1
 (0,2) |  2
(2 rows)

-- ctid = ScalarArrayOp - implemented as tidscan
EXPLAIN (COSTS OFF)
SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
                      QUERY PLAN                       
-------------------------------------------------------
 Tid Scan on tidscan
   TID Cond: (ctid = ANY ('{"(0,1)","(0,2)"}'::tid[]))
(2 rows)

SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
 ctid  | id 
-------+----
 (0,1) |  1
 (0,2) |  2
(2 rows)

-- ctid != ScalarArrayOp - can't be implemented as tidscan
EXPLAIN (COSTS OFF)
SELECT ctid, * FROM tidscan WHERE ctid != ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on tidscan
   Filter: (ctid <> ANY ('{"(0,1)","(0,2)"}'::tid[]))
(2 rows)

SELECT ctid, * FROM tidscan WHERE ctid != ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
 ctid  | id 
-------+----
 (0,1) |  1
 (0,2) |  2
 (0,3) |  3
(3 rows)

-- tid equality extracted from sub-AND clauses
EXPLAIN (COSTS OFF)
SELECT ctid, * FROM tidscan
WHERE (id = 3 AND ctid IN ('(0,2)', '(0,3)')) OR (ctid = '(0,1)' AND id = 1);
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Tid Scan on tidscan
   TID Cond: ((ctid = ANY ('{"(0,2)","(0,3)"}'::tid[])) OR (ctid = '(0,1)'::tid))
   Filter: (((id = 3) AND (ctid = ANY ('{"(0,2)","(0,3)"}'::tid[]))) OR ((ctid = '(0,1)'::tid) AND (id = 1)))
(3 rows)

SELECT ctid, * FROM tidscan
WHERE (id = 3 AND ctid IN ('(0,2)', '(0,3)')) OR (ctid = '(0,1)' AND id = 1);
 ctid  | id 
-------+----
 (0,1) |  1
 (0,3) |  3
(2 rows)

-- nestloop-with-inner-tidscan joins on tid
SET enable_hashjoin TO off;  -- otherwise hash join might win
EXPLAIN (COSTS OFF)
SELECT t1.ctid, t1.*, t2.ctid, t2.*
FROM tidscan t1 JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1;
             QUERY PLAN             
------------------------------------
 Nested Loop
   ->  Seq Scan on tidscan t1
         Filter: (id = 1)
   ->  Tid Scan on tidscan t2
         TID Cond: (t1.ctid = ctid)
(5 rows)

SELECT t1.ctid, t1.*, t2.ctid, t2.*
FROM tidscan t1 JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1;
 ctid  | id | ctid  | id 
-------+----+-------+----
 (0,1) |  1 | (0,1) |  1
(1 row)

EXPLAIN (COSTS OFF)
SELECT t1.ctid, t1.*, t2.ctid, t2.*
FROM tidscan t1 LEFT JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1;
             QUERY PLAN             
------------------------------------
 Nested Loop Left Join
   ->  Seq Scan on tidscan t1
         Filter: (id = 1)
   ->  Tid Scan on tidscan t2
         TID Cond: (t1.ctid = ctid)
(5 rows)

SELECT t1.ctid, t1.*, t2.ctid, t2.*
FROM tidscan t1 LEFT JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1;
 ctid  | id | ctid  | id 
-------+----+-------+----
 (0,1) |  1 | (0,1) |  1
(1 row)

RESET enable_hashjoin;
-- exercise backward scan and rewind
BEGIN;
DECLARE c CURSOR FOR
SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
FETCH ALL FROM c;
 ctid  | id 
-------+----
 (0,1) |  1
 (0,2) |  2
(2 rows)

FETCH BACKWARD 1 FROM c;
 ctid  | id 
-------+----
 (0,2) |  2
(1 row)

FETCH FIRST FROM c;
 ctid  | id 
-------+----
 (0,1) |  1
(1 row)

ROLLBACK;
-- tidscan via CURRENT OF
BEGIN;
DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan;
FETCH NEXT FROM c; -- skip one row
 ctid  | id 
-------+----
 (0,1) |  1
(1 row)

FETCH NEXT FROM c;
 ctid  | id 
-------+----
 (0,2) |  2
(1 row)

-- perform update
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
                    QUERY PLAN                     
---------------------------------------------------
 Update on tidscan (actual rows=1 loops=1)
   ->  Tid Scan on tidscan (actual rows=1 loops=1)
         TID Cond: CURRENT OF c
(3 rows)

FETCH NEXT FROM c;
 ctid  | id 
-------+----
 (0,3) |  3
(1 row)

-- perform update
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
                    QUERY PLAN                     
---------------------------------------------------
 Update on tidscan (actual rows=1 loops=1)
   ->  Tid Scan on tidscan (actual rows=1 loops=1)
         TID Cond: CURRENT OF c
(3 rows)

SELECT * FROM tidscan;
 id 
----
  1
 -2
 -3
(3 rows)

-- position cursor past any rows
FETCH NEXT FROM c;
 ctid | id 
------+----
(0 rows)

-- should error out
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
ERROR:  cursor "c" is not positioned on a row
ROLLBACK;
-- bulk joins on CTID
-- (these plans don't use TID scans, but this still seems like an
-- appropriate place for these tests)
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid;
               QUERY PLAN               
----------------------------------------
 Aggregate
   ->  Hash Join
         Hash Cond: (t1.ctid = t2.ctid)
         ->  Seq Scan on tenk1 t1
         ->  Hash
               ->  Seq Scan on tenk1 t2
(6 rows)

SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid;
 count 
-------
 10000
(1 row)

SET enable_hashjoin TO off;
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid;
               QUERY PLAN                
-----------------------------------------
 Aggregate
   ->  Merge Join
         Merge Cond: (t1.ctid = t2.ctid)
         ->  Sort
               Sort Key: t1.ctid
               ->  Seq Scan on tenk1 t1
         ->  Sort
               Sort Key: t2.ctid
               ->  Seq Scan on tenk1 t2
(9 rows)

SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid;
 count 
-------
 10000
(1 row)

RESET enable_hashjoin;
-- check predicate lock on CTID
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM tidscan WHERE ctid = '(0,1)';
 id 
----
  1
(1 row)

-- locktype should be 'tuple'
SELECT locktype, mode FROM pg_locks WHERE pid = pg_backend_pid() AND mode = 'SIReadLock';
 locktype |    mode    
----------+------------
 tuple    | SIReadLock
(1 row)

ROLLBACK;
DROP TABLE tidscan;