Re: dunction issue

Sam Mason wrote:
>> ALTER TABLE tmp_newsletterreg ADD CONSTRAINT simplistic_email_check
>> CHECK lower(trim(both ' ' from email)) LIKE '%_(at)_%';
>>
>
> Just out of interest, what's the lower() function call doing?
>
>
Absolutely nothing. That's what I get for reading my mail at
stupid-o-clock in the morning (Australia) instead of something sensible
like sleeping.

It's there because I was thinking about case insensitive domain
comparison, but I couldn't begin to guess how it made its way into the
constraint expression.
> I'd almost be tempted to do something like:
>
> CREATE DOMAIN emailaddr AS text
> CHECK (VALUE ~ '^[^ ]+(at)[^ ]+$');
>
> and then use this instead of text/varchar types.
>
I was thinking about something like that, but my own storage of email
addresses actually splits them into user part and domain part (so it can
handle the case sensitivity differently - user parts are may be case
sensitive depending on the mail system so you can't assume they're the
same if they only differ in case; domain parts are never case sensitive)
and that would've unnecessarily complicated the example. I didn't think
to go for the half way point.
>> ... or preferably throw informative exceptions.
>>
>
> This would be my preference. It'll probably do the "right" thing if the
> code is called from other stored procedures then.
>
Yep, it's what I'll do in almost all cases. I often land up writing
client / UI data validation code to perform the same checks and catch
the issue before submitting anything to the DB, but I don't consider
this unreasonable. The DB's checks are protecting data integrity and
consistency; the UI's checks are improving the user/app interaction by
providing early (and usually more friendly) notification of data issues.
They're really quite different jobs.

Occasionally, though, I do have something where the DB-using app must
just submit a request to the DB and see if it works. Either the UI
doesn't have the privileges to run the same checks its self, or they're
just too expensive to do from the client (or to do twice). In those
cases I start to find Pg's error reporting frustrating, and I either
resort to a "return value" sort of approach or embed a unique error code
and some parseable values in the exception string. Eg:

Some kind of human-readable error description goes here
[ERR114:ID=12200;CONFLICTING-ID=1111]

It's not pretty, but it works.
> Yup, why is this so often ignored when people write database drivers. I
> used the "pyPgSQL" python module (I think) for a bit, before realising
> that it even went as far as to "helpfully" automatically start a new
> transaction when the last one aborted. The resulting semantics meant my
> code did the most horrible things.
>
That is indeed horrible, and I'd be running from a DB interface like
that as fast as I could.

Much of what I've done with PostgreSQL has been with Python (I do a lot
of C++ too, but not with databases) and I've thankfully not run into
anything like that. psycopg (the alternative PostgreSQL interface for
Python) handles exceptions about as well as is possible with
PostgreSQL's purely text based exception reporting, and I've found it
very useful. I understand that it's also a LOT faster than PyPgSQL,
though I don't have any direct experience there as I've never used
PyPgSQL. It sounds like I unwittingly dodged a bullet there.

As far as I'm concerned any DB interface that's ignoring errors behind
your back needs to die. Especially in an exception-capable language like
Python, where throwing and letting the upper layers handle it is the
obviously sane thing to do.

--
Craig Ringer