sql/opt: always preserve ordering of SELECT FOR UPDATE subqueries

[michae2]

(In the same vein as #121479.) Unless propagate_input_ordering is set, we currently ignore ORDER BY in subqueries. If the subquery uses FOR UPDATE or FOR SHARE, however, the ordering can be important for preventing deadlocks. We should always preserve the ordering of SFU subqueries. (This might also apply to mutation subqueries / CTEs that use ordering?)

Here's a demonstration on v24.1.0-alpha.5:

CREATE TABLE ab (a int PRIMARY KEY, b int);
SET optimizer_use_lock_op_for_serializable = on;

-- We lose the ORDER BY here, even though it could help prevent deadlocks.
EXPLAIN (OPT, VERBOSE) SELECT count(*) FROM (SELECT * FROM ab WHERE b % 10 = 6 ORDER BY a FOR UPDATE);

Jira issue: CRDB-37564

[michae2]

This should probably be done for UPDATE and DELETE as well.

[mgartner]

I'm curious, does Postgres preserve the ordering of subqueries with FOR UPDATE clauses?

[michae2]

I'm curious, does Postgres preserve the ordering of subqueries with FOR UPDATE clauses?

Yes, it appears to. Here's an example using 16.3:

michae2=# CREATE TABLE mno (m INT PRIMARY KEY, n INT, o INT);
CREATE TABLE

michae2=# CREATE INDEX ON mno (n);
CREATE INDEX

michae2=# EXPLAIN SELECT count(*) FROM (SELECT * FROM mno WHERE n > 3 ORDER BY o FOR UPDATE);
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Aggregate  (cost=76.91..76.92 rows=1 width=8)
   ->  Subquery Scan on unnamed_subquery  (cost=59.91..75.21 rows=680 width=0)
         ->  LockRows  (cost=59.91..68.41 rows=680 width=18)
               ->  Sort  (cost=59.91..61.61 rows=680 width=18)
                     Sort Key: mno.o
                     ->  Bitmap Heap Scan on mno  (cost=9.42..27.92 rows=680 width=18)
                           Recheck Cond: (n > 3)
                           ->  Bitmap Index Scan on mno_n_idx  (cost=0.00..9.25 rows=680 width=0)
                                 Index Cond: (n > 3)
(9 rows)