select_for_update

query I
SELECT 1 FOR UPDATE
----
1

query I
SELECT 1 FOR NO KEY UPDATE
----
1

query I
SELECT 1 FOR SHARE
----
1

query I
SELECT 1 FOR KEY SHARE
----
1

query I
SELECT 1 FOR UPDATE FOR SHARE FOR NO KEY UPDATE FOR KEY SHARE
----
1

query error pgcode 42P01 relation "a" in FOR UPDATE clause not found in FROM clause
SELECT 1 FOR UPDATE OF a

query error pgcode 42P01 relation "a" in FOR SHARE clause not found in FROM clause
SELECT 1 FOR SHARE OF a, b

query error pgcode 42P01 relation "a" in FOR UPDATE clause not found in FROM clause
SELECT 1 FOR UPDATE OF a FOR SHARE OF b, c FOR NO KEY UPDATE OF d FOR KEY SHARE OF e, f

query I
SELECT 1 FROM
    (SELECT 1) a,
    (SELECT 1) b,
    (SELECT 1) c,
    (SELECT 1) d,
    (SELECT 1) e,
    (SELECT 1) f
FOR UPDATE OF a FOR SHARE OF b, c FOR NO KEY UPDATE OF d FOR KEY SHARE OF e, f
----
1

# However, we do mirror Postgres in that we require FOR UPDATE targets to be
# unqualified names and reject anything else.

query error pgcode 42601 FOR UPDATE must specify unqualified relation names
SELECT 1 FOR UPDATE OF public.a

query error pgcode 42601 FOR UPDATE must specify unqualified relation names
SELECT 1 FOR UPDATE OF db.public.a

query I
SELECT 1 FOR UPDATE SKIP LOCKED
----
1

query I
SELECT 1 FOR NO KEY UPDATE SKIP LOCKED
----
1

query I
SELECT 1 FOR SHARE SKIP LOCKED
----
1

query I
SELECT 1 FOR KEY SHARE SKIP LOCKED
----
1

query error pgcode 42P01 relation "a" in FOR UPDATE clause not found in FROM clause
SELECT 1 FOR UPDATE OF a SKIP LOCKED

query error pgcode 42P01 relation "a" in FOR UPDATE clause not found in FROM clause
SELECT 1 FOR UPDATE OF a SKIP LOCKED FOR NO KEY UPDATE OF b SKIP LOCKED

query error pgcode 42P01 relation "a" in FOR UPDATE clause not found in FROM clause
SELECT 1 FOR UPDATE OF a SKIP LOCKED FOR NO KEY UPDATE OF b NOWAIT

query error pgcode 42P01 relation "a" in FOR UPDATE clause not found in FROM clause
SELECT 1 FOR UPDATE OF a SKIP LOCKED FOR SHARE OF b, c SKIP LOCKED FOR NO KEY UPDATE OF d SKIP LOCKED FOR KEY SHARE OF e, f SKIP LOCKED

query I
SELECT 1 FOR UPDATE NOWAIT
----
1

query I
SELECT 1 FOR NO KEY UPDATE NOWAIT
----
1

query I
SELECT 1 FOR SHARE NOWAIT
----
1

query I
SELECT 1 FOR KEY SHARE NOWAIT
----
1

query error pgcode 42P01 relation "a" in FOR UPDATE clause not found in FROM clause
SELECT 1 FOR UPDATE OF a NOWAIT

query error pgcode 42P01 relation "a" in FOR UPDATE clause not found in FROM clause
SELECT 1 FOR UPDATE OF a NOWAIT FOR NO KEY UPDATE OF b NOWAIT

query error pgcode 42P01 relation "a" in FOR UPDATE clause not found in FROM clause
SELECT 1 FOR UPDATE OF a NOWAIT FOR SHARE OF b, c NOWAIT FOR NO KEY UPDATE OF d NOWAIT FOR KEY SHARE OF e, f NOWAIT

# Locking clauses both inside and outside of parenthesis are handled correctly.

