Re: [GENERAL] Unicode problem again

[Albe Laurenz]

Garry Saddington wrote:
> I have the following error:
> 
> Postgres 8.3 via psycopg 1.1.21 and zope 2.10.
> 
> ProgrammingError Error Value: ERROR: character 0xe28099 of encoding "UTF8" 
> has no equivalent in "LATIN1" select distinct 
[...]

This is UNICODE 0x2019, a "right single quotation mark".

This is a "Windows character" - the only non-UNICODE codepages I
know that contain this character are the Microsoft codepages.

Microsoft programs are known to automagically change ASCII
characters to characters like that, so a frequent source of
such characters is copy & paste from a Microsoft text processor.

> I have changed client_encoding to Latin1 to get over errors 
> caused by having the database in UTF8 and users trying to 
> enter special characters like £ signs.
> 
> Unfortunately, it seems there are already UTF8 encodings in 
> the DB that have no equivalent in Latin1 from before the change.
> 
> How can I get over this problem, and still allow special 
> characters, ie have no error reports.

If you want to allow *all* special characters, you will have to
use UNICODE (and a pretty comprehensive font).
You could check if all software that you use supports UNICODE.

By using LATIN1 (or any other non-UNICODE codepage) you allow
*some* special characters. In that case you should not allow all
characters into your database.
You'll have to check data at entry time.
If you are confident that you will never need any non-LATIN1
characters in your database, you could create the database
with LATIN1 encoding; that way there will be an error message at
data entry time.

If you know that all your data is from and for Windows, you could
also use encoding WIN1252 throughout.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] table "inheritance" and uniform access

[Ivan Sergio Borgonovo]

This is more a general programming question rather than a pg
question but maybe some postgresql features may offer a better
solution.

I'd describe the problem from an OO point of view and I'd like to
know how I could obtain a similar solution with postgresql.

If I have a hierarchy of classes, some with some proprieties some
that doesn't have them, I'd group proprieties and return them
through methods.
The base class will have all the methods returning "empty"
proprieties, the specialisations will overload those methods to
actually return the data.

A way I used to deal with this problem was to add a "has" flag to
the "parent" table and build up queries dynamically inside plpgsql:

create table parent (
  parentID int primary key,
  hasProp1 boolean,
  name varchar(32) not null unique
);
create table child (
  childID int primary key,
  parentID int not null references parent
);
create table parentname_prop1 (
  childID int not null references child,
  somemore_Prop1
);

create or replace function GetChild(_childID int
 out col1, out col2, out somemore_Prop1)
) return setof records
as
$$
declare
  statement varchar(255);
begin
  select into Name, hasProp1 p.hasProp1, p.name
  from child c
  join parent p on c.parentID=p.parentID
  where c.childID=_childID;
  if(hasProp1) then
statement:='select somemore_Prop1 from ' || Name || '_prop1 '
  ' where childID=' || _childID;
...

That's far from elegant but it looks digestible.
Once the proprieties increase in number this system start to get too
messy.

Another way would be to have a list of PropN and build up the query
dynamically checking if Name || '_' || PropN exist... but it start
to look as squeezing too much OOP out of a DB system and it makes me
think I still have to earn my DBA black belt and maybe it's time to
rewrite the schema.

I'm trying to force all this stuff in the DB rather than on the
client code since this code should be wrapped in a serializable
transaction.

I can't see any way to use postgresql own inheritance system.


-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Bulk load data from one table to another

[Nikola]

I have a partitioned table that uses monthly temporal partitions.
Every night I want to load the last 10 weeks of data into a separate
non-partitioned table.

Currently, I truncate the destination table, drop the indexes,
execute:
"INSERT INTO destination SELECT * FROM source_partition"
and recreate indexes.

This loads about 66.5 million rows into the destination table in about
1 hour. Recreating of indexes (4 of them) takes additional 2 hours. Is
there a more efficient way to perform this procedure?

Thanks.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] replication

[Henry - Zen Search SA]

On Tue, June 24, 2008 8:41 am, Adrian Moisey wrote:
> Hi
>
> We have a 100GB database (16GB dumped) running on 8.2.
>
> Since the bandwidth in South Africa isn't that freely available it is
> difficult for us to get a new copy of out DB in our office (our fastest
> link in the office is 4Mbps).
>
> What can you recommend for me to get an up to date copy of my DB in the
> office with as little transfer as possible.
>
> A few ideas I've been toying with are rsyncing a dump of the db, or
> replication.
>
> Any ideas?

Give Slony (http://www.slony.info/) a try - the initial replication of the
entire DB takes a while, thereafter it only replicates changes (as they
happen).

We've been using it successfully (it has it's occasional problems like all
things) for over a year now replicating across a cluster (DB is also about
100GB+).

Regards
Henry


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Problem by adding libpq++

[sheikh salman]

 
Hallo fellows
i am new user of postgresql and need ur help to understand it.I am now using 
postgresql 3.0 with Microsoft Visual C++ 2005 express edition.But i can't 
connect MV C++ with postgresql.There is always library failure.
Pls help me. I have installed all on window XP.
salman


  __
Gesendet von Yahoo! Mail.
Dem pfiffigeren Posteingang.
http://de.overview.mail.yahoo.com

[GENERAL] Query

[kartik]

Hello there

