postgresql Error handling in client app - Mailing list pgsql-general

Hi,

i have a question about how to handle postgresql constraint errors in
the client app. I found some mails in the archive about it, too. But
i have still so many questions about how to do it, where to check it
and how to display a good error message. I would love to hear some
comments about my ideas:



1. usual way: double check all values in client app

CREATE TABLE accounts (
  id SERIAL PRIMARY KEY
);

CREATE TABLE users (
  account  text REFERENCES accounts(id),
  username text,
  CONSTRAINT username_valid CHECK(username~'^[a-zA-Z0-9-]{3,20}$'),
  PRIMARY KEY (account, username)
);

# insert into users VALUES ('jk_user');
ERROR:  ExecInsert: rejected due to CHECK constraint "username_valid"
on "users"

The client app (like a php web app or whatever) has two possibilties:

a) check the username before trying to insert it. The client app will
have to know all the constraints already defined in the database and
has a catalog of valueable error messages.

drawbacks:
- If database constraint changes, all client apps will have to change
its validation routines.
- Some constraints can't be easily checked outside the database. If
there is only a limited amount of users allowed per account, the
client app has to SELECT FOR UPDATE the users table first, check it,
and insert it in one transaction. And this check has to be also done
on insert by the database, too.

b) just do the insert and parse the sql error message for known
constraint_names and lookup a reasonable error message. One user
proposed in the archives to insert the error message in the
constraint name like this:

CONSTRAINT "#valid_dusername#Please only use alphanumeric values##"
CHECK(username~'^[a-zA-Z0-9-]{3,20}$')

drawbacks:
- sql Error messages can't be parsed easily and are subject of change.
- you can only show one error message at a time because postgresql
returns on first failing constraint.





2. way: building triggers for validation

Now i thought of keeping all information inside the database and have
all errors logged inside a table "errors". The returning errorcode
can be looked up in the error table to get real world error messages:

Its real world code and works with postgresl 7.3.3 if your databse
supports plpgsql language (createdb test; creatlang plpgsql test)

CREATE TABLE errors (
  id           SERIAL,
  tablename    text,
  colname      text,
  errormsg     text
);

CREATE TABLE accounts (
  id text PRIMARY KEY
);

CREATE TABLE users (
  account  text REFERENCES accounts(id),
  name     text,
  email    text,
  PRIMARY KEY (account, name)
);

CREATE FUNCTION tg_users_check () RETURNS TRIGGER AS '
  DECLARE
    var_errmsg   text := ''users'';
    var_error    boolean;
    var_error_nr int8;
    rec_any      RECORD;
    var_count    int4;
    var_maxmitgl int4 := 3;
  BEGIN

    SELECT INTO var_error_nr nextval(''errors_id_seq'');

    ---------------
    -- count     --
    ---------------

    IF TG_OP = ''INSERT'' THEN
      SELECT INTO var_count
             COUNT(name)
      FROM   users
      WHERE  account = NEW.account;

      IF var_count >= var_maxmitgl  THEN
        var_error  := ''true'';
        INSERT INTO errors VALUES (currval(''errors_id_seq''),
''users'', ''name'', ''Maximum amount of users ('' || var_maxmitgl ||
'') reached.'');
      END IF;
    END IF;

    ---------------
    -- name      --
    ---------------

    NEW.name := btrim(NEW.name);

    IF TG_OP = ''INSERT'' THEN
      SELECT INTO rec_any
             name
      FROM   users
      WHERE  name = NEW.name AND account = NEW.account;

      IF FOUND THEN
        var_error  := ''true'';
        INSERT INTO errors VALUES (currval(''errors_id_seq''),
''users'', ''name'', ''username is already registered.'');
      END IF;
    END IF;

    IF NEW.name !~ ''^[A-Za-z][A-Za-z0-9-]+$'' THEN
      var_error  := ''true'';
      INSERT INTO errors VALUES (currval(''errors_id_seq''),
''users'', ''name'', ''username has to been alphanumeric and start
with a letter.'');
    END IF;

    IF length(NEW.name) > 20  THEN
      var_error  := ''true'';
      INSERT INTO errors VALUES (currval(''errors_id_seq''),
''users'', ''name'', ''username is too long.'');
    END IF;

    IF length(NEW.name) < 3  THEN
      var_error  := ''true'';
      INSERT INTO errors VALUES (currval(''errors_id_seq''),
''users'', ''name'', ''username is too short.'');
    END IF;

    IF var_error THEN
      RAISE EXCEPTION ''%'', var_error_nr;
    END IF;
    RETURN NEW;
  END;
' language 'plpgsql';

CREATE TRIGGER tg_users BEFORE INSERT OR UPDATE ON users FOR EACH ROW
EXECUTE PROCEDURE tg_users_check();

INSERT INTO accounts VALUES ('foo');
INSERT INTO accounts VALUES ('bar');
INSERT INTO users VALUES ('bar', 'john', 'john@doe.com');
INSERT INTO users VALUES ('bar', 'john', 'john@doe.com');
INSERT INTO users VALUES ('bar', 'john.=donjohnsonistoolong',
'john@doe.com');




you can even place all error messages inside a table and reference it
from errors. but this code should be enough to understand my approach.

benefits:
- most of the errorchecking can be done at one place: inside the db.
- you get reasonable error messages.
- the client app doesn't care anymore about the validation of data.
- you can lookup the most common errors of your users
- parsing the sql error message for error code is easy

drawbacks:
- if you want to tell the user beforehand which characters he can use
for a username, you still have to hardcode it into the client app.
- you usually dont want to give him a "Register as new user" form if
there are already to many users
- performance issues??
- sometimes you even have to check all the basic postgresql
constraints like a UNIQUE username
- database code becomes maybe unmaintainable??


What do you think about it? How do you usually do it?

I would really love to hear some comments on it.

kind regards,
janning