query I
((SELECT 1)) FOR UPDATE SKIP LOCKED
----
1

query I
((SELECT 1) FOR UPDATE SKIP LOCKED)
----
1

query I
((SELECT 1 FOR UPDATE SKIP LOCKED))
----
1

# FOR READ ONLY is ignored, like in Postgres.
query I
SELECT 1 FOR READ ONLY
----
1

# Various operations are not supported when locking clauses are provided.
# FeatureNotSupported errors are thrown for each of them.

statement error pgcode 0A000 FOR UPDATE is not allowed with UNION/INTERSECT/EXCEPT
SELECT 1 UNION SELECT 1 FOR UPDATE

statement error pgcode 0A000 FOR UPDATE is not allowed with UNION/INTERSECT/EXCEPT
SELECT * FROM (SELECT 1 UNION SELECT 1) a FOR UPDATE

statement error pgcode 0A000 FOR UPDATE is not allowed with VALUES
VALUES (1) FOR UPDATE

statement error pgcode 0A000 FOR UPDATE is not allowed with VALUES
SELECT * FROM (VALUES (1)) a FOR UPDATE

statement error pgcode 0A000 FOR UPDATE is not allowed with DISTINCT clause
SELECT DISTINCT 1 FOR UPDATE

statement error pgcode 0A000 FOR UPDATE is not allowed with DISTINCT clause
SELECT * FROM (SELECT DISTINCT 1) a FOR UPDATE

statement error pgcode 0A000 FOR UPDATE is not allowed with GROUP BY clause
SELECT 1 GROUP BY 1 FOR UPDATE

statement error pgcode 0A000 FOR UPDATE is not allowed with GROUP BY clause
SELECT * FROM (SELECT 1 GROUP BY 1) a FOR UPDATE

statement error pgcode 0A000 FOR UPDATE is not allowed with HAVING clause
SELECT 1 HAVING TRUE FOR UPDATE

statement error pgcode 0A000 FOR UPDATE is not allowed with HAVING clause
SELECT * FROM (SELECT 1 HAVING TRUE) a FOR UPDATE

statement error pgcode 0A000 FOR UPDATE is not allowed with aggregate functions
SELECT count(1) FOR UPDATE

statement error pgcode 0A000 FOR UPDATE is not allowed with aggregate functions
SELECT * FROM (SELECT count(1)) a FOR UPDATE

statement error pgcode 0A000 FOR UPDATE is not allowed with window functions
SELECT count(1) OVER () FOR UPDATE

statement error pgcode 0A000 FOR UPDATE is not allowed with window functions
SELECT * FROM (SELECT count(1) OVER ()) a FOR UPDATE

statement error pgcode 0A000 FOR UPDATE is not allowed with set-returning functions in the target list
SELECT generate_series(1, 2) FOR UPDATE

statement error pgcode 0A000 FOR UPDATE is not allowed with set-returning functions in the target list
SELECT * FROM (SELECT generate_series(1, 2)) a FOR UPDATE

# Set-returning functions in the from list are allowed.
query I
SELECT * FROM generate_series(1, 2) FOR UPDATE
----
1
2

query I
SELECT * FROM (SELECT * FROM generate_series(1, 2)) a FOR UPDATE
----
1
2

# Use of SELECT FOR UPDATE/SHARE requires SELECT and UPDATE privileges.

statement ok
CREATE TABLE t (k INT PRIMARY KEY, v int, FAMILY (k, v))

user testuser

statement error pgcode 42501 user testuser does not have SELECT privilege on relation t
SELECT * FROM t

statement error pgcode 42501 user testuser does not have SELECT privilege on relation t
SELECT * FROM t FOR UPDATE

statement error pgcode 42501 user testuser does not have SELECT privilege on relation t
SELECT * FROM t FOR SHARE

user root

statement ok
GRANT SELECT ON t TO testuser

user testuser

statement ok
SELECT * FROM t

