BUG #6246: character encoding for 64 bit

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Bo
Reply | Threaded
Open this post in threaded view
|

BUG #6246: character encoding for 64 bit

Bo

The following bug has been logged online:

Bug reference:      6246
Logged by:          Bo
Email address:      [hidden email]
PostgreSQL version: 9.1
Operating system:   Windows server 2008
Description:        character encoding for 64 bit
Details:

I have just installed postgresql-9.1.1 on a windows server 2008 64 (from
binary postgresql-9.1.1-1-windows-x64)
One database is in latin1 encoding and when connecting to it with psql i get
an error:
(two examples, one working and one not. postgres db is in utf8)
**********************************
D:\>d:\PostgreSQL\9.1\bin\psql.exe -d postgres -U postgres -c "comment on
database demo_ is 'test'"
Password for user postgres:
COMMENT

D:\>d:\PostgreSQL\9.1\bin\psql.exe -d demo_ -U postgres -c "comment on
database demo_ is 'test'"
Password for user postgres:
psql: FATAL:  conversion between WIN1252 and LATIN1 is not supported
*****************************
No matter what codepage the console is set to (with chcp). Default was 850,
i have tried 1252, 28591 and 65001.

I have the exact same setup on a windows xp 32 bit without this issue. So im
guessing it has to do with the 64 bit version of psql..

Thanks for any light you can shed on this.
In any case i hope you can use this report.

Regards Bo TJ

--
Sent via pgsql-bugs mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply | Threaded
Open this post in threaded view
|

Re: BUG #6246: character encoding for 64 bit

Craig Ringer-2
On 10/10/2011 04:20 PM, Bo wrote:

> The following bug has been logged online:
>
> Bug reference:      6246
> Logged by:          Bo
> Email address:      [hidden email]
> PostgreSQL version: 9.1
> Operating system:   Windows server 2008
> Description:        character encoding for 64 bit
> Details:
>
> I have just installed postgresql-9.1.1 on a windows server 2008 64 (from
> binary postgresql-9.1.1-1-windows-x64)
> One database is in latin1 encoding and when connecting to it with psql i get
> an error:

What's the output of the psql command:

    \l+

?

What does "SHOW client_encoding;" report for the working and non-working
tests?

Can you connect to the problem database "demo_" from PgAdmin III? Is it
only psql on the Windows command line that's a problem?

What's your default codepage for your windows commandline, when you
change nothing?

--
Craig Ringer

--
Sent via pgsql-bugs mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Bo
Reply | Threaded
Open this post in threaded view
|

Re: BUG #6246: character encoding for 64 bit

Bo
> 12. oktober 2011 04:17


> Emne: Re: [BUGS] BUG #6246: character encoding for 64 bit
>
> On 10/10/2011 04:20 PM, Bo wrote:
> > The following bug has been logged online:
> >
> > Bug reference:      6246
> > Logged by:          Bo
> > Email address:      [hidden email]
> > PostgreSQL version: 9.1
> > Operating system:   Windows server 2008
> > Description:        character encoding for 64 bit
> > Details:
> >
> > I have just installed postgresql-9.1.1 on a windows server 2008 64
> > (from binary postgresql-9.1.1-1-windows-x64) One database is in latin1
> > encoding and when connecting to it with psql i get an error:
>
> What's the output of the psql command:
>
>     \l+
>
> ?
>
> What does "SHOW client_encoding;" report for the working and non-working
> tests?
>
> Can you connect to the problem database "demo_" from PgAdmin III? Is it
> only psql on the Windows command line that's a problem?
>
> What's your default codepage for your windows commandline, when you
> change nothing?
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-bugs mailing list ([hidden email]) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

Hi
Heres some info (and hopefully some answers)

Firstly i removed 9.1.1 and installed 9.0.5. Now it seems to work.. mostly (lugging latin1 dbs around is still no picknick)..
But just logging into the postgres db now gives me this:

**
D:\PostgreSQL\9.0\bin>psql.exe -U postgres -d postgres
Password for user postgres:
psql (9.0.5)
WARNING: Console code page (850) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=#
**

Standard commanline codepage is 850, previous bit is with 850 as well.
"psql -l" returns a rather long list, heres part of it:

**
demo_ | demos        | LATIN1   | C         |C     |
.....
postgres             | postgres      | UTF8     | C         |C     |
**

Client encodings:

**
D:\PostgreSQL\9.0\bin>psql.exe -U postgres -d postgres -c "show client_encoding"

Password for user postgres:
 client_encoding
-----------------
 UTF8
(1 row)

D:\PostgreSQL\9.0\bin>psql.exe -U postgres -d demo_ -c "show client_encoding"
Password for user postgres:
 client_encoding
-----------------
 LATIN1
(1 row)
**

And lastly, yes i could connect with pgadmin without problems.
Remember these examples comes from another version, but i hope its not entirely useless.

I would like to add that although solved, the command line psql is showing some quirks in the same enviroment (windows server 2008 R2 Standard). If i run scripts with psql -f it chokes on BOM discriptors in utf8 files. If i convert the files to windows 1252 it works fine..
like this (update is first word in first line, file contains no non-acsii characters):
**
psql:page_spelling.sql:1: ERROR:  syntax error at or near "update"
**

Again, i hope its usefull.
Regards
Bo Thorbjørn Jensen


--
Sent via pgsql-bugs mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply | Threaded
Open this post in threaded view
|

Re: BUG #6246: character encoding for 64 bit

bushcraftrelf
In reply to this post by Bo
Bo <bo <at> budget123.dk> writes:

>
>
> The following bug has been logged online:
>
> Bug reference:      6246
> Logged by:          Bo
> Email address:      bo <at> budget123.dk
> PostgreSQL version: 9.1
> Operating system:   Windows server 2008
> Description:        character encoding for 64 bit
> Details:

