Re: dunction issue - Mailing list pgsql-general

On Sat, Mar 29, 2008 at 04:05:15AM +0900, Craig Ringer wrote:
> Sam Mason wrote:
> >>ALTER TABLE tmp_newsletterreg ADD CONSTRAINT simplistic_email_check
> >>CHECK lower(trim(both ' ' from email)) LIKE '%_@_%';
> >
> >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.

OK, good to know I wasn't missing something :)

> >I'd almost be tempted to do something like:
> >
> >  CREATE DOMAIN emailaddr AS text
> >    CHECK (VALUE ~ '^[^ ]+@[^ ]+$');
> >
> >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.

I'd never bothered to go that way before.  My reasoning being that
emails get forwarded, aliases exist and other such fun and games.  I
only want the unique constraint there to keep my code working.

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

Humm, I'm getting the feeling we both learned programming at the same
school!

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

sounds sensible.  Do any other databaes/other tools work better that you
know of?  I keep looking for projects, but this could end up touching
quite a lot of code.

I'm lucky in that I've got a small userbase and they seem to be OK
(after a few initial frights) with the raw error messages from the
database.

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

Yes, luckily I found out reasonably early.  I don't do much with python
and wanted to see how well it worked.  It was a bit of an off putting
experience.

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

Most of the stuff I do with PG at work is through VB.  At least I've
managed get away from access at the back end!

My little hobby programming things tend to be in much more formally
specified things like Haskell, or lower level in C.

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

If I have reason to go back to Python I'll try and remember, thanks!

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

I think the author was honestly trying to he helpful.  It's just
that (s)he hadn't quite realised the consequences of this automatic
transaction handling.  Looking in its readme it's got comments about
"To achieve the DB-API 2.0 mandated behaviour"..."a new transaction is
created on the next call to execute()".  I think maybe something got
changed so that it also did an automatic rollback on an exception (I'm
pretty sure I'd turned off autocommit pretty early, but it was a while
ago).


  Sam