Upgrade from 8.2 to 8.3 & catching errors in functions

From: Joshua Berry <yoberi(at)gmail(dot)com>
To: PostgreSQL - General <pgsql-general(at)postgresql(dot)org>
Subject: Upgrade from 8.2 to 8.3 & catching errors in functions
Date: 2009-10-27 13:52:30
Message-ID: 5ccd53c10910270652i526671c7v65feedcf7f064e77@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Raimon Fernandez 2009-10-27 14:00:06 Re: Implementing Frontend/Backend Protocol TCP/IP
Previous Message John Cheng 2009-10-27 13:46:30 Just saw a blog on Amazon Relational Database Service (Beta)