settings for multi-language unicode DB
Please could somebody give me advice on settings for PGSQL with a database
with UTF-8 strings in a large number of languages? I've had no problems so
far in storing/retrieving UTF-8 strings but can't find clear answers to
other issues.
What locale would be best for sorting this dataset? Does it have to be the
same as the one set for the OS (these seem very limited for Solaris)?
As well as European accented characters, I need to handle some others used
for transcription. I have to produce simple to use, sorted lists by initial
letter for users and staff.
The database is for a library which takes journals from all over the world
and has to store titles in all european langauges (broadly defined) plus
western transcriptions of others. We are in the process of moving to
unicode. The DBMS currently runs on Solaris 9 with a backup system on
Fedora 3 Linux and user/editor access is via servlets (Java).
Thanks for any help.
Janet Bagg, CSAC, University of Kent, UK
Janet Bagg wrote:
Please could somebody give me advice on settings for PGSQL with a database
with UTF-8 strings in a large number of languages? I've had no problems so
far in storing/retrieving UTF-8 strings but can't find clear answers to
other issues.What locale would be best for sorting this dataset? Does it have to be the
same as the one set for the OS (these seem very limited for Solaris)?
Yes, right now PostgreSQL uses the operating system to do locale
ordering. That might change in a future release.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
I haven't seen anything to indicate that 8.x improves foreign key
refererence locking and fixes the foreign key 'deadlock detected' issue.
Has that been addressed in 8.1?
I sort my records before loading, but am still getting 'deadlock detected'
(still running 7.3.4).
I have
B references C
B references A
A references C
Any new records for C are loaded first (both A and B depend on C).
A is loaded next (B depends on A).
B is loaded last.
Both A and B are loaded in 'Table C' sorted order. However, a deadlock can
occur with one process's A records and another's B records, due to the fact
that both have a foreign key reference to C.
The only way I know of to eliminate the deadlocks without serializing the
processes is to remove the foreign key reference from either A or B.
However, jeopardizes referential integrity if something goes wrong with the
load process.
I've never quite understood why a READ of a record with a foreign key
reference results in the referenced record being locked with more than a
shared lock.
Wes
On Fri, 25 Nov 2005, Wes wrote:
I haven't seen anything to indicate that 8.x improves foreign key
refererence locking and fixes the foreign key 'deadlock detected' issue.
Has that been addressed in 8.1?
8.1 should be using the new shared row locks for doing the checks. This
should fix the case mentioned.
I've never quite understood why a READ of a record with a foreign key
reference results in the referenced record being locked with more than a
shared lock.
Up until now, we didn't have one to get on a per-record basis.
On 11/25/05 2:40 AM, "Stephan Szabo" <sszabo@megazone.bigpanda.com> wrote:
8.1 should be using the new shared row locks for doing the checks. This
should fix the case mentioned.
Hmm. I'm looking in the "What's new" for 8.1 and see that now. I could
have sworn it wasn't there the last time I looked. Sorry.
Up until now, we didn't have one to get on a per-record basis.
Ok, I guess I'm confused... From the 7.3.2 manual (6.5 updates):
"Multiversion concurrency control(MVCC) This removes our old table-level
locking, and replaces it with a locking system that is superior to most
commercial database systems. In a traditional system, each row that is
modified is locked until committed, preventing reads by other users. MVCC
uses the natural multiversion nature of PostgreSQL to allow readers to
continue reading consistent data during writer activity. Writers continue to
use the compact pg_log transaction system. This is all performed without
having to allocate a lock for every row like traditional database systems.
So, basically, we no longer are restricted by simple table-level locking; we
have something better than row-level locking."
So, until 8.1 PostgreSQL had "something better than row-level locking" for
some things, but no row locking when needed? Or was it row locking is
there, but just no shared row locking?
Wes
On Fri, 25 Nov 2005, Wes wrote:
On 11/25/05 2:40 AM, "Stephan Szabo" <sszabo@megazone.bigpanda.com> wrote:
8.1 should be using the new shared row locks for doing the checks. This
should fix the case mentioned.Hmm. I'm looking in the "What's new" for 8.1 and see that now. I could
have sworn it wasn't there the last time I looked. Sorry.Up until now, we didn't have one to get on a per-record basis.
Ok, I guess I'm confused... From the 7.3.2 manual (6.5 updates):
"Multiversion concurrency control(MVCC) This removes our old table-level
locking, and replaces it with a locking system that is superior to most
commercial database systems. In a traditional system, each row that is
modified is locked until committed, preventing reads by other users. MVCC
uses the natural multiversion nature of PostgreSQL to allow readers to
continue reading consistent data during writer activity. Writers continue to
use the compact pg_log transaction system. This is all performed without
having to allocate a lock for every row like traditional database systems.
So, basically, we no longer are restricted by simple table-level locking; we
have something better than row-level locking."So, until 8.1 PostgreSQL had "something better than row-level locking" for
some things, but no row locking when needed? Or was it row locking is
there, but just no shared row locking?
The latter, the row locks before were single owner and were such that a
second lock request for the same row would wait for the first to be
released. Now effectively you have two levels of locks at the row level,
the weaker of which conflicts with the stronger but not with itself. The
thing about MVCC is that readers do not have to get either lock if they
aren't trying to prevent modifications.
On 11/25/05 9:12 PM, "Stephan Szabo" <sszabo@megazone.bigpanda.com> wrote:
The latter, the row locks before were single owner and were such that a
second lock request for the same row would wait for the first to be
released. Now effectively you have two levels of locks at the row level,
the weaker of which conflicts with the stronger but not with itself.
Now I understand. Thanks for clarifying.
It sounds like there may be some performance improvements that will be
beneficial to us also. Hopefully I can convince them to agree to the 8.1
upgrade. Besides verifying our application, it takes a full weekend to do
the dump/load - the database is just under a billion rows.
Wes
Some time ago I reported a problem where right after an index rebuild vacuum
might take a couple of hours, but gets slower and slower at a seeming
increasing rate as time goes on. What takes a couple of ours after a full
index rebuild now takes 12 hours or more.
The problem was determined to be due to the fact that indexes are vacuumed
in index order, not in disk storage order. I don't see anything about this
in the "What's new" for 8.1. Has anything been done to resolve this?
Scheduled index rebuilds are not feasible. I really don't even want to take
a chance on starting one. The database is just a hair under one billion
rows, and could take the entire weekend or more to rebuild.
Wes
Wes <wespvp@syntegra.com> writes:
The problem was determined to be due to the fact that indexes are vacuumed
in index order, not in disk storage order. I don't see anything about this
in the "What's new" for 8.1. Has anything been done to resolve this?
No. Avoiding that would require a new approach to
vacuum-vs-ordinary-indexscan interlocking, so it won't happen until
someone has a Bright Idea (tm).
regards, tom lane
On Mon, Dec 12, 2005 at 06:26:37PM -0500, Tom Lane wrote:
Wes <wespvp@syntegra.com> writes:
The problem was determined to be due to the fact that indexes are vacuumed
in index order, not in disk storage order. I don't see anything about this
in the "What's new" for 8.1. Has anything been done to resolve this?No. Avoiding that would require a new approach to
vacuum-vs-ordinary-indexscan interlocking, so it won't happen until
someone has a Bright Idea (tm).
Plus there is a TODO to only vacuum pages that are known to have dead
tuples, which should hopefully mean no more index-scans during vacuum as
well. Hopefully this makes it into 8.2...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
"Jim C. Nasby" <jnasby@pervasive.com> writes:
On Mon, Dec 12, 2005 at 06:26:37PM -0500, Tom Lane wrote:
No. Avoiding that would require a new approach to
vacuum-vs-ordinary-indexscan interlocking, so it won't happen until
someone has a Bright Idea (tm).
Plus there is a TODO to only vacuum pages that are known to have dead
tuples, which should hopefully mean no more index-scans during vacuum as
well.
No such luck. You delete any tuples, you need to scan the indexes.
regards, tom lane
On Mon, Dec 12, 2005 at 11:09:01PM -0500, Tom Lane wrote:
"Jim C. Nasby" <jnasby@pervasive.com> writes:
On Mon, Dec 12, 2005 at 06:26:37PM -0500, Tom Lane wrote:
No. Avoiding that would require a new approach to
vacuum-vs-ordinary-indexscan interlocking, so it won't happen until
someone has a Bright Idea (tm).Plus there is a TODO to only vacuum pages that are known to have dead
tuples, which should hopefully mean no more index-scans during vacuum as
well.No such luck. You delete any tuples, you need to scan the indexes.
Even though you can see what the index values were for the now-dead
tuple?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On 12/12/05 5:26 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
The problem was determined to be due to the fact that indexes are vacuumed
in index order, not in disk storage order. I don't see anything about this
in the "What's new" for 8.1. Has anything been done to resolve this?No. Avoiding that would require a new approach to
vacuum-vs-ordinary-indexscan interlocking, so it won't happen until
someone has a Bright Idea (tm).
Any ideas on how I might I reconfigure to mitigate the issue? Separating
the most offending indexes to separate drives probably isn't an option.
Wes
Wes <wespvp@syntegra.com> writes:
Any ideas on how I might I reconfigure to mitigate the issue? Separating
the most offending indexes to separate drives probably isn't an option.
What are you using for vacuum_mem? A larger value should reduce the
number of times we have to scan the indexes of a large table.
regards, tom lane
Because of our build environment, we need to build PostgreSQL specifying the
location of the openssl libraries to insure everyone is using the same
libraries, regardless of the system software is built and executed on. At
7.4.5, we used:
./configure --with-openssl=path
This no longer works with 8.x. See:
<http://archives.postgresql.org/pgsql-committers/2003-11/msg00278.php>
What is the correct way to work around this restriction? Set
LD_LIBRARY_PATH?
This is on a variety of unix platforms.
Wes
Wes <wespvp@syntegra.com> writes:
./configure --with-openssl=path
This no longer works with 8.x. See:
<http://archives.postgresql.org/pgsql-committers/2003-11/msg00278.php>
What is the correct way to work around this restriction?
Use --with-includes and --with-libraries as needed.
regards, tom lane
On 1/6/06 2:47 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
./configure --with-openssl=path
This no longer works with 8.x. See:
<http://archives.postgresql.org/pgsql-committers/2003-11/msg00278.php>
What is the correct way to work around this restriction?Use --with-includes and --with-libraries as needed.
That allows me to build, but doesn't set the library search path in the
binaries ("-Wl,-rpath /blah/blah" on linux or "-R /blah/blah" for Solaris).
This prevents programs from loading, since they can't find the libraries if
they are not in the default library search path of the user.
Wes
On 1/6/06 2:47 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
./configure --with-openssl=path
This no longer works with 8.x. See:
<http://archives.postgresql.org/pgsql-committers/2003-11/msg00278.php>
What is the correct way to work around this restriction?Use --with-includes and --with-libraries as needed.
That doesn't get the library paths into the binary. If the libraries are
not in the default system search path, the user is screwed. Is there a way
to solve this? I think the previous --with-openssl=path set the search path
so the libraries would always be found.
Wes
Wes <wespvp@syntegra.com> writes:
On 1/6/06 2:47 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
Use --with-includes and --with-libraries as needed.
That doesn't get the library paths into the binary. If the libraries are
not in the default system search path, the user is screwed. Is there a way
to solve this? I think the previous --with-openssl=path set the search path
so the libraries would always be found.
No, it didn't. Really the best solution to this is to not have commonly
used libraries that aren't in the system search path ...
regards, tom lane