>
> D:\>d:\PostgreSQL\9.1\bin\psql.exe -d demo_ -U postgres -c "comment on
> database demo_ is 'test'"
> Password for user postgres:
> psql: FATAL:  conversion between WIN1252 and LATIN1 is not supported
> *****************************
>
> Regards Bo TJ
>


I get a very similar error with both 64 bit and 32 bit postgres installations. I
have a table in LATIN1, but whenever I try to connect using psql, I get error
message:
postgres=# \c mydb
FATAL:  conversion between WIN1252 and LATIN1 is not supported
Previous connection kept

I can connect through pgAdmin and create tables etc, just not with psql.


--
Sent via pgsql-bugs mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply | Threaded
Open this post in threaded view
|

Re: BUG #6246: character encoding for 64 bit

Kevin Grittner
Susannah <[hidden email]> wrote:
 
> FATAL:  conversion between WIN1252 and LATIN1 is not supported
 
WIN1512 supports characters which are not supported by LATIN1.  If
you sent such characters to the database, what would you expect to
happen?
 
> I can connect through pgAdmin and create tables etc, just not with
> psql.
 
Perhaps pgAdmin detects the mismatch and sets the client-side
character set to the more limited LATIN1?  What does pgAdmin do if
you make such a connection and then try to store a character which
is not in the LATIN1 character set, like a euro character?
 
-Kevin

--
Sent via pgsql-bugs mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply | Threaded
Open this post in threaded view
|

Re: BUG #6246: character encoding for 64 bit

bushcraftrelf
Kevin Grittner wrote
 
WIN1512 supports characters which are not supported by LATIN1.  If
you sent such characters to the database, what would you expect to
happen?
 
-Kevin
Hi Kevin,

I forgot to mention a few things. One is that the problem only arises in postgres 9.1 - I can create exactly the same databases and tables in 9.0 and have no problems accessing them using psql 9.0.
Also, I set the client_encoding to "LATIN1" and so there should have been no problem with a mismatch in encoding.

I have subsequently found that if I use psql 9.0 on the postgresql 9.1 server, then I can access the table as normal.

The main difference I have found (if it's any use) is that when I first open psql 9.0, the \encoding is always set at UTF8 until I change it (which postgres can convert to LATIN1). When I first open psql 9.1, the \encoding is set at WIN1252 before I change it. WIN1252 cannot be converted to LATIN1.

For now, I have reverted to using Postgres 9.0, as I have no problems with that version.
Reply | Threaded
Open this post in threaded view
|

Re: BUG #6246: character encoding for 64 bit

Craig Ringer-2
On 10/13/2011 03:09 AM, bushcraftrelf wrote:

>
> Kevin Grittner wrote:
>>
>>
>> WIN1512 supports characters which are not supported by LATIN1.  If
>> you sent such characters to the database, what would you expect to
>> happen?
>>
>> -Kevin
>>
>
> Hi Kevin,
>
> I forgot to mention a few things. One is that the problem only arises in
> postgres 9.1 - I can create exactly the same databases and tables in 9.0 and
> have no problems accessing them using psql 9.0.
> Also, I set the client_encoding to "LATIN1" and so there should have been no
> problem with a mismatch in encoding.

Actually, there still is, because your terminal isn't speaking latin1 so
either it'll have errors when converting from the WINxxxx terminal
encoding to latin1 or characters will be corrupted where they're not the
same in both encodings. Even if it seemed to work, unless you set your
*terminal* to latin1 as well it'd produce wrong results.

psql may want to `chcp 65001' to put the shell in unicode mode. You may
also need to select a Unicode font for the shell. I seem to recall that
"cmd /u" is also useful in some way, but I'm not in front of Windows at
the moment so I can't check.

> The main difference I have found (if it's any use) is that when I first open
> psql 9.0, the \encoding is always set at UTF8 until I change it (which
> postgres can convert to LATIN1). When I first open psql 9.1, the \encoding
> is set at WIN1252 before I change it. WIN1252 cannot be converted to LATIN1.

It sounds like psql on 9.1 is correctly detecting the shell's encoding,
wheras 9.0 is assuming utf-8. Whoopsie!

If that's the case, try running "chcp 65001" before "psql". See if that
helps.

--
Craig Ringer

--
Sent via pgsql-bugs mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply | Threaded
Open this post in threaded view
|

Re: BUG #6246: character encoding for 64 bit

bushcraftrelf
Surely the windows installation of psql ought to be set up to work with the terminal already?
Reply | Threaded
Open this post in threaded view
|

Re: BUG #6246: character encoding for 64 bit

bushcraftrelf
I think I've figured it out - thanks all for your help. Craig Ringer appears to be right.

From documentation for psql 9.1 under Usage>Connecting to a database

"If at least one of standard input or standard output are a terminal, then psql sets the client encoding to "auto", which will detect the appropriate client encoding from the locale settings (LC_CTYPE environment variable on Unix systems). If this doesn't work out as expected, the client encoding can be overridden using the environment variable PGCLIENTENCODING."

This is not in 9.0 documentation, so I think is new? Anyway, that explains the difference in behaviour between 9.0 and 9.1

So...I've altered runpsql.bat to include these lines at the beginning:

REM change terminal to UTF8
chcp 65001

and added an environment variable PGCLIENTENCODING=UTF8

Now I can connect to the LATIN1 database without any problems (postgres automatically converts between UTF8 and LATIN1 - "Character set conversion between server and client" http://www.postgresql.org/docs/9.1/static/multibyte.html)

Please, does anyone know if I am likely to cause myself problems running the terminal with the UTF8 codepage?
Previous Thread Next Thread