Can anyone please tell me how to make a column uneditable..

Plz  give me some output

thans

Re: [GENERAL] Query

[Pavel Stehule]

hello

2008/6/24 kartik <[EMAIL PROTECTED]>:
> Hello there
>
> Can anyone please tell me how to make a column uneditable….
>
> Plz  give me some output
>
> thans

try trigger

create table foo(
  a varchar
);

create function a_update_trg()
returns trigger as $$
begin
  if new.a is distinct from old.a then
raise exception 'cannot change column a';
  end;
  return new;
end;
$$ language plpgsql;

create trigger foo_trg_update after update on foo for each row execute
procedure a_update_trg();

regards
Pavel Stehule

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Query

[A. Kretschmer]

am  Tue, dem 24.06.2008, um 15:19:46 +0530 mailte kartik folgendes:
> Hello there
> 
> Can anyone please tell me how to make a column uneditable?.

You can write a TRIGGER to avoid changes on the column. Within the
TRIGGER you can compare OLD.column with NEW.column and there you can
raise an error if there are differences.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Query

[Ludwig Kniprath]

Perhaps trivial, but:
Additional to or instead of triggers You can use grants to allow updates only 
to special users.

Ludwig

>Hello there
>
>Can anyone please tell me how to make a column uneditable..
>
>Plz  give me some output
>
>thans
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Bulk load data from one table to another

[Tom Lane]

Nikola <[EMAIL PROTECTED]> writes:
> Currently, I truncate the destination table, drop the indexes,
> execute:
> "INSERT INTO destination SELECT * FROM source_partition"
> and recreate indexes.

> This loads about 66.5 million rows into the destination table in about
> 1 hour. Recreating of indexes (4 of them) takes additional 2 hours. Is
> there a more efficient way to perform this procedure?

Do you have maintenance_work_mem set to a suitable value for building
the indexes?

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] backslashes in 8.3.3

[Brandon Metcalf]

I just upgraded to 8.3.3 from 8.1.5 and noticed that backslashes are
handled completely differently now.  For example,

  db=# insert into junk (cifs) values ('\\f\bar');
  WARNING:  nonstandard use of \\ in a string literal
  LINE 1: insert into junk (cifs) values ('\\f\bar');
  ^
  HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
  INSERT 0 1
  db=# select * from junk;
   cifs
  --
   \f\x08ar
  (1 row)

Is there anyway to disable this behavior or fix this on the server
side?  I really don't want to have to rework all of our code that
inserts or queries data.

It seems this works,

  db=# insert into junk (cifs) values (E'foobar\\bar');
  INSERT 0 1



-- 
Brandon

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] backslashes in 8.3.3

[Tom Lane]

"Brandon Metcalf" <[EMAIL PROTECTED]> writes:
> I just upgraded to 8.3.3 from 8.1.5 and noticed that backslashes are
> handled completely differently now.  For example,

See standard_conforming_strings and escape_string_warning.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] backslashes in 8.3.3

[Brandon Metcalf]

b == [EMAIL PROTECTED] writes:

 b> I just upgraded to 8.3.3 from 8.1.5 and noticed that backslashes are
 b> handled completely differently now.  For example,


It looks like the default for escape_string_warning is now "on".
However, it says in the docs that future versions will treat the
backslash literally.

I suppose this means versions beyond 8.3.3?

But this is really an annoying change if I now have to go and escape
every backslash that we are inserting.


 b>   db=# insert into junk (cifs) values ('\\f\bar');
 b>   WARNING:  nonstandard use of \\ in a string literal
 b>   LINE 1: insert into junk (cifs) values ('\\f\bar');
 b>   ^
 b>   HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
 b>   INSERT 0 1
 b>   db=# select * from junk;
 b>cifs
 b>   --
 b>\f\x08ar
 b>   (1 row)

 b> Is there anyway to disable this behavior or fix this on the server
 b> side?  I really don't want to have to rework all of our code that
 b> inserts or queries data.

 b> It seems this works,

 b>   db=# insert into junk (cifs) values (E'foobar\\bar');
 b>   INSERT 0 1





-- 
Brandon

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] backslashes in 8.3.3

[Brandon Metcalf]

t == [EMAIL PROTECTED] writes:

 t> "Brandon Metcalf" <[EMAIL PROTECTED]> writes:
 t> > I just upgraded to 8.3.3 from 8.1.5 and noticed that backslashes are
 t> > handled completely differently now.  For example,

 t> See standard_conforming_strings and escape_string_warning.


Excellent.  I had missed the standard_conforming_strings and will turn
this on.  Hold on.  Yes, that did it.  Thanks so much.


-- 
Brandon

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] what are rules for?

[Alvaro Herrera]

Adam Rich wrote:

> One interesting example is having rules and triggers watching for
> deletes or updates on a table.  If many rows are modified, rules
> can be faster.  Take this statement:
> 
> DELETE FROM mydata WHERE idval BETWEEN 1 and 2;
> 
> Say this statement deletes 10,000 rows.  The delete trigger would
> get called 10,000 times whereas the rule is essentially executed
> once, since it can share the WHERE clause of the user's query.

This is a use case we should be able to better implement using FOR EACH
STATEMENT triggers, it seems.  We just need to be able to pass the list
of affected tuples to the trigger function, which until now has remained
unimplemented.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Probably been asked a hundred times before.

