X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 952FCD1B1D5; Wed, 9 Jun 2004 07:33:34 -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 08918-10; Wed, 9 Jun 2004 10:33:35 +0000 (GMT) Received: from mail.najdi.si (elfstone.noviforum.si [193.189.169.107]) by svr1.postgresql.org (Postfix) with ESMTP id B9E13D1B19F; Wed, 9 Jun 2004 07:33:31 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by elfstone.noviforum.si (ESMTP) with ESMTP id 24A331B922; Wed, 9 Jun 2004 12:29:25 +0200 (CEST) Received: from elbereth.noviforum.si (elbereth.noviforum.si [192.168.2.26]) by elfstone.noviforum.si (ESMTP) with ESMTP id E00A61B912; Wed, 9 Jun 2004 12:29:24 +0200 (CEST) Received: from elbereth.noviforum.si (localhost [127.0.0.1]) by elbereth.noviforum.si (8.12.10/8.12.10) with ESMTP id i59AX3Wg004032; Wed, 9 Jun 2004 12:33:04 +0200 Received: (from gregab@localhost) by elbereth.noviforum.si (8.12.10/8.12.10/Submit) id i59AX3A2004031; Wed, 9 Jun 2004 12:33:03 +0200 Date: Wed, 9 Jun 2004 12:33:03 +0200 From: Grega Bremec To: pgsql-admin@postgresql.org Cc: pgsql-hackers@postgresql.org Subject: Refined LC_COLLATE or multiple database clusters? Message-ID: <20040609103303.GA4016@elbereth.noviforum.si> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="qDbXVdCdHGoSgWSk" Content-Disposition: inline User-Agent: Mutt/1.4.2i Organization: Noviforum, Ltd., Software & Media X-Virus-Scanned: by Najdi.si X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/105 X-Sequence-Number: 13765 --qDbXVdCdHGoSgWSk Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Hello, List, I recently stumbled across a problem that I can't really get across. We have a database cluster (PG 7.4.2) that was initialized as follows: $ pg_controldata /data/dir pg_control version number: 72 Catalog version number: 200310211 Database cluster state: in production pg_control last modified: sre 09 jun 2004 03:00:26 CEST Current log file ID: 20 Next log file segment: 63 ... ... Database block size: 8192 Blocks per segment of large relation: 131072 Maximum length of identifiers: 64 Maximum number of function arguments: 32 Date/time type storage: 64-bit integers Maximum length of locale name: 128 LC_COLLATE: C LC_CTYPE: C Inside that cluster, there are several databases using different encodings: template1=3D# \l List of databases Name | Owner | Encoding=20 -------------+----------+---------- db1 | ownera | UNICODE db2 | ownera | UNICODE db3 | ownerb | LATIN2 db4 | ownerc | LATIN2 db5 | ownera | LATIN2 db6 | ownera | LATIN2 template0 | postgres | UNICODE template1 | postgres | UNICODE Collate order for those databases, however, needs to be different. Obviousl= y, db3, db4, db5 and db6 will want to use some collate ordering scheme based on ISO-8859-2, whereas the other two could possibly have table- or even column- based collate requirements, as they contain unicode data in UTF-8 encoding, which doesn't give any warranties wrt the contents of these databases. Producing a list of requirements and/or imposing certain conventions on the format of data stored in those tables is outside the scope of my authoritie= s, the only reasonable assumption I can make is that these databases could be limited to one collating order per database (or several databases, as it is) without much hassle. Also, running several postmasters on this same machine is not an option, as it only has 1.5GB RAM, of which only 1GB is available for cache (~260MB is swapped after roughly a month's uptime, but that doesn't change much after settling down in a week or two). My question to the list would be the following: Is it possible to do either of these things that could solve this problem adequately: - somehow manage to make one postmaster run on top of two separate database clusters that would each have a different collate ordering scheme - use some other method of initializing one database from a different template and taking with it LC_COLLATE setting (I suppose not, as the "${PGDATA}/global/" directory is global to the cluster) - use a patch that would add such functionality or upgrade to a version (even if release-candidate, beta is not really an option, i gather) of PostgreSQL that supported it - in absence of any other viable solution, change the global setting of the database cluster without having to dump/reinitdb/restore it I thank you in advance for your valuable input, --=20 Grega Bremec Senior Administrator Noviforum Ltd., Software & Media http://www.noviforum.si/ --qDbXVdCdHGoSgWSk Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAxudfDo/EMYD4+osRAu1FAJ9E7HmIhsAkXkGsUVH6WpBgGFln0wCdEws5 l3b59YZRZu3yTcXE6pRve0U= =wlXV -----END PGP SIGNATURE----- --qDbXVdCdHGoSgWSk--