Casting

"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:

When I use my cast I get:
mydb=> select cast('truE'::text as boolean);
WARNING: Error occurred while executing PL/pgSQL function text_to_bool
WARNING: line 4 at select into variables
ERROR: CopyQuerySnapshot: no snapshot has been set

Hmm. The problem here is that parse_coerce.c thinks it can apply
eval_const_expressions() to simplify the CAST; but in the general case
that doesn't work because the query snapshot may not be set yet.
Had we left the expression to be folded at plan time everything would
be okay.

I dislike removing the optimization altogether, but maybe we don't
have much choice. Anyone see another way around it?

regards, tom lane

On Tue, 8 Apr 2003, Tom Lane wrote:

"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:

When I use my cast I get:
mydb=> select cast('truE'::text as boolean);
WARNING: Error occurred while executing PL/pgSQL function text_to_bool
WARNING: line 4 at select into variables
ERROR: CopyQuerySnapshot: no snapshot has been set

Hmm. The problem here is that parse_coerce.c thinks it can apply
eval_const_expressions() to simplify the CAST; but in the general case
that doesn't work because the query snapshot may not be set yet.
Had we left the expression to be folded at plan time everything would
be okay.

I dislike removing the optimization altogether, but maybe we don't
have much choice. Anyone see another way around it?

Well, checking the source, and the comments in there, thanks to Tom's lead I
saw that only immutable functions were folded in the parse. Marking my function
as stable instead of immutable has indeed allowed my test statment above to
run.

So, that could be a published work around, perhaps in the CREATE CAST
page? I'm not sure of the expense of the function being stable and not
immutable though. I suspect it's most likely not a huge cost for many cases.

--
Nigel J. Andrews

"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:

So, that could be a published work around, perhaps in the CREATE CAST
page?

I intend to fix the bug, not publish a workaround ;-)

regards, tom lane

"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:

mydb=> select cast('truE'::text as boolean);
WARNING: Error occurred while executing PL/pgSQL function text_to_bool
WARNING: line 4 at select into variables
ERROR: CopyQuerySnapshot: no snapshot has been set

I've applied the attached patch to fix this.

regards, tom lane

*** REL7_3/src/backend/parser/parse_coerce.c.orig	Thu Oct 24 18:09:00 2002
--- REL7_3/src/backend/parser/parse_coerce.c	Wed Apr  9 22:47:57 2003
***************
*** 226,251 ****
  				result = (Node *) makeRelabelType(result, targetTypeId, -1,
  												  cformat);
  			}
- 
- 			/*
- 			 * If the input is a constant, apply the type conversion
- 			 * function now instead of delaying to runtime.  (We could, of
- 			 * course, just leave this to be done during
- 			 * planning/optimization; but it's a very frequent special
- 			 * case, and we save cycles in the rewriter if we fold the
- 			 * expression now.)
- 			 *
- 			 * Note that no folding will occur if the conversion function is
- 			 * not marked 'immutable'.
- 			 *
- 			 * HACK: if constant is NULL, don't fold it here.  This is needed
- 			 * by make_subplan(), which calls this routine on placeholder
- 			 * Const nodes that mustn't be collapsed.  (It'd be a lot
- 			 * cleaner to make a separate node type for that purpose...)
- 			 */
- 			if (IsA(node, Const) &&
- 				!((Const *) node)->constisnull)
- 				result = eval_const_expressions(result);
  		}
  		else
  		{
--- 226,231 ----
***************
*** 496,502 ****
  	{
  		List	   *args;
  		Const	   *cons;
- 		Node	   *fcall;

  		/* Pass given value, plus target typmod as an int4 constant */
  		cons = makeConst(INT4OID,
--- 476,481 ----
***************
*** 523,541 ****
  			args = lappend(args, cons);
  		}

! fcall = build_func_call(funcId, targetTypeId, args, cformat);
!
! /*
! * If the input is a constant, apply the length coercion
! * function now instead of delaying to runtime.
! *
! * See the comments for the similar case in coerce_type.
! */
! if (node && IsA(node, Const) &&
! !((Const *) node)->constisnull)
! node = eval_const_expressions(fcall);
! else
! node = fcall;
}

  	return node;
--- 502,508 ----
  			args = lappend(args, cons);
  		}

! node = build_func_call(funcId, targetTypeId, args, cformat);
}

return node;

Thanks Tom,

I thought that was something you were thinking about/putting on a list to do
later. As usual the speed of fixes being given is amazing.

I'll see if I can apply it later today or tomorrow, I need to move to 7.3.2 as
well so I can try it out then.

--
Nigel J. Andrews