Thread: [GENERAL] Love Your Database project — Thoughts on effectively handling constraints?

Guyren Howe <guyren@gmail.com> writes:
> I believe a lot of application programmers, particularly but by no means limited to web application developers, have
atragic prejudice against treating their database as anything but a dumb data bucket. 
> They also often lack awareness of even simple-to-use SQL/Postgres features that would make their lives easier (top of
thislist would be CTEs and Window Functions). 

> So I’ve started a project to fix this. I’m initially going to write a series of blog posts demonstrating in principle
howa developer can put much/all of their model logic in their database. 

Cool.  This sounds well worth while.

> I’m starting with constraints. Using Ruby on Rails as my example, a server-side constraint violation shows up in
vanillaRails as an Exception, that looks like this: 

> PG::CheckViolation: ERROR:  new row for relation "users" violates check constraint "family_name_length"
> DETAIL:  Failing row contains (11, foo).

> What I need to do is turn this into something similar to the equivalent Rails-side constraint failure, which is a
nicelyformatted error message on the model object. 

> The obvious thing would be to parse the error message.

Well, that really isn't considered good practice, because your code will
fail on localized error messages.

> It occurs to me that I might instead do something on the server side. I’d like to get back a more structured error.

The server already does deliver more-structured error data, although I confess
that I have no idea how to get at it in Ruby on Rails.  In psql the case
looks about like this:

regression=# create table users (id int, name text, constraint family_name_length check(length(name) > 4));
CREATE TABLE
regression=# insert into users values (11,'foo');
ERROR:  new row for relation "users" violates check constraint "family_name_length"
DETAIL:  Failing row contains (11, foo).
regression=# \errverbose
ERROR:  23514: new row for relation "users" violates check constraint "family_name_length"
DETAIL:  Failing row contains (11, foo).
SCHEMA NAME:  public
TABLE NAME:  users
CONSTRAINT NAME:  family_name_length
LOCATION:  ExecConstraints, execMain.c:1810

The 23514 part is ERRCODE_CHECK_VIOLATION, which evidently corresponds to
"PG::CheckViolation" in the Ruby code, so at least that much is exposed
to you in a reasonable way.  What you want is to get at the error-report
fields shown here as "SCHEMA NAME", "TABLE NAME", "CONSTRAINT NAME".
I don't know if the Ruby client library makes those accessible.

(In the spirit of full disclosure, I should point out that those
fields have only been provided since PG 9.3.  So older installations
may not have the ability to do this in the Right Way.  But still,
you should be evangelizing for doing it the Right Way, no?)

            regards, tom lane

On Fri, Dec 16, 2016 at 3:54 PM, Guyren Howe <guyren@gmail.com> wrote:

> What I need to do is turn this into something similar to the equivalent
> Rails-side constraint failure, which is a nicely formatted error message on
> the model object.

Can you show what the text in such a message looks like?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company