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
