Thread: Upgrade from 8.2 to 8.3 & catching errors in functions

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?

Thus far we exclusively use plpgsql.

Regards,
 -Joshua Berry

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

On Tue, Oct 27, 2009 at 1:35 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
|
| 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 )
| >
| > 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.

Thank you! So before, 8.1 and 8.2 would try to cast to text as a last
resort? Are there adverse effects that the added casts can cause? My
plan is to put these casts in place for a time while we are able to
verify that none of the casts are needed. It would be good to know if
there are any side effects of using them.

Regards,
Joshua Berry

| Regards
| Pavel Stehule

2009/10/27 Joshua Berry <yoberi@gmail.com>:
> On Tue, Oct 27, 2009 at 1:35 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> |
> | 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 )
> | >
> | > 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.
>
> Thank you! So before, 8.1 and 8.2 would try to cast to text as a last
> resort? Are there adverse effects that the added casts can cause? My
> plan is to put these casts in place for a time while we are able to
> verify that none of the casts are needed. It would be good to know if
> there are any side effects of using them.
>
These casts was removed because should to hide some bugs. Like cast int to date

with this cast you can to write predicate current_date < 2009-10-10,
it is nonsens, but correct. That is all.

Regards
Pavel


> Regards,
> Joshua Berry
>
> | Regards
> | Pavel Stehule
>