Thread: Re: [GENERAL] Debugging SQL queries
Anastasios Hatzis <ahatzis@gmx.net> writes: > I have sometimes very long queries, for example in setup-purposed sql > file where plenty of tables of a new database are created in a single > transaction. 600 lines of code or more is common. Sometimes, while > developing, an error may appear after submitting such a query for > testing purposes via pgAdmin III query dialog. pgAdmin III displays the > PostgreSQL error, like shown below (example) > syntax error at or near "1" at character 2641 > My problem is that I don't know where the given character number is > inside the query/file. You should complain to the pgAdmin people that they are delivering an inadequate error message. psql, for example, does something like this in similar situations: regression=# select 1 + 1 +1 regression-# + 1 + 1 1 + 1 regression-# + 1 + 1; ERROR: syntax error at or near "1" at character 26 LINE 2: + 1 + 1 1 + 1 ^ regression=# If you ask me, a GUI that fails to make use of the cursor-position info that the backend supplies is a poor excuse for a GUI. We have actually removed the "at character N" bit from the default message format in CVS tip, on the assumption that every client app worth worrying about has got better methods than that for showing the error cursor position. pgAdmin needs to catch up before 8.2 comes out ;-) regards, tom lane
Tony Caduto <tony_caduto@amsoftwaredesign.com> writes: > Tom Lane wrote: >> We have actually removed the "at character N" bit from the default message format in CVS >> > What will the error format be for 8.2? In CVS tip you just see regression=# select 1 2; ERROR: syntax error at or near "2" LINE 1: select 1 2; ^ regression=# We still include "at character N" if the message is written to the server log, but the "primary message text" field of the error report sent to the client doesn't have it any more. This could still be revisited if anyone's really unhappy about it, but ISTM that if the client app is holding up its end about using the error-position field, putting it in the text is just clutter. regards, tom lane
On Thu, May 11, 2006 at 10:56:20AM -0400, Tom Lane wrote: > We still include "at character N" if the message is written to the > server log, but the "primary message text" field of the error report > sent to the client doesn't have it any more. Will the text returned by PQerrorMessage still include the character position? Wayne Conrad
Tom Lane wrote: > > We have actually removed the "at character N" bit from the default message format in CVS > Tom, What will the error format be for 8.2? Since the char number has been removed, what will it show? Line number? Thanks, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration
On Thu, May 11, 2006 at 09:39:59AM -0500, Tony Caduto wrote: > Tom Lane wrote: > > > >We have actually removed the "at character N" bit from the default message > >format in CVS > > > Tom, > What will the error format be for 8.2? Since the char number has been > removed, what will it show? > Line number? bench=# SELECT blah bench-# FROMx; ERROR: parse error at or near "FROMx" LINE 2: FROMx; ^ bench=# Note that it's still there in the server logs, though... ERROR: parse error at or near "FROMx" at character 13 -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461