[BUGS] sort order (ORDER BY) hu_HU.UTF-8 locale with UTF-8 encoding is not working correctly on FreeBSD
Dear List, I'm trying to solve this problem for more than 2 days, but I have no other idea. My system is: FreeBSD titanium 8.1-RELEASE FreeBSD 8.1-RELEASE #0: Mon Jul 19 02:36:49 UTC 2010 r...@mason.cse.buffalo.edu:/usr/obj/usr/src/sys/GENERIC amd64 Tested PgSQL versions are: 8.4 and 9.0.3 (fresh install using ports) initdb flags are: --encoding=UTF-8 --locale=hu_HU.UTF-8 Initdb output: The files belonging to this database system will be owned by user pgsql. This user must also own the server process. The database cluster will be initialized with locale hu_HU.UTF-8. The default text search configuration will be set to hungarian. creating directory /usr/local/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... 40 selecting default shared_buffers ... 28MB creating configuration files ... ok creating template1 database in /usr/local/pgsql/data/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... ok creating dictionaries ... ok setting privileges on built-in objects ... ok creating information schema ... ok loading PL/pgSQL server-side language ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok step 1: createdb -U wooh test --encoding=UTF-8 --locale=hu_HU.UTF-8 step 2: psql -U wooh -l List of databases Name| Owner | Encoding | Collation |Ctype| Access privileges ---+---+--+-+-+--- postgres | pgsql | UTF8 | hu_HU.UTF-8 | hu_HU.UTF-8 | template0 | pgsql | UTF8 | hu_HU.UTF-8 | hu_HU.UTF-8 | =c/pgsql + | | | | | pgsql=CTc/pgsql template1 | pgsql | UTF8 | hu_HU.UTF-8 | hu_HU.UTF-8 | =c/pgsql + | | | | | pgsql=CTc/pgsql test | wooh | UTF8 | hu_HU.UTF-8 | hu_HU.UTF-8 | Everything seems to be OK. let's create a table with idn, and name. step 3: test=# create table test (idn serial primary key not null, name varchar(255)); NOTICE: CREATE TABLE will create implicit sequence test_idn_seq for serial column test.idn NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index test_pkey for table test CREATE TABLE step 4: [root@titanium /home/wooh]# file insert.sql insert.sql: UTF-8 Unicode text step 5: [root@titanium /home/wooh]# cat insert.sql INSERT INTO test (name) values ( 'a' ); INSERT INTO test (name) values ( 'á' ); INSERT INTO test (name) values ( 'b' ); INSERT INTO test (name) values ( 'c' ); INSERT INTO test (name) values ( 'd' ); INSERT INTO test (name) values ( 'e' ); INSERT INTO test (name) values ( 'é' ); INSERT INTO test (name) values ( 'f' ); INSERT INTO test (name) values ( 'g' ); INSERT INTO test (name) values ( 'h' ); step 6: [root@titanium /home/wooh]# psql -U wooh test insert.sql INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 step 7: [root@titanium /home/wooh]# psql -U wooh test psql (9.0.3) Type help for help. test=# select * from test order by name asc; idn | name -+-- 1 | a 3 | b 4 | c 5 | d 6 | e 8 | f 9 | g 10 | h 2 | á 7 | é (10 rows) test=# show lc_collate; lc_collate - hu_HU.UTF-8 (1 row) test=# show lc_ctype; lc_ctype - hu_HU.UTF-8 (1 row) The sort order is incorrect, and I cannot find out why. Everything seems to be Ok. (locale, collate, encoding) But this whole thing works if I chose LATIN2 encoding with hu_HU.ISO8859-2 locale. Unfortunately we use UTF-8 databases, so I need to use the UTF8 Encoding with hu_HU.UTF-8 Any ideas? Additional information: [root@titanium /home/wooh]# pg_controldata /usr/local/pgsql/data/ pg_control version number:903 Catalog version number: 201008051 Database system identifier: 5570517093231621070 Database cluster state: in production pg_control last modified: Sun Feb 6 11:19:27 2011 Latest checkpoint location: 0/1610CA0 Prior checkpoint location:0/15F8028 Latest checkpoint's REDO location:0/1610CA0 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 0/675 Latest checkpoint's NextOID: 24576 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Latest checkpoint's oldestXID:654 Latest checkpoint's oldestXID's DB: 1 Latest checkpoint's oldestActiveXID: 0 Time of latest checkpoint:Sun Feb 6 11:19:19 2011 Minimum recovery ending location: 0/0 Backup start location:0/0 Current wal_level setting:minimal Current max_connections setting: 40 Current max_prepared_xacts setting: 0 Current max_locks_per_xact setting: 64 Maximum
[BUGS] Re: sort order (ORDER BY) hu_HU.UTF-8 locale with UTF-8 encoding is not working correctly on FreeBSD
Adam PAPAI wrote: The correct encoding of the values are: INSERT INTO test (name) values ( 'a' ); INSERT INTO test (name) values ( 'á' ); INSERT INTO test (name) values ( 'b' ); INSERT INTO test (name) values ( 'c' ); INSERT INTO test (name) values ( 'd' ); INSERT INTO test (name) values ( 'e' ); INSERT INTO test (name) values ( 'é' ); INSERT INTO test (name) values ( 'f' ); INSERT INTO test (name) values ( 'g' ); INSERT INTO test (name) values ( 'h' ); I forgot to change the encoding of my mail client. test=# select * from test order by name asc; idn | name -+-- 1 | a 3 | b 4 | c 5 | d 6 | e 8 | f 9 | g 10 | h 2 | á 7 | é (10 rows) -- Adam PAPAI BSD Support Service http://www.bsdsupportservice.hu E-mail: adam.pa...@bsdsupportservice.hu Phone: +36 30 33-55-735 (Hungary) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] sort order (ORDER BY) hu_HU.UTF-8 locale with UTF-8 encoding is not working correctly on FreeBSD
Adam PAPAI adam.pa...@bsdsupportservice.hu writes: [ hu_HU.UTF-8 locale doesn't sort as expected on ] FreeBSD titanium 8.1-RELEASE FreeBSD 8.1-RELEASE #0: Mon Jul 19 02:36:49 UTC 2010 r...@mason.cse.buffalo.edu:/usr/obj/usr/src/sys/GENERIC amd64 Do you get the expected sort ordering from the sort command either? I'm betting not. This is not something Postgres controls -- we just rely on the results of strcoll(). I don't know the state of affairs on FreeBSD for sure, but I do know that the UTF-8 locales are fairly broken on Mac OS X, which is a BSD derived platform too. It looks like nobody in BSD-land has bothered to put in the work to make multibyte characters sort properly. You might have better luck with hu_HU using a suitable LATINn character set instead of UTF8. If that still doesn't do what you want, you might have no choice but to move off BSD :-( regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] sort order (ORDER BY) hu_HU.UTF-8 locale with UTF-8 encoding is not working correctly on FreeBSD
Tom Lane wrote: Adam PAPAI adam.pa...@bsdsupportservice.hu writes: [ hu_HU.UTF-8 locale doesn't sort as expected on ] FreeBSD titanium 8.1-RELEASE FreeBSD 8.1-RELEASE #0: Mon Jul 19 02:36:49 UTC 2010 r...@mason.cse.buffalo.edu:/usr/obj/usr/src/sys/GENERIC amd64 Do you get the expected sort ordering from the sort command either? I'm betting not. This is not something Postgres controls -- we just rely on the results of strcoll(). I don't know the state of affairs on FreeBSD for sure, but I do know that the UTF-8 locales are fairly broken on Mac OS X, which is a BSD derived platform too. It looks like nobody in BSD-land has bothered to put in the work to make multibyte characters sort properly. You might have better luck with hu_HU using a suitable LATINn character set instead of UTF8. If that still doesn't do what you want, you might have no choice but to move off BSD :-( regards, tom lane Hi Tom, I've tested the sort command. It fails too. Perhaps it's really with the *BSD and not with PostgreSQL. Thanks, -- Adam PAPAI BSD Support Service http://www.bsdsupportservice.hu E-mail: adam.pa...@bsdsupportservice.hu Phone: +36 30 33-55-735 (Hungary) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5559: Full SSL verification fails when hostaddr provided
Christopher Head wrote: On Wed, 14 Jul 2010 18:35:55 -0400 Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: Do the docs need any more updating? No doubt, but it's a bit premature to consider that while we're still arguing whether the code needs to change more. regards, tom lane Sorry to bother everyone, but AFAICT this discussion kind of disappeared. Did I perhaps get dropped from CC? I'm interested to know what the final resolution of this is. My own thought would be: host means the thing you intended to connect to: a unique identifier for the server, probably (usually) the hostname, and also the thing that goes in a certificate. Should (probably) never be omitted. hostaddr means the thing you actually send your TCP SYN packet to: maybe an IP address if you want to save a DNS lookup, maybe even localhost if you want to use an SSH tunnel (or even some other hostname if you have an even stranger tunnel set up), but purely a network-layer thing about *how to get to* the server, and not a user-trust-layer thing about *who the server is*. If omitted, defaults to being equal to host. I don't know if that's what was intended, but that's what I thought they would mean. I have adjusted the libpq docs to be clearer about 'hostaddr' by using an itemized list and rewording; attached and applied. I am not sure what else needs to be done, and I don't think anyone else knows either, so unless I hear otherwise, I will consider this item closed. Perhaps the clearer docs will highlight a new open item. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml index e78d708..3824588 100644 *** a/doc/src/sgml/libpq.sgml --- b/doc/src/sgml/libpq.sgml *** PGconn *PQconnectdbParams(const char **k *** 164,185 Using literalhostaddr/ instead of literalhost/ allows the application to avoid a host name look-up, which might be important in applications with time constraints. However, a host name is !required for Kerberos, GSSAPI, or SSPI authentication, as well as !for full SSL certificate verification. The following rules are !used: !If literalhost/ is specified without literalhostaddr/, !a host name lookup occurs. !If literalhostaddr/ is specified without literalhost/, !the value for literalhostaddr/ gives the server network address. !The connection attempt will fail in any of the cases where a !host name is required. !If both literalhost/ and literalhostaddr/ are specified, !the value for literalhostaddr/ gives the server network address. !The value for literalhost/ is ignored unless needed for !authentication or verification purposes, in which case it will be !used as the host name. Note that authentication is likely to fail !if literalhost/ is not the name of the machine at !literalhostaddr/. Also, note that literalhost/ rather than literalhostaddr/ is used to identify the connection in filename~/.pgpass/ (see xref linkend=libpq-pgpass). --- 164,199 Using literalhostaddr/ instead of literalhost/ allows the application to avoid a host name look-up, which might be important in applications with time constraints. However, a host name is !required for Kerberos, GSSAPI, or SSPI authentication !methods, as well as for literalverify-full/ SSL !certificate verification. The following rules are used: !itemizedlist ! listitem ! para ! If literalhost/ is specified without literalhostaddr/, ! a host name lookup occurs. ! /para ! /listitem ! listitem ! para ! If literalhostaddr/ is specified without literalhost/, ! the value for literalhostaddr/ gives the server network address. ! The connection attempt will fail if the authentication ! method requires a host name. ! /para ! /listitem ! listitem ! para ! If both literalhost/ and literalhostaddr/ are specified, ! the value for literalhostaddr/ gives the server network address. ! The value for literalhost/ is ignored unless the ! authentication method requires it, in which case it will be ! used as the host name. ! /para ! /listitem !/itemizedlist !Note that authentication is likely to fail if literalhost/ !
[BUGS] BUG #5867: wish: plpgsql print table for debug
The following bug has been logged online: Bug reference: 5867 Logged by: Richard Neill Email address: postgre...@richardneill.org PostgreSQL version: 9.03 Operating system: Linux Description:wish: plpgsql print table for debug Details: When debugging a plpgsql function, it would be really amazingly useful to be able to do a regular psql-style SELECT, and have the result printed to screen. Something like: Raise Notice table 'SELECT ' and then plpgsql would run the query and dump the result to screen, using its helpful formatting. As far as I can see, this isn't possible (though there are a lot of people searching for how to do it), and the only workaround is to manually handle the looping and formatting, raising lots of individual notices. This makes debugging much harder than it should be. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
