I'm sitting deep in the belly of the Ruby on Rails PostgreSQL adapter
and the database has just notified me of an error. I've got an error
message (no codes) and an aborted connection. The pertinent errors are
violated uniqueness constraints and foreign keys. I'm about to raise an
exception which I'd like to spruce up with some machine understandable
information about the error.

I can find out, which columns are involved in the relevant unique index,
but I need a good connection for that. The only one I've got is aborted.
Given where I am, I have little information about the context. I can't
just ROLLBACK the current transaction and start a new one as there might
be a savepoint I need to go back to. Is there anything I can do?

Michael

--
Michael Schuerig
mailto:michael (AT) schuerig (DOT) de
http://www.schuerig.de/michael/

Michael Schuerig wrote:
I can't think of anything.

"Machine understandable" to me means SQL state.
Is there really no way to access the SQL state from Ruby?

But it seems that you want text, i.e. something for the DBA.
Since you mentioned "uniqueness", and "foreign keys", do you
really need more information than:

ERROR: duplicate key value violates unique constraint "testtab_pkey"

or

ERROR: insert or update on table "testtab2" violates foreign
key constraint "testtab2_id2_fkey"
DETAIL: Key (id2)=(2) is not present in table "testtab".

I guess I did not really understand your need.

Yours,
Laurenz Albe

Laurenz Albe wrote:

It is possible to execute SQL statements, but PostgreSQL won't accept
them as the connection is aborted. What other options are there?

It's a bit more tricky than that. At its ORM-layer, Ruby on Rails has
so-called validations that add error messages to persistent objects when
they fail the checks. Right now, these validations are independent of
the database, they are executed before an attempt to insert/update
anything. I'd like to add database-generated errors to the mix as that's
the only way to handle uniqueness violations anyway.

So, if an object can't be saved because it violates a uniqueness
constraint, I'd like to find out which attributes are involved in the
violation in order to show them in an error message. Because of the
architectural layering, I can't (and don't want to) access the database
from the UI layer. Rather, I'd like to look at the validation messages
attached to the objectionable object. Remember, that so far database
errors are not handled by the validation mechanism. Therefore my problem
is to put these messages in place after finding out what went wrong with
the failing SQL statement.

That's where I'm stuck: I don't see how to get any useful information
apart from the name of the violated constraint. As I wrote in my
original message, I don't have a good spare connection at hand and I
can't rollback the connection I have as I don't know whether a
transaction was in progress at all or if there are any savepoints behind
me.

Michael

--
Michael Schuerig
mailto:michael (AT) schuerig (DOT) de
http://www.schuerig.de/michael/

Michael Schuerig wrote:
I meant the 5 digit/letter error message number that is supposed to
be standardized across different DBMS, but from what you write below
I see that this is not what you need.

I understand now.

I know nothing about Ruby, but let me phantasize:
You could store this information somewhere in the class, in fields
that get filled when the class is first instantiated or something
like that.
Get the metadata from the database before you use the objects.
That way you retrieve the information *before* you get the error
and use it when you need it.

Does an approach like this make sense?

Yours,
Laurenz Albe

Laurenz Albe wrote:

Yes, I think it does. Thanks.

Michael

--
Michael Schuerig
mailto:michael (AT) schuerig (DOT) de
http://www.schuerig.de/michael/