Re: Upgrade from 8.2 to 8.3 & catching errors in functions - Mailing list pgsql-general

2009/10/27 Joshua Berry <yoberi@gmail.com>:
> Greetings,
>
> It seems that in Postgresql 8.2 less casting was necessary to coax the
> backend to execute queries.
> For example:
> * Comparing a varchar with a numeric
>
> In 8.3, these will result in errors like this:
> HINT:  No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
> QUERY:  SELECT  ( $1  <  $2 )
>
> In my experience, when loading to 8.3 a database dump from 8.2, the data is
> loaded without error. It is only later, when the errant functions are
> executed that the errors start to show. In the past we've waited for the
> errors to show, before going in and correcting things. But this is not a
> good way to go about it as missing casts can reside in code paths that stay
> dormant for quite a while before being exposed.
>
> No, we have no unit tests to test all these code paths; much of the code was
> generated by the clients as customizations, so that partially excuses us
> from that ;)
>
> Is it possible to get the backend to check the function bodies upon loading
> of the dump? I've tried this, from the head of the pg_dump generated
> dumpfile:
>  SET client_encoding = 'UTF8';
>  SET standard_conforming_strings = off;
> -SET check_function_bodies = false;
> +SET check_function_bodies = true;
>  SET client_min_messages = warning;
>  SET escape_string_warning = off;
>
> This has caught a few problems, but not most. If it is not possible to do
> this, is there (an easy) way to parse the function body relating the known
> datatypes of the columns referenced to check for such conflicts?

It isn't possible yet.

I wrote missing cast functions with notifications. So you can use it
on 8.3 for some time and then you can identify mostly problematic
places.

CREATE OR REPLACE FUNCTION generator_81_casts()
RETURNS void AS $$
DECLARE
  src varchar[] := '{integer,smallint,oid,date,double
precision,real,time with time zone, time without time zone, timestamp
with time zone, interval,bigint,numeric,timestamp without time zon\
e}';
  fn varchar[] :=

'{int4out,int2out,oidout,date_out,float8out,float4out,timetz_out,time_out,timestamptz_out,interval_out,int8out,numeric_out,timestamp_out}';
  fn_name varchar;
  fn_msg varchar; fn_body varchar;
BEGIN
  FOR i IN array_lower(src,1)..array_upper(src,1) LOOP
    fn_name := 'aux_cast_func_' || replace(src[i],' ','_') ||'_to_text';
    fn_msg := '''using obsolete implicit casting from ' || src[i] || '
to text''';
    fn_body := 'CREATE OR REPLACE FUNCTION '|| fn_name || '(' ||
src[i] ||') RETURNS text AS $_$ BEGIN RAISE WARNING '
                  || fn_msg || ';RETURN textin(' || fn[i] || '($1));
END; $_$ LANGUAGE plpgsql IMMUTABLE';
    EXECUTE fn_body;
    -- for 8.1
    --EXECUTE 'UPDATE pg_cast SET castfunc = ''' || fn_name ||
'''::regproc WHERE castsource = ''' || src[i] || '''::regtype AND
casttarget = ''text''::regtype';
   DROP CAST 'CREATE CAST (' || src[i] || ' AS text)';
   EXECUTE 'CREATE CAST (' || src[i] || ' AS text) WITH FUNCTION ' ||
fn_name || '(' || src[i] || ') AS IMPLICIT';
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT generator_81_casts();

Regards
Pavel Stehule
>
> Thus far we exclusively use plpgsql.
>
> Regards,
>  -Joshua Berry
>
>