Check-out mutable functions in check constraints - Mailing list pgsql-hackers

Hello.

As mentioned in the following message:

https://www.postgresql.org/message-id/20190712.150527.145133646.horikyota.ntt%40gmail.com

Mutable function are allowed in check constraint expressions but
it is not right. The attached is a proposed fix for it including
regression test.

Other "constraints vs xxxx" checks do not seem to be exercised
but it would be another issue.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center
From ed02bcf24a2fd666c80ab0b7ff63ccb370e26b03 Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horiguchi.kyotaro@lab.ntt.co.jp>
Date: Fri, 12 Jul 2019 15:35:20 +0900
Subject: [PATCH] Reject mutable functions in check constraints

Check constraint expressions cannot contain mutable functions but it
is not checked out. Fix it.
---
 src/backend/parser/parse_expr.c            | 9 +++++++++
 src/backend/parser/parse_func.c            | 7 +++++++
 src/test/regress/expected/create_table.out | 7 +++++++
 src/test/regress/sql/create_table.sql      | 3 +++
 4 files changed, 26 insertions(+)

diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 97f535a2f0..2fd233bf18 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -2376,6 +2376,15 @@ transformMinMaxExpr(ParseState *pstate, MinMaxExpr *m)
 static Node *
 transformSQLValueFunction(ParseState *pstate, SQLValueFunction *svf)
 {
+    /*
+     * All SQL value functions are stable so we reject them in check
+     * constraint expressions.
+     */
+    if (pstate->p_expr_kind == EXPR_KIND_CHECK_CONSTRAINT)
+        ereport(ERROR,
+                (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+                 errmsg("mutable functions are not allowed in check constraints")));
+
     /*
      * All we need to do is insert the correct result type and (where needed)
      * validate the typmod, so we just modify the node in-place.
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 2a44b434a5..6ea2f0326d 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -271,6 +271,13 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
                                &nvargs, &vatype,
                                &declared_arg_types, &argdefaults);
 
+    /* mutable functions are not allowed in constraint expressions */
+    if (pstate->p_expr_kind == EXPR_KIND_CHECK_CONSTRAINT &&
+        func_volatile(funcid) != PROVOLATILE_IMMUTABLE)
+        ereport(ERROR,
+                (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+                 errmsg("mutable functions are not allowed in constraint expression")));
+
     cancel_parser_errposition_callback(&pcbstate);
 
     /*
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 262abf2bfb..aee192c2b5 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -331,6 +331,13 @@ CREATE TABLE default_expr_agg (a int DEFAULT (generate_series(1,3)));
 ERROR:  set-returning functions are not allowed in DEFAULT expressions
 LINE 1: CREATE TABLE default_expr_agg (a int DEFAULT (generate_serie...
                                                       ^
+-- invalid use of mutable function
+CREATE TABLE mutable_incheckconstr_sqlvar (a text CHECK (a = CURRENT_USER));
+ERROR:  mutable functions are not allowed in check constraints
+CREATE TABLE mutable_incheckconstr_expr (a real CHECK (a = random()));
+ERROR:  mutable functions are not allowed in constraint expression
+LINE 1: ...BLE mutable_incheckconstr_expr (a real CHECK (a = random()))...
+                                                             ^
 --
 -- Partitioned tables
 --
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index 9c6d86a0bf..48e4e0c7db 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -317,6 +317,9 @@ CREATE TABLE default_expr_agg (a int DEFAULT (avg(1)));
 CREATE TABLE default_expr_agg (a int DEFAULT (select 1));
 -- invalid use of set-returning function
 CREATE TABLE default_expr_agg (a int DEFAULT (generate_series(1,3)));
+-- invalid use of mutable function
+CREATE TABLE mutable_incheckconstr_sqlvar (a text CHECK (a = CURRENT_USER));
+CREATE TABLE mutable_incheckconstr_expr (a real CHECK (a = random()));
 
 --
 -- Partitioned tables
-- 
2.16.3