[David Siebert]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Which disto is best for running a Postgres server?
I just installed OpenSuse and downloaded and compiled the latest version
of Postgres.  It isn't that big of a hassle but I noticed that almost
none of the big distros keep all that up to date with Postgres as far as
what they have in their repositories.

I was wondering if anybody has made an Postgres centric distro?  Just a
nice stripped down server Distro that is ideal for running a database
server on?

More just curious since I already got mine installed and compiled.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3-nr1 (Windows XP)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iQEVAwUBSGEE9uLnn4qAcbUGAQJpkQf/VkeYeG9aCklcybj6qFPAgNRxT7foIiqt
sbjQI/Geik1qDX3WwD7o9bbPR7V8VmZqcY8JhXjsxD8ggNA9qi92YI5Sx/l7Nmaq
yhCPUPZfaexyGynI5DgUxx7glgZP4iPZfYcbjoy0nyaZPLXXDR11i3q8CXvdPhoE
oTGQBT07CijtGPN6y4h2ymlgePUQKoN0NPT9JQ5KQOc8PECpqJsFDUuIon0BtbeN
S+TB1lwAgiceINQBys6wYpw1epbYb3LV2zuN6GjEx41SQQO+8vF1tBpJTvkXBHZI
G3j457pL4NaRgbZtGC0aAz90G2QFFV1MJ9ocMX4Im6HYL2SYOBbDyQ==
=n84g
-END PGP SIGNATURE-

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] backslashes in 8.3.3

[Tom Lane]

"Brandon Metcalf" <[EMAIL PROTECTED]> writes:
> t == [EMAIL PROTECTED] writes:
>  t> See standard_conforming_strings and escape_string_warning.

> Excellent.  I had missed the standard_conforming_strings and will turn
> this on.  Hold on.  Yes, that did it.  Thanks so much.

Well, if your intent is to replicate 8.1's behavior, you should instead
frob the other switch.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] could not select a suitable default timezone on Initdb, 8.3.3, Windows 2008 x64

[JEAN-PIERRE PELLETIER]

Initdb throws "could not select a suitable default timezone" on PostgreSQL 
8.3.3

and Windows x64.

We'd like to use those settings --lc-collate=C --lc-ctype=French_Canada
pginstaller does initdb properly but not with the parameters we want.

We want upper, lower, regex, ... to process acccented characters properly 
but don't care

about the collating sequence of those accented characters.

We have many indexes of varchar columns and want to avoid having both 
regular indexes

and index with *_pattern_ops.

we first install PostgreSQL with pgInstaller and no initdb

we then issue:
initdb -d --pgdata="C:/Program Files (x86)/PostgreSQL/8.3/data" 
--encoding=WIN1252 --lc-collate=C --lc-ctype=French_Canada --pwprompt -L 
"C:/Program Files (x86)/PostgreSQL/8.3/share"


creating template1 database in C:/Program Files 
(x86)/PostgreSQL/8.3/data/base/1 ...

DEBUG:  TZ "US/Eastern" matches Windows timezone "Eastern Daylight Time"
LOG:  could not open directory "/share/timezone": No such file or directory
LOG:  could not open directory "/share/timezone": No such file or directory
FATAL:  could not select a suitable default timezone
DETAIL:  It appears that your GMT time zone uses leap seconds. PostgreSQL 
does not support leap seconds.

DEBUG:  proc_exit(1)
DEBUG:  shmem_exit(1)
DEBUG:  exit(1)
child process exited with exit code 1

We tried putting a copy of C:/Program Files (x86)/PostgreSQL/8.3/share in 
c:/share

to get a little bit further and got this fatal error:

creating conversions ...
FATAL:  could not access file "$libdir/ascii_and_mic": No such file or 
directory
STATEMENT:  CREATE OR REPLACE FUNCTION ascii_to_mic (INTEGER, INTEGER, 
CSTRING, INTERNAL, INTEGER)

RETURNS VOID AS '$libdir/ascii_and_mic', 'ascii_to_mic' LANGUAGE C STRICT;
child process exited with exit code 1

The file ascii_and_mic.dll does exist in C:/Program Files 
(x86)/PostgreSQL/8.3/lib


Thanks,
Jean-Pierre Pelletier
e-djuster



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] what are rules for?

[Michael Shulman]

On Mon, Jun 23, 2008 at 11:54 PM, Adam Rich <[EMAIL PROTECTED]> wrote:
>> Can you describe, or point me to somewhere which describes, all the
>> things you can do with a rule that you can't do with a trigger?  The
>> only examples of rules in the manual are (1) logging, which I've just
>> been told is much better done with a trigger, and (2) making update,
>> insert, and delete work for a view, which is the only way to do it
>> because views are not allowed to have update, insert, or delete
>> triggers.  However, as I have learned in several recent threads, this
>> use of rules is fraught with difficulties, especially when the view
>> has more than one table, and it seems that it would be much easier if
>> triggers were just allowed on views.  What is the real purpose of the
>> rule system?
>
> You can read more about rules here:
>
> http://www.postgresql.org/docs/8.3/interactive/rules.html

I have read the manual.  As I pointed out, the only examples of rules
in the manual are (1) something which may be better done with a
trigger, and certainly *can* be done with a trigger, whatever the
speed considerations may be, and (2) something which doesn't seem to
work very well, and seemingly (from my PoV) *would* be done better
with triggers if triggers were allowed in that situation.

