| From: | Mats Rydberg <mats(at)planetscale(dot)com> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Subject: | GREATEST/LEAST ignores comparison operator volatility in contain_volatile_functions_walker |
| Date: | 2026-06-02 08:48:32 |
| Message-ID: | CABu=s9Uz29GRAu-xzHO142jFsWa4xpySUNRT6pxcyQpi3vjvkw@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
PostgreSQL version: 18.4 (also reproduced on 18.3)
Platform: aarch64-apple-darwin, Apple clang 17
GREATEST and LEAST (MinMaxExpr nodes) are missing from
contain_volatile_functions_walker in src/backend/optimizer/util/clauses.c.
OpExpr nodes are correctly handled -- the walker resolves the oprcode via
set_opfuncid and checks func_volatile. But there is no MinMaxExpr case, so
GREATEST/LEAST always appears function-free to the volatility checker
regardless of the < or > operator's provolatile.
The practical consequence: a GREATEST/LEAST expression whose comparison
operator is STABLE or VOLATILE is incorrectly treated as IMMUTABLE. The
reproducer below demonstrates this via a generated column, which requires
an IMMUTABLE expression. PostgreSQL accepts the column definition when it
should reject it.
-- Reproducer (self-contained, tested on 18.4):
DROP TABLE IF EXISTS t CASCADE;
DROP TYPE IF EXISTS myval CASCADE;
CREATE TYPE myval;
CREATE FUNCTION myval_in(cstring) RETURNS myval
LANGUAGE internal STRICT IMMUTABLE AS 'textin';
CREATE FUNCTION myval_out(myval) RETURNS cstring
LANGUAGE internal STRICT IMMUTABLE AS 'textout';
CREATE TYPE myval (INPUT = myval_in, OUTPUT = myval_out, LIKE = text);
-- < is STABLE: result depends on the myval.reverse session GUC
CREATE FUNCTION myval_lt(a myval, b myval) RETURNS boolean
LANGUAGE sql STABLE STRICT AS $$
SELECT CASE current_setting('myval.reverse', true)
WHEN 'on' THEN a::text > b::text
ELSE a::text < b::text
END
$$;
CREATE OPERATOR < (
leftarg = myval, rightarg = myval,
procedure = myval_lt, negator = >=
);
-- btree opclass so GREATEST can resolve a sort operator for myval
CREATE FUNCTION myval_cmp(a myval, b myval) RETURNS integer
LANGUAGE sql IMMUTABLE STRICT AS $$
SELECT CASE WHEN a::text < b::text THEN -1
WHEN a::text > b::text THEN 1
ELSE 0 END
$$;
CREATE OPERATOR CLASS myval_ops DEFAULT FOR TYPE myval USING btree AS
OPERATOR 1 <,
FUNCTION 1 myval_cmp(myval, myval);
CREATE TABLE t (x myval, y myval);
-- Control: a direct call to the Stable function is correctly rejected.
-- ALTER TABLE t ADD COLUMN ctrl boolean
-- GENERATED ALWAYS AS (myval_lt(x, y)) STORED;
-- => ERROR: generation expression is not immutable (correct)
-- Bug: GREATEST uses the same Stable < operator but the missing
-- MinMaxExpr case in contain_volatile_functions_walker means
-- PostgreSQL treats the expression as immutable.
ALTER TABLE t ADD COLUMN z myval
GENERATED ALWAYS AS (GREATEST(x, y)) STORED;
-- Expected: ERROR: generation expression is not immutable
-- Actual: ALTER TABLE (bug: accepted without error)
The fix would be to add a MinMaxExpr case to
contain_volatile_functions_walker that resolves the comparison operator's
oprcode (analogous to the existing OpExpr handling) and checks its
provolatile.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | PG Bug reporting form | 2026-06-02 08:58:26 | BUG #19504: Segmentation fault in plpython3u function (pg19) |
| Previous Message | Andrey Borodin | 2026-06-02 08:18:29 | Re: Possible G2-item at SERIALIZABLE |