

I'm obviously missing something here but I don't know what. This is on 7.3.2.
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

Any clues as to why that is?

The code is simplistic to say the least:


-- text_to_bool ()
--
-- Cast from text type to boolean.
-- 
CREATE OR REPLACE FUNCTION text_to_bool ( text )
	RETURNS boolean
	AS '
	DECLARE
		rv	boolean;
	BEGIN
		SELECT INTO rv
			CASE
				WHEN lower($1) = ''true''
					OR lower($1) = ''t''
					OR lower($1) = ''1''
				THEN
					true
				WHEN lower($1) = ''false''
					OR lower($1) = ''f''
					OR lower($1) = ''0''
				THEN
					false
				ELSE
					NULL
				END;
		RETURN rv;
	END;
	'
	LANGUAGE 'plpgsql'
	STRICT
	IMMUTABLE;

CREATE CAST (text AS boolean) WITH FUNCTION text_to_bool ( text );


Thanks,

-- 
Nigel J. Andrews


Tue, 8 Apr 2003 10:04:30 +0100 (BST)
"Nigel J. Andrews" <nandrews@investsystems.co.uk>
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


Tue, 08 Apr 2003 10:25:39 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [GENERAL] Casting 
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


Tue, 8 Apr 2003 16:13:30 +0100 (BST)
"Nigel J. Andrews" <nandrews@investsystems.co.uk>
Re: [GENERAL] Casting
"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


Tue, 08 Apr 2003 11:16:14 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [GENERAL] Casting 
"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;


Wed, 09 Apr 2003 22:49:52 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Casting 


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


On Wed, 9 Apr 2003, Tom Lane wrote:
> "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,


Thu, 10 Apr 2003 09:12:37 +0100 (BST)
"Nigel J. Andrews" <nandrews@investsystems.co.uk>
Re: Casting