From d3356a2485143dc81e5b4d4e0311ffeaec56153c Mon Sep 17 00:00:00 2001 From: jian he Date: Mon, 26 May 2025 11:19:00 +0800 Subject: [PATCH v1 1/1] Ensure CREATE OR REPLACE FUNCTION validates its dependents Currently, CREATE OR REPLACE FUNCTION doesn't validate if the new function definition satisfies its dependents. This patch changes that: it will now validate the new function definition against its dependents, raising an error and failing the command if it's not satisfied. demo: create function sqlcheck(int) returns bool as 'select $1 > 0' language sql; create domain checkedint as int check(sqlcheck(value)); select 1::checkedint; -- ok create or replace function sqlcheck(int) returns bool as 'select $1 <= 0' language sql; -- Currently succeeds on master, but would fail with this patch. select 1::checkedint; context: https://postgr.es/m/12539.1544107316%40sss.pgh.pa.us discussion: https://postgr.es/m/ --- doc/src/sgml/config.sgml | 20 ++ doc/src/sgml/ref/create_function.sgml | 9 + src/backend/catalog/pg_proc.c | 202 ++++++++++++++++++ src/backend/commands/typecmds.c | 3 +- src/backend/utils/cache/typcache.c | 3 +- src/backend/utils/misc/guc_tables.c | 10 + src/backend/utils/misc/postgresql.conf.sample | 1 + src/bin/pg_dump/pg_backup_archiver.c | 3 + src/include/catalog/pg_proc.h | 1 + src/include/commands/typecmds.h | 1 + src/include/utils/guc.h | 1 + src/include/utils/typcache.h | 2 + .../regress/expected/create_function_sql.out | 48 ++++- src/test/regress/expected/domain.out | 43 ++++ src/test/regress/sql/create_function_sql.sql | 38 ++++ src/test/regress/sql/domain.sql | 30 +++ 16 files changed, 410 insertions(+), 5 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index ca2a567b2b1..d81d08962f7 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -9895,6 +9895,26 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; + + check_function_dependencies (boolean) + + check_function_dependencies configuration parameter + + + + + This parameter is normally on. When set to off, it + disables validation of objects that depentent object on the routine during . Disabling validation avoids side + effects of the validation process, in particular valildating existing invalidated constraint. + Set this parameter + to off before loading functions on behalf of other + users; pg_dump does so automatically. + This parameter should be false if check_function_bodies is set to false. + + + + default_transaction_isolation (enum) diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml index 0d240484cd3..4c938bb391c 100644 --- a/doc/src/sgml/ref/create_function.sgml +++ b/doc/src/sgml/ref/create_function.sgml @@ -76,6 +76,15 @@ CREATE [ OR REPLACE ] FUNCTION OUT parameters except by dropping the function.) + + If any existing domains or CHECK constraints rely on the + current function, and check_function_bodies is set to + true, then using CREATE OR REPLACE FUNCTION will + effectively verify whether the new function definition satisfies with those + domains or CHECK constraints. If not, an error will be + raised. + + When CREATE OR REPLACE FUNCTION is used to replace an existing function, the ownership and permissions of the function diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c index 5fdcf24d5f8..e2caa6bde21 100644 --- a/src/backend/catalog/pg_proc.c +++ b/src/backend/catalog/pg_proc.c @@ -15,17 +15,20 @@ #include "postgres.h" #include "access/htup_details.h" +#include "access/heapam.h" #include "access/table.h" #include "access/xact.h" #include "catalog/catalog.h" #include "catalog/dependency.h" #include "catalog/indexing.h" #include "catalog/objectaccess.h" +#include "catalog/pg_constraint.h" #include "catalog/pg_language.h" #include "catalog/pg_namespace.h" #include "catalog/pg_proc.h" #include "catalog/pg_transform.h" #include "catalog/pg_type.h" +#include "commands/typecmds.h" #include "executor/functions.h" #include "funcapi.h" #include "mb/pg_wchar.h" @@ -34,6 +37,7 @@ #include "parser/parse_coerce.h" #include "pgstat.h" #include "rewrite/rewriteHandler.h" +#include "storage/lmgr.h" #include "tcop/pquery.h" #include "tcop/tcopprot.h" #include "utils/acl.h" @@ -50,6 +54,11 @@ typedef struct char *prosrc; } parse_error_callback_arg; +typedef struct DomainFuncContext +{ + Oid funcid; /* OID of the view to be locked */ +} DomainFuncContext; + static void sql_function_parse_error_callback(void *arg); static int match_prosrc_to_query(const char *prosrc, const char *queryText, int cursorpos); @@ -57,6 +66,35 @@ static bool match_prosrc_to_literal(const char *prosrc, const char *literal, int cursorpos, int *newcursorpos); +static bool +contain_thisfunc_walker(Node *node, DomainFuncContext *context) +{ + if (node == NULL) + return false; + + if (IsA(node, FuncExpr)) + { + FuncExpr *f = (FuncExpr *) node; + + if (f->funcid == context->funcid) + return true; + } + + return expression_tree_walker(node, + contain_thisfunc_walker, + context); +} + +static bool +contain_thisfunc(Node *expr, Oid funcid) +{ + DomainFuncContext context; + context.funcid = funcid; + + return contain_thisfunc_walker(expr, &context); +} + + /* ---------------------------------------------------------------- * ProcedureCreate * @@ -405,6 +443,14 @@ ProcedureCreate(const char *procedureName, aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_FUNCTION, procedureName); + /* + * Acquire a lock on the existing function, which we won't release until + * commit. This ensures that two backends aren't concurrently modifying + * the same function. Since later we are going to validate functions + * dependent (eg domains), so this is really necessary. + */ + LockDatabaseObject(ProcedureRelationId, oldproc->oid, 0, ExclusiveLock); + /* Not okay to change routine kind */ if (oldproc->prokind != prokind) ereport(ERROR, @@ -727,6 +773,10 @@ ProcedureCreate(const char *procedureName, AtEOXact_GUC(true, save_nestlevel); } + /* we may need to validate function's dependent */ + if (is_update && prokind == PROKIND_FUNCTION) + validateFunctionDependent(retval); + /* ensure that stats are dropped if transaction aborts */ if (!is_update) pgstat_create_function(retval); @@ -734,6 +784,158 @@ ProcedureCreate(const char *procedureName, return myself; } +/* + * place_holder +*/ +void +validateFunctionDependent(Oid procoid) +{ + Relation pg_constraint; + Relation testrel; + TableScanDesc scan; + HeapTuple tuple; + List *domains = NIL; + List *conbins = NIL; + List *changedConstraintRelid = NIL; + ListCell *lc, + *lc2; + EState *estate; + + if (!check_function_dependencies) + return; + + estate = CreateExecutorState(); + + pg_constraint = table_open(ConstraintRelationId, AccessShareLock); + + scan = table_beginscan_catalog(pg_constraint, 0, NULL); + while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL) + { + Datum val; + bool isnull; + char *conbin; + + Form_pg_constraint conForm = (Form_pg_constraint) GETSTRUCT(tuple); + + if (conForm->connamespace == PG_CATALOG_NAMESPACE || + conForm->connamespace == PG_TOAST_NAMESPACE) + continue; + + if (conForm->contype != CONSTRAINT_CHECK) + continue; + + val = fastgetattr(tuple, + Anum_pg_constraint_conbin, + pg_constraint->rd_att, &isnull); + if (isnull) + elog(ERROR, "null conbin for rel %s", get_rel_name(conForm->conrelid)); + + conbin = TextDatumGetCString(val); + if (!contain_thisfunc(stringToNode(conbin), procoid)) + continue; + + /* + * we do not validating invalid constraint, so we should exit? + * otherwise, we are in effect validating data. + * TODO: XXX is this the right thing to do here? + */ + if (!conForm->convalidated) + break; + + /* for domain check constraint */ + if (OidIsValid(conForm->contypid)) + { + if (list_member_oid(domains, conForm->contypid)) + continue; + + domains = lappend_oid(domains, conForm->contypid); + + conbins = lappend(conbins, makeString(conbin)); + } + else + { + /* for table check constraint */ + if (list_member_oid(changedConstraintRelid, conForm->conrelid)) + continue; + + changedConstraintRelid = lappend_oid(changedConstraintRelid, + conForm->conrelid); + + /* + * Acquire ShareUpdateExclusiveLock on relation for validating + * domain value. + */ + LockRelationOid(conForm->conrelid, ShareUpdateExclusiveLock); + } + } + table_endscan(scan); + table_close(pg_constraint, AccessShareLock); + + forboth(lc, domains, lc2, conbins) + { + DomainConstraintRef *constraint_ref; + + Oid domainoid = lfirst_oid(lc); + String *conbin = lfirst_node(String, lc2); + + constraint_ref = (DomainConstraintRef *) + palloc(sizeof(DomainConstraintRef)); + + InitDomainConstraintRef(domainoid, + constraint_ref, + CurrentMemoryContext, + false); + + /* + * Domain type info is already loaded, but it's constraint cache may be + * staled, Since CREATE OR REPLACE FUNCTION will not update domain + * constraint definition immediately. We can call load_domaintype_info + * manually, since calling it is harm less. This is a hack, but do + * refresh the domain constraint info. + */ + load_domaintype_info(constraint_ref->tcache); + UpdateDomainConstraintRef(constraint_ref); + + validateDomainCheckConstraint(domainoid, strVal(conbin)); + } + + foreach_oid(relid, changedConstraintRelid) + { + ResultRelInfo *rInfo = NULL; + MemoryContext oldcontext; + TupleTableSlot *slot; + Snapshot snapshot; + + testrel = relation_open(relid, NoLock); + + oldcontext = MemoryContextSwitchTo(estate->es_query_cxt); + rInfo = makeNode(ResultRelInfo); + InitResultRelInfo(rInfo, + testrel, + 0, /* dummy rangetable index */ + NULL, + estate->es_instrument); + MemoryContextSwitchTo(oldcontext); + + Assert (testrel->rd_att->constr != NULL); + snapshot = RegisterSnapshot(GetLatestSnapshot()); + scan = table_beginscan(testrel, snapshot, 0, NULL); + slot = table_slot_create(testrel, NULL); + while (table_scan_getnextslot(scan, ForwardScanDirection, slot)) + { + ExecConstraints(rInfo, slot, estate); + } + ExecDropSingleTupleTableSlot(slot); + table_endscan(scan); + UnregisterSnapshot(snapshot); + + /* Hold relation lock till commit (XXX bad for concurrency) */ + table_close(testrel, NoLock); + } + + FreeExecutorState(estate); +} + /* diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 45ae7472ab5..9afebcd07de 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -126,7 +126,6 @@ static Oid findTypeSubscriptingFunction(List *procname, Oid typeOid); static Oid findRangeSubOpclass(List *opcname, Oid subtype); static Oid findRangeCanonicalFunction(List *procname, Oid typeOid); static Oid findRangeSubtypeDiffFunction(List *procname, Oid subtype); -static void validateDomainCheckConstraint(Oid domainoid, const char *ccbin); static void validateDomainNotNullConstraint(Oid domainoid); static List *get_rels_with_domain(Oid domainOid, LOCKMODE lockmode); static void checkEnumOwner(HeapTuple tup); @@ -3184,7 +3183,7 @@ validateDomainNotNullConstraint(Oid domainoid) * Verify that all columns currently using the domain satisfy the given check * constraint expression. */ -static void +void validateDomainCheckConstraint(Oid domainoid, const char *ccbin) { Expr *expr = (Expr *) stringToNode(ccbin); diff --git a/src/backend/utils/cache/typcache.c b/src/backend/utils/cache/typcache.c index f9aec38a11f..17815937c45 100644 --- a/src/backend/utils/cache/typcache.c +++ b/src/backend/utils/cache/typcache.c @@ -315,7 +315,6 @@ static uint64 tupledesc_id_counter = INVALID_TUPLEDESC_IDENTIFIER; static void load_typcache_tupdesc(TypeCacheEntry *typentry); static void load_rangetype_info(TypeCacheEntry *typentry); static void load_multirangetype_info(TypeCacheEntry *typentry); -static void load_domaintype_info(TypeCacheEntry *typentry); static int dcs_cmp(const void *a, const void *b); static void decr_dcc_refcount(DomainConstraintCache *dcc); static void dccref_deletion_callback(void *arg); @@ -1079,7 +1078,7 @@ load_multirangetype_info(TypeCacheEntry *typentry) * CurrentMemoryContext, and reparent it under CacheMemoryContext when * complete. */ -static void +void load_domaintype_info(TypeCacheEntry *typentry) { Oid typeOid = typentry->type_id; diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c index 2f8cbd86759..e1b47fa0fcb 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -526,6 +526,7 @@ char *event_source; bool row_security; bool check_function_bodies = true; +bool check_function_dependencies = true; /* * This GUC exists solely for backward compatibility, check its definition for @@ -1701,6 +1702,15 @@ struct config_bool ConfigureNamesBool[] = true, NULL, NULL, NULL }, + { + {"check_function_dependencies", PGC_USERSET, CLIENT_CONN_STATEMENT, + gettext_noop("Check routine dependencies during CREATE OR REPLACE FUNCTION."), + NULL + }, + &check_function_dependencies, + true, + NULL, NULL, NULL + }, { {"array_nulls", PGC_USERSET, COMPAT_OPTIONS_PREVIOUS, gettext_noop("Enables input of NULL elements in arrays."), diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 63f991c4f93..28e0f2d4351 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -758,6 +758,7 @@ autovacuum_worker_slots = 16 # autovacuum worker slots to allocate #temp_tablespaces = '' # a list of tablespace names, '' uses # only default tablespace #check_function_bodies = on +#check_function_dependencies = on #default_transaction_isolation = 'read committed' #default_transaction_read_only = off #default_transaction_deferrable = off diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c index afa42337b11..ecd2121f366 100644 --- a/src/bin/pg_dump/pg_backup_archiver.c +++ b/src/bin/pg_dump/pg_backup_archiver.c @@ -3358,6 +3358,9 @@ _doSetFixedOutputState(ArchiveHandle *AH) /* Make sure function checking is disabled */ ahprintf(AH, "SET check_function_bodies = false;\n"); + /* Make sure function dependency checking is disabled */ + ahprintf(AH, "SET check_function_dependencies = false;\n"); + /* Ensure that all valid XML data will be accepted */ ahprintf(AH, "SET xmloption = content;\n"); diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index d7353e7a088..5f974dfac27 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -217,6 +217,7 @@ extern ObjectAddress ProcedureCreate(const char *procedureName, float4 procost, float4 prorows); +extern void validateFunctionDependent(Oid procoid); extern bool function_parse_error_transpose(const char *prosrc); extern List *oid_array_to_list(Datum datum); diff --git a/src/include/commands/typecmds.h b/src/include/commands/typecmds.h index 6cc387e3337..8fead2b2b1e 100644 --- a/src/include/commands/typecmds.h +++ b/src/include/commands/typecmds.h @@ -37,6 +37,7 @@ extern ObjectAddress AlterDomainNotNull(List *names, bool notNull); extern ObjectAddress AlterDomainAddConstraint(List *names, Node *newConstraint, ObjectAddress *constrAddr); extern ObjectAddress AlterDomainValidateConstraint(List *names, const char *constrName); +extern void validateDomainCheckConstraint(Oid domainoid, const char *ccbin); extern ObjectAddress AlterDomainDropConstraint(List *names, const char *constrName, DropBehavior behavior, bool missing_ok); diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h index f619100467d..c90c62da6f3 100644 --- a/src/include/utils/guc.h +++ b/src/include/utils/guc.h @@ -264,6 +264,7 @@ extern PGDLLIMPORT bool log_btree_build_stats; extern PGDLLIMPORT char *event_source; extern PGDLLIMPORT bool check_function_bodies; +extern PGDLLIMPORT bool check_function_dependencies; extern PGDLLIMPORT bool current_role_is_superuser; extern PGDLLIMPORT bool AllowAlterSystem; diff --git a/src/include/utils/typcache.h b/src/include/utils/typcache.h index 1cb30f1818c..05e055c64ce 100644 --- a/src/include/utils/typcache.h +++ b/src/include/utils/typcache.h @@ -185,6 +185,8 @@ extern void UpdateDomainConstraintRef(DomainConstraintRef *ref); extern bool DomainHasConstraints(Oid type_id); +extern void load_domaintype_info(TypeCacheEntry *typentry); + extern TupleDesc lookup_rowtype_tupdesc(Oid type_id, int32 typmod); extern TupleDesc lookup_rowtype_tupdesc_noerror(Oid type_id, int32 typmod, diff --git a/src/test/regress/expected/create_function_sql.out b/src/test/regress/expected/create_function_sql.out index 963b6f863ff..4f984818faa 100644 --- a/src/test/regress/expected/create_function_sql.out +++ b/src/test/regress/expected/create_function_sql.out @@ -771,9 +771,51 @@ ERROR: return type mismatch in function declared to return integer[] DETAIL: Function's final statement must be SELECT or INSERT/UPDATE/DELETE/MERGE RETURNING. CONTEXT: SQL function "test1" during startup RESET check_function_bodies; +-- test check constraint function changes +-- +create or replace function sqlcheck(int) returns bool as 'select $1 > 0' language sql; +create or replace function sqlcheck1(int) returns bool as 'select $1 > 0' language sql; +create or replace function plpgsql_check(int) returns bool AS $$ begin return $1 > 0; end; $$ language plpgsql immutable; +create table t1(a int, b int default 1); +alter table t1 add constraint cc check (sqlcheck(a)); +alter table t1 add constraint cc_a1 check (sqlcheck1(a)) not valid; +alter table t1 add constraint cc_b check (plpgsql_check(b)); +insert into t1 values(1,2), (2,3); +insert into t1(a) values(0); +ERROR: new row for relation "t1" violates check constraint "cc" +DETAIL: Failing row contains (0, 1). +--test sql function change +SET check_function_dependencies TO ON; +create or replace function sqlcheck(int) returns bool as 'select $1 <= 0' language sql; --erorr +ERROR: new row for relation "t1" violates check constraint "cc" +DETAIL: Failing row contains (1, 2). +create or replace function sqlcheck(int) returns bool as 'select $1 <= 1' language sql; --erorr +ERROR: new row for relation "t1" violates check constraint "cc" +DETAIL: Failing row contains (2, 3). +--ok. the function associated constraint is not validated +create or replace function sqlcheck1(int) returns bool as 'select $1 <= 1' language sql; +SET check_function_dependencies TO OFF; +create or replace function sqlcheck(int) returns bool as 'select $1 <= 1' language sql; --ok +SET check_function_dependencies TO ON; +create or replace function sqlcheck(int) returns bool as 'select $1 <= 2' language sql; --ok +ERROR: new row for relation "t1" violates check constraint "cc_a1" +DETAIL: Failing row contains (2, 3). +--test immuatble plpgsql function function change +--ok +SET check_function_dependencies TO OFF; +create or replace function plpgsql_check(int) returns bool AS $$ begin return $1 > 2; end; $$ language plpgsql immutable; +--error +SET check_function_dependencies TO ON; +create or replace function plpgsql_check(int) returns bool AS $$ begin return $1 > 2; end; $$ language plpgsql immutable; +ERROR: new row for relation "t1" violates check constraint "cc_b" +DETAIL: Failing row contains (1, 2). +--ok +create or replace function plpgsql_check(int) returns bool AS $$ begin return $1 > 1; end; $$ language plpgsql immutable; +ERROR: new row for relation "t1" violates check constraint "cc" +DETAIL: Failing row contains (2, 3). -- Cleanup DROP SCHEMA temp_func_test CASCADE; -NOTICE: drop cascades to 35 other objects +NOTICE: drop cascades to 39 other objects DETAIL: drop cascades to function functest_a_1(text,date) drop cascades to function functest_a_2(text[]) drop cascades to function functest_a_3() @@ -809,5 +851,9 @@ drop cascades to table ddl_test drop cascades to function alter_and_insert() drop cascades to function double_append(anyarray,anyelement) drop cascades to function test1(anyelement) +drop cascades to function sqlcheck(integer) +drop cascades to function sqlcheck1(integer) +drop cascades to function plpgsql_check(integer) +drop cascades to table t1 DROP USER regress_unpriv_user; RESET search_path; diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out index ba6f05eeb7d..6dc0442063c 100644 --- a/src/test/regress/expected/domain.out +++ b/src/test/regress/expected/domain.out @@ -671,6 +671,49 @@ table dcomptable; drop table dcomptable; drop type comptype cascade; NOTICE: drop cascades to type dcomptype +-- Test domain check constraint condition changes. +--context: https://www.postgresql.org/message-id/12539.1544107316%40sss.pgh.pa.us +SET check_function_dependencies = on; +create or replace function sqlcheck(int) returns bool as 'select $1 > 0' language sql; +create domain checkedint as int check(sqlcheck(value)); +select 1::checkedint; -- ok + checkedint +------------ + 1 +(1 row) + +select 0::checkedint; -- fail +ERROR: value for domain checkedint violates check constraint "checkedint_check" +SET check_function_dependencies TO OFF; +create or replace function sqlcheck(int) returns bool as 'select $1 <= 0' language sql; +select 1::checkedint; -- ok + checkedint +------------ + 1 +(1 row) + +select 0::checkedint; -- fail +ERROR: value for domain checkedint violates check constraint "checkedint_check" +SET check_function_dependencies TO ON; +create or replace function sqlcheck(int) returns bool as 'select $1 <= 0' language sql; +select 1::checkedint; -- fail +ERROR: value for domain checkedint violates check constraint "checkedint_check" +select 0::checkedint; -- ok + checkedint +------------ + 0 +(1 row) + +create table t(a checkedint); +insert into t values (0); +SET check_function_dependencies TO ON; +--fail. will do a validation check. +create or replace function sqlcheck(int) returns bool as 'select $1 > 0' language sql; +ERROR: column "a" of table "t" contains values that violate the new constraint +SET check_function_dependencies TO OFF; +--now ok +create or replace function sqlcheck(int) returns bool as 'select $1 > 0' language sql; +RESET check_function_dependencies; -- Test not-null restrictions create domain dnotnull varchar(15) NOT NULL; create domain dnull varchar(15); diff --git a/src/test/regress/sql/create_function_sql.sql b/src/test/regress/sql/create_function_sql.sql index 6d1c102d780..7760823e91b 100644 --- a/src/test/regress/sql/create_function_sql.sql +++ b/src/test/regress/sql/create_function_sql.sql @@ -459,6 +459,44 @@ CREATE FUNCTION test1 (anyelement) RETURNS anyarray LANGUAGE SQL SELECT test1(0); RESET check_function_bodies; +-- test check constraint function changes +-- +create or replace function sqlcheck(int) returns bool as 'select $1 > 0' language sql; +create or replace function sqlcheck1(int) returns bool as 'select $1 > 0' language sql; +create or replace function plpgsql_check(int) returns bool AS $$ begin return $1 > 0; end; $$ language plpgsql immutable; + +create table t1(a int, b int default 1); +alter table t1 add constraint cc check (sqlcheck(a)); +alter table t1 add constraint cc_a1 check (sqlcheck1(a)) not valid; +alter table t1 add constraint cc_b check (plpgsql_check(b)); +insert into t1 values(1,2), (2,3); +insert into t1(a) values(0); + +--test sql function change +SET check_function_dependencies TO ON; +create or replace function sqlcheck(int) returns bool as 'select $1 <= 0' language sql; --erorr +create or replace function sqlcheck(int) returns bool as 'select $1 <= 1' language sql; --erorr +--ok. the function associated constraint is not validated +create or replace function sqlcheck1(int) returns bool as 'select $1 <= 1' language sql; + +SET check_function_dependencies TO OFF; +create or replace function sqlcheck(int) returns bool as 'select $1 <= 1' language sql; --ok + +SET check_function_dependencies TO ON; +create or replace function sqlcheck(int) returns bool as 'select $1 <= 2' language sql; --ok + +--test immuatble plpgsql function function change +--ok +SET check_function_dependencies TO OFF; +create or replace function plpgsql_check(int) returns bool AS $$ begin return $1 > 2; end; $$ language plpgsql immutable; + +--error +SET check_function_dependencies TO ON; +create or replace function plpgsql_check(int) returns bool AS $$ begin return $1 > 2; end; $$ language plpgsql immutable; + +--ok +create or replace function plpgsql_check(int) returns bool AS $$ begin return $1 > 1; end; $$ language plpgsql immutable; + -- Cleanup DROP SCHEMA temp_func_test CASCADE; DROP USER regress_unpriv_user; diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql index b752a63ab5f..19320d84d28 100644 --- a/src/test/regress/sql/domain.sql +++ b/src/test/regress/sql/domain.sql @@ -330,6 +330,36 @@ drop table dcomptable; drop type comptype cascade; +-- Test domain check constraint condition changes. +--context: https://www.postgresql.org/message-id/12539.1544107316%40sss.pgh.pa.us +SET check_function_dependencies = on; +create or replace function sqlcheck(int) returns bool as 'select $1 > 0' language sql; +create domain checkedint as int check(sqlcheck(value)); +select 1::checkedint; -- ok +select 0::checkedint; -- fail + +SET check_function_dependencies TO OFF; +create or replace function sqlcheck(int) returns bool as 'select $1 <= 0' language sql; +select 1::checkedint; -- ok +select 0::checkedint; -- fail + +SET check_function_dependencies TO ON; +create or replace function sqlcheck(int) returns bool as 'select $1 <= 0' language sql; +select 1::checkedint; -- fail +select 0::checkedint; -- ok + +create table t(a checkedint); +insert into t values (0); + +SET check_function_dependencies TO ON; +--fail. will do a validation check. +create or replace function sqlcheck(int) returns bool as 'select $1 > 0' language sql; + +SET check_function_dependencies TO OFF; +--now ok +create or replace function sqlcheck(int) returns bool as 'select $1 > 0' language sql; +RESET check_function_dependencies; + -- Test not-null restrictions create domain dnotnull varchar(15) NOT NULL; -- 2.34.1