My question was, what else *can* you do with a rule that you *can't*
do with a trigger?  Are rules only a way to speed up things that could
also be done with triggers?

Thanks!
Mike

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] what are rules for?

[Martijn van Oosterhout]

On Tue, Jun 24, 2008 at 10:28:00AM -0500, Michael Shulman wrote:
> I have read the manual.  As I pointed out, the only examples of rules
> in the manual are (1) something which may be better done with a
> trigger, and certainly *can* be done with a trigger, whatever the
> speed considerations may be, and (2) something which doesn't seem to
> work very well, and seemingly (from my PoV) *would* be done better
> with triggers if triggers were allowed in that situation.

It possible that if rules wern't the only way currently to implement
(updateable) views, then they might have been ripped out a long time
ago. Thing is, people like views and there is currently no other way to
do them.

> My question was, what else *can* you do with a rule that you *can't*
> do with a trigger?  Are rules only a way to speed up things that could
> also be done with triggers?

Well, views for one. Rules work by manipulating queries, trigger work
by manipulating tuples. Rules have their problems, but sometimes
they're the only way. Updateable views are the major use case.

FWIW, I wonder if the multiple evaluation problem could be solved by
the WITH patch, since the original rule could be stuffed in a WITH
clause and then the rule applied on top of it.

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while 
> boarding. Thank you for flying nlogn airlines.


signature.asc
Description: Digital signature

Re: [GENERAL] what are rules for?

[Tom Lane]

Martijn van Oosterhout <[EMAIL PROTECTED]> writes:
> On Tue, Jun 24, 2008 at 10:28:00AM -0500, Michael Shulman wrote:
>> My question was, what else *can* you do with a rule that you *can't*
>> do with a trigger?  Are rules only a way to speed up things that could
>> also be done with triggers?

> Well, views for one.

To expand on that: it's pretty hard to see how update or delete triggers
on a view would work.  Insert is easy, because if left to its own
devices the system would in fact try to insert a tuple into the view
relation, and that action could fire a trigger which could redirect the
insertion someplace else.  But updates and deletes require a
pre-existing target tuple, and there just aren't any of those in a view
relation.  (Another way to say it is that update/delete require a CTID
column, which a view hasn't got.)

So view update/delete appear to require a transformational-rule kind
of approach instead of an actions-on-physical-tuples kind of approach.

If you've got a better idea we're all ears ...

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] String Encoding Conversion Problem

[Ryan Wells]

We've got a .NET application that's trying to move data from an old MySQL 
database to a shiny new Postgres db, but we keep getting this error: 
invalid byte sequence for encoding "UTF8": 0xf66a6e69
 
The MySQL table is using "latin1 -- cp1252 West European" and the Postgres 
server is using UTF8.  We're narrowed the problem down to the special 
characters in what appears to be some Swedish text (hopefully they'll make the 
transition to email):
 
Onormal höjning av kroppstemperaturen, oftast till följd av något 
sjukdomsförlopp. (MeSH Swedish) 
 
This may actually be more of a .NET string conversion issue, but I wanted to 
check and make sure there's not some obvious database angle we're missing.  
Does anyone have any experience with or insight into these sorts of string 
compatibility issues?
 
Thanks!
Ryan

Ryan Wells
Application Developer, SOAPware, Inc.
www.SOAPware.com  
1-800-455-7627 

Re: [GENERAL] backslashes in 8.3.3

[Brandon Metcalf]

t == [EMAIL PROTECTED] writes:

 t> "Brandon Metcalf" <[EMAIL PROTECTED]> writes:
 t> > t == [EMAIL PROTECTED] writes:
 t> >  t> See standard_conforming_strings and escape_string_warning.

 t> > Excellent.  I had missed the standard_conforming_strings and will turn
 t> > this on.  Hold on.  Yes, that did it.  Thanks so much.

 t> Well, if your intent is to replicate 8.1's behavior, you should instead
 t> frob the other switch.

I now have

  escape_string_warning = off

and

  standard_conforming_strings = on

in postgresql.conf and things are back to how they were.  That is no
warnings and backslashes treated literally.

A related question, is it in any way possible that a control sequence
could have been sent from a client that caused a fast shutdown?  Our
server log shows a fast shutdown request last night, but nobody
manually issued such a request.

-- 
Brandon

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] backslashes in 8.3.3

[Tom Lane]

"Brandon Metcalf" <[EMAIL PROTECTED]> writes:
> t == [EMAIL PROTECTED] writes:
>  t> Well, if your intent is to replicate 8.1's behavior, you should instead
>  t> frob the other switch.

> I now have
>   escape_string_warning = off
> and
>   standard_conforming_strings = on
> in postgresql.conf and things are back to how they were.  That is no
> warnings and backslashes treated literally.

Uh, no, that is certainly *not* the behavior you were getting in 8.1;
8.1's behavior corresponds to both switches off.

> A related question, is it in any way possible that a control sequence
> could have been sent from a client that caused a fast shutdown?  Our
> server log shows a fast shutdown request last night, but nobody
> manually issued such a request.

