Today we have one new feature – extension of plpgsql's RAISE command.
Patch was written by Pavel Stehule and Tom Lane, and committed by Tom.
Commit log:
Improve plpgsql's RAISE command. It is now possible to attach DETAIL and HINT fields to a user-thrown error message, and to specify the SQLSTATE error code to use. The syntax has also been tweaked so that the Oracle-compatible case "RAISE exception_name" works (though you won't get a very nice error message if you just write that much). Lastly, support the Oracle-compatible syntax "RAISE" with no parameters to re-throw the current error from within an EXCEPTION block. In passing, allow the syntax SQLSTATE 'nnnnn' within EXCEPTION lists, so that there is a way to trap errors with custom SQLSTATE codes. Pavel Stehule and Tom Lane
Well, up until now RAISE was limited to very basic things like:
RAISE NOTICE 'DEBUG: Variable x = %', x; RAISE EXCEPTION 'User with given email (%) already exist.', in_email;
Of course you could use another log levels as well, but that was pretty much all of functionality.
Now, with this new patch there are much more options.
First of all, we can send “HINT" message together with error:
# CREATE OR REPLACE FUNCTION test_raise(i INT4) RETURNS void AS $BODY$ DECLARE BEGIN IF i < 15 THEN raise exception 'Too small value of i = %', i USING HINT = 'Try using value of 15 or higher.'; END IF; RETURN; END; $BODY$ LANGUAGE plpgsql; # SELECT test_raise(2); ERROR: Too small VALUE OF i = 2 HINT: Try USING VALUE OF 15 OR higher.
Nice. We can make it even better:
CREATE OR REPLACE FUNCTION test_raise(i INT4) RETURNS void AS $BODY$ DECLARE BEGIN IF i < 15 THEN raise exception 'Too small value of i.' USING HINT = 'Try using value of 15 or higher.', DETAIL = 'Given i = ' || i; END IF; RETURN; END; $BODY$ LANGUAGE plpgsql; # SELECT test_raise(2); ERROR: Too small VALUE OF i. DETAIL: Given i = 2 HINT: Try USING VALUE OF 15 OR higher.
This is of course pretty useful, but what I like the most from this new patch is:
CREATE OR REPLACE FUNCTION test_raise(i INT4) RETURNS void AS $BODY$ DECLARE BEGIN IF i < 15 THEN raise exception 'Too small value of i.' USING ERRCODE = 'unique_violation'; END IF; RETURN; END; $BODY$ LANGUAGE plpgsql;
When I run it, nothing really cool happens:
# SELECT test_raise(2); ERROR: Too small VALUE OF i.
So, what is this ERRCODE?
This can be used in error trapping with BEGIN EXCEPTION END blocks to distinguish between various errors!
For example. Let's change our function to:
CREATE OR REPLACE FUNCTION test_raise(i INT4) RETURNS void AS $BODY$ BEGIN IF i < 15 THEN raise exception 'Bad i.' USING ERRCODE = 'unique_violation'; END IF; IF i > 1000000 THEN raise exception 'Bad i.' USING ERRCODE = 'program_limit_exceeded'; END IF; RETURN; END; $BODY$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION call_test(i INT4) RETURNS void AS $BODY$ BEGIN BEGIN perform test_raise(i); EXCEPTION WHEN unique_violation THEN raise notice 'It looks like we have unique violation.'; WHEN program_limit_exceeded THEN raise notice 'It looks like we have program LIMIT exceeded.'; END; RETURN; END; $BODY$ LANGUAGE plpgsql;
# SELECT call_test(2); NOTICE: It looks LIKE we have UNIQUE violation. call_test ----------- (1 ROW) # SELECT call_test(20000000); NOTICE: It looks LIKE we have program LIMIT exceeded. call_test ----------- (1 ROW) # SELECT call_test(20); call_test ----------- (1 ROW)
As you can see, now the external function can easily check what happened. Before this, all “raise exceptions" raised “raise_exception" error, which was not really useful.
ERRCODE can take codename of number of error. List of all possible errors is available in PostgreSQL docs.
Interesting part is, that while you can't create your own textual errcodes, you can easily return unknown “numerical" codes:
CREATE OR REPLACE FUNCTION test_raise(i INT4) RETURNS void AS $BODY$ BEGIN IF i < 15 THEN raise exception 'Bad i.' USING ERRCODE = 'P0004'; END IF; IF i > 1000000 THEN raise exception 'Bad i.' USING ERRCODE = 'program_limit_exceeded'; END IF; RETURN; END; $BODY$ LANGUAGE plpgsql;
In this case, calling external function will work like this:
# SELECT call_test(2); ERROR: Bad i. CONTEXT: SQL statement "SELECT test_raise( $1 )" PL/pgSQL FUNCTION "call_test" line 3 at PERFORM
And lastly, this patch allows You to simplify exception throwing, by allowing syntax like this:
CREATE OR REPLACE FUNCTION test_raise(i INT4) RETURNS void AS $BODY$ BEGIN raise unique_violation; RETURN; END; $BODY$ LANGUAGE plpgsql; # SELECT test_raise(2); ERROR: unique_violation
This raised exception with errcode ‘unique_violation', and since there was no message, errcode was used instead. To add a message, you can use method describe above, or:
CREATE OR REPLACE FUNCTION test_raise(i INT4) RETURNS void AS $BODY$ BEGIN RAISE unique_violation USING MESSAGE = 'test message'; RETURN; END; $BODY$ LANGUAGE plpgsql; # SELECT test_raise(2); ERROR: test message
Happy hacking 🙂
“Patch was written by Pavel Stehule and Tom Land, and committed by Tom.” should be read “(…) and Tom Lane, (…)”
@daniel: thanks, fixed.
Of all the wonderful things coming in 8.4 this has to be the best so far.
It’s the first feature that’s made me check out cvs and upgrade my development database on the spot. The best thing is that the protocol already supports these features for normal server error messages so the JDBC driver doesn’t even need to catch up. Stored-proc heavy database use just got a whole lot nicer.
Every Pg release knocks a few items off my private wishlist, but this probably second only to automatic cached query plan invalidation on the “awesome” scale.
I guess I’d better start dreaming about being able to use INSERT/UPDATE/DELETE RETURNING in subqueries or as input to INSERT / UPDATE … FROM . At this rate some kind (brilliant) soul will then contribute them to 8.5 .
Hi all,
May I know where this RAISE messages stored in computer.
@Srivani:
sorry, I don’t understand your question.
@Depesz
What is so hard to understand from Srivani’s question. Xe is asking where he can see the complete log. I am personally hoping for a system database view, but a file will also work for me too. The question is where can someone see those messages – it is a kind of log after all, isn’t it. So where is this log logged to and “stored in computer”. People want to read logs, no? Where to read it from? Where is it stored, so people can open it and read it… in computer for sure, but where???
So I was wondering this as was Srivani. So I say to myself: “Google it.. that should help right”. And google sends to to this site. And I am like: “Great, the answer is near, internet is great”. And… then I read your reply. I see you have questions yourself and so I hope I helped you answer them. Now lets see if internet can help me answer my question, which is : where in computer is stored this RAISE message
and now for the answer to my question. Which may or may not be what Srivani is asking. But if google leads other lost souls here instead of here: http://www.postgresql.org/docs/9.0/static/runtime-config-logging.html
on linux check: /var/log/postgresql/
on windows: i don’t know
NOTICE level i think is not enabled by default so such messages will not appear, but if “RAISE LOG ….” it actually works.
levels according to the link above are possible to be changed somehow with something like: log_min_messages (enum)
search for it in the html page… dont know if it works… dont care… RAISE LOG works for me
@Bastun:
what is so hard? Well, I just didn’t understand it. raise messages stored? Logged would be obvious. I just didn’t make the connection between “stored” and “logged”.
As for where it is logged – in log. Where is the log – depends on configuration. And no, it’s not that simple that on linux it’s in /var/log/postgresql.
I wrote about how to set (and check) logging in: https://www.depesz.com/2011/05/06/understanding-postgresql-conf-log/