Обсуждение: Debugging SQL queries
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
According my current PostgreSQL configuration error message looks
similar in the log files:
2006-05-11 09:51:55 ERROR: 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. I can search in my code editors (e.g. those of
Eclipse IDE) for line number + character number in given line number...
but I can not search for an overall character number. Counting all
characters from 1 to 2641 or any other character number would be really
frustrating in such big queries and it wouldn't be satisfying for me to
split the sql file into multiple smaller queries. How can I configure
PostgreSQL to get line number + character number in that line in the
error statements, or which other solution may help?
Development version is PostgreSQL 8.1.3 on Windows XP Pro.
Many thanks!
Anastasios
Anastasios Hatzis <ahatzis@gmx.net> writes: > My problem is that I don't know where the given character number is > inside the query/file. I can search in my code editors (e.g. those of > Eclipse IDE) for line number + character number in given line > number... but I can not search for an overall character > number. Emacs has the "goto-char" function and runs on Windows, so you could use that (there are probably other editors with this feature as well).. -Doug
Douglas McNaught wrote: > Emacs has the "goto-char" function and runs on Windows, so you could > use that (there are probably other editors with this feature as > well).. > Doug, thank you. I will use it. - Ha, I never thought I will ever use legendary Emacs. Probably I will even become an entry-class geek now? ;-) Anastasios
From my point of view, more important problem is that log doesn't help to find the query (in other words, log message doesn't show context) On 5/11/06, Douglas McNaught <doug@mcnaught.org> wrote: > Anastasios Hatzis <ahatzis@gmx.net> writes: > > > My problem is that I don't know where the given character number is > > inside the query/file. I can search in my code editors (e.g. those of > > Eclipse IDE) for line number + character number in given line > > number... but I can not search for an overall character > > number. > > Emacs has the "goto-char" function and runs on Windows, so you could > use that (there are probably other editors with this feature as > well).. > > -Doug > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- Best regards, Nikolay
On 5/11/06, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote: > From my point of view, more important problem is that log doesn't help > to find the query (in other words, log message doesn't show context) Yes it does.. but it depends on your logging setup. in psql: test=# blah; ERROR: syntax error at or near "blah" at character 1 my log shows: ... [9-1] LOG: statement: blah; ... [10-1] ERROR: syntax error at or near "blah" at character 1 Do you have: log_statement = true in your postgresql.conf ? -- Postgresql & php tutorials http://www.designmagick.com/
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
> -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane > Sent: 11 May 2006 15:00 > To: Anastasios Hatzis > Cc: pgsql-general@postgresql.org; pgadmin-hackers@postgresql.org > Subject: Re: [GENERAL] Debugging SQL queries > > pgAdmin needs to catch up before 8.2 comes out ;-) pgAdmin marks the line that the failure occurred at, and has done for years. Whether or not it should dump the cursor at the exact location is another issue altogether - it might upset ongoing editting (the user could be editting or adding to the query whilst a long running part is already running). Regards, Dave.
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
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
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 > > Hi Tom, I guess I didn't know there was a error-position field returned, I have been parsing out the "at character xx" to get the char number. Do you know offhand if there is the error-position field is discussed in the docs anywhere? 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 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
>> > Hi Tom, > I guess I didn't know there was a error-position field returned, I have > been parsing out the "at character xx" to get the char number. > > Do you know offhand if there is the error-position field is discussed in > the docs anywhere? Not a C guy but possibly? http://www.commandprompt.com/community/pgdocs81/libpq-exec http://www.commandprompt.com/community/pgdocs81/protocol-error-fields Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> I guess I didn't know there was a error-position field returned, I have
> been parsing out the "at character xx" to get the char number.
> Do you know offhand if there is the error-position field is discussed in
> the docs anywhere?
See PQresultErrorField().
regards, tom lane
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