Getting error codes for failed queries?

[Alejandro Forero Cuervo]

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!yjoV0msf Q!sfiupob!utvK' x44;print map{("\e[7m \e[0m",chr ord
(chop$0)-1)[$_].("\n")[++$i%77]}split//,unpack'B*',pac k'H*',($F='F'x1 9).
"F0F3E0607879CC 1E0F0F339F3FF39 9C666733333CCF8 7F99E6133999999 E67CFFCCF3".
"219CC1CCC033E7 E660198CCE4E667 98303873CCE60F3 387$F"#Don't you love Perl?

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQE/jXvqkEv0SM2OU1k RAmnFAJ9FCiI+F7 x/krk+Y5kGeEr2oS3 zlwCeJwYS
sheWjBSjJWm6CfC EdCtWHRU=
=mlGd
-----END PGP SIGNATURE-----

[Peter Eisentraut]

Re: Getting error codes for failed queries?

Alejandro Forero Cuervo writes:
[color=blue]
> 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?[/color]

In 7.4 there will be error codes.
[color=blue]
> Do those strings get translated to different languages, for
> instance?[/color]

Yes.

--
Peter Eisentraut peter_e@gmx.net


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Alejandro Forero Cuervo]

Re: Getting error codes for failed queries?

> 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?
[color=blue][color=green]
> > Do those strings get translated to different languages, for
> > instance?[/color]
>
> Yes.[/color]

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!yjoV0msf Q!sfiupob!utvK' x44;print map{("\e[7m \e[0m",chr ord
(chop$0)-1)[$_].("\n")[++$i%77]}split//,unpack'B*',pac k'H*',($F='F'x1 9).
"F0F3E0607879CC 1E0F0F339F3FF39 9C666733333CCF8 7F99E6133999999 E67CFFCCF3".
"219CC1CCC033E7 E660198CCE4E667 98303873CCE60F3 387$F"#Don't you love Perl?

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQE/jZe9kEv0SM2OU1k RAtuuAJ9gL2/NGwyG7KQZ2+HYh/cDioAfIACdFvoU
G6d+OQD/qLcSF3zsMqydR9Y =
=kWHR
-----END PGP SIGNATURE-----

[Alvaro Herrera]

Re: Getting error codes for failed queries?

On Wed, Oct 15, 2003 at 01:53:49PM -0500, Alejandro Forero Cuervo wrote:[color=blue][color=green]
> > In 7.4 there will be error codes.[/color]
>
> Does that imply that the socket-level protocol for backend /
> frontend communication will change in version 7.4?[/color]

Yes.
[color=blue][color=green][color=darkred]
> > > Do those strings get translated to different languages, for
> > > instance?[/color]
> >
> > Yes.[/color]
>
> I guess that rules out the option of parsing them to detect what
> went wrong.[/color]

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)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Peter Eisentraut]

Re: Getting error codes for failed queries?

Alvaro Herrera writes:
[color=blue]
> 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.[/color]

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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Tom Lane]

Re: Getting error codes for failed queries?

Peter Eisentraut <peter_e@gmx.ne t> writes:[color=blue]
> Alvaro Herrera writes:[color=green]
>> 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.[/color][/color]
[color=blue]
> Because otherwise any random user could obscure his actions in the server
> log by setting the language to something the admin can't read.[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Re: Getting error codes for failed queries?

Alejandro Forero Cuervo <bachue@bachue. com> writes:[color=blue][color=green]
>> In 7.4 there will be error codes.[/color][/color]
[color=blue]
> Does that imply that the socket-level protocol for backend /
> frontend communication will change in version 7.4?[/color]

You can still use the old protocol (and even the one before that...)
but it won't give you error codes.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Peter Eisentraut]

Re: Getting error codes for failed queries?

Tom Lane writes:
[color=blue]
> 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".[/color]

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."
[color=blue]
> 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.[/color]

Indeed, but it needs to be fixed sometime.

--
Peter Eisentraut peter_e@gmx.net


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Re: Getting error codes for failed queries?

Peter Eisentraut <peter_e@gmx.ne t> writes:[color=blue]
> 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."[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Peter Eisentraut]

Re: Getting error codes for failed queries?

Tom Lane writes:
[color=blue]
> 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?[/color]

The only way we can really get anywhere is if we redesign the locale
layer. Coming soon.

--
Peter Eisentraut peter_e@gmx.net


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html