Thread: Getting error codes for failed queries?
Hello. I'm new to PostgreSQL and I'm using it for some project. I am currently creating an entity engine that provides a web interface, which is oriented towards end users, to the database based on some enriched description of the tables (from which it also generates the SQL code to create them). My application connects to the database using the socket-level frontend/backend protocol rather than providing a wrapper around the libpq library. When an error takes places executing a query, all I get is string describing the error, such as ``ERROR: Cannot insert a duplicate key into unique index locations_name_key''. However, I can't pass this error description back to the end-users: I have to translate it into something meaningful for them (for instance, something that refers to the fields they are seeing in their HTML forms and something in the language they selected). Currently, when an error takes place, I send multiple queries back to the database trying to detect what could be the cause(s) of the error. This seems wrong as I can't know in advance, in a maintainable way, all the possible causes for errors. It is also slow as multiple (ideally redundant) queries have to be sent to the database. I could also parse the error string and try to detect what went wrong based on it, but I'd rather stay away from that option, which doesn't look very maintainable. Or should I do that? Do those strings get translated to different languages, for instance? What would experienced PostgreSQL users suggest I do? Alejo. http://bachue.com/alejo -- The mere formulation of a problem is far more essential than its solution. -- Albert Einstein. $0='!/sfldbi!yjoV0msfQ!sfiupob!utvK'x44;print map{("\e[7m \e[0m",chr ord (chop$0)-1)[$_].("\n")[++$i%77]}split//,unpack'B*',pack'H*',($F='F'x19). "F0F3E0607879CC1E0F0F339F3FF399C666733333CCF87F99E6133999999E67CFFCCF3". "219CC1CCC033E7E660198CCE4E66798303873CCE60F3387$F"#Don't you love Perl?
Attachment
Alejandro Forero Cuervo writes: > I could also parse the error string and try to detect what went > wrong based on it, but I'd rather stay away from that option, > which doesn't look very maintainable. Or should I do that? In 7.4 there will be error codes. > Do those strings get translated to different languages, for > instance? Yes. -- Peter Eisentraut peter_e@gmx.net
> In 7.4 there will be error codes. Does that imply that the socket-level protocol for backend / frontend communication will change in version 7.4? > > Do those strings get translated to different languages, for > > instance? > > Yes. I guess that rules out the option of parsing them to detect what went wrong. Thank you. Alejo. http://bachue.com/alejo -- The mere formulation of a problem is far more essential than its solution. -- Albert Einstein. $0='!/sfldbi!yjoV0msfQ!sfiupob!utvK'x44;print map{("\e[7m \e[0m",chr ord (chop$0)-1)[$_].("\n")[++$i%77]}split//,unpack'B*',pack'H*',($F='F'x19). "F0F3E0607879CC1E0F0F339F3FF399C666733333CCF87F99E6133999999E67CFFCCF3". "219CC1CCC033E7E660198CCE4E66798303873CCE60F3387$F"#Don't you love Perl?
Attachment
On Wed, Oct 15, 2003 at 01:53:49PM -0500, Alejandro Forero Cuervo wrote:
> > In 7.4 there will be error codes.
>
> Does that imply that the socket-level protocol for backend /
> frontend communication will change in version 7.4?
Yes.
> > > Do those strings get translated to different languages, for
> > > instance?
> >
> > Yes.
>
> I guess that rules out the option of parsing them to detect what
> went wrong.
Yes, unless you leave to lc_messages option defined to "C".
Unfortunately this cannot be changed at runtime by non-superusers, I
don't know why.
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Porque Kim no hacia nada, pero, eso sí,
con extraordinario éxito" ("Kim", Kipling)
Alvaro Herrera writes: > Yes, unless you leave to lc_messages option defined to "C". > Unfortunately this cannot be changed at runtime by non-superusers, I > don't know why. Because otherwise any random user could obscure his actions in the server log by setting the language to something the admin can't read. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
> Alvaro Herrera writes:
>> Yes, unless you leave to lc_messages option defined to "C".
>> Unfortunately this cannot be changed at runtime by non-superusers, I
>> don't know why.
> Because otherwise any random user could obscure his actions in the server
> log by setting the language to something the admin can't read.
Hmm, that seems overly paranoid to me. J Random Luser could not obscure
his actions (if the admin is logging SQL statements). He could possibly
obscure his error messages, but is that enough of a security risk to
take away the ability of users to read their own errors in a convenient
language? I'd lean to "no".
Of course the really nice answer would be to have different language
settings for messages to the system log and messages to the client, but
I suppose that's probably not very practical given the way gettext works.
regards, tom lane
Alejandro Forero Cuervo <bachue@bachue.com> writes:
>> In 7.4 there will be error codes.
> Does that imply that the socket-level protocol for backend /
> frontend communication will change in version 7.4?
You can still use the old protocol (and even the one before that...)
but it won't give you error codes.
regards, tom lane
Tom Lane writes: > Hmm, that seems overly paranoid to me. J Random Luser could not obscure > his actions (if the admin is logging SQL statements). He could possibly > obscure his error messages, but is that enough of a security risk to > take away the ability of users to read their own errors in a convenient > language? I'd lean to "no". The other problem is that changing the language at runtime doesn't really work well, because there are obscure dependencies on LC_CTYPE and depending on the implementation of gettext there is some caching going on. So the real answer is actually, "It doesn't work, but the superuser is free to try." > Of course the really nice answer would be to have different language > settings for messages to the system log and messages to the client, but > I suppose that's probably not very practical given the way gettext works. Indeed, but it needs to be fixed sometime. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
> The other problem is that changing the language at runtime doesn't really
> work well, because there are obscure dependencies on LC_CTYPE and
> depending on the implementation of gettext there is some caching going on.
> So the real answer is actually, "It doesn't work, but the superuser is
> free to try."
Ugh. But LC_CTYPE isn't really going anywhere in our current setup, and
the feature would still be useful if we forced users to select their
message language at backend start time (via PGOPTIONS, for instance).
Can we get anywhere if we make those assumptions?
regards, tom lane
Tom Lane writes: > Ugh. But LC_CTYPE isn't really going anywhere in our current setup, and > the feature would still be useful if we forced users to select their > message language at backend start time (via PGOPTIONS, for instance). > Can we get anywhere if we make those assumptions? The only way we can really get anywhere is if we redesign the locale layer. Coming soon. -- Peter Eisentraut peter_e@gmx.net