Fast shutdown means something sent SIGINT to the postmaster.
The only way I've heard for that to happen "accidentally" is
if you normally launch the postmaster by hand in a way that
leaves it attached to your terminal session --- then control-C
in that session would SIGINT the postmaster.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] String Encoding Conversion Problem

[Karsten Hilbert]

On Tue, Jun 24, 2008 at 11:31:16AM -0500, Ryan Wells wrote:
> Subject: [GENERAL] String Encoding Conversion Problem
> 
> We've got a .NET application that's trying to move data from an old MySQL 
> database to a shiny new Postgres db, but we keep getting this error: 
> invalid byte sequence for encoding "UTF8": 0xf66a6e69
>  
> The MySQL table is using "latin1 -- cp1252 West European" and the Postgres 
> server is using UTF8.

You'll have to use any of the documented methods of telling
PostgreSQL that the client is loading data in the "cp1252"
encoding.

Doing so revolves around setting the client_encoding
parameter at either of the transaction, database, client-,
or server-OS level, whatever is more appropriate.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] backslashes in 8.3.3

[Brandon Metcalf]

t == [EMAIL PROTECTED] writes:

 t> "Brandon Metcalf" <[EMAIL PROTECTED]> writes:
 t> > t == [EMAIL PROTECTED] writes:
 t> >  t> Well, if your intent is to replicate 8.1's behavior, you should 
instead
 t> >  t> frob the other switch.

 t> > I now have
 t> >   escape_string_warning = off
 t> > and
 t> >   standard_conforming_strings = on
 t> > in postgresql.conf and things are back to how they were.  That is no
 t> > warnings and backslashes treated literally.

 t> Uh, no, that is certainly *not* the behavior you were getting in 8.1;
 t> 8.1's behavior corresponds to both switches off.

OK.  I'm confused.  With 8.1.5 we never had to do anything special
with backslashes.  When we upgraded to 8.3.3, backslashes in our
INSERTs caused problems until we turn _on_
standard_conforming_strings.

 t> > A related question, is it in any way possible that a control sequence
 t> > could have been sent from a client that caused a fast shutdown?  Our
 t> > server log shows a fast shutdown request last night, but nobody
 t> > manually issued such a request.

 t> Fast shutdown means something sent SIGINT to the postmaster.
 t> The only way I've heard for that to happen "accidentally" is
 t> if you normally launch the postmaster by hand in a way that
 t> leaves it attached to your terminal session --- then control-C
 t> in that session would SIGINT the postmaster.

That could have been it.


-- 
Brandon

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Probably been asked a hundred times before.

[Jorge Godoy]

On Tuesday 24 June 2008 11:30:14 David Siebert wrote:
> Which disto is best for running a Postgres server?
> I just installed OpenSuse and downloaded and compiled the latest version
> of Postgres.  It isn't that big of a hassle but I noticed that almost
> none of the big distros keep all that up to date with Postgres as far as
> what they have in their repositories.
>
> I was wondering if anybody has made an Postgres centric distro?  Just a
> nice stripped down server Distro that is ideal for running a database
> server on?
>
> More just curious since I already got mine installed and compiled.

I'm running OpenSuSE 11.0 and I have PostgreSQL 8.3.1 right from the 
installation DVD.

I know it isn't 8.3.3, but they couldn't package and test everything until the 
release date.

There are packagers for Fedora here...  But I would rather compile PG myself 
than switch to Fedora just because of a PostgreSQL package.

Generating new RPMs / updating existing ones isn't so hard.

-- 
Jorge Godoy  <[EMAIL PROTECTED]>




signature.asc
Description: This is a digitally signed message part.

Re: [GENERAL] Probably been asked a hundred times before.

[Scott Marlowe]

On Tue, Jun 24, 2008 at 8:30 AM, David Siebert <[EMAIL PROTECTED]> wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Which disto is best for running a Postgres server?

That is the subject of many a holy flame war.  FreeBSD 7.0 seems to
currently be regarded as being one of the top performers.

I use ubuntu because it's easier to maintain and it provides very good
performance

> I just installed OpenSuse and downloaded and compiled the latest version
> of Postgres.  It isn't that big of a hassle but I noticed that almost
> none of the big distros keep all that up to date with Postgres as far as
> what they have in their repositories.

Ubuntu does, and there are the PGDG rpms on the postgresql website
that work on RHEL / Centos / Whitebox linux quite nicely.

> I was wondering if anybody has made an Postgres centric distro?  Just a
> nice stripped down server Distro that is ideal for running a database
> server on?

There's been a few pg_live CDs floating around.  They're more of a
demo / super easy setup type of thing, not intended for production
use.  But they are very handy for trying out pgsql.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] backslashes in 8.3.3

[Tom Lane]

"Brandon Metcalf" <[EMAIL PROTECTED]> writes:
> t == [EMAIL PROTECTED] writes:
>  t> Uh, no, that is certainly *not* the behavior you were getting in 8.1;
>  t> 8.1's behavior corresponds to both switches off.

> OK.  I'm confused.  With 8.1.5 we never had to do anything special
> with backslashes.  When we upgraded to 8.3.3, backslashes in our
> INSERTs caused problems until we turn _on_
> standard_conforming_strings.

Maybe something changed on the client side?  8.1 definitely does not have
standard_conforming_strings, but perhaps you had some client-side
code that compensated by inserting backslashes.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Probably been asked a hundred times before.

