David Wheeler reported the following bug: when a protocol-level prepared statement with a parameter of UNKNOWN type is used, any domain constraints that are associated with the inferred type of the parameter are not checked when the statement is executed. Attached is a script David sent me to reproduce the problem: when pg_server_prepare is enabled (the default), DBD::Pg uses protocol-level prepared statements, and the script is able to insert a tuple that violates the domain's check constraint. Disabling pg_server_prepare results in a constraint failure, as it should.
I've also attached a patch that should fix the issue -- coerce_type() neglected to apply coerce_to_domain() to the type inferred for an UNKNOWN Param. Barring any objections, I intend to apply the patch to HEAD and release branches as far back as the problem exists (likely 8.0 and 8.1, and possibly 7.4 -- I haven't checked yet). It would be nice to add regression tests for this issue, but AFAICS there's no way to specify parameters of UNKNOWN type to a prepared statement created via SQL -- which might be worth implementing anyway... -Neil
test.pl
Description: Perl program
============================================================ *** src/backend/parser/parse_coerce.c 8b4850b0ee25092c7c6166233049c4b48f05d443 --- src/backend/parser/parse_coerce.c 35431971d0f97977d363dcd4c46bab18526d5269 *************** *** 209,214 **** --- 209,216 ---- */ Param *param = (Param *) node; int paramno = param->paramid; + Type targetType; + char targetTyptype; ParseState *toppstate; toppstate = pstate; *************** *** 243,249 **** } param->paramtype = targetTypeId; ! return (Node *) param; } if (find_coercion_pathway(targetTypeId, inputTypeId, ccontext, &funcId)) --- 245,263 ---- } param->paramtype = targetTypeId; ! result = (Node *) param; ! ! targetType = typeidType(targetTypeId); ! targetTyptype = typeTypType(targetType); ! ! /* If target is a domain, apply constraints */ ! if (targetTyptype == 'd') ! result = coerce_to_domain(result, InvalidOid, targetTypeId, ! cformat, false, false); ! ! ReleaseSysCache(targetType); ! ! return result; } if (find_coercion_pathway(targetTypeId, inputTypeId, ccontext, &funcId))
---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
