X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B7A72528BE for ; Tue, 9 Aug 2005 13:49:37 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 99543-07 for ; Tue, 9 Aug 2005 16:49:27 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id C4A7A529F3 for ; Tue, 9 Aug 2005 13:49:26 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j79Gm9lC017068; Tue, 9 Aug 2005 12:48:09 -0400 (EDT) To: Martijn van Oosterhout Cc: Salem Berhanu , pgsql-general@postgresql.org Subject: Re: postgres & server encodings In-reply-to: <20050809155946.GF8244@svana.org> References: <20050809155946.GF8244@svana.org> Comments: In-reply-to Martijn van Oosterhout message dated "Tue, 09 Aug 2005 17:59:46 +0200" Date: Tue, 09 Aug 2005 12:48:09 -0400 Message-ID: <17067.1123606089@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200508/458 X-Sequence-Number: 81866 Martijn van Oosterhout writes: > SQL_ASCII means that the database does no locale specific or language > specific encoding ever. It won't check what you send it either. If > you're content to let clients deal with any encoding issues, this may > be what you want. > But anything to do with lower(), upper(), case-insenstive in the > database itself will be totally stupid since it's assuming ASCII. Actually it's worse than that: case conversion and comparisons inside the database will still be done using whatever locale setting the database was initdb'd with. If that locale is C, then you have just ASCII-aware case conversions and memcmp-like sorting, but everything will behave reasonably sanely within those limitations. If the locale is not C then it is assuming some specific encoding that *the database is not enforcing*. This is bad news and can result in all sorts of inconsistent behavior. We really need to figure out some way of enforcing that the database encoding is OK to use with the locale setting. Peter put some trial code for this into initdb in 8.0, but it's only issuing a warning rather than enforcing the restriction, so I don't have a lot of confidence that it is right. (How many people even noticed the warning message?) regards, tom lane