statement error pgcode 42501 user testuser does not have UPDATE privilege on relation t
SELECT * FROM t FOR UPDATE

statement error pgcode 42501 user testuser does not have UPDATE privilege on relation t
SELECT * FROM t FOR SHARE

user root

statement ok
REVOKE SELECT ON t FROM testuser

statement ok
GRANT UPDATE ON t TO testuser

user testuser

statement error pgcode 42501 user testuser does not have SELECT privilege on relation t
SELECT * FROM t

statement error pgcode 42501 user testuser does not have SELECT privilege on relation t
SELECT * FROM t FOR UPDATE

statement error pgcode 42501 user testuser does not have SELECT privilege on relation t
SELECT * FROM t FOR SHARE

user root

statement ok
GRANT SELECT ON t TO testuser

user testuser

statement ok
SELECT * FROM t

statement ok
SELECT * FROM t FOR UPDATE

statement ok
SELECT * FROM t FOR SHARE

user root

# Use of SELECT FOR UPDATE/SHARE in ReadOnly Transaction

statement ok
BEGIN READ ONLY

statement error cannot execute FOR UPDATE in a read-only transaction
SELECT * FROM t FOR UPDATE

statement ok
ROLLBACK

statement ok
BEGIN READ ONLY

statement error cannot execute FOR NO KEY UPDATE in a read-only transaction
SELECT * FROM t FOR NO KEY UPDATE

statement ok
ROLLBACK

statement ok
BEGIN READ ONLY

statement error cannot execute FOR SHARE in a read-only transaction
SELECT * FROM t FOR SHARE

statement ok
ROLLBACK

statement ok
BEGIN READ ONLY

statement error cannot execute FOR KEY SHARE in a read-only transaction
SELECT * FROM t FOR KEY SHARE

statement ok
ROLLBACK

# The NOWAIT wait policy returns error when a conflicting lock is encountered.

statement ok
INSERT INTO t VALUES (1, 1)

statement ok
BEGIN; UPDATE t SET v = 2 WHERE k = 1

user testuser

query error pgcode 55P03 could not obtain lock on row \(k\)=\(1\) in t@t_pkey
SELECT * FROM t FOR UPDATE NOWAIT

query error pgcode 55P03 could not obtain lock on row \(k\)=\(1\) in t@t_pkey
SELECT * FROM t FOR SHARE FOR UPDATE OF t NOWAIT

query error pgcode 55P03 could not obtain lock on row \(k\)=\(1\) in t@t_pkey
SELECT * FROM t FOR SHARE NOWAIT FOR UPDATE OF t

query error pgcode 55P03 could not obtain lock on row \(k\)=\(1\) in t@t_pkey
BEGIN; SELECT * FROM t FOR UPDATE NOWAIT

statement ok
ROLLBACK

user root

statement ok
ROLLBACK

# The NOWAIT wait policy can be applied to a subset of the tables being locked.

statement ok
CREATE TABLE t2 (k INT PRIMARY KEY, v2 int)

statement ok
GRANT SELECT ON t2 TO testuser

statement ok
GRANT UPDATE ON t2 TO testuser

statement ok
INSERT INTO t2 VALUES (1, 11)

statement ok
BEGIN; UPDATE t SET v = 2 WHERE k = 1

user testuser

query error pgcode 55P03 could not obtain lock on row \(k\)=\(1\) in t@t_pkey
SELECT v, v2 FROM t JOIN t2 USING (k) FOR SHARE FOR SHARE OF t NOWAIT

query error pgcode 55P03 could not obtain lock on row \(k\)=\(1\) in t@t_pkey
SELECT v, v2 FROM t JOIN t2 USING (k) FOR SHARE OF t2 FOR SHARE OF t NOWAIT

query error pgcode 55P03 could not obtain lock on row \(k\)=\(1\) in t@t_pkey
SELECT v, v2 FROM t JOIN t2 USING (k) FOR SHARE NOWAIT FOR SHARE OF t