[Tom Lane]

Jorge Godoy <[EMAIL PROTECTED]> writes:
> On Tuesday 24 June 2008 11:30:14 David Siebert wrote:
>> I was wondering if anybody has made an Postgres centric distro?

> I'm running OpenSuSE 11.0 and I have PostgreSQL 8.3.1 right from the 
> installation DVD.

Fedora 9 likewise shipped with PG 8.3.1.  It's all a matter of what was
current when a particular major distro release was frozen.

Some distros are more aggressive than others about updating to new minor
PG releases, but there is no one who will auto-update you to a new major
PG release.  Lack of upgrade-in-place is part of the reason; but even
if we had that, few distros would consider it because of the application
compatibility issues that new major releases typically create.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Probably been asked a hundred times before.

[Kevin Hunter]

At 2:12p -0400 on Tue, 24 Jun 2008, Tom Lane wrote:
> Jorge Godoy <[EMAIL PROTECTED]> writes:
>> On Tuesday 24 June 2008 11:30:14 David Siebert wrote:
>>> I was wondering if anybody has made an Postgres centric distro?
> 
>> I'm running OpenSuSE 11.0 and I have PostgreSQL 8.3.1 right from the 
>> installation DVD.
> 
> Fedora 9 likewise shipped with PG 8.3.1.  It's all a matter of what was
> current when a particular major distro release was frozen.

Isn't there the Postgres Live CD?  I don't suppose that "distro" is at
all meant to be liberated or installed?

Other than that, what're your criterion for "best"?

Short of a response, I've read a number of reports that given some
tuning FreeBSD 7.0 is the current top performer.

Kevin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] backslashes in 8.3.3

[Brandon Metcalf]

t == [EMAIL PROTECTED] writes:

 t> "Brandon Metcalf" <[EMAIL PROTECTED]> writes:
 t> > t == [EMAIL PROTECTED] writes:
 t> >  t> Uh, no, that is certainly *not* the behavior you were getting in 8.1;
 t> >  t> 8.1's behavior corresponds to both switches off.

 t> > OK.  I'm confused.  With 8.1.5 we never had to do anything special
 t> > with backslashes.  When we upgraded to 8.3.3, backslashes in our
 t> > INSERTs caused problems until we turn _on_
 t> > standard_conforming_strings.

 t> Maybe something changed on the client side?  8.1 definitely does not have
 t> standard_conforming_strings, but perhaps you had some client-side
 t> code that compensated by inserting backslashes.


No, nothing changed on the client side.  I know you know what you're
talking about, though.  At any rate, I've got the behavior I need now.

-- 
Brandon

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Probably been asked a hundred times before.

[Greg Smith]

On Tue, 24 Jun 2008, Kevin Hunter wrote:


Short of a response, I've read a number of reports that given some
tuning FreeBSD 7.0 is the current top performer.


Those reports are all not quite right and I'm trying to get time to fully 
debunk them in PostgreSQL land.


First off, they were running a small read-only benchmark, which is not 
representative at all of real database performance.  The FreeBSD team was 
looking for something that stressed database kernel operations, and never 
intended this to be a true database comparison.


Second, there was a problem with the new Linux CFS scheduler running 
sysbench at the time the FreeBSD 7.0 reports touting its superiority were 
released.  It's since been fixed; 
http://www.kernel.org/pub/linux/kernel/people/npiggin/sysbench/ shows you 
the after and links to the before when using MySQL.  PostgreSQL sysbench 
results also benefitted, I haven't seen someone do a new set of benchmarks 
there yet.


Third, there was also a bad interaction between the kernel and the 
malloc/free sections of glibc that really impacted results here.  The 
FreeBSD 7.0 results had a specific fix in this area for their kernel. 
Shortly afterward, a similar one was merged into Linux: 
http://lkml.org/lkml/2007/4/1/268 (that fix was also active at the point 
the previous benchmarks I pointed to were done)


So, yes, there was a brief window where the new FreeBSD 7.0 had a 
performance advantage over Linux on this artificial (and bad) benchmark 
running both its old scheduler and the still buggy and new CFS one, but 
the two issues responsible have been resolved and current Linux kernels 
using CFS are back to being on top again.  The exact performance you'll 
get depends on which Linux distribution/kernel combination you use, but 
it's just not true that FreeBSD has an unambiguous lead here.  The minute 
the FreeBSD team declared that these benchmark results were somehow 
interesting, it was simple for the Linux team to blow right by them by 
optimizing for the weird things sysbench does the same way.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] SUMMARY: Solaved. apache perl cgi script cant load libpq.5.dylib on mac os tiger

[Tom Allison]

I found a similar problem only with a slight variation.

postgresql, for me, is installed in /opt/local/ with the particular file 
being located at:

/opt/local/lib/postgresql83/libpq.5.dylib

This is all a part of the mac ports installation for apache2 and postgres.

It seems that the problem isn't so much pgsql but apache2 and/or mac 
ports.  But would you (or anyone else) have a suggestion on how to 
proceed?  I could probably just make a lot of soft links, but that 
doesn't seem very robust.


Fergus McMenemie wrote:

Hmmm,

It was the protection on the /usr/local/pgsql directory. Solved, by myself
an hour after making this post. Odd that, its been bugging the heck out of
me for over a week now, and then one hour after posting



