Обсуждение: [GENERAL] psql error (encoding related?)

I see this with PostgreSQL 9.4.7 and some 8.x versions running on Linux Red Hat.
Older versions "supposedly" do not exhibit this behavior, but I didn't check.

$ psql
Password:
psql: FATAL:  could not access file "\x246c69626469722f757466385f616e645f69736f383835395f31": No such file or directory

The same error is written to the database log:
FATAL:  could not access file "\x246c69626469722f757466385f616e645f69736f383835395f31": No such file or directory

This is the environment where this problem was reported
$ locale
LANG=en_US
LC_CTYPE="en_US"
LC_NUMERIC="en_US"
LC_COLLATE="en_US"
. . .   . . .    . . .

This is the "normal" environment where I don't see any problems launching psql
$  locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
. . .   . . .    . . .

The database was created using these parameters "initdb  -E UTF8   --locale=C   -D <data-dir> ... "

When I display the list of databases with \l I see that all of them have  these parameters:
Encoding    UTF8
Collate        C
Ctype           C

I can work around this error by doing this "setenv PGCLIENTENCODING UTF-8"

I would like to understand what happens... is this some kind of a bug or just an inherent incompatibility between the
client(psql) and the database settings? 
Is there a different and maybe a better remedy than using PGCLIENTENCODING?

And if anyone from the Postgres team listening... in the old tradition of whining I would add that the error message
referringto a long hex string is not helpful! 
Thanks in advance.
Michael.

This email and any attachments are intended solely for the use of the individual or entity to whom it is addressed and
maybe confidential and/or privileged. 

If you are not one of the named recipients or have received this email in error,

(i) you should not read, disclose, or copy it,

(ii) please notify sender of your receipt by reply email and delete this email and all attachments,

(iii) Dassault Systemes does not accept or assume any liability or responsibility for any use of or reliance on this
email.

For other languages, go to http://www.3ds.com/terms/email-disclaimer

=?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com> writes:
> This hex string decodes to something sensible:
> $ perl -le 'print pack "H*", shift'
> 246c69626469722f757466385f616e645f69736f383835395f31
> $libdir/utf8_and_iso8859_1

> Maybe it rings a bell.

Hah, that's pretty suggestive.  So probably, there's an encoding
conversion function that's been registered with a messed-up library name,
and the failure occurs when trying to connect with a client-side locale
that needs to use that encoding conversion.  We know the database uses
UTF8, so the failure must occur with ISO-8859-1 as the client encoding.

It's hard to guess how the function's probin value got messed up though.
All those functions should have been created during initdb, and there's
no good reason why they should get changed later.  I see this in a 9.4
database:

regression=#  select oid, xmin, proname, probin from pg_proc where probin ~ 'utf8_and_iso8859_1';
  oid  | xmin |      proname      |           probin
-------+------+-------------------+----------------------------
 12392 | 1635 | iso8859_1_to_utf8 | $libdir/utf8_and_iso8859_1
 12394 | 1639 | utf8_to_iso8859_1 | $libdir/utf8_and_iso8859_1
(2 rows)

although the OIDs and xmin value might vary in other installations.

>> And if anyone from the Postgres team listening... in the old tradition of
>> whining I would add that the error message referring to a long hex string
>> is not helpful!

This should be a can't-happen failure ... it's not very clear to me how we
could produce a better message for it.

            regards, tom lane

Thank you Torsten and Tom, this rang the bell and put me on the right track.
>> " This should be a can't-happen failure ..." - indeed, the wound is self-inflicted.
Michael.


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, January 05, 2017 5:04 PM
To: Torsten Förtsch
Cc: BRUSSER Michael; pgsql-general@postgresql.org
Subject: Re: [GENERAL] psql error (encoding related?)

Torsten Fortsch <tfoertsch123@gmail.com> writes:
> This hex string decodes to something sensible:
> $ perl -le 'print pack "H*", shift'
> 246c69626469722f757466385f616e645f69736f383835395f31
> $libdir/utf8_and_iso8859_1

> Maybe it rings a bell.

Hah, that's pretty suggestive.  So probably, there's an encoding
conversion function that's been registered with a messed-up library name,
and the failure occurs when trying to connect with a client-side locale
that needs to use that encoding conversion.  We know the database uses
UTF8, so the failure must occur with ISO-8859-1 as the client encoding.

It's hard to guess how the function's probin value got messed up though.
All those functions should have been created during initdb, and there's
no good reason why they should get changed later.  I see this in a 9.4
database:

regression=#  select oid, xmin, proname, probin from pg_proc where probin ~ 'utf8_and_iso8859_1';
  oid  | xmin |      proname      |           probin
-------+------+-------------------+----------------------------
 12392 | 1635 | iso8859_1_to_utf8 | $libdir/utf8_and_iso8859_1
 12394 | 1639 | utf8_to_iso8859_1 | $libdir/utf8_and_iso8859_1
(2 rows)

although the OIDs and xmin value might vary in other installations.

>> And if anyone from the Postgres team listening... in the old tradition of
>> whining I would add that the error message referring to a long hex string
>> is not helpful!

This should be a can't-happen failure ... it's not very clear to me how we
could produce a better message for it.

                        regards, tom lane
This email and any attachments are intended solely for the use of the individual or entity to whom it is addressed and
maybe confidential and/or privileged. 

If you are not one of the named recipients or have received this email in error,

(i) you should not read, disclose, or copy it,

(ii) please notify sender of your receipt by reply email and delete this email and all attachments,

(iii) Dassault Systemes does not accept or assume any liability or responsibility for any use of or reliance on this
email.

For other languages, go to http://www.3ds.com/terms/email-disclaimer

On 01/06/2017 05:32 AM, BRUSSER Michael wrote:
> Thank you Torsten and Tom, this rang the bell and put me on the right track.
>>> " This should be a can't-happen failure ..." - indeed, the wound is self-inflicted.

Can you share what the cause was as history has shown that if one person
makes a mistake someone else will eventually make the same mistake.

> Michael.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com