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...


Attachment: 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,
--- 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,
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to