Thread: Exception messages -> application?
First of all, many thanks to all the developers for creating such a great DB.
I have a moderately DB-ignorant question: is there a "built-in" way for an
application to receive the message emitted by a RAISE .... in a PgSQL function?
Context: I have a moderately complex application (in python, using psycopg2)
that we use to help guide our research support operations. Occasionally
the application doesn't allow the user to do something because of some
trigger or other PgSQL function has raised an exception. While sometimes the
application can make a plausible conjecture as to the cause, other times
it is necessary to dig through the database logs to read the message emitted
by the server-side DB function. This is frustrating for the users.
Presumably the function could do something like writing the message to
a special-purpose table that the user application could read (but perhaps
only when not in a transaction); or perhaps some sort of notify/listen system
could be set up. What method[s] are generally the best in these circumstances?
It would be best if it didn't require changing all of the PgSQL functions
(ouch!), though that is not impossible.
Thanks for your insightful ideas!
-f
On Fri, Apr 04, 2008 at 12:02:20PM -0700, Frank Miles wrote:
> I have a moderately DB-ignorant question: is there a "built-in" way for an
> application to receive the message emitted by a RAISE .... in a PgSQL function?
>
> Context: I have a moderately complex application (in python, using psycopg2)
psycopg2 does a pretty good job of converting any PostgreSQL
side exceptions into Python exceptions which are then
getting raised in your Python code. Unless you explicitely
silence/catch PG exceptions inside your plpgsql code you
should be seeing them turn up in Python.
There's a bunch of arguments on the Python exception raised.
try:
...
except TheExceptionType, e:
print dir(e)
might help with that.
Also, you may need to fiddle with the PG client logging
settings (in postgresql.conf) which control what level of
detail of error information is getting sent to the client in
the first place.
BTW, are you talking about RAISE EXCEPTION or RAISE NOTICE ?
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
>On Fri, Apr 04, 2008 at 12:02:20PM -0700, Frank Miles wrote:
>
>> I have a moderately DB-ignorant question: is there a "built-in" way for an
>> application to receive the message emitted by a RAISE .... in a PgSQL function?
>>
>> Context: I have a moderately complex application (in python, using psycopg2)
>
>psycopg2 does a pretty good job of converting any PostgreSQL
>side exceptions into Python exceptions which are then
>getting raised in your Python code. Unless you explicitely
>silence/catch PG exceptions inside your plpgsql code you
>should be seeing them turn up in Python.
>
>There's a bunch of arguments on the Python exception raised.
>
>try:
> ...
>except TheExceptionType, e:
> print dir(e)
>
>might help with that.
>
>Also, you may need to fiddle with the PG client logging
>settings (in postgresql.conf) which control what level of
>detail of error information is getting sent to the client in
>the first place.
>
>BTW, are you talking about RAISE EXCEPTION or RAISE NOTICE ?
>
>Karsten
Thanks, Karsten. At some point in the forgotten past, I had set up
a DB-handling python class which (among many other things) catches
psycopg exceptions - your guess there was right on the mark. I should
be able to alter that code to fix this problem.
Unfortunately this does not help for lesser events (i.e. NOTICE and WARNING).
My preliminary effort suggests that psycopg isn't passing these. I'll
take a further look at the client logging settings - will these be
sufficient?
Thanks again!
-f
On Sat, Apr 05, 2008 at 08:42:34PM -0700, Frank Miles wrote: > Unfortunately this does not help for lesser events (i.e. NOTICE and WARNING). > My preliminary effort suggests that psycopg isn't passing these. Not as exceptions, certainly. For one thing there's cursor.statusmessage which (should) contain whatever PostgreSQL replies to a command, say, SELECT or UPDATE and the number of rows affected. It's what you see in psql. > I'll > take a further look at the client logging settings - will these be > sufficient? They should. They'll define what gets shipped to the client in the status message. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
I've got a Postgres v8.2.4 server that we recently upped from 2GB RAM to 6GB. I added: effective_cache_size = 5120MB to the postgresql.conf file but when I restart the server and do a "show all" it always comes back with "1000MB". I can set it manually and it takes but for some reason it just seems to ignore it in the config file. Is there something else that needs to be present for this? I've tried setting it in both the numeric-only and "MB" forms. I've included a portion of the config at the end here, I realize it's probably not quite right yet. Thanks, Joe #----------------------------------------------------------------------- ---- # RESOURCE USAGE (except WAL) #----------------------------------------------------------------------- ---- # - Memory - shared_buffers = 600MB work_mem = 100MB maintenance_work_mem = 300MB max_stack_depth = 5MB effective_cache_size = 5120MB ______________ The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you. _____________
"Hardwick, Joe" <Joe.Hardwick@fnis.com> writes:
> I've got a Postgres v8.2.4 server that we recently upped from 2GB RAM to
> 6GB. I added:
> effective_cache_size = 5120MB
> to the postgresql.conf file but when I restart the server and do a "show
> all" it always comes back with "1000MB". I can set it manually and it
> takes but for some reason it just seems to ignore it in the config file.
Sure sounds to me like you're editing the wrong config file ...
regards, tom lane
Turns out someone had placed a second line further down in the file I
didn't realize was there... So it set it, then reset it.
Thanks,
Joe
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, April 08, 2008 8:53 AM
To: Hardwick, Joe
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Trouble getting effective_cache_size parameter to
take
"Hardwick, Joe" <Joe.Hardwick@fnis.com> writes:
> I've got a Postgres v8.2.4 server that we recently upped from 2GB RAM
> to 6GB. I added:
> effective_cache_size = 5120MB
> to the postgresql.conf file but when I restart the server and do a
> "show all" it always comes back with "1000MB". I can set it manually
> and it takes but for some reason it just seems to ignore it in the
config file.
Sure sounds to me like you're editing the wrong config file ...
regards, tom lane
______________
The information contained in this message is proprietary and/or confidential. If you are not the
intended recipient, please: (i) delete the message and all copies; (ii) do not disclose,
distribute or use the message in any manner; and (iii) notify the sender immediately. In addition,
please be aware that any message addressed to our domain is subject to archiving and review by
persons other than the intended recipient. Thank you.
_____________