Thread: pgAdmin III Query Tool bug
OS: WinXP SP2 PC: Pentium D 3 GHz, 2 GB RAM pgAdmin ver: 1.8.0, 1.8.1 postgresql ver: 8.2.5-1 I haven't been able to find these issues on the Internet or on the pgAdmin web site, so hopefully they are not duplicates of existing. Issue 1: Working on a ~ 1 MB *.sql file from the Query Tool - a schema file, inclusive of reference data of a database that I'm attempting to migrate from MySQL. When I save from the pgAdmin Query Tool, it saves a 0 byte UTF-8 encoded file (presumably there is still some data saved for Windows to realise that it is not an ANSI file). Yet it opens the 1 MB file fine when restored from svn, or when the data is copied from the Query Tool and saved through notepad as a UTF-8 file and other than missing datatypes such as uuid's that I still need to address, the parser is happy with the syntax. The save silently changes the dirty status of the file to clean - as indicated by removing the asterix from the file name in the application title bar, without providing an indication of a failed save (which is the worst part). Initially, it only seemed to occur after the application had been running for a few hours, with saves in the initial editing time saving fine, then after being running for awhile (several hours), subsequent saves began to save as 0 kb files. At least this seemed to be the behaviour with pgAdmin 1.8.0. Since upgrading to pgAdmin 1.8.1, every save of the file results in a 0 kb output, regardless of how long the application has been running. Potentially the content in the file itself has become corrupted somewhere along the line leading to the bad saves, but at the very least, I would expect the Query Tool to flag in some manner that the file could not be saved, not silently create a 0 kb file and indicate a successful save. Issue 2: This is trivial, but when a syntax error is detected in a large file (20,000+ lines), the parser gets the line number correct of the syntax error, but the further down the file the syntax error is, the greater the distance the error marker is off the target, with the error marker pulling up short of the faulty line. Cheers, Andy
On 07/01/2008, Andrew <archa@pacific.net.au> wrote: > OS: WinXP SP2 > PC: Pentium D 3 GHz, 2 GB RAM > pgAdmin ver: 1.8.0, 1.8.1 > postgresql ver: 8.2.5-1 > > I haven't been able to find these issues on the Internet or on the > pgAdmin web site, so hopefully they are not duplicates of existing. > > Issue 1: > > Working on a ~ 1 MB *.sql file from the Query Tool - a schema file, > inclusive of reference data of a database that I'm attempting to migrate > from MySQL. When I save from the pgAdmin Query Tool, it saves a 0 byte > UTF-8 encoded file (presumably there is still some data saved for > Windows to realise that it is not an ANSI file). Yet it opens the 1 MB > file fine when restored from svn, or when the data is copied from the > Query Tool and saved through notepad as a UTF-8 file and other than > missing datatypes such as uuid's that I still need to address, the > parser is happy with the syntax. The save silently changes the dirty > status of the file to clean - as indicated by removing the asterix from > the file name in the application title bar, without providing an > indication of a failed save (which is the worst part). > > Initially, it only seemed to occur after the application had been > running for a few hours, with saves in the initial editing time saving > fine, then after being running for awhile (several hours), subsequent > saves began to save as 0 kb files. At least this seemed to be the > behaviour with pgAdmin 1.8.0. Since upgrading to pgAdmin 1.8.1, every > save of the file results in a 0 kb output, regardless of how long the > application has been running. I cannot reproduce this here. Can you send me a sample file for which it always fails please? > Potentially the content in the file itself has become corrupted > somewhere along the line leading to the bad saves, but at the very > least, I would expect the Query Tool to flag in some manner that the > file could not be saved, not silently create a 0 kb file and indicate a > successful save. Agreed. > Issue 2: > > This is trivial, but when a syntax error is detected in a large file > (20,000+ lines), the parser gets the line number correct of the syntax > error, but the further down the file the syntax error is, the greater > the distance the error marker is off the target, with the error marker > pulling up short of the faulty line. This sounds like a known bug in wxWidgets. The Styled Text Control doesn't play well with multi-byte characters and can return position values in bytes when they should be in characters. Regards, Dave.
Hi Dave, Ignore issue 2 then if it is a known issue. With issue 1, I have isolated two offending characters that cause the problem: "ī" (c4ab) and "ḥ" (e1b8a5). The inclusion of either one of them will lead to an empty file being stored. I'm using the characters for phonetic Arabic transliterations, but the characters are probably used in other real world contexts as well. I have included a file as an attachment with an example that causes the file to be saved as a 0 byte file from the Query tool. I have also included the actual Arabic characters as well, which also if present will cause the same behaviour. Essentially, it does not appear that the Query tool supports true UTF-8 or unicode characters. I doubt that the inclusion of these characters with version 1.8.0 would have ever worked either, but I am now up to 1.8.1 and know that with this version the inclusion of either of these characters will always cause the problem. So ignore my probably erroneous observation that the previous version only started being problematic after being open for a period of time. Regards, Andy Dave Page wrote: > On 07/01/2008, Andrew <archa@pacific.net.au> wrote: > >> OS: WinXP SP2 >> PC: Pentium D 3 GHz, 2 GB RAM >> pgAdmin ver: 1.8.0, 1.8.1 >> postgresql ver: 8.2.5-1 >> >> I haven't been able to find these issues on the Internet or on the >> pgAdmin web site, so hopefully they are not duplicates of existing. >> >> Issue 1: >> >> Working on a ~ 1 MB *.sql file from the Query Tool - a schema file, >> inclusive of reference data of a database that I'm attempting to migrate >> from MySQL. When I save from the pgAdmin Query Tool, it saves a 0 byte >> UTF-8 encoded file (presumably there is still some data saved for >> Windows to realise that it is not an ANSI file). Yet it opens the 1 MB >> file fine when restored from svn, or when the data is copied from the >> Query Tool and saved through notepad as a UTF-8 file and other than >> missing datatypes such as uuid's that I still need to address, the >> parser is happy with the syntax. The save silently changes the dirty >> status of the file to clean - as indicated by removing the asterix from >> the file name in the application title bar, without providing an >> indication of a failed save (which is the worst part). >> >> Initially, it only seemed to occur after the application had been >> running for a few hours, with saves in the initial editing time saving >> fine, then after being running for awhile (several hours), subsequent >> saves began to save as 0 kb files. At least this seemed to be the >> behaviour with pgAdmin 1.8.0. Since upgrading to pgAdmin 1.8.1, every >> save of the file results in a 0 kb output, regardless of how long the >> application has been running. >> > > I cannot reproduce this here. Can you send me a sample file for which > it always fails please? > > >> Potentially the content in the file itself has become corrupted >> somewhere along the line leading to the bad saves, but at the very >> least, I would expect the Query Tool to flag in some manner that the >> file could not be saved, not silently create a 0 kb file and indicate a >> successful save. >> > > Agreed. > > >> Issue 2: >> >> This is trivial, but when a syntax error is detected in a large file >> (20,000+ lines), the parser gets the line number correct of the syntax >> error, but the further down the file the syntax error is, the greater >> the distance the error marker is off the target, with the error marker >> pulling up short of the faulty line. >> > > This sounds like a known bug in wxWidgets. The Styled Text Control > doesn't play well with multi-byte characters and can return position > values in bytes when they should be in characters. > > Regards, Dave. > > ---------------------------(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 > > >
Attachment
As the attachment did not appear to make it through, the actual SQL that causes the 0 byte save is as follows: DROP TABLE IF EXISTS bad_data; CREATE TABLE bad_data ( id int NOT NULL, bad_data varchar(50) NOT NULL, PRIMARY KEY (id) ); INSERT INTO bad_data (id, bad_data) VALUES(1, 'Dhabīḥah'),(2, 'ذَبِيْحَة'); Cheers, Andy
Hi. Please see, http://winpg.jp/~saito/pgAdmin/QueryTool_check/ It is necessary to set reading and writing of an option to UTF-8. Although ANSI and UTF-8 are possible as for reading, the option is required for writing. Regards, Hiroshi Saito ----- Original Message ----- From: "Andrew" <archa@pacific.net.au> As the attachment did not appear to make it through, the actual SQL that causes the 0 byte save is as follows: DROP TABLE IF EXISTS bad_data; CREATE TABLE bad_data ( id int NOT NULL, bad_data varchar(50) NOT NULL, PRIMARY KEY (id) ); INSERT INTO bad_data (id, bad_data) VALUES(1, 'Dhabīḥah'),(2, 'ذَبِيْحَة'); Cheers, Andy ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Excellent, Thanks Hiroshi Saito, that worked a treat, I really appreciate you posting those images. Apologies for taking up your time for something I probably could have figured out by RTFM. However, IMHO I still think it is a defect, in that it shouldn't really be saving a 0 byte file silently, when you think it has successfully saved your changes, and then when you go back to the file you find the data you have worked on is lost. If it is in a mode not to support the characters, then it should alert you that it cannot save those characters. However, I will leave that for the development team to decide if it is defective behaviour or behaviour by design. Cheers, Andy Hiroshi Saito wrote: > Hi. > > Please see, > http://winpg.jp/~saito/pgAdmin/QueryTool_check/ > It is necessary to set reading and writing of an option to UTF-8. > Although ANSI and UTF-8 are possible as for reading, the option > is required for writing. > > Regards, > Hiroshi Saito > > ----- Original Message ----- From: "Andrew" <archa@pacific.net.au> > > > As the attachment did not appear to make it through, the actual SQL that > causes the 0 byte save is as follows: > > DROP TABLE IF EXISTS bad_data; > CREATE TABLE bad_data ( > id int NOT NULL, > bad_data varchar(50) NOT NULL, > PRIMARY KEY (id) > ); > > > > INSERT INTO bad_data (id, bad_data) VALUES > (1, 'Dhabīḥah'), > (2, 'ذَبِيْحَة'); > > Cheers, > > Andy > > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > >
On 08/01/2008, Andrew <archa@pacific.net.au> wrote:
> Excellent,
>
> Thanks Hiroshi Saito, that worked a treat, I really appreciate you
> posting those images. Apologies for taking up your time for something I
> probably could have figured out by RTFM.
Yes, thanks Hiroshi (our resident encoding guru :-) ).
> However, IMHO I still think it is a defect, in that it shouldn't really
> be saving a 0 byte file silently, when you think it has successfully
> saved your changes, and then when you go back to the file you find the
> data you have worked on is lost. If it is in a mode not to support the
> characters, then it should alert you that it cannot save those
> characters. However, I will leave that for the development team to
> decide if it is defective behaviour or behaviour by design.
Absolutely agree. Hiroshi; we do something similar in frmExport:
if (rbUnicode->GetValue()) file.Write(line, wxConvUTF8); else { buf =
line.mb_str(wxConvLibc); if (!buf) skipped++; else file.Write(line,
wxConvLibc); }
(we then warn the user if skipped > 0). Would something similar
suffice do you think?
/D
Hi Dave.
Yeah, we know the user who had the same problem in the past. However, In Japan,
it needed to be written by encoding of a client, and We did not want to set an initial
value to UTF-8. Therefore, It is need recognized by the warning message.
How is an attached patch proposal?
Regards,
Hiroshi Saito
----- Original Message -----
From: "Dave Page" <dpage@postgresql.org>
> On 08/01/2008, Andrew <archa@pacific.net.au> wrote:
>> Excellent,
>>
>> Thanks Hiroshi Saito, that worked a treat, I really appreciate you
>> posting those images. Apologies for taking up your time for something I
>> probably could have figured out by RTFM.
>
> Yes, thanks Hiroshi (our resident encoding guru :-) ).
>
>> However, IMHO I still think it is a defect, in that it shouldn't really
>> be saving a 0 byte file silently, when you think it has successfully
>> saved your changes, and then when you go back to the file you find the
>> data you have worked on is lost. If it is in a mode not to support the
>> characters, then it should alert you that it cannot save those
>> characters. However, I will leave that for the development team to
>> decide if it is defective behaviour or behaviour by design.
>
> Absolutely agree. Hiroshi; we do something similar in frmExport:
>
> if (rbUnicode->GetValue())
> file.Write(line, wxConvUTF8);
> else
> {
> buf = line.mb_str(wxConvLibc);
> if (!buf)
> skipped++;
> else
> file.Write(line, wxConvLibc);
> }
>
> (we then warn the user if skipped > 0). Would something similar
> suffice do you think?
>
> /D
On 09/01/2008, Hiroshi Saito <z-saito@guitar.ocn.ne.jp> wrote: > Hi Dave. > > Yeah, we know the user who had the same problem in the past. However, In Japan, > it needed to be written by encoding of a client, and We did not want to set an initial > value to UTF-8. Therefore, It is need recognized by the warning message. > How is an attached patch proposal? That looks like it should work to me. Please apply to both trunk and the 1.8 branch :-) /D