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!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).
"F0F3E0607879CC1E0F0F339F3FF399C666733333CCF87F99E 6133999999E67CFFCCF3".
"219CC1CCC033E7E660198CCE4E66798303873CCE60F3387$F "#Don't you love Perl?

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

iD8DBQE/jXvqkEv0SM2OU1kRAmnFAJ9FCiI+F7x/krk+Y5kGeEr2oS3zlwCeJwYS
sheWjBSjJWm6CfCEdCtWHRU=
=mlGd
-----END PGP SIGNATURE-----

[Peter Eisentraut]

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 pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

[Alejandro Forero Cuervo]

> 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).
"F0F3E0607879CC1E0F0F339F3FF399C666733333CCF87F99E 6133999999E67CFFCCF3".
"219CC1CCC033E7E660198CCE4E66798303873CCE60F3387$F "#Don't you love Perl?

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

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

[Alvaro Herrera]

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)

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

[Peter Eisentraut]

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 pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

Peter Eisentraut <pe*****@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

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

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

[Tom Lane]

Alejandro Forero Cuervo <ba****@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Peter Eisentraut]

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 pe*****@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]

Peter Eisentraut <pe*****@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

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

[Peter Eisentraut]

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 pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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