473,570 Members | 2,857 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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!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-----

Nov 12 '05 #1
9 2125
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

Nov 12 '05 #2
> 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!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-----

Nov 12 '05 #3
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*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #4
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 YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 12 '05 #5
Peter Eisentraut <pe*****@gmx.ne t> 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

Nov 12 '05 #6
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 YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 12 '05 #7
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

Nov 12 '05 #8
Peter Eisentraut <pe*****@gmx.ne t> 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

Nov 12 '05 #9
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

Nov 12 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
2251
by: mikey_boy | last post by:
Hello! Curious if anyone could give me a hand. I wrote this PHP script with makes a simple connection to a mysql database called firstdb and just pulls back the results and displays on the webpage. Here is the script. I appreciate any light that could be shed on the script and why I would get the errors. Thank you,
0
3029
by: Morten Gulbrandsen | last post by:
mysql> USE company; Database changed mysql> mysql> DROP TABLE IF EXISTS EMPLOYEE; -------------- DROP TABLE IF EXISTS EMPLOYEE -------------- Query OK, 0 rows affected (0.00 sec)
1
8037
by: annie | last post by:
Hi all, I have recently ported my Access 2000 app to SQL Server, keeping the Access client as the front end using linked tables. I am also using triggers on my SQL tables to trap orphan records and validate added data. My question is..
2
19029
by: MLH | last post by:
I cut a mail function off the m'soft site. Has always worked. However, I would like to include error codes returned by the sendmail Fn and be able to understand what they mean. I had my first occasion to experience a failure and got a code of 2??? Would like to know just what that means. Here's how I call the fn... Result =...
4
5986
by: Sean Shanny | last post by:
To all, Running into an out of memory error on our data warehouse server. This occurs only with our data from the 'September' section of a large fact table. The exact same query running over data from August or any prior month for that matter works fine which is why this is so weird. Note that June 2004 through today is stored in the same...
1
2431
by: sshankar | last post by:
Hi, New to Stored procedure. Basically just installed DB2 v8.1.0.36 Was trying to build a stored procedure.. It is giving following error.. Looks like some error related to configuration files... Can you help I tried to have a look at all the files .. however unable to solve it .. can anyone let me know what i'm missing.. This is...
3
6387
by: Bobby | last post by:
Hi I'm using Access 2003 with SQL server 2000, linked via ODBC. Can anybody tell me how to capture SQL error codes in Access? If this is not possible, is there any way I can simply turn off SQL errors? Can I use docmd.setwarnings false? If so which event would I put it on. On error doesn't seem to work. Thanks
3
2110
by: nimajneb via AccessMonster.com | last post by:
Can anyone offer me any insight on the following problem? I have an Access database on a company shared drive. I'm the designer and the only user (so far). Suddenly, any time I try to open a table, Access crashes... but if I look at that same data by *querying* the table (SELECT * FROM ), everything works fine. Even though my data seems...
15
3233
by: Lawrence Krubner | last post by:
Does anything about this script look expensive, in terms of resources or execution time? This script dies after processing about 20 or 25 numbers, yet it leaves no errors in the error logs. This is on a server that handles a fairly demanding site. The defaults, in php.ini, have all been cranked fairly high: scripts get 180 seconds to run, and...
0
7950
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8151
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8000
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6332
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5247
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3684
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3671
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1238
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
974
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.