src/test/regress/sql/tidscan.sql

   1 -- tests for tidscans
   2
   3 CREATE TABLE tidscan(id integer);
   4
   5 -- only insert a few rows, we don't want to spill onto a second table page
   6 INSERT INTO tidscan VALUES (1), (2), (3);
   7
   8 -- show ctids
   9 SELECT ctid, * FROM tidscan;
  10
  11 -- ctid equality - implemented as tidscan
  12 EXPLAIN (COSTS OFF)
  13 SELECT ctid, * FROM tidscan WHERE ctid = '(0,1)';
  14 SELECT ctid, * FROM tidscan WHERE ctid = '(0,1)';
  15
  16 EXPLAIN (COSTS OFF)
  17 SELECT ctid, * FROM tidscan WHERE '(0,1)' = ctid;
  18 SELECT ctid, * FROM tidscan WHERE '(0,1)' = ctid;
  19
  20 -- OR'd clauses
  21 EXPLAIN (COSTS OFF)
  22 SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
  23 SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
  24
  25 -- ctid = ScalarArrayOp - implemented as tidscan
  26 EXPLAIN (COSTS OFF)
  27 SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
  28 SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
  29
  30 -- ctid != ScalarArrayOp - can't be implemented as tidscan
  31 EXPLAIN (COSTS OFF)
  32 SELECT ctid, * FROM tidscan WHERE ctid != ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
  33 SELECT ctid, * FROM tidscan WHERE ctid != ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
  34
  35 -- tid equality extracted from sub-AND clauses
  36 EXPLAIN (COSTS OFF)
  37 SELECT ctid, * FROM tidscan
  38 WHERE (id = 3 AND ctid IN ('(0,2)', '(0,3)')) OR (ctid = '(0,1)' AND id = 1);
  39 SELECT ctid, * FROM tidscan
  40 WHERE (id = 3 AND ctid IN ('(0,2)', '(0,3)')) OR (ctid = '(0,1)' AND id = 1);
  41
  42 -- nestloop-with-inner-tidscan joins on tid
  43 SET enable_hashjoin TO off;  -- otherwise hash join might win
  44 EXPLAIN (COSTS OFF)
  45 SELECT t1.ctid, t1.*, t2.ctid, t2.*
  46 FROM tidscan t1 JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1;
  47 SELECT t1.ctid, t1.*, t2.ctid, t2.*
  48 FROM tidscan t1 JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1;
  49 EXPLAIN (COSTS OFF)
  50 SELECT t1.ctid, t1.*, t2.ctid, t2.*
  51 FROM tidscan t1 LEFT JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1;
  52 SELECT t1.ctid, t1.*, t2.ctid, t2.*
  53 FROM tidscan t1 LEFT JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1;
  54 RESET enable_hashjoin;
  55
  56 -- exercise backward scan and rewind
  57 BEGIN;
  58 DECLARE c CURSOR FOR
  59 SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
  60 FETCH ALL FROM c;
  61 FETCH BACKWARD 1 FROM c;
  62 FETCH FIRST FROM c;
  63 ROLLBACK;
  64
  65 -- tidscan via CURRENT OF
  66 BEGIN;
  67 DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan;
  68 FETCH NEXT FROM c; -- skip one row
  69 FETCH NEXT FROM c;
  70 -- perform update
  71 EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
  72 UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
  73 FETCH NEXT FROM c;
  74 -- perform update
  75 EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
  76 UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
  77 SELECT * FROM tidscan;
  78 -- position cursor past any rows
  79 FETCH NEXT FROM c;
  80 -- should error out
  81 EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
  82 UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
  83 ROLLBACK;
  84
  85 -- bulk joins on CTID
  86 -- (these plans don't use TID scans, but this still seems like an
  87 -- appropriate place for these tests)
  88 EXPLAIN (COSTS OFF)
  89 SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid;
  90 SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid;
  91 SET enable_hashjoin TO off;
  92 EXPLAIN (COSTS OFF)
  93 SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid;
  94 SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid;
  95 RESET enable_hashjoin;
  96
  97 -- check predicate lock on CTID
  98 BEGIN ISOLATION LEVEL SERIALIZABLE;
  99 SELECT * FROM tidscan WHERE ctid = '(0,1)';
 100 -- locktype should be 'tuple'
 101 SELECT locktype, mode FROM pg_locks WHERE pid = pg_backend_pid() AND mode = 'SIReadLock';
 102 ROLLBACK;
 103
 104 DROP TABLE tidscan;