At 16:05 +0100 23/5/08, Fergus McMenemie wrote:

I have a perl cgi script that wont run on a new mac.  The script runs fine
from the command line, so the perl and postgresql seem good. However
when run as an apache cgi script the postgresql stuff fails as shown
below.  I altered the script to dump %ENV and the paths seem in order.

The script is not using mod perl. It is using the apple os x 10.4.11 supplied
version of apache, postgresql-8.3.0  and perl, v5.8.8 with DBD::Pg, DBI and
JSON installed.

I am at a total loss as to why the library cant be found and would be
very grateful for any help.

Content-type: text/html Software error:

install_driver(Pg) failed:
  Can't load 
'/usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/auto/DBD/Pg/Pg.bundle'
 for module DBD::Pg: 
dlopen(/usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/auto/DBD/Pg/Pg.bundle,
 1):
 Library not loaded: /usr/local/pgsql/lib/libpq.5.dylib
 Referenced from: 
/usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/auto/DBD/Pg/Pg.bundle
 Reason: image not found at 
/usr/local/lib/perl5/5.8.8/darwin-2level/DynaLoader.pm line 230.
at (eval 45) line 3
 Compilation failed in require at (eval 45) line 3.
Perhaps a required shared library or dll isn't installed where expected at 
/Library/WebServer/cgi-bin/pgcgi.pl line 383

$ENV{AUTH_TYPE} = Basic
$ENV{DOCUMENT_ROOT} = /Library/WebServer/Documents
$ENV{DYLD_LIBRARY_PATH} = /usr/local/pgsql/lib:/usr/lib:/usr/local/lib
$ENV{GATEWAY_INTERFACE} = CGI/1.1
$ENV{HTTPS} = on
$ENV{HTTP_ACCEPT} = 
text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,/;q=0.5
$ENV{HTTP_ACCEPT_CHARSET} = ISO-8859-1,utf-8;q=0.7,;q=0.7
$ENV{HTTP_ACCEPT_ENCODING} = gzip,deflate
$ENV{HTTP_ACCEPT_LANGUAGE} = en,en-us;q=0.5
$ENV{HTTP_CONNECTION} = keep-alive
$ENV{HTTP_HOST} = X.co.uk
$ENV{HTTP_KEEP_ALIVE} = 300
$ENV{HTTP_USER_AGENT} = Mozilla/5.0 (Macintosh; U; PPC Mac OS X Mach-O; 
en-US; rv:1.8.1.14) Gecko/20080404 Firefox/2.0.0.14
$ENV{LD_LIBRARY_PATH} = /usr/local/pgsql/lib:/usr/lib:/usr/local/lib
$ENV{PATH} = 
/usr/local/pgsql/bin:/usr/local/bin:/bin:/sbin:/usr/bin:/usr/sbin
$ENV{QUERY_STRING} =
$ENV{REMOTE_ADDR} = XX
$ENV{REMOTE_PORT} = 53698
$ENV{REMOTE_USER} = fergus
$ENV{REQUEST_METHOD} = GET
$ENV{REQUEST_URI} = /cgi-bin/pgcgi.pl
$ENV{SCRIPT_FILENAME} = /Library/WebServer/cgi-bin/pgcgi.pl
$ENV{SCRIPT_NAME} = /cgi-bin/pgcgi.pl
$ENV{SERVER_ADDR} = 
$ENV{SERVER_ADMIN} = [EMAIL PROTECTED]
$ENV{SERVER_NAME} = X.co.uk
$ENV{SERVER_PORT} = 443
$ENV{SERVER_PROTOCOL} = HTTP/1.1
$ENV{SERVER_SIGNATURE} =  Apache/1.3.41 Server at .co.uk Port 443
$ENV{SERVER_SOFTWARE} = Apache/1.3.41 (Darwin) mod_ssl/2.8.31 OpenSSL/0.9.7l

--

===
Fergus McMenemie   Email:[EMAIL PROTECTED]
Techmore Ltd   Phone:(UK) 07721 376021

Unix/Mac/Intranets Analyst Programmer
===

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_dump estimation

[Marcelo Martins]

is there a way to find out / calculate / estimate how big a pg_dump  
using plain text format for a DB will be ?
I have this system with a 7.4 version and a DB that is over 60GB and I  
know that the admins have never done a vacuum there.
The system only has about 20GB of free space so I don't want to take  
any chances of filling up the disk due to a pg_dump  you know.



thanks,

Marcelo
Linux/Solaris System Administrator
[EMAIL PROTECTED]
http://www.zeroaccess.org


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] LIKE not using indexes (due to locale issue?)

[Ow Mun Heng]

explain select * from d_trr where revision like '^B2.%.SX'
--where ast_revision  = 'B2.M.SX'

Seq Scan on d_trr  (cost=0.00..2268460.98 rows=1 width=16)
  Filter: ((revision)::text ~~ '^B2.%.SX'::text)

show lc_collate;
en_US.UTF-8

Is it that this is handled by tsearch2? Or I need to do the locale to
"C" for this to function?

the revision is indexed.

Bitmap Heap Scan on d_trr  (cost=4492.43..547709.26 rows=193453
width=16)
  Recheck Cond: ((revision)::text = 'B2.M.SX'::text)
  ->  Bitmap Index Scan on idx_d_trr_iw_ast  (cost=0.00...06
rows=193453 width=0)
Index Cond: ((revision)::text = 'B2.M.SX'::text)

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] LIKE not using indexes (due to locale issue?)

[Klint Gore]

Ow Mun Heng wrote:

explain select * from d_trr where revision like '^B2.%.SX'
--where ast_revision  = 'B2.M.SX'

Seq Scan on d_trr  (cost=0.00..2268460.98 rows=1 width=16)
  Filter: ((revision)::text ~~ '^B2.%.SX'::text)

show lc_collate;
en_US.UTF-8

Is it that this is handled by tsearch2? Or I need to do the locale to
"C" for this to function?
  
See http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html. 
It tells you how to create an index that like might use in non-C locales.


As a side note, the ^ in the string above has no special meaning for like.

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789  
Fax: 02 6773 3266

EMail: [EMAIL PROTECTED]


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Probably been asked a hundred times before.

[Clemens Schwaighofer]

--On Tuesday, June 24, 2008 10:30:14 AM -0400 David Siebert 
<[EMAIL PROTECTED]> wrote:




Which disto is best for running a Postgres server?


I run most of my postgres servers on Debian. I really love it, because 
once a new major version comes out you can very easy install it 
parallel to your current version and test and once you are done, 
migrate the data and just switch the port.


I also have some external servers with RedHat ES and postgres. But when 
I upgrade there I use the RPMs provided by postgres. Major version 
upgrades are a bit more tricky here, but they work.



[ Clemens Schwaighofer  -=:~ ]
[ IT Engineer/Manager, TEQUILA\ Japan IT Group   ]
[6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.co.jp   ]

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] replication

[Asko Oja]

Hi

Take a look also on Londiste from SkyTools. It is easy to set up and manage.
In addition SkyTools contains other useful scripts and tools.
We use Londiste to replicate data ovwe WAN where walshipping would consume
too much bandwidth.

Asko

On Tue, Jun 24, 2008 at 9:41 AM, Adrian Moisey <[EMAIL PROTECTED]>
wrote:

> Hi
>
> We have a 100GB database (16GB dumped) running on 8.2.
>
> Since the bandwidth in South Africa isn't that freely available it is
> difficult for us to get a new copy of out DB in our office (our fastest link
> in the office is 4Mbps).
>
> What can you recommend for me to get an up to date copy of my DB in the
> office with as little transfer as possible.
>
> A few ideas I've been toying with are rsyncing a dump of the db, or
> replication.
>
> Any ideas?
>
> --
> Adrian Moisey
> Systems Administrator | CareerJunction | Your Future Starts Here.
> Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED]
> Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] LIKE not using indexes (due to locale issue?)

[Ow Mun Heng]

On Wed, 2008-06-25 at 14:58 +1000, Klint Gore wrote:
> Ow Mun Heng wrote:
> > explain select * from d_trr where revision like '^B2.%.SX'
> > --where ast_revision  = 'B2.M.SX'
> >
> > Seq Scan on d_trr  (cost=0.00..2268460.98 rows=1 width=16)
> >   Filter: ((revision)::text ~~ '^B2.%.SX'::text)
> >
> > show lc_collate;
> > en_US.UTF-8
> >
> > Is it that this is handled by tsearch2? Or I need to do the locale to
> > "C" for this to function?
> >   
> See http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html. 
> It tells you how to create an index that like might use in non-C locales.

Just more information. This columns is created with the varchar type.

original index is created using

CREATE INDEX idx_d_ast
  ON xmms.d_trh
  USING btree
  (revision varchar_pattern_ops);


CREATE INDEX idx_d_ast2
  ON xmms.d_trh
  USING btree
  (revision);

after creating it, seems like it is still doing the seq_scan.
So what gives? Can I get more clues here?





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Full Text Search - i18n

[Andrew]

Re-reading the documentation, and I have the answer.  If adding a 
tsvector column, then for per row selection, I should also add a second 
column of type regconfig to specify the language that rows contents are in.


Cheers,

Andy

Andrew wrote:
Apologies if this question has been previously covered, but I was not 
able to find something similar in any of the mailing list archives.


With full text search, if you need to support a table where the 
content of individual tuples/rows may be in different languages with 
the language of the content known, and the content of any individual 
row does _not_ contain a mixture of languages, what is the best way to 
design for this?


Would you create a different GIN/GiST index of the tsvector  for each 
language?  Or if using columns to store the tsvector, would you have a 
different column per language, or the one column with the associated 
trigger using the appropriate language when building the tsvector for 
that row?


Are there better alternatives than what I have listed?

Thanks,

Andy

[GENERAL] Full Text Search - i18n

[Andrew]

Apologies if this question has been previously covered, but I was not 
able to find something similar in any of the mailing list archives.


With full text search, if you need to support a table where the content 
of individual tuples/rows may be in different languages with the 
language of the content known, and the content of any individual row 
does _not_ contain a mixture of languages, what is the best way to 
design for this?


Would you create a different GIN/GiST index of the tsvector  for each 
language?  Or if using columns to store the tsvector, would you have a 
different column per language, or the one column with the associated 
trigger using the appropriate language when building the tsvector for 
that row?


Are there better alternatives than what I have listed?

Thanks,

Andy