Re: [GENERAL] List of countries (WAS: National Number to text conversion)
brian ally wrote:
Thanks, Nis. Here's my revised list, using those (errors of letter-case
entirely my own, owing to my hazy understanding of capitilisation for
hyphenated french nouns):
With the risk of getting horribly off topic, you may want to be
consistent between the languages in whether you use the "official" name
or the "common" name.
In general, this problem arises in cases where the official name is
controversial ("TAÏWAN, PROVINCE DE CHINE"), much longer than the common
one ("IRAN, RÉPUBLIQUE ISLAMIQUE D'"), differs in spelling from the
common form ("VIET NAM") or is less likely to be immediately recognized
("KOREA, REPUBLIC OF").
/Nis
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Charset conversion error
As far as I know the conversion table was not changed. Are you saying that Win-1251 0xb9 is converted to a non-space-char in the previous version? -- Tatsuo Ishii SRA OSS, Inc. Japan > Previous version converted such characters all right. And there wasn't any > bogus ASCII spaces. But I looked at the KOI8 charset table and found out > that there is not equivalent symbol (0xb9) in this table. > > 2006/5/30, Tatsuo Ishii <[EMAIL PROTECTED]>: > > > > > I think you are right. But everything was alright before I updated my > > > database. > > > > Previous version converted such characters to ASCII spaces. So > > probably you have lots of bogus spaces anyway. If you think it's ok, > > then you could your own CONVERSION which behaves similar to previous > > version. > > -- > > Tatsuo Ishii > > SRA OSS, Inc. Japan > > > > > So there are a lot of "incorrect" values in tables. And errors appear > > when I > > > execute "SELECT * FROM table". > > > > > > 2006/5/30, Tatsuo Ishii <[EMAIL PROTECTED]>: > > > > > > > > > Hi, I have an error after updating my database up to 8.1.4 version. > > > > > "SQL Error: ERROR: character 0xb9 of encoding "WIN1251" has no > > > > equivalent > > > > > in "MULE_INTERNAL"'. " > > > > > My client program encoding is windows-1251 and database encoding is > > > > koi8. > > > > > What can I do to rectify the situation? > > > > > > > > It suggests that Windows-1251's 0xb9 cannot be converted to KOI8. You > > > > should check your Windows-1251 data. > > > > -- > > > > Tatsuo Ishii > > > > SRA OSS, Inc. Japan > > > > > > > > > > > > > > > > -- > > > Verba volent, scripta manent > > > Dan Black > > > > > > -- > Verba volent, scripta manent > Dan Black ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
R: [GENERAL] [ODBC] information request on postgresql --> oracle
Hi all, Ragnar and Tino many thanks for you feedback. Using this type of approach probably i will try first with pl-j or pl/java because i am more comfortable with the java language. It would be nice if i can use, inside postgresql, something like "select * from [EMAIL PROTECTED]" ... Probably it would be nice if elephants can fly too ! Best regards, Glauco Mancini -Messaggio originale- Da: Tino Wildenhain [mailto:[EMAIL PROTECTED] Inviato: lunedì 29 maggio 2006 11.50 A: Ragnar Cc: Glauco Mancini; pgsql-general@postgresql.org Oggetto: Re: [GENERAL] [ODBC] information request on postgresql --> oracle Ragnar schrieb: > On mán, 2006-05-29 at 10:21 +0200, Glauco Mancini wrote: > > >>i'm looking for a method to connect natively a postgresql db to oracle >>( maybe via odbc ? ) with something similar to the oracle dblink. >> >>I connected successfully a oracle instance to a postgresql instance >>using unix-odbc, now i need to proceed on the other way. >> >>Can you please help me with some suggestion ? > > > maybe plperlu and DBD::Oracle ? > Or easier: http://pgfoundry.org/projects/dbi-link/ Which uses that approach imho. Regards Tino ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: R: [GENERAL] [ODBC] information request on postgresql --> oracle
Glauco Mancini schrieb: Hi all, Ragnar and Tino many thanks for you feedback. Using this type of approach probably i will try first with pl-j or pl/java because i am more comfortable with the java language. It would be nice if i can use, inside postgresql, something like "select * from [EMAIL PROTECTED]" ... Probably it would be nice if elephants can fly too ! well if you like the java language but not its footprint, you can also try plpythonu, which is quite similar - syntax wise. if you use dbilink, I think you do not even get in touch with the underlying implementation in perl. So if it isnt the academic approach you are seeking, you should just use it as it is. And it can be used quite similar to what you want above iirc. I guess you already read its documentation? ;-) Regards Tino ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] 8.1.4 - problem with PITR - .backup.done / backup.ready version of the same file at the same time.
Hello We had a strange problem yesterday in one of our servers using PITR (postgresql 8.1.4). The script used by archive_command is designed to refuse to overwrite any pre-existing archive file. Yesterday this situation happened and it refused to overwrite a file, filling the log file with this error: - LOG: archive command "archive_wal.sh -P pg_xlog/000100080010.0006D5E8.backup -F 000100080010.0006D5E8.backup" failed: return code 256 WARNING: transaction log file "000100080010.0006D5E8.backup" could not be archived: too many failures - The problem was that 000100080010.0006D5E8.backup was already archived, but under pg_xlog/archive_status/ there were two files: - 000100080010.0006D5E8.backup.done 000100080010.0006D5E8.backup.ready - so postgresql tryed to archive this file again after the first time. We deleted the *.backup.ready file and the problem was gone. This situation should not happen, anyone has seen this problem before? Ideas?, tips? to find why this happened. -- Rafael Martinez, <[EMAIL PROTECTED]> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] DB structure for logically similar objects in different
Roman Neuhauser wrote: # [EMAIL PROTECTED] / 2006-05-29 08:10:43 -0400: Roman Neuhauser wrote: # [EMAIL PROTECTED] / 2006-05-28 16:13:20 -0400: Basically we've got several different "states" that an item can be in. From what I've seen the way many places seem to deal with them is something along the lines of making bool values that act as switches... Ex: table items: item_id name description is_active is_sold_out is_banned Now we've started to see some problems with this sort of design. Namely we need to run sanity tests on every page that hits the items table to make sure is_active is true, is_sold_out is false, is_banned is false so on and so forth. I was thinking of splitting up states into different tables ala... table items_active: item_active_id name description table items_sold_out: item_sold_out_id name description would views help? CREATE VIEW items_to_sell AS SELECT item_id, name, description FROM items WHERE is_active = 1 AND is_sold_out = 0 AND is_banned = 0; Views work for querying the chunks of data that match different states, but if I was looking for information based on a single item_id wouldn't I still need the sanity checks? No. SELECT * FROM items_to_sell WHERE item_id = 123 will be transformed into something like SELECT item_id, name, description FROM items WHERE item_id = 123 AND is_active = 1 AND is_sold_out = 0 AND is_banned = 0 Hmmm that works too. So I guess my next question is which is a better designed system; one large table with bools and views or six small tables with stored procs to move data between tables? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pgcrypto sha256/384/512 don't work on Redhat. Please help!
Patch applied to CVS HEAD and 8.1.X. Thanks. --- Marko Kreen wrote: > On 5/9/06, Joe Kramer <[EMAIL PROTECTED]> wrote: > > On 5/9/06, Marko Kreen <[EMAIL PROTECTED]> wrote: > > > The fact that Fedora pgcrypto is linked with OpenSSL that does not > > > support SHA256 is not a bug, just a fact. > > > > It's not Fedora only, same problem with Gentoo/portage. > > I think it's problem for all distros. You need recompile pgcrypto or install > > openssl 0.9.8 which is considered as "unstable" by most distros. > > > > Maybe pgcrypto should use built-in algorithms until OpenSSL 0.9.8 is > > mainstream/default install. > > To be honest, pgcrypto actually falls back on built-in code for AES, > in case old OpenSSL that does not have AES. Thats because AES > should be "always there", together with md5/sha1/blowfish. > > I do not consider SHA2 that important (yet?), so they don't > get same treatment. > > > > OTOH, the nicest solution to your problem would be self-compiled > > > pgcrypto, that would work with stock PostgreSQL. As the conflict > > > happens with only (new) SHA2 functions, I can prepare a patch for > > > symbol conflict, would that be satisfactory for you? > > > > Ideally, would be great if pgcrypto could fallback to built-in algorithm of > > OpenSSL don't support it. > > But since it's compile switch, completely seld-compiled pgcrypto would be > > great. > > Attached is a patch that re-defines SHA2 symbols so that they would not > conflict with OpenSSL. > > Now that I think about it, if your OpenSSL does not contain SHA2, then > there should be no conflict. But ofcourse, if someone upgrades OpenSSL, > server starts crashing. So I think its best to always apply this patch. > > I think I'll send the patch to 8.2 later, not sure if it's important > enough for 8.1. > > -- > marko [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Compound words giving undesirable results with tsearch2
I've setup a database using tsearch2, configured with support for compound
words according to the excellent guide found here:
http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_compound_words
This works fine. There is however one drawback that I'd like to know
whether can be remedied. Let's say I want to search for records containing
the word 'fritekst', which is a compound Norwegian word meaning
'free text'.
testdb=# select to_tsquery('default_norwegian', 'fritekst');
to_tsquery
--
'fritekst' | 'fri' & 'tekst'
(1 row)
Now, this will indeed match those records, but it will also match any
records containing both of the words 'fri' and 'tekst', without regard
to whether they are next to each other or in completely different parts
of the text being indexed. In many situations, this will lead to a lot
of 'false' matches, seen from a user perspective.
Ideas on how to handle this problem will be much appreciated.
--
Lars Haugseth
"If anyone disagrees with anything I say, I am quite prepared not only to
retract it, but also to deny under oath that I ever said it." -Tom Lehrer
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Charset conversion error
It seems not. $ cat koi8.sh printf "SELECT '==\xb9=='"|PGCLIENTENCODING=WIN1251 psql -p 5435 koi8 $ psql -p 5435 -c 'select version()' koi8 version - PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 20031218 (Vine Linux 3.3.2-0vl8) (1 row) $ sh koi8.sh ?column? -- == == (1 row) As you can see PostgreSQL 8.1.3 converts 0xb9 in Windows-1251 to a space of KOI8. -- Tatsuo Ishii SRA OSS, Inc. Japan > Yes, character converted to a non-space-char. > And there is reverse error. It's happened when I try to extract information > from database. > SQL Error: ERROR: character 0x8bbf of encoding "MULE_INTERNAL" has no > equivalent in "WIN1251"" > > 2006/5/30, Tatsuo Ishii <[EMAIL PROTECTED]>: > > > > As far as I know the conversion table was not changed. Are you saying > > that Win-1251 0xb9 is converted to a non-space-char in the previous > > version? > > -- > > Tatsuo Ishii > > SRA OSS, Inc. Japan > > > > > Previous version converted such characters all right. And there wasn't > > any > > > bogus ASCII spaces. But I looked at the KOI8 charset table and found out > > > that there is not equivalent symbol (0xb9) in this table. > > > > > > 2006/5/30, Tatsuo Ishii <[EMAIL PROTECTED]>: > > > > > > > > > I think you are right. But everything was alright before I updated > > my > > > > > database. > > > > > > > > Previous version converted such characters to ASCII spaces. So > > > > probably you have lots of bogus spaces anyway. If you think it's ok, > > > > then you could your own CONVERSION which behaves similar to previous > > > > version. > > > > -- > > > > Tatsuo Ishii > > > > SRA OSS, Inc. Japan > > > > > > > > > So there are a lot of "incorrect" values in tables. And errors > > appear > > > > when I > > > > > execute "SELECT * FROM table". > > > > > > > > > > 2006/5/30, Tatsuo Ishii <[EMAIL PROTECTED]>: > > > > > > > > > > > > > Hi, I have an error after updating my database up to 8.1.4version. > > > > > > > "SQL Error: ERROR: character 0xb9 of encoding "WIN1251" has no > > > > > > equivalent > > > > > > > in "MULE_INTERNAL"'. " > > > > > > > My client program encoding is windows-1251 and database encoding > > is > > > > > > koi8. > > > > > > > What can I do to rectify the situation? > > > > > > > > > > > > It suggests that Windows-1251's 0xb9 cannot be converted to KOI8. > > You > > > > > > should check your Windows-1251 data. > > > > > > -- > > > > > > Tatsuo Ishii > > > > > > SRA OSS, Inc. Japan > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > > Verba volent, scripta manent > > > > > Dan Black > > > > > > > > > > > > > > > > -- > > > Verba volent, scripta manent > > > Dan Black > > > > > > -- > Verba volent, scripta manent > Dan Black ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Compound words giving undesirable results with tsearch2
On Tue, 30 May 2006, Lars Haugseth wrote:
I've setup a database using tsearch2, configured with support for compound
words according to the excellent guide found here:
http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_compound_words
This works fine. There is however one drawback that I'd like to know
whether can be remedied. Let's say I want to search for records containing
the word 'fritekst', which is a compound Norwegian word meaning
'free text'.
testdb=# select to_tsquery('default_norwegian', 'fritekst');
to_tsquery
--
'fritekst' | 'fri' & 'tekst'
(1 row)
Now, this will indeed match those records, but it will also match any
records containing both of the words 'fri' and 'tekst', without regard
to whether they are next to each other or in completely different parts
of the text being indexed. In many situations, this will lead to a lot
of 'false' matches, seen from a user perspective.
Ideas on how to handle this problem will be much appreciated.
this is where order by relevance should helps.
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] Lossy character conversion to Latin-1
I have a client that only supports Latin-1 and needs to connect to a UTF-8 database to retrieve some data. Some columns may contain characters that have no Latin-1 equivalent. I would like to convert these to a blank or perhaps some hex value. Is there any way to do this in PostgreSQL without using anything other than built in functions or pl/pgsql? It would be nice if the built in convert function had an option to handle this rather than only generating an error. Any pointers to an existing pl/pgsql function to perform this conversion? Thanks, John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Charset conversion error
You are right. I was in a harry when wrote my last message.I solved my problem by changing some source files. But It is not very good because I have to do it every time when I need to update my database. And I can't check all fields in all tables because it take a few days. 2006/5/30, Tatsuo Ishii <[EMAIL PROTECTED]>: It seems not.$ cat koi8.shprintf "SELECT '==\xb9=='"|PGCLIENTENCODING=WIN1251 psql -p 5435 koi8$ psql -p 5435 -c 'select version()' koi8 version - PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 20031218 (Vine Linux 3.3.2-0vl8) (1 row)$ sh koi8.sh ?column?-- == ==(1 row)As you can see PostgreSQL 8.1.3 converts 0xb9 in Windows-1251 to aspace of KOI8.--Tatsuo IshiiSRA OSS, Inc. Japan> Yes, character converted to a non-space-char. > And there is reverse error. It's happened when I try to extract information> from database.> SQL Error: ERROR: character 0x8bbf of encoding "MULE_INTERNAL" has no> equivalent in "WIN1251"" >> 2006/5/30, Tatsuo Ishii <[EMAIL PROTECTED]>:> >> > As far as I know the conversion table was not changed. Are you saying> > that Win-1251 0xb9 is converted to a non-space-char in the previous > > version?> > --> > Tatsuo Ishii> > SRA OSS, Inc. Japan> >> > > Previous version converted such characters all right. And there wasn't> > any> > > bogus ASCII spaces. But I looked at the KOI8 charset table and found out > > > that there is not equivalent symbol (0xb9) in this table.> > >> > > 2006/5/30, Tatsuo Ishii <[EMAIL PROTECTED]>:> > > > > > > > > I think you are right. But everything was alright before I updated> > my> > > > > database.> > > >> > > > Previous version converted such characters to ASCII spaces. So > > > > probably you have lots of bogus spaces anyway. If you think it's ok,> > > > then you could your own CONVERSION which behaves similar to previous> > > > version.> > > > -- > > > > Tatsuo Ishii> > > > SRA OSS, Inc. Japan> > > >> > > > > So there are a lot of "incorrect" values in tables. And errors> > appear > > > > when I> > > > > execute "SELECT * FROM table".> > > > >> > > > > 2006/5/30, Tatsuo Ishii <[EMAIL PROTECTED] >:> > > > > >> > > > > > > Hi, I have an error after updating my database up to 8.1.4version.> > > > > > > "SQL Error: ERROR: character 0xb9 of encoding "WIN1251" has no > > > > > > equivalent> > > > > > > in "MULE_INTERNAL"'. "> > > > > > > My client program encoding is windows-1251 and database encoding > > is> > > > > > koi8.> > > > > > > What can I do to rectify the situation?> > > > > >> > > > > > It suggests that Windows-1251's 0xb9 cannot be converted to KOI8. > > You> > > > > > should check your Windows-1251 data.> > > > > > --> > > > > > Tatsuo Ishii> > > > > > SRA OSS, Inc. Japan > > > > > >> > > > >> > > > >> > > > >> > > > > --> > > > > Verba volent, scripta manent> > > > > Dan Black > > > >> > >> > >> > >> > > --> > > Verba volent, scripta manent> > > Dan Black> > -- > Verba volent, scripta manent> Dan Black-- Verba volent, scripta manentDan Black
Re: [GENERAL] UTF-8 context of BYTEA datatype??
Did you try escaping the data:
my $rc=$sth->bind_param(1, escape_bytea($imgdata), { pg_type =>
DBD::Pg::PG_BYTEA });
Susan
Rafal Pietrak
<[EMAIL PROTECTED]To:
pgsql-general@postgresql.org
om>cc:
Sent by: Subject: Re: [GENERAL]
UTF-8 context of BYTEA datatype??
|---|
[EMAIL PROTECTED] | [ ] Expand Groups |
tgresql.org |---|
05/29/2006 06:10
AM
On Mon, 2006-05-29 at 14:01 +0200, Martijn van Oosterhout wrote:
> >
> > How come the bytearea is *interpreted* as having encoding?
>
> Actually, it's not the bytea type that is being interpreted, it's the
> string you're sending to the server that is. Before you send bytea data
> in a query string, you have to bytea encode it first. The DBD::Pg
> manpage seems to suggest something like:
>
> $rv = $sth->bind_param($param_num, $bind_value,
> { pg_type => DBD::Pg::PG_BYTEA });
>
Hmmm, despite initial euphoria, this doesn't actually work.
Subsequently I've also tried putting SQL_BINARY in place of that
hash-ref, and plain DBD::Pg::PG_BYTEA, and also I tried to use 'TYPE =>'
instead of pg_type. (All those hints in man DBI). None of that worked
either.
But I also did:
$db->do('SET client_encoding = LATIN1') or die "SET";
just after connect and before prepare, and this produced a slightly
different result no ERROR, but the image was cut short to 9-bytes
inside the database data-row.
Would perl have interpreted this command according to it's semantics?
And change it's own default string handling accordingly!?
Not knowing the internals, I wouldn't bet on whichever, but I have my
doughts - my quess is thet DBI driver doesn't go that far. So if it
hasn't interpretted the 'SET client_encodding' internally, but just
passed that to database, the only thing that changed is the database
frontend context.
So may be the original error came from the database itself anyway?
Any ideas? (still hopping I wont have to write a C-level interface
function just to test what's really happening :)
--
-R
---(end of broadcast)---
TIP 6: explain analyze is your friend
--
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at
http://www.overlandstorage.com
--
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] Seeing locks (was Status of gist locking in 8.1.3)
Chris writes: SELECT * from pg_locks ; And this is per DB right? No, this is per system. On a DB doing no/little work I always see two records returned. One has a value in the 'database' column. How can I find what database it is? Looking for it in pg_database did not yield any databases with a matching number. The number in the 'database' column did not match any number of the columns in pg_database. The two records I always see are: locktype|| mode | granted ---++-+- transactionid || ExclusiveLock | t relation || AccessShareLock | t ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Seeing locks (was Status of gist locking in 8.1.3)
Francisco Reyes <[EMAIL PROTECTED]> writes: > The two records I always see are: >locktype|| mode | granted > ---++-+- > transactionid || ExclusiveLock | t > relation || AccessShareLock | t That would be your own transaction's lock on its own XID, and its share lock on the pg_locks view. Joining to pg_database.oid and pg_class.oid will help you interpret the numbers --- see http://www.postgresql.org/docs/8.1/static/view-pg-locks.html regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Which RPM for RH Linux ES 4? PGDB or RH?
Hi, I have to install PGSQL 7.4.13 under RH Linux Entreprise Server 4, and I can apparently use either the rpm "postgresql--7.4.13-1PGDG.i686.rpm" from the Postgresql Development Group, or use the rpm "postgresql--7.4.13-2.RHEL4.1.i386" from Redhat itself. What is the best to do? Thanks, -- Philippe Lang, Ing. Dipl. EPFL Attik System rte de la Fonderie 2 1700 Fribourg Switzerland http://www.attiksystem.ch Tel: +41 (26) 422 13 75 Fax: +41 (26) 422 13 76 smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Which RPM for RH Linux ES 4? PGDB or RH?
On Tuesday 30 May 2006 09:39, "Philippe Lang" <[EMAIL PROTECTED]> wrote: > Hi, > > I have to install PGSQL 7.4.13 under RH Linux Entreprise Server 4, and I > can apparently use either the rpm "postgresql--7.4.13-1PGDG.i686.rpm" > from the Postgresql Development Group, or use the rpm > "postgresql--7.4.13-2.RHEL4.1.i386" from Redhat itself. > > What is the best to do? > The best thing, if you have the choice, would be to download the 8.1.4 RPMs from postgresql.org and run those. Or run the 8.1.4 RPMs from the CentOS 4 testing repo, which include a compatibility library from an older version, that some other RHEL 4 binaries depend on. If you have to run 7.4, though, you might as well run the ones Red Hat ships. I doubt they support any other config. -- In a world where the citizens of most developed nations have half their incomes wrested from them by the state, how widely supported do you think the idea of "freedom" really is? - Russ Nelson ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] UTF-8 context of BYTEA datatype??
On Tue, 2006-05-30 at 09:05 -0700, [EMAIL PROTECTED] wrote:
> Did you try escaping the data:
> my $rc=$sth->bind_param(1, escape_bytea($imgdata), { pg_type =>
> DBD::Pg::PG_BYTEA });
No. But:
$ ./test
Undefined subroutine &main::escape_bytea called at ./test line 34.
Where can I find one?
$ grep -Rl escape_bytea /usr/share/perl* /usr/lib/perl*
... returns nothing. Neither is listed among:
psql>\df
output.
Where should I look for it?
--
-R
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] [HACKERS] Schema Limitations ?
Moving to -general, where this belongs. On Sat, May 27, 2006 at 11:13:58PM -0500, Chris Broussard wrote: > Hello Hackers, > > I have the following questions, after reading this FAQ (http:// > www.postgresql.org/docs/faqs.FAQ.html#item4.4) are there statistics > around the max number of schemas in a database, max number of tables > In a schema, and max number of tables in a database (number that > spans schemas) ? Are the only limitations based on disk & ram/swap ? One hard limit you'll run into is OIDs, which max at either 2^31 or 2^32 (I can't remember offhand which it is). That would be number of schemas, and number of total tables (there's a unique index on pg_class.oid). Actually, you'll be limited to 2 or 4 billion tables, indexes, and views. In reality, I suspect you'll become very unhappy with performance well before those numbers. Running a database with just 1 tables can be a bit tricky, though it's certainly doable. > Does anybody have a rough ballpark figures of the largest install > base on those questions? > > I'm curious about these stats, because I'm debating on how best to > break up data, between schemas, physical separate databases, and the > combination of the two. > > Thanks In Advanced. > > Chris > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] UTF-8 context of BYTEA datatype??
Sorry, forgot:
sub escape_bytea {
my ($instring)[EMAIL PROTECTED];
my $returnstring=join ('',map {
my $tmp=ord($_);
($tmp >= 32 and $tmp <= 126 and $tmp != 92) ? $_ :
sprintf('\%03o',$tmp);} split (//,$instring));
return $returnstring;
} # end sub escape_bytea
Rafal Pietrak
<[EMAIL PROTECTED]To: [EMAIL PROTECTED],
pgsql-general@postgresql.org
om>cc:
Sent by: Subject: Re: [GENERAL]
UTF-8 context of BYTEA datatype??
|---|
[EMAIL PROTECTED] | [ ] Expand Groups |
tgresql.org |---|
05/30/2006 10:06
AM
On Tue, 2006-05-30 at 09:05 -0700, [EMAIL PROTECTED] wrote:
> Did you try escaping the data:
> my $rc=$sth->bind_param(1, escape_bytea($imgdata), { pg_type =>
> DBD::Pg::PG_BYTEA });
No. But:
$ ./test
Undefined subroutine &main::escape_bytea called at ./test line 34.
Where can I find one?
$ grep -Rl escape_bytea /usr/share/perl* /usr/lib/perl*
... returns nothing. Neither is listed among:
psql>\df
output.
Where should I look for it?
--
-R
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at
http://www.overlandstorage.com
--
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] UTF-8 context of BYTEA datatype??
Rafal Pietrak wrote:
> On Mon, 2006-05-29 at 14:01 +0200, Martijn van Oosterhout wrote:
> > >
> > > How come the bytearea is *interpreted* as having encoding?
> >
> > Actually, it's not the bytea type that is being interpreted, it's the
> > string you're sending to the server that is. Before you send bytea data
> > in a query string, you have to bytea encode it first. The DBD::Pg
> > manpage seems to suggest something like:
> >
> > $rv = $sth->bind_param($param_num, $bind_value,
> > { pg_type => DBD::Pg::PG_BYTEA });
> >
> Hmmm, despite initial euphoria, this doesn't actually work.
Just an idea: make sure DBD::Pg::PG_BYTEA is defined.
If not, you're just lacking a "use DBD::Pg;" and the result
you describe is to be expected.
Otherwise, you could use the DBI_TRACE environment variable to learn
what the db driver is issuing to the database at the libpq level.
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Compound words giving undesirable results with tsearch2
testdb=# select to_tsquery('default_norwegian', 'fritekst');
to_tsquery
--
'fritekst' | 'fri' & 'tekst'
(1 row)
Now, this will indeed match those records, but it will also match any
records containing both of the words 'fri' and 'tekst', without regard
to whether they are next to each other or in completely different parts
of the text being indexed. In many situations, this will lead to a lot
of 'false' matches, seen from a user perspective.
It's a special feature (piece from mail from our norwegian customer)
Let us take the compound 'fotballbane'. (Soccer field)
Split : 'fotball' 'fot' 'ball' 'bane'
Example record : "Vedlikehold av baner for fotballklubber"
(Literal translation : "Maintenance of fields for soccer clubs")
The search for 'fotballbane' ('fotballbane' & 'fotball' & 'fot' &
'ball') will not match, even though the record is precisely about this
sort of thing. 'fotballbane' | ('fotball' & 'bane') | ('fot' & 'ball' &
'bane') will match.
So, all variants to split compound words are joined with OR, words in one
variant are joined with AND.
If thats isn't desirable you can forbid word split for ispell (just comment z
flag) or use for searching different configuration of tsearch.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] DB structure for logically similar objects in different
On May 30, 2006, at 5:48 AM, Eci Souji wrote: Hmmm that works too. So I guess my next question is which is a better designed system; one large table with bools and views or six small tables with stored procs to move data between tables? That depends entirely on your access patterns and how your data is broken down. Moving data between tables will be more involved from a code standpoint, and thus more prone to errors. On other databases it would also be less efficient, but because of how PostgreSQL does MVCC I don't think it would make too much of a difference performance-wise. You also need to consider the breakdown of your data. If you've got one set of conditions that are very prevalent, you can see some storage (and hence, speed) gains by splitting into different tables, perhaps by having one table for the common case and another one that handles all the uncommon cases. For example, if you have a users table, if you have a very large number of users it will probably help to have a seperate user_lockout table that contains only the user_id of users that are denied access to the system. The downside is that you have to do a join every time you want to check that. The upside is that you're saving as much as 4 bytes in the user table, which depending on how many users you have and your access patterns can add up. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] UTF-8 context of BYTEA datatype??
On Tue, 2006-05-30 at 20:12 +0200, Daniel Verite wrote: > Rafal Pietrak wrote: > > Hmmm, despite initial euphoria, this doesn't actually work. > > Just an idea: make sure DBD::Pg::PG_BYTEA is defined. > If not, you're just lacking a "use DBD::Pg;" and the result :) This time it's a hit. Thenx! Now, this is probably not exactly the furum to discuss that, but: 1. I did quite a few scripts with DBI, not only for Postgesql in fact - scripts worked flowlessly between Oracle/Sybase and the old DBASE files, too. And I have never fell into a problem of missing the an include for a particular driver - simple "use DBI;" did all the magic. 2. I admitt, that I should have spotted myself, that the DBD::Pg::PG_BYTEA might not have been recognized without the use clausure, but the driver itself understands prity much of the underlaying datatypes - I fon't need to bind explicitly for SQL_DATE or SQL_INTEGER. Why should I care more for binary objects? So may be the pgtype_bytea should also be recognised? May be current driver behavior should be regarded as a BUG? Does anyone know if this behavior is in the driver for a reason? -- -R ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Best open source tool for database design / ERDs?
postgresql_autodoc and dia. On 28 May 2006 05:19:04 -0700, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: What open source tool do people here like for creating ER diagrams? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Restoring databases from a different installment on Windows
I have recently reinstalled my Windows mychine, including the PostgreSQL server, but (due to a system crash, unrelated to Postgres) I wasn't able to dump my databases to import them now. However, I have a full copy of the original system, including all the files of the original Postgres installation. Is there a way to restore the databases from the original installation into the new one? For example, in MySQL I would be able to just copy the data files; is there something similar in Postgres? Thanks, Berislav ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] out parameter and setof record
In PostgreSql 8.1 i've tried to use Out parameter but when I've changed function (added Out parameter) CREATE OR REPLACE FUNCTION reffunc3(FROM_ID in integer,COUNT_ROW OUT integer) RETURNS SETOF Person AS ' DECLARE rec RECORD; BEGIN SELECT count(*) INTO COUNT_ROW FROM Person WHERE ssn>FROM_ID ; FOR rec IN SELECT ssn,name FROM Person WHERE ssn>FROM_ID LOOP c;RETURN NEXT rec ; END LOOP ; RETURN ; END; ' LANGUAGE plpgsql; --SELECT * from reffunc3(1) ; i've seen : ERROR: function result type must be integer because of OUT parameters Of course when I change parameter to Integer it must be Record error appears. What should I change, and how to call this function? Mirek ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Restoring databases from a different installment on Windows
Well, I did try that, but PostgreSQL service refused to start. :( Berislav ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Best open source tool for database design / ERDs?
Thanks. What about DIA - http://www.gnome.org/projects/dia/ ...or DB Designer - http://fabforce.net/dbdesigner4/ (this one claims to be feature-equivalent, or in the sphere of, products like Oracle's Designer, ERWin, and Rational Rose. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Problem V8.1.4 - providing pwd for commandline tools doesn't work anymore
The latest version (V8.1.4) breaks the setup process of our application's installation wizard. We used to call "createdb.exe" and piped the password for the postgres user (which has been entered by the user in our setup wizard's dialogs) into it. With version V8.1.4 this is not possible anymore, the commandline process prompts for the password nevertheless! This causes our setup to "hang" (of course it doesn't hang, it waits for the invisible commandline process to return, which waits for a user input that will never complete as the user cannot and shall not see these commandline tools). Any suggestion? (V8.1.3 and earlier did work fine in this respect.) Thank you in advance and sorry if this is a (new) V8.1.4 FAQ, I didn't found anything in this respect. Best Regards, Alexander. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] UTF-8 context of BYTEA datatype??
On Tue, May 30, 2006 at 10:26:31PM +0200, Rafal Pietrak wrote: > Now, this is probably not exactly the furum to discuss that, but: > 1. I did quite a few scripts with DBI, not only for Postgesql in fact - > scripts worked flowlessly between Oracle/Sybase and the old DBASE files, > too. And I have never fell into a problem of missing the an include for > a particular driver - simple "use DBI;" did all the magic. > 2. I admitt, that I should have spotted myself, that the > DBD::Pg::PG_BYTEA might not have been recognized without the use > clausure, but the driver itself understands prity much of the > underlaying datatypes - I fon't need to bind explicitly for SQL_DATE or > SQL_INTEGER. Why should I care more for binary objects? Well actually, the driver doesn't understand any datatypes at all, that's the problem. What's happening is that to send the query to the server, the driver has to load all your paramters into the query string and send it. And the server has to decode it all before it's even looked at the string so it has no idea it's a bytea. That's why bytea need special encoding to get around this check. However, there is a solution: send the paramters seperate from the query. In fact, postgres has been able to do that for a while now but not all interfaces have been made to use it. My guess is that those other databases you've used were already doing this so didn't see the issue. I don't know if DBD:Pg does this though, maybe it needs to be triggered somehow. Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Restoring databases from a different installment on Windows
On Sun, May 28, 2006 at 07:24:56 -0700, Berislav Lopac <[EMAIL PROTECTED]> wrote: > I have recently reinstalled my Windows mychine, including the > PostgreSQL server, but (due to a system crash, unrelated to Postgres) I > wasn't able to dump my databases to import them now. However, I have a > full copy of the original system, including all the files of the > original Postgres installation. > > Is there a way to restore the databases from the original installation > into the new one? For example, in MySQL I would be able to just copy > the data files; is there something similar in Postgres? Yes it should work. There should be a recovery if postgres was running when the boc crashed. Note, you don't want to copy files out from under a running server to do backups. That won't work. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Best open source tool for database design / ERDs?
[EMAIL PROTECTED] wrote on 29.05.2006 13:06: Thanks. What about DIA - http://www.gnome.org/projects/dia/ ...or DB Designer - http://fabforce.net/dbdesigner4/ (this one claims to be feature-equivalent, or in the sphere of, products like Oracle's Designer, ERWin, and Rational Rose. This is not maintained any longer (unfortunately because I really like it). And the support for non-MySQL databases is nearly non-existing. As the actual model is saved in XML I wrote an XSLT task to convert the DbDesigner to an Oracle SQL script (CREATE TABLE). It shouldn't be that hard to adjust it for Postgres. If anyone is interested I can post it (the Oracle version) here. Regards Thomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] SCSI disk: still the way to go?
Hi guys, I have to update a Linux box with PostgreSQL on it, essentially for data warehousing purposes. I had set it up about 3 years ago and at that time the best solution I had been recommended was to use SCSI disks with hardware RAID controllers. Is this still the way to go or things have recently changed? Any other suggestion/advice? What about SAN? Thanks.Cheers,Riccardo
Re: [GENERAL] SCSI disk: still the way to go?
On Tue, 2006-05-30 at 16:28, Riccardo Inverni wrote: > Hi guys, > >I have to update a Linux box with PostgreSQL on it, essentially for > data warehousing purposes. I had set it up about 3 years ago and at > that time the best solution I had been recommended was to use SCSI > disks with hardware RAID controllers. > >Is this still the way to go or things have recently changed? Any > other suggestion/advice? What about SAN? Actually, modern SATA server drives are now considered competitive with the proper RAID controller. Nowadays most people seem to recommend the Areca controllers. I haven't used them myself, but would be happy to test them some day. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SCSI disk: still the way to go?
SAS and SATA will give you the best throughput for your array total. U320 is limited to 320MB/channel.AlexOn 5/30/06, Scott Marlowe < [EMAIL PROTECTED]> wrote:On Tue, 2006-05-30 at 16:28, Riccardo Inverni wrote: > Hi guys,>>I have to update a Linux box with PostgreSQL on it, essentially for> data warehousing purposes. I had set it up about 3 years ago and at> that time the best solution I had been recommended was to use SCSI > disks with hardware RAID controllers.>>Is this still the way to go or things have recently changed? Any> other suggestion/advice? What about SAN?Actually, modern SATA server drives are now considered competitive with the proper RAID controller.Nowadays most people seem to recommend the Areca controllers. I haven'tused them myself, but would be happy to test them some day.---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SCSI disk: still the way to go?
How much money do you want to spend? If you don't care, SAN is probably the way to go. How much data do you have to store? If you can afford to fit it onto scsi, scsi probably is still the way to go. Otherwise, sata arrays have come a long way in 3 years, and they are by FAR the cheapest solution out there. Do some research and see if they're good enough for you. On Tue, 30 May 2006, Riccardo Inverni wrote: Hi guys, I have to update a Linux box with PostgreSQL on it, essentially for data warehousing purposes. I had set it up about 3 years ago and at that time the best solution I had been recommended was to use SCSI disks with hardware RAID controllers. Is this still the way to go or things have recently changed? Any other suggestion/advice? What about SAN? Thanks. Cheers, Riccardo ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] SCSI disk: still the way to go?
Scott Marlowe wrote: On Tue, 2006-05-30 at 16:28, Riccardo Inverni wrote: Hi guys, I have to update a Linux box with PostgreSQL on it, essentially for data warehousing purposes. I had set it up about 3 years ago and at that time the best solution I had been recommended was to use SCSI disks with hardware RAID controllers. Is this still the way to go or things have recently changed? Any other suggestion/advice? What about SAN? Actually, modern SATA server drives are now considered competitive with the proper RAID controller. And for a DW application they are the most megabyte per dollar you can by. Nowadays most people seem to recommend the Areca controllers. I haven't used them myself, but would be happy to test them some day. I have heard good things about the Areca, but I have never used them. I have had excellent luck with the LSI controllers however. Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [HACKERS] Schema Limitations ?
Thanks Jim for the interesting information. in theory what Is the best method (clustering software, or regular postgresql configuration ?) to spread/partition schemas between physical machines within a single database? Is it even possible?? I have been using postgres for many years, and the vanilla type install / configuration has always suited my development & production needs... currently, i have separate databases that i can obviously scale by having different database servers, and i have j2ee application servers that sits in front of postgres to manage/synchronize the relationships between the databases. I'm thinking I can possibly gain efficiencies and simplify the application logic by collapsing the data into one database, and sharing the sharable data through a "shareable" schema, and each deployed application into it's own schema... how are other people scaling out ? just wondering what other people think is the best approach ? thanks, Chris On May 30, 2006, at 1:04 PM, Jim C. Nasby wrote: Moving to -general, where this belongs. On Sat, May 27, 2006 at 11:13:58PM -0500, Chris Broussard wrote: Hello Hackers, I have the following questions, after reading this FAQ (http:// www.postgresql.org/docs/faqs.FAQ.html#item4.4) are there statistics around the max number of schemas in a database, max number of tables In a schema, and max number of tables in a database (number that spans schemas) ? Are the only limitations based on disk & ram/swap ? One hard limit you'll run into is OIDs, which max at either 2^31 or 2^32 (I can't remember offhand which it is). That would be number of schemas, and number of total tables (there's a unique index on pg_class.oid). Actually, you'll be limited to 2 or 4 billion tables, indexes, and views. In reality, I suspect you'll become very unhappy with performance well before those numbers. Running a database with just 1 tables can be a bit tricky, though it's certainly doable. Does anybody have a rough ballpark figures of the largest install base on those questions? I'm curious about these stats, because I'm debating on how best to break up data, between schemas, physical separate databases, and the combination of the two. Thanks In Advanced. Chris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Which RPM for RH Linux ES 4? PGDB or RH?
"Philippe Lang" <[EMAIL PROTECTED]> writes: > I have to install PGSQL 7.4.13 under RH Linux Entreprise Server 4, and I can > apparently use either the rpm "postgresql--7.4.13-1PGDG.i686.rpm" from > the Postgresql Development Group, or use the rpm > "postgresql--7.4.13-2.RHEL4.1.i386" from Redhat itself. > What is the best to do? They are the same thing to within measurement error ;-). Or at least, if you find an important difference, feel free to tell off Devrim or me respectively. But I agree with Alan's point: if you are running RHEL at all, it's probably because you want Red Hat support, and Red Hat won't support RPMs not built by Red Hat. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SCSI disk: still the way to go?
Compare these two drives:http://www.storagereview.com/php/benchmark/suite_v4.php?typeID=10&testbedID=4&osID=6&raidconfigID=1&numDrives=1&devID_0=279&devID_1=308&devCnt=2 Prices:http://www.cdw.com/shop/products/default.aspx?EDC=984588 - SAS - ~$950 http://www.cdw.com/shop/products/default.aspx?EDC=912784 SATA - ~$320For a third of the price you can have 90% of the throughput performance, which is probably where you will be most stressing your drives in a data warehouse. I have only seen good benchmarks from LSI's MegaRAID controllers for SCSI in linux, I have seen good results from LSI, 3Ware (now AMCC) and Areca in Linux for their SATA products (in RAID 10). There are plenty of large drive number chasis out there with SATA hot swap bays if you want them. Tyan makes a great dual CPU board with two independant PCI-X buses. that will give 1066MB/sec total through put each which I have great benchmark number from. it's possible to reach these numbers with SAN, but it will cost major major $$s. Each FC line in a SAN is typically 2Gb last time I checked, so you need multiple channels to acheive a max of 1066MB/sec throughput per PCI-X bus. If you run the numbers, you theoretically need 24 drives in a RAID 10 to get max throughput (Areca makes a 24 channel SATA card: http://www.newegg.com/Product/Product.asp?Item=N82E16816151004 - Although I couldn't find one with multilane support). I have seen chassis that can hold 40 drives. If you go for the 74Gig cousin that has similar throughput, which you can get OEM for $160/each you are talking just about $6400 in drives, plus about $4k for the chasis ( http://rackmountmart.stores.yahoo.net/rm8uracchasw.html), plus about $5k for other components (depending on RAM/CPU), so a massively kick ass whitebox can be had for about $16k that will acheive close to the maximum theoretical throughput acheivable in a single server for MB/sec. Now there are arguments to be had about splitting up table spaces etc, but I present this as a concrete example of components that can be had for not alot of money to build a majorly kick ass server using SATA technology. AlexOn 5/30/06, Ben <[EMAIL PROTECTED]> wrote: How much money do you want to spend? If you don't care, SAN is probably the wayto go.How much data do you have to store? If you can afford to fit it onto scsi, scsiprobably is still the way to go.Otherwise, sata arrays have come a long way in 3 years, and they are by FAR the cheapest solution out there. Do some research and see if they're good enough foryou.On Tue, 30 May 2006, Riccardo Inverni wrote:> Hi guys,>> I have to update a Linux box with PostgreSQL on it, essentially for data > warehousing purposes. I had set it up about 3 years ago and at that time the> best solution I had been recommended was to use SCSI disks with hardware> RAID controllers.>> Is this still the way to go or things have recently changed? Any other > suggestion/advice? What about SAN?>> Thanks.>> Cheers,> Riccardo>---(end of broadcast)---TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] out parameter and setof record
am 30.05.2006, um 3:21:27 -0700 mailte [EMAIL PROTECTED] folgendes: > In PostgreSql 8.1 i've tried to use Out parameter but when I've changed > function (added Out parameter) > ... > > ERROR: function result type must be integer because of OUT parameters > > > Of course when I change parameter to Integer it must be Record error > appears. A good explanation for IN/OUT-Parameters can you find there: http://people.planetpostgresql.org/xzilla/index.php?/archives/149-out-parameter-sql-plpgsql-examples.html#extended HTH Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] How to link database A in server X to database B in server Y?
hi all, At the first, please let me say sorry for my poor english. I have two postgresql database(A and B) running in different machines(X and Y). I want to visit tables of B from A as if they are tables of A, could you tell me how can I do? For example, table t1 belongs to database A, which is located in server X; table t2 belongs to database B, which is located in server Y. What I want is : do the below query in database A: select t1.field1,t2.field2 from t1,t2 where.. Is it possible? If you have any idea, please let me know. Thanks in advance. Best regards, Long _ 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Which RPM for RH Linux ES 4? PGDB or RH?
Hi, I only run RHEL because of the "progress" database, running on this server. Technical support for progress is only available when servers are running RHEL, so that's why we are running this OS. But if I had the choice, I would have installed freebsd there... Alan, you are right, it's time maybe to try migrating from Postgresql 7 to 8. I'll try that... Thanks Philippe -Message d'origine- De : Tom Lane [mailto:[EMAIL PROTECTED] Envoyé : mercredi, 31. mai 2006 05:42 À : Philippe Lang Cc : pgsql-general@postgresql.org Objet : Re: [GENERAL] Which RPM for RH Linux ES 4? PGDB or RH? "Philippe Lang" <[EMAIL PROTECTED]> writes: > I have to install PGSQL 7.4.13 under RH Linux Entreprise Server 4, and > I can apparently use either the rpm > "postgresql--7.4.13-1PGDG.i686.rpm" from the Postgresql > Development Group, or use the rpm "postgresql--7.4.13-2.RHEL4.1.i386" from Redhat itself. > What is the best to do? They are the same thing to within measurement error ;-). Or at least, if you find an important difference, feel free to tell off Devrim or me respectively. But I agree with Alan's point: if you are running RHEL at all, it's probably because you want Red Hat support, and Red Hat won't support RPMs not built by Red Hat. regards, tom lane smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] How to link database A in server X to database B in server Y?
am 31.05.2006, um 5:30:45 + mailte liu long folgendes: > hi all, > > At the first, please let me say sorry for my poor english. > > I have two postgresql database(A and B) running in different machines(X and > Y). I want to visit tables of B from A as if they are tables of A, could > you tell me how can I do? dblink. 07:57 < akretschmer> ??dblink 07:57 < rtfm_please> For information about dblink 07:57 < rtfm_please> see http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/dblink/ 07:57 < rtfm_please> or http://pgfoundry.org/projects/dblink-tds/ HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Compound words giving undesirable results with tsearch2
* oleg@sai.msu.su (Oleg Bartunov) wrote:
|
| On Tue, 30 May 2006, Lars Haugseth wrote:
|
| > I've setup a database using tsearch2, configured with support for compound
| > words according to the excellent guide found here:
| >
| > http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_compound_words
| >
| > This works fine. There is however one drawback that I'd like to know
| > whether can be remedied. Let's say I want to search for records containing
| > the word 'fritekst', which is a compound Norwegian word meaning
| > 'free text'.
| >
| > testdb=# select to_tsquery('default_norwegian', 'fritekst');
| > to_tsquery
| > --
| > 'fritekst' | 'fri' & 'tekst'
| > (1 row)
| >
| > Now, this will indeed match those records, but it will also match any
| > records containing both of the words 'fri' and 'tekst', without regard
| > to whether they are next to each other or in completely different parts
| > of the text being indexed. In many situations, this will lead to a lot
| > of 'false' matches, seen from a user perspective.
| >
| > Ideas on how to handle this problem will be much appreciated.
|
| this is where order by relevance should helps.
Thank you for pointing me to this, I hadn't thought about that.
However, my first try with the rank_cd() function does not quite
produce the results I had expected:
SELECT set_curcfg('default_norwegian');
SELECT id, rank_cd(n, mytscol, to_tsquery('fritekst')) AS rank
FROM mytable
WHERE mytscol @@ to_tsquery('fritekst')
ORDER BY rank DESC;
No matter what value I use for n here, a record where the compound word
'fritekst' appears gets a rank of 0, where as records where the words
'fri' and 'tekst' appears separately all gets a rank > 0, the closer
together, the higher the rank.
If I try to set the value of n to 0, I still get a rank of 0 for a
record containing 'fritekst', and 1 for all records containing 'fri'
and 'tekst'.
When using the rank() function instead of rank_cd() in the query above,
records with the word 'fritekst' seem to score better, but I still get
higher ranks for some records containing the separate words and not the
compound word.
--
Lars Haugseth
"If anyone disagrees with anything I say, I am quite prepared not only to
retract it, but also to deny under oath that I ever said it." -Tom Lehrer
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