query error pgcode 55P03 could not obtain lock on row \(k\)=\(1\) in t@t_pkey
SELECT v, v2 FROM t JOIN t2 USING (k) FOR SHARE NOWAIT FOR SHARE OF t2

statement ok
SET statement_timeout = '10ms'

query error pgcode 57014 query execution canceled due to statement timeout
SELECT v, v2 FROM t JOIN t2 USING (k) FOR SHARE FOR SHARE OF t2 NOWAIT

query error pgcode 57014 query execution canceled due to statement timeout
SELECT v, v2 FROM t JOIN t2 USING (k) FOR SHARE OF t FOR SHARE OF t2 NOWAIT

statement ok
SET statement_timeout = 0

user root

statement ok
ROLLBACK

# The SKIP LOCKED wait policy skip rows when a conflicting lock is encountered.

statement ok
INSERT INTO t VALUES (2, 2), (3, 3), (4, 4)

statement ok
CREATE TABLE t3 (
  k INT PRIMARY KEY,
  v INT,
  u INT,
  INDEX (u),
  FAMILY (k, v, u)
);
INSERT INTO t3 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 2);
GRANT SELECT ON t3 TO testuser;
GRANT UPDATE ON t3 TO testuser

statement ok
BEGIN; UPDATE t SET v = 3 WHERE k = 2; UPDATE t3 SET v = 3 WHERE k = 2

user testuser

statement ok
BEGIN

query II
SELECT * FROM t FOR UPDATE SKIP LOCKED
----
1  1
3  3
4  4

statement ok
UPDATE t SET v = 4 WHERE k = 3

query II
SELECT * FROM t FOR UPDATE SKIP LOCKED
----
1  1
3  4
4  4

# All columns are available from the secondary index on u, so no index join is
# needed. The secondary index can produce the row where k=2 since the row is
# only locked in the primary index.
query II
SELECT k, u FROM t3 WHERE u = 2 FOR UPDATE SKIP LOCKED
----
2  2
4  2

# An index join is needed to fetch column v. The index join filters out the
# first row, which is locked.
query III
SELECT * FROM t3 WHERE u = 2 FOR UPDATE SKIP LOCKED
----
4  4  2

# Since the limit is not pushed below the index join, we still see the second row.
query III
SELECT * FROM t3 WHERE u = 2 LIMIT 1 FOR UPDATE SKIP LOCKED
----
4  4  2

user root

query II
SELECT * FROM t FOR UPDATE SKIP LOCKED
----
2  3

statement ok
ROLLBACK

user testuser

statement ok
ROLLBACK

user root

# Regression test for not propagating lock spans with leaf txns (#94290).
statement ok
CREATE TABLE t94290 (a INT, b INT, c INT, PRIMARY KEY(a), UNIQUE INDEX(b));
INSERT INTO t94290 VALUES (1,2,3);

# If the lock spans are not propagated, then the second query would take almost
# 5 seconds.
statement ok
SET statement_timeout = '2s';

statement ok
SELECT * FROM t94290 WHERE b = 2 FOR UPDATE;

statement ok
SELECT * FROM t94290 WHERE b = 2 FOR UPDATE;

statement ok
RESET statement_timeout;

# SELECT FOR UPDATE is prohibited under weaker isolation levels until we improve
# locking. See #57031, #75457, #100144.

statement ok
CREATE TABLE supermarket (
  person STRING PRIMARY KEY,
  aisle INT NOT NULL,
  starts_with STRING GENERATED ALWAYS AS (left(person, 1)) STORED,
  ends_with STRING GENERATED ALWAYS AS (right(person, 3)) STORED,
  INDEX (starts_with),
  INDEX (ends_with)
)

statement ok
INSERT INTO supermarket (person, aisle)
  VALUES ('abbie', 1), ('gideon', 2), ('matilda', 3), ('michael', 4)

# SELECT FOR UPDATE should still work under serializable isolation.
statement ok
BEGIN

