github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/select_for_update (about) 1 # Cockroach currently supports all of the row locking modes as no-ops, so just 2 # test that they parse and run. 3 query I 4 SELECT 1 FOR UPDATE 5 ---- 6 1 7 8 query I 9 SELECT 1 FOR NO KEY UPDATE 10 ---- 11 1 12 13 query I 14 SELECT 1 FOR SHARE 15 ---- 16 1 17 18 query I 19 SELECT 1 FOR KEY SHARE 20 ---- 21 1 22 23 query I 24 SELECT 1 FOR UPDATE FOR SHARE FOR NO KEY UPDATE FOR KEY SHARE 25 ---- 26 1 27 28 query error pgcode 42P01 relation "a" in FOR UPDATE clause not found in FROM clause 29 SELECT 1 FOR UPDATE OF a 30 31 query error pgcode 42P01 relation "a" in FOR SHARE clause not found in FROM clause 32 SELECT 1 FOR SHARE OF a, b 33 34 query error pgcode 42P01 relation "a" in FOR UPDATE clause not found in FROM clause 35 SELECT 1 FOR UPDATE OF a FOR SHARE OF b, c FOR NO KEY UPDATE OF d FOR KEY SHARE OF e, f 36 37 query I 38 SELECT 1 FROM 39 (SELECT 1) a, 40 (SELECT 1) b, 41 (SELECT 1) c, 42 (SELECT 1) d, 43 (SELECT 1) e, 44 (SELECT 1) f 45 FOR UPDATE OF a FOR SHARE OF b, c FOR NO KEY UPDATE OF d FOR KEY SHARE OF e, f 46 ---- 47 1 48 49 # However, we do mirror Postgres in that we require FOR UPDATE targets to be 50 # unqualified names and reject anything else. 51 52 query error pgcode 42601 FOR UPDATE must specify unqualified relation names 53 SELECT 1 FOR UPDATE OF public.a 54 55 query error pgcode 42601 FOR UPDATE must specify unqualified relation names 56 SELECT 1 FOR UPDATE OF db.public.a 57 58 # We can't support SKIP LOCKED or NOWAIT, since they would actually behave 59 # differently - NOWAIT returns an error to the client instead of blocking, 60 # and SKIP LOCKED returns an inconsistent view. 61 62 query error unimplemented: SKIP LOCKED lock wait policy is not supported 63 SELECT 1 FOR UPDATE SKIP LOCKED 64 65 query error unimplemented: SKIP LOCKED lock wait policy is not supported 66 SELECT 1 FOR NO KEY UPDATE SKIP LOCKED 67 68 query error unimplemented: SKIP LOCKED lock wait policy is not supported 69 SELECT 1 FOR SHARE SKIP LOCKED 70 71 query error unimplemented: SKIP LOCKED lock wait policy is not supported 72 SELECT 1 FOR KEY SHARE SKIP LOCKED 73 74 query error unimplemented: SKIP LOCKED lock wait policy is not supported 75 SELECT 1 FOR UPDATE OF a SKIP LOCKED 76 77 query error unimplemented: SKIP LOCKED lock wait policy is not supported 78 SELECT 1 FOR UPDATE OF a SKIP LOCKED FOR NO KEY UPDATE OF b SKIP LOCKED 79 80 query error unimplemented: SKIP LOCKED lock wait policy is not supported 81 SELECT 1 FOR UPDATE OF a SKIP LOCKED FOR NO KEY UPDATE OF b NOWAIT 82 83 query error unimplemented: NOWAIT lock wait policy is not supported 84 SELECT 1 FOR UPDATE NOWAIT 85 86 query error unimplemented: NOWAIT lock wait policy is not supported 87 SELECT 1 FOR NO KEY UPDATE NOWAIT 88 89 query error unimplemented: NOWAIT lock wait policy is not supported 90 SELECT 1 FOR SHARE NOWAIT 91 92 query error unimplemented: NOWAIT lock wait policy is not supported 93 SELECT 1 FOR KEY SHARE NOWAIT 94 95 query error unimplemented: NOWAIT lock wait policy is not supported 96 SELECT 1 FOR UPDATE OF a NOWAIT 97 98 query error unimplemented: NOWAIT lock wait policy is not supported 99 SELECT 1 FOR UPDATE OF a NOWAIT FOR NO KEY UPDATE OF b NOWAIT 100 101 # Locking clauses both inside and outside of parenthesis are handled correctly. 102 103 query error unimplemented: SKIP LOCKED lock wait policy is not supported 104 ((SELECT 1)) FOR UPDATE SKIP LOCKED 105 106 query error unimplemented: SKIP LOCKED lock wait policy is not supported 107 ((SELECT 1) FOR UPDATE SKIP LOCKED) 108 109 query error unimplemented: SKIP LOCKED lock wait policy is not supported 110 ((SELECT 1 FOR UPDATE SKIP LOCKED)) 111 112 # FOR READ ONLY is ignored, like in Postgres. 113 query I 114 SELECT 1 FOR READ ONLY 115 ---- 116 1 117 118 # Various operations are not supported when locking clauses are provided. 119 # FeatureNotSupported errors are thrown for each of them. 120 121 statement error pgcode 0A000 FOR UPDATE is not allowed with UNION/INTERSECT/EXCEPT 122 SELECT 1 UNION SELECT 1 FOR UPDATE 123 124 statement error pgcode 0A000 FOR UPDATE is not allowed with UNION/INTERSECT/EXCEPT 125 SELECT * FROM (SELECT 1 UNION SELECT 1) a FOR UPDATE 126 127 statement error pgcode 0A000 FOR UPDATE is not allowed with VALUES 128 VALUES (1) FOR UPDATE 129 130 statement error pgcode 0A000 FOR UPDATE is not allowed with VALUES 131 SELECT * FROM (VALUES (1)) a FOR UPDATE 132 133 statement error pgcode 0A000 FOR UPDATE is not allowed with DISTINCT clause 134 SELECT DISTINCT 1 FOR UPDATE 135 136 statement error pgcode 0A000 FOR UPDATE is not allowed with DISTINCT clause 137 SELECT * FROM (SELECT DISTINCT 1) a FOR UPDATE 138 139 statement error pgcode 0A000 FOR UPDATE is not allowed with GROUP BY clause 140 SELECT 1 GROUP BY 1 FOR UPDATE 141 142 statement error pgcode 0A000 FOR UPDATE is not allowed with GROUP BY clause 143 SELECT * FROM (SELECT 1 GROUP BY 1) a FOR UPDATE 144 145 statement error pgcode 0A000 FOR UPDATE is not allowed with HAVING clause 146 SELECT 1 HAVING TRUE FOR UPDATE 147 148 statement error pgcode 0A000 FOR UPDATE is not allowed with HAVING clause 149 SELECT * FROM (SELECT 1 HAVING TRUE) a FOR UPDATE 150 151 statement error pgcode 0A000 FOR UPDATE is not allowed with aggregate functions 152 SELECT count(1) FOR UPDATE 153 154 statement error pgcode 0A000 FOR UPDATE is not allowed with aggregate functions 155 SELECT * FROM (SELECT count(1)) a FOR UPDATE 156 157 statement error pgcode 0A000 FOR UPDATE is not allowed with window functions 158 SELECT count(1) OVER () FOR UPDATE 159 160 statement error pgcode 0A000 FOR UPDATE is not allowed with window functions 161 SELECT * FROM (SELECT count(1) OVER ()) a FOR UPDATE 162 163 statement error pgcode 0A000 FOR UPDATE is not allowed with set-returning functions in the target list 164 SELECT generate_series(1, 2) FOR UPDATE 165 166 statement error pgcode 0A000 FOR UPDATE is not allowed with set-returning functions in the target list 167 SELECT * FROM (SELECT generate_series(1, 2)) a FOR UPDATE 168 169 # Set-returning functions in the from list are allowed. 170 query I 171 SELECT * FROM generate_series(1, 2) FOR UPDATE 172 ---- 173 1 174 2 175 176 query I 177 SELECT * FROM (SELECT * FROM generate_series(1, 2)) a FOR UPDATE 178 ---- 179 1 180 2 181 182 # Use of SELECT FOR UPDATE/SHARE requires UPDATE privileges, not just SELECT privileges. 183 184 statement ok 185 CREATE TABLE t (k INT PRIMARY KEY, v int) 186 187 user testuser 188 189 statement error pgcode 42501 user testuser does not have SELECT privilege on relation t 190 SELECT * FROM t 191 192 user root 193 194 statement ok 195 GRANT SELECT ON t TO testuser 196 197 user testuser 198 199 statement ok 200 SELECT * FROM t 201 202 statement error pgcode 42501 user testuser does not have UPDATE privilege on relation t 203 SELECT * FROM t FOR UPDATE 204 205 statement error pgcode 42501 user testuser does not have UPDATE privilege on relation t 206 SELECT * FROM t FOR SHARE 207 208 user root 209 210 statement ok 211 GRANT UPDATE ON t TO testuser 212 213 user testuser 214 215 statement ok 216 SELECT * FROM t FOR UPDATE 217 218 statement ok 219 SELECT * FROM t FOR SHARE 220 221 user root 222 223 statement ok 224 DROP TABLE t