hi. The attached patch allows CREATE OR REPLACE FUNCTION to correctly update domain constraints when the function they depend on is changed.
so this thread[1] mentioned the problem can be resolved. for example: create function sqlcheck(int) returns bool as 'select $1 > 0' language sql; create domain checkedint as int check(sqlcheck(value)); select 0::checkedint; -- fail ERROR: value for domain checkedint violates check constraint "checkedint_check" create or replace function sqlcheck(int) returns bool as 'select $1 <= 0' language sql; select 1::checkedint; -- fail? the last query won't fail on the master. with the patch it will fail. I also make CREATE OR REPLACE FUNCTION validate each domain value when the domain constraint conditions is associated the function we are gonname changes Of course, this will make CREATE OR REPLACE FUNCTION take way longer time compared to the current. Similar to domain constraints, attached patch also apply to table check constraints too. Is this what we want to do? [1]: https://postgr.es/m/12539.1544107316%40sss.pgh.pa.us
From d3356a2485143dc81e5b4d4e0311ffeaec56153c Mon Sep 17 00:00:00 2001 From: jian he <jian.universal...@gmail.com> 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; </listitem> </varlistentry> + <varlistentry id="guc-check-function-dependencies" xreflabel="check_function_dependencies"> + <term><varname>check_function_dependencies</varname> (<type>boolean</type>) + <indexterm> + <primary><varname>check_function_dependencies</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + This parameter is normally on. When set to <literal>off</literal>, it + disables validation of objects that depentent object on the routine during <xref + linkend="sql-createfunction"/>. Disabling validation avoids side + effects of the validation process, in particular valildating existing invalidated constraint. + Set this parameter + to <literal>off</literal> before loading functions on behalf of other + users; <application>pg_dump</application> does so automatically. + This parameter should be false if <varname>check_function_bodies</varname> is set to false. + </para> + </listitem> + </varlistentry> + <varlistentry id="guc-default-transaction-isolation" xreflabel="default_transaction_isolation"> <term><varname>default_transaction_isolation</varname> (<type>enum</type>) <indexterm> 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 <literal>OUT</literal> parameters except by dropping the function.) </para> + <para> + If any existing domains or <literal>CHECK</literal> constraints rely on the + current function, and <varname>check_function_bodies</varname> is set to + true, then using <command>CREATE OR REPLACE FUNCTION</command> will + effectively verify whether the new function definition satisfies with those + domains or <literal>CHECK</literal> constraints. If not, an error will be + raised. + </para> + <para> When <command>CREATE OR REPLACE FUNCTION</command> 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