query I
SELECT aisle FROM supermarket WHERE person = 'gideon' FOR UPDATE
----
2

statement ok
UPDATE supermarket SET aisle = 2 WHERE person = 'abbie'

statement ok
COMMIT

# It should fail under read committed isolation.
statement ok
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED

query error pgcode 0A000 cannot execute SELECT FOR UPDATE statements under ReadCommitted isolation
SELECT aisle FROM supermarket WHERE person = 'matilda' FOR UPDATE

statement ok
ROLLBACK

# It should also fail under snapshot isolation. TODO(michae2): uncomment after #103488 is merged.
# statement ok
# SET CLUSTER SETTING sql.txn.snapshot_isolation.enabled = true
#
# statement ok
# BEGIN TRANSACTION ISOLATION LEVEL SNAPSHOT
#
# query error pgcode 0A000 cannot execute SELECT FOR UPDATE statements under Snapshot isolation
# SELECT aisle FROM supermarket WHERE person = 'matilda' FOR UPDATE
#
# statement ok
# ROLLBACK
#
# statement ok
# RESET CLUSTER SETTING sql.txn.snapshot_isolation.enabled

# SELECT FOR UPDATE in a subquery should also fail under read committed.
statement ok
BEGIN TRANSACTION

statement ok
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

query error pgcode 0A000 cannot execute SELECT FOR UPDATE statements under ReadCommitted isolation
UPDATE supermarket
  SET aisle = (SELECT aisle FROM supermarket WHERE person = 'matilda' FOR UPDATE)
  WHERE person = 'michael'

statement ok
ROLLBACK

# It should also fail in a CTE.
statement ok
BEGIN TRANSACTION

statement ok
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

query error pgcode 0A000 cannot execute SELECT FOR UPDATE statements under ReadCommitted isolation
WITH s AS
  (SELECT aisle FROM supermarket WHERE person = 'matilda' FOR UPDATE)
SELECT aisle + 1 FROM s

statement ok
ROLLBACK

statement ok
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED

# Creating a UDF using SELECT FOR UPDATE should succeed under read committed.
statement ok
CREATE FUNCTION wrangle (name STRING) RETURNS INT LANGUAGE SQL AS $$
  SELECT aisle FROM supermarket WHERE person = name FOR UPDATE
$$

# But calling that function should fail.
query error pgcode 0A000 cannot execute SELECT FOR UPDATE statements under ReadCommitted isolation
INSERT INTO supermarket (person, aisle) VALUES ('grandma', wrangle('matilda'))

statement ok
DROP FUNCTION wrangle

# Preparing a SELECT FOR UPDATE should succeed under read committed.
statement ok
PREPARE psa AS SELECT aisle FROM supermarket WHERE person = $1::STRING FOR UPDATE

# But execution should fail.
query error pgcode 0A000 cannot execute SELECT FOR UPDATE statements under ReadCommitted isolation
EXECUTE psa('matilda')

statement ok
DEALLOCATE psa

# SELECT FOR UPDATE using a lookup join should also fail.
query error pgcode 0A000 cannot execute SELECT FOR UPDATE statements under ReadCommitted isolation
WITH names AS MATERIALIZED
  (SELECT 'matilda' AS person)
SELECT aisle
  FROM names
  NATURAL INNER LOOKUP JOIN supermarket
  FOR UPDATE

# SELECT FOR UPDATE using an index join should also fail.
query error pgcode 0A000 cannot execute SELECT FOR UPDATE statements under ReadCommitted isolation
SELECT aisle
  FROM supermarket@supermarket_starts_with_idx
  WHERE starts_with = 'm'
  FOR UPDATE

# SELECT FOR UPDATE using a zigzag join should also fail.
query error pgcode 0A000 cannot execute SELECT FOR UPDATE statements under ReadCommitted isolation
SELECT aisle
  FROM supermarket@{FORCE_ZIGZAG}
  WHERE starts_with = 'm' AND ends_with = 'lda'
  FOR UPDATE

statement ok
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE