Monday, April 23, 2007

good errors vs. bad errors

One of the things I love most about PostgreSQL is its beautiful error messages. For instance, let's say I've inexplicably forgotten to add my home state to a table of states, thus violating a foreign key constraint when I try to insert a Minnesotan city. Here it is in Oracle:

SQL> insert into city values ('International Falls','MN');
insert into city values ('International Falls','MN')
ERROR at line 1:
ORA-02291: integrity constraint (MYSCHEMA.FK_CITY_STATE) violated - parent key not found

OK, that doesn't suck. In this case, it's pretty easy to figure out the problem, especially since I gave my constraint a meaningful name. However, there are cases where the foreign key is not so obvious - maybe it involves multiple columns - and I'm sometimes sent off to look up details on what FK_CITY_STATE involves. Since DESCRIBE won't work on a constraint, that means either an intricate query joining several V$ tables, or a graphical tool like Toad or SQL Developer.

Now I'll make the same mistake in psql.

mydb=# insert into city values (2,'International Falls','MN');
ERROR: insert or update on table "city" violates foreign key constraint "fk_city_state"
DETAIL: Key (state)=(MN) is not present in table "state".

Now that's clarity! It's one of the things I love about PostgreSQL. After all, the database has all this detail easily accessible to it; why shouldn't it deliver it to me along with the error message? It's even more important when an error like this is thrown from somewhere deep inside some code, where it may not be obvious to me which statement or which row is responsible for it - having the offending value displayed is incredibly convenient. It's considerate programming - no more, no less.

I'll say this for Oracle, though - Oracle's standardized ABC-12345 error codes are really handy for looking them up, for searches on Metalink or Google.

In any case, either of these SQL environments have good error reporting. But what about Oracle Enterprise Manager?

Where am I supposed to go with this? Shall we go search on Metalink for "An error has occurred"? Does someone at Oracle imagine that this message is, in some mysterious way, useful? Maybe there's some logfile somewhere on the server that will tell me more... maybe... somewhere. Maybe. Somewhere.

Seriously, folks. OEM has been around for a long time now. It still throws lots of errors, often for no apparent fault of the DBA (I got this one simply by trying to access the "Administration" tab.) But I would forgive throwing lots of errors, if the errors were clear, and if they included information to facilitate finding out more. This, however, I cannot forgive, and it is all too typical of my experiences with OEM. (Well, a few years back, the errors tended to be screenfuls of Java error-stack vomit; now most of those seem to have been "cleaned up" into these poker-faced non-messages. That's progress? It's like Windows 3.1 all over again.)

So, if you see me rolling my eyes next time an Oracle rep shows a slide telling us how wonderful lovely OEM makes DBAs' jobs so much easier... this is why.

1 comment:

Anonymous said...

I'm surprised Oracle's infamous "<some_object_type> does not exist" family of errors (as in "Table or view does not exist"). Does anyone actually think it wouldn't be infinitely more helpful to say something like PostgreSQL's "Relation <name> does not exist"?