0

I'm trying to find a good way to return error messages from postgresql back to my client-side application. I've read about RAISE, which appears to be what I want, but I have couple questions. Using Postgresql 9.1.

An overview of what's going on, I'm receiving a JSON object from the client, parse the JSON into a Perl Hash, loop through the keys and do an update/insert on each iteration. The hash keys correlate with the my database table names.

I have 3 sql functions like this (all the same name). One accepts text,timestamp with timezone, and an integer. (query used from https://stackoverflow.com/a/6527838/722238) 

-- $1 = Table Key, $2 = Table Value, $3 = Table Name.
create function ex.foo(integer,text,text) Arg2 could be timestamp,text,or integer.  
    returns void as $$
    begin
    execute format('update %3$s set v= %2$L where k = %1$L;
                insert into %3$s (k,v) select %1$s, %2$L 
                  where not exists (select 1 from %3$s where k = %1$L)', $1,$2,$3);
    -- RAISE a descriptive error. 
    return;
    end;
$$ language 'plpgsql';

At the moment, I'm doing this (pseudo-ish code):

if (Begin) {
    eval {
        my $tk = 123;
        foreach my $k (keys %js) {
            ## tk = table key, $k = table name, $js{$k} = value 
            executeq("select ex.foo(tk,$js{$k},$k);") or die error(pgError); # Function to handle error?
         }
         Commit;
     };
     if ($@) {
         RollBack;
         ## Return $@ 
     }
}

My problem is, I'm not sure how I should generate a descriptive error (where/why), return it up the chain and in language the user can understand. Again, how should I raise the error, and should it be passed to an error handler function? What is the standard? How do others achieve this?

Also, are there issues I may run into while looping through my hash and insert/updating values like this?

Community
  • 1
  • 1
fbynite
  • 2,651
  • 1
  • 21
  • 25
  • 1
    Generating an error suitable for the **user** is something that should be done on the UI layer. (It's better suited for handling things like localising the error message.) On the database layer, you should probably focus on emitting a stable error code of some sort. Obviously, if you can, it makes sense to raise an error code *and* some sort of readable description, but aimed more at the developer handling this error (who may choose to show an error message but could also resolve it in another way) than at the end user. – millimoose Oct 31 '12 at 22:06
  • 1
    That code is prone to a race condition. You must be prepared to retry the transaction if you get a key constraint violation error. So you shouldn't really be trying to format a user-friendly message at that level. Do it after you've retried the upsert a couple of times, and format it at the application level. Don't parse the error message, check the `SQLSTATE` in the application to determine what the error was. See http://www.postgresql.org/docs/current/interactive/errcodes-appendix.html . You can set the error code in `RAISE`. – Craig Ringer Oct 31 '12 at 22:47
  • @CraigRinger, If I understand, I RAISE a SQLSTATE code, pass that to my application, and display the message in a user-friendly way. How is the code passed to Perl? – fbynite Nov 01 '12 at 00:10
  • @fbynite http://www.google.com.au/search?q=perl+DBI+sqlstate&oq=perl+DBI+sqlstate finds http://search.cpan.org/~turnstep/DBD-Pg-2.19.3/Pg.pm as the first link, which has the sub-heading http://search.cpan.org/~turnstep/DBD-Pg-2.19.3/Pg.pm#state . `perldoc DBD::Pg` would've done the trick too. – Craig Ringer Nov 01 '12 at 01:20
  • That's exactly what I need, right there in the docs. Thanks! – fbynite Nov 01 '12 at 01:42

1 Answers1

1

Use the Try::Tiny module. It gives you the more readable try/catch syntax, and also avoids a couple of potential gotchas involved with using eval.

To handle the error, either do that in the catch block (or the 'if ($@)' block in your current code), or call a separate error handling function from that block.

sockmonk
  • 4,195
  • 24
  • 40