[GENERAL] Q about NAS mounting things
Long and rambling question, here -- we have a customer with a database (Bthat is outgrowing their original storage, and they want to upgrade (Bstorage as cheaply and smoothly as possible. (B (BPresently, they have one 120G or so RAID 5 unit, and the entire system (Band data are stored on that drive. (B (BWe've talked about cloning the current drive to a larger capacity RAID (Band swapping the larger one in. We've also talked about hanging a second (B(external) RAID 5 unit on the machine and just copying the data over, (Bleaving the OS, the system, and the software components on the original (Bdrive. These solutions are perceived by the customer as too expensive or (Bsomething. (B (BNow we are considering NAS, mounted over NFS. (So far they have only (Bbeen able to find a Terabyte class cheap NAS running MSWindows, but I (Bthink we're going to look harder for Linux NAS.) (B (BI've suggested running postmaster on the NAS, but we don't seem to want (Bto dig into the code. (B (BOne of our group wants to copy both data and postgresql to the NAS, (Brunning postmaster on the server from the binary saved on the NAS. I (Bthink this sounds too fragile. Specifically, if the NAS goes off line (Bwhile postmaster is running, it will tend to go looking for code and die (Bthe horrible, won't it? (B (BSo I have recommended data only on the NAS, modifying the startup and (Bshutdown scripts to check the presence of the NAS before attempting to (Bmount, and adding a watchdog to check every second whether the NAS is (Bstill mounted. I'm also expecting we'll be able to set postgreql to fire (Boff an e-mail if it suddenly can't find the files because the NFS (Bmounted NAS has disappeared for some reason. (B (BOpinions, anyone? (B (B-- (BJoel Rees <[EMAIL PROTECTED]> (Bdigitcom, inc. $B3t<02qhttp://www.ddcom.co.jp> ** (B (B (B---(end of broadcast)--- (BTIP 5: Have you checked our extensive FAQ? (B (B http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Problems importing Unicode
[EMAIL PROTECTED] wrote: Well, they were generated by MySQL and I can open them with e.g. the Windows Editor Notepad. But I don't know if they are actually encoded in UNICODE. Since I can open the file with Notepad and read the statements, I assume, it is not UNICODE. They look just like in the email below. Probably some WINxxx encoding. I've seen something similar with data from MS-Access. The problem are apparently those characters Å or ô and I really would like to know how to import those files into PostgreSQL 8.0.0 Is there a switch I can use to do a codepage / encoding translation? Why are MS Access or even MySQL able to read those files without trouble but PostgreSQL reports an error? Because they're using the same WIN locale details. What you might want to try is to set your client encoding at the top of the batch file and see if PostgreSQL can't convert it for you. SET CLIENT_ENCODING = WIN1250; There's a list of encodings PG can convert for you in the manual (see the chapter "Automatic Character Set Conversion Between Server and Client" in the Localization section. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Fwd: Problem installing Postgresql on MDK10.0
I think you have to install ncurses. On Mdk 10 its libncurses5-devel I think. Try that and see what happens. Regards, Ben ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Transactions in different DB
On Tue, 16 Nov 2004 11:22:27 +0100, Stefano Farina <[EMAIL PROTECTED]> wrote: > > Hi all > I have to develop an embedded database using memory FLASH and memory RAM > Some tables must be saved in RAM and some tables in FLASH ( RAM and FLASH > are two different mounted directories in the file system). > I saw it's possible to save different databases in different directories > with CREATE DATABASE options. > > It's possible to save only some tables in different directories?? Beginning with PostgreSQL 8.0, yes - this provides tablespaces. > If I want to work with two different databases, it's possible to do a > single transaction on two different databases ?? Not AFAIK, though contrib/dblink might be able to help you. HTH Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Fwd: Problem installing Postgresql on MDK10.0
Hi, I have the same problem and I solved it. This question then was asked and I answer it. Try looking at the google and you will find it. In general - Open the package manager and searche for keyword "redline". The neccessary library will apper and you just will have to install it. Regards, Kaloyan Alejandro D. Burne wrote: Sorry, but no reply on [EMAIL PROTECTED] -- Forwarded message -- From: Alejandro D. Burne <[EMAIL PROTECTED]> Date: Mon, 15 Nov 2004 15:00:58 +0100 Subject: Problem installing Postgresql on MDK10.0 To: [EMAIL PROTECTED] I'll be trying to install postgres 8.0b1/b4; when I make the config a message appear: "configure: error: readline library not found" $ rpm -qa | grep readline libreadline4-4.3-7mdk libreadline4-devel-4.3-7mdk Then I try; $ ./configure --with-libraries=/lib --with-includes=/usr/include/readline (where the libs are) I read a message from Tom Lane talking about termcap, it's installed too. The same problem occurs with zlib. I fix it skipping both libs with ./configure --without-readline --without-zlib but... Alejandro ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] PostgreSQL certification
Hello everyone! I have a question.. Does PostgreSQL have official certification? If yes, where can I find certification courses to get a certificate? Konstantin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Lost databases
Look at the documentation here: http://www.postgresql.org/docs/7.4/interactive/maintenance.html The upshot is that you should vacuum every table in the database at least once every billion transaction. It doesn't have to be a VACUUM FULL, just run VACUUM without specifying a table. There's some info in there about how to work out when it is necessary. Hope this helps, On Tue, Nov 16, 2004 at 12:22:46PM +0200, Kostis Mentzelos wrote: > Only VACUUM FULL solves XID wraparound problem? I mean VACUUM FULL locks > the entire table. > > How can I test if I am close to XID wraparround? > > Kostis > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpMv6HqYv5fF.pgp Description: PGP signature
Re: [GENERAL] Controlling maximal on-disk size of a table
What you're looking for sounds like and extended (destructive) version of what autovacuum does. So you might try to look at the statistics tables like autovacuum does. I don't know how it does that, but it seems that that way you'd be able to incrementally have approximate information about what happens with the table. Good luck, d. -- David Helgason, Business Development et al., Over the Edge I/S (http://otee.dk) Direct line +45 2620 0663 Main line +45 3264 5049 On 16. nov 2004, at 13:21, Nils Rennebarth wrote: I have a table that is essentially a log where new entries are streaming in continually and from time to time I throw old entries away to keep the table from growing. I understand that in addition to issue a DELETE FROM log WHERE date < xxx I also need to issue a VACUUM log so that new entries will use the space of deleted entries. Now I want to reserve a certain amount of disk storage to hold the log table. So I first let the table (plus its index, its toast table and toast index) grow until it is about to reach the maximum size. Then a daemon continually deletes old entries and vacuums the table so the on-disk usage stays more or less constant from now on, at least this is the idea. Of course I would like to keep as much history as possible, given the available space. Also the log may sometimes be quiet and sometimes quite busy, also the size of the text entries may vary quite a bit. Now to make a good guess about when to issue the next delete, I need to estimate how much of the on-disk usage is accounted for by deleted entries. I can of course count the number of entries, estimate the bytes needed for storage by averaging the length of the text column, adding the size of the fixed columns and compare that to the on-disk size to conclude how much space is still available. As for the index I assume it is has a fixed size per row. But these queries are expensive because the log may easily contain millions of entries with an on disk size in the range of a few GB, and must be repeated quite often to prevent sudden bursts of new entries from overflowing the log. Is there a better way to get at the current "free space" inside of a table/index? __ Mit WEB.DE FreePhone mit hoechster Qualitaet ab 0 Ct./Min. weltweit telefonieren! http://freephone.web.de/?mc=021201 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Problems importing Unicode
> Well, they were generated by MySQL and I can open them with > e.g. the Windows Editor Notepad. But I don't know if they are > actually encoded in UNICODE. > Since I can open the file with Notepad and read the > statements, I assume, it is not UNICODE. They look just like > in the email below. Windows Notepad handles Unicode just fine, both UTF-16 (labeled Unicode in notepad) and UTF-8 (labeled UTF-8). To test, open the file in Notepad, then do "File->Save As". The "Encoding" dropdown box will default to whatever Notepad detected when it opened the file. If it's UTF-16 and you need UTF-8, just change the encoding and save under a different name. //Magnus ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] OID's
Just to clarify, there is a difference between OIDs and XIDs. Object IDs (OID) are a system assigned field to every row that eventually wraps around. If you don't use them in your application you'll hever really have a problem. The only exception is that statements that modify structures in the database (CREATE TRIGGER/TABLE/INDEX/etc) may fail if you're unlucky enough to try them and it happens to be exactly the OID of an existing thing of that type. Most people don't create 4 billion rows in their database so it's not an issue. People who do are recommended to create their high churn tables WITHOUT OIDS so they don't get used as fast. As an added bonus, in recent versions you can actually save diskspace by not having them. Transaction IDs (XID) are a different story, they track transactions and what is visible and what isn't. Transaction wraparound means that rows will disappear when their transaction ID (which was considered in the past) is now in the future. Since 7.2 this problem is avoided by doing a database wide VACUUM (not necessarily FULL) at least once every billion transactions. This is not an onerous requirement so people don't run into this anymore. Before 7.2 you'd simply find your data missing one morning as the only way to reset the XID was with an initdb. If you're still running a busy database on something older than that, you *really* need to consider taking appropriate measures! 7.2 is already fairly old now and all of the major database destroying issues from then are now fixed. Hope this helps, On Tue, Nov 16, 2004 at 01:06:57PM +0200, Kostis Mentzelos wrote: > I have read about oid wraparound in many messages but I don't understand > when it happens and when it is dangerus for my tables. > > It affects developers that uses OIDS in their queryies? > What about database and tables (not total or total) disappearences? > > Kostis. > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpYDtH8fkXLx.pgp Description: PGP signature
Re: [GENERAL] OID's
Helps me. Thanks for the clairification. On Wednesday 17 November 2004 06:49 am, Martijn van Oosterhout saith: > Just to clarify, there is a difference between OIDs and XIDs. Object > IDs (OID) are a system assigned field to every row that eventually > wraps around. If you don't use them in your application you'll hever > really have a problem. The only exception is that statements that > modify structures in the database (CREATE TRIGGER/TABLE/INDEX/etc) may > fail if you're unlucky enough to try them and it happens to be exactly > the OID of an existing thing of that type. > > Most people don't create 4 billion rows in their database so it's not > an issue. People who do are recommended to create their high churn > tables WITHOUT OIDS so they don't get used as fast. As an added bonus, > in recent versions you can actually save diskspace by not having them. > > Transaction IDs (XID) are a different story, they track transactions > and what is visible and what isn't. Transaction wraparound means that > rows will disappear when their transaction ID (which was considered in > the past) is now in the future. Since 7.2 this problem is avoided by > doing a database wide VACUUM (not necessarily FULL) at least once every > billion transactions. This is not an onerous requirement so people > don't run into this anymore. > > Before 7.2 you'd simply find your data missing one morning as the only > way to reset the XID was with an initdb. If you're still running a busy > database on something older than that, you *really* need to consider > taking appropriate measures! 7.2 is already fairly old now and all of > the major database destroying issues from then are now fixed. > > Hope this helps, > > On Tue, Nov 16, 2004 at 01:06:57PM +0200, Kostis Mentzelos wrote: > > I have read about oid wraparound in many messages but I don't understand > > when it happens and when it is dangerus for my tables. > > > > It affects developers that uses OIDS in their queryies? > > What about database and tables (not total or total) disappearences? > > > > Kostis. > > > > ---(end of broadcast)--- > > TIP 9: the planner will ignore your desire to choose an index scan if > > your joining column's datatypes do not match -- Quote: 28 "Without question, we need to disarm Saddam Hussein. He is a brutal, murderous dictator, leading an oppressive regime . . . He presents a particularly grievous threat because he is so consistently prone to miscalculation. . . . And now he is miscalculating America's response to his continued deceit and his consistent grasp for weapons of mass destruction. . . . So the threat of Saddam Hussein with weapons of mass destruction is real. . . ." -- Sen. John F. Kerry (D, MA), Jan. 23. 2003 Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Fwd: Problem installing Postgresql on MDK10.0
Thanks Ben, libncures5 was installed but libncurses5-devel don't. Problem solved! I must find zlib deps now ;+) On Wed, 17 Nov 2004 09:20:00 +, Ben Trewern <[EMAIL PROTECTED]> wrote: > I think you have to install ncurses. On Mdk 10 its libncurses5-devel I > think. > > Try that and see what happens. > > Regards, > > Ben > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Transactions in different DB
Hi, Am Dienstag, den 16.11.2004, 11:22 +0100 schrieb Stefano Farina: > Hi all > I have to develop an embedded database using memory FLASH and memory > RAM > Some tables must be saved in RAM and some tables in FLASH ( RAM and > FLASH are two different mounted directories in the file system). > I saw it's possible to save different databases in different > directories with CREATE DATABASE options. > > It's possible to save only some tables in different directories?? With 8.0 its possible. With pre 8.0 it would involve manual work (linking) and you are restricted when you recreate tables and indices. > If I want to work with two different databases, it's possible to do a > single transaction on two different databases ?? No. You might be able to simulate it with dblink, but generally no. Regards Tino ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] error querying temp table in plpgsql function
(PostgreSQL 8.0 beta 4 on Mac OS X 10.3.6)
I'm working on a function which creates and populates a temporary
table, then returns the number of records it has inserted. I'm getting
an error, though, after successive invocations of the function (I can
call it once successfully, but on the next call I get an error). I've
been able to reproduce the error with the following sample function:
create or replace function test() returns integer as
$$
declare result integer;
begin
-- drop temp table, if it exists (ignore exception if it doesn't)
begin
execute 'drop table test';
exception
when undefined_table then
null; -- do nothing
end;
-- create the vehicle route table
execute 'create temp table test ('
|| 'seq_num serial not null, '
|| 'foo text'
|| ')';
select count(*) into result from test;
return result;
end;
$$
language 'plpgsql';
Here is a clipping of a psql session which creates the function and
calls it twice, along with the error that results:
silo=# \i test.sql
CREATE FUNCTION
silo=# select test();
NOTICE: CREATE TABLE will create implicit sequence "test_seq_num_seq"
for serial column "test.seq_num"
CONTEXT: SQL statement "create temp table test (seq_num serial not
null, foo text)"
PL/pgSQL function "test" line 13 at execute statement
test
--
0
(1 row)
silo=# select test();
NOTICE: CREATE TABLE will create implicit sequence "test_seq_num_seq"
for serial column "test.seq_num"
CONTEXT: SQL statement "create temp table test (seq_num serial not
null, foo text)"
PL/pgSQL function "test" line 13 at execute statement
ERROR: relation with OID 524907 does not exist
CONTEXT: SQL statement "SELECT count(*) from test"
PL/pgSQL function "test" line 18 at select into variables
silo=#
If, instead of executing the "select count(*)" directly, I use the
EXECUTE command, then everything works. Is this expected behavior?
Thanks,
Tim
---(end of broadcast)---
TIP 3: 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] error querying temp table in plpgsql function
Timothy Perrigo wrote: (PostgreSQL 8.0 beta 4 on Mac OS X 10.3.6) I'm working on a function which creates and populates a temporary table, then returns the number of records it has inserted. I'm getting an error, though, after successive invocations of the function (I can call it once successfully, but on the next call I get an error). I've been able to reproduce the error with the following sample function: select count(*) into result from test; If, instead of executing the "select count(*)" directly, I use the EXECUTE command, then everything works. Is this expected behavior? Yes - the reference to table "test" will be compiled down to it's OID. When you re-create the test table it will have a new OID and so you get an error. As you say, you need to use EXECUTE in cases like this with plpgsql. An interpreted language (pltcl/plperl) should be OK in situations like this, though at the cost of parsing the query each time the function is called. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] TSearch2: Problems with compound words and stop words
sorry for the late answer, i was on holyday,
see my remarks below
Oleg Bartunov wrote:
On Fri, 5 Nov 2004, Timo Haberkern wrote:
Oleg,
i use TSearch2 with PostgreSQL 7.4.6 and i applied the compoundword
patch yesterday. The configuration changed a little bit but the
result is the same. I get no compound words. I'm using the locale
de_DE with encoding ISO8859-1 for the database.
I think i spell is working correctly except the compound words. If i try
SELECT lexize('de_ispell', 'springt')
i get
lexize
{springen,springen}
which seems correct.
But a SELECT lexize('de_ispell', 'Autobahn')
results in
lexize
{autobahn}
i would expect {auto,bahn, autobahn}
Hmm, have you checked 'Autobahn' in ispell dictionary ? Does
dictionary you used supports 'Z' flag for compound words ?
Autobahn is in the ispell dictionary. What does a ispell dictionary
need to support the Z flag???
Timo
The new configuration after the compound word patch:
Seems you overestimate my capabilities :)
Actions dict_name
dict_init
dict_initoption
dict_lexize
dict_comment
Edit
Delete
simple dex_init(text) /NULL/
dex_lexize(internal,internal,integer) Simple example of dictionary.
Edit
Delete
en_stem snb_en_init(text)
/usr/local/pgsql/share/contrib/english.stop
snb_lexize(internal,internal,integer) English Stemmer. Snowball.
Edit
Delete
ru_stem snb_ru_init(text)
/usr/local/pgsql/share/contrib/russian.stop
snb_lexize(internal,internal,integer) Russian Stemmer. Snowball.
Edit
[GENERAL] Knowing when a row was last updated
Hi all, Is it possible to know when a row was last updated. Using 7.3.3. rgds Antony Paul ---(end of broadcast)--- TIP 3: 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] Knowing when a row was last updated
On Wed, Nov 17, 2004 at 07:43:31PM +0530, Antony Paul wrote: > Is it possible to know when a row was last updated. Using 7.3.3. Add a TIMESTAMP or TIMESTAMP WITH TIME ZONE column to the table and use a trigger to update it. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] TSearch2: Problems with compound words and stop words
On Wed, 17 Nov 2004, Timo Haberkern wrote:
sorry for the late answer, i was on holyday,
see my remarks below
Oleg Bartunov wrote:
On Fri, 5 Nov 2004, Timo Haberkern wrote:
Oleg,
i use TSearch2 with PostgreSQL 7.4.6 and i applied the compoundword patch
yesterday. The configuration changed a little bit but the result is the
same. I get no compound words. I'm using the locale de_DE with encoding
ISO8859-1 for the database.
I think i spell is working correctly except the compound words. If i try
SELECT lexize('de_ispell', 'springt')
i get
lexize
{springen,springen}
which seems correct.
But a SELECT lexize('de_ispell', 'Autobahn')
results in
lexize
{autobahn}
i would expect {auto,bahn, autobahn}
Hmm, have you checked 'Autobahn' in ispell dictionary ? Does dictionary you
used supports 'Z' flag for compound words ?
Autobahn is in the ispell dictionary. What does a ispell dictionary need to
support the Z flag???
Try ispell -C Autobahn
search 'compound' in 'man ispell' for details.
the problem exists only if ispell *does* splits word correctly while tsearch2
doesn't. You should find correct ispell dictionary for german or create it
yourself. You may consult monzilla.net
http://staff.science.uva.nl/~christof/monzilla/research/project-dr.html
Timo
The new configuration after the compound word patch:
Seems you overestimate my capabilities :)
Actions dict_name
dict_init
dict_initoption
dict_lexize
dict_comment
Edit
Delete
simple dex_init(text) /NULL/
dex_lexize(internal,internal,integer) Simple example of dictionary.
Edit
Delete
en_stem snb_en_init(text) /usr/local/pgsql/share/contrib/english.stop
snb_lexize(internal,internal,integer) English Stemmer. Snowball.
Edit
Delete
ru_stem snb_ru_init(text)
Re: [GENERAL] Certifications in military environment
--- Marco Bizzarri <[EMAIL PROTECTED]> wrote: > Hi all. > > I would like to know if postgresql has any > certification for the > military environment. There are no official certifications, nor are there likely to be. But certifications may be offered by individual companies, like Red Had does with Linux. Here is one which might be of interest: http://groups.google.com/groups?hl=en&lr=&selm=20041014.104826.128620349.t-ishii%40sra.co.jp > > If possible, please answer me directly (I'm not on > this mailing list). > Otherwise, I will read from the web interface. > > Regards > Marco > > ---(end of > broadcast)--- > TIP 8: explain analyze is your friend > __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com ---(end of broadcast)--- TIP 3: 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] PostgreSQL certification
--- Konstantin Danilov <[EMAIL PROTECTED]> wrote: > Hello everyone! > I have a question.. Does PostgreSQL have official > certification? If yes, where can I find > certification courses to get a certificate? There are no official certifications. Anyone who wants to can offer their own certifications, like Red Hat does with Linux. Here is a link that may be of interest: http://groups.google.com/groups?hl=en&lr=&selm=20041014.104826.128620349.t-ishii%40sra.co.jp > Konstantin > > ---(end of > broadcast)--- > TIP 8: explain analyze is your friend > __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Tsearch2 and Unicode?
I'm trying to use tsearch2 with database which is in 'UNICODE' encoding.
It works fine for English text, but as I intend to search Polish texts I did:
insert into pg_ts_cfg('default_polish', 'default', 'pl_PL.UTF-8');
(and I updated other pg_ts_* tables as written in manual).
However, Polish-specific chars are being eaten alive, it seems.
I.e. doing select to_tsvector('default_polish', body) from messages;
results in list of words but with national chars stripped...
I wonder, am I doing something wrong, or just tsearch2 doesn't grok
Unicode, despite the locales setting? This also is a good question
regarding ispell_dict and its feelings regarding Unicode, but that's
another story.
Assuming Unicode unsupported means I should perhaps... oh, convert
the data to iso8859 prior feeding it to_tsvector()... interesting idea,
but so far I have failed to actually do it. Maybe store the data as
'bytea' and add a column with encoding information (assuming I don't
want to recreate whole database with new encoding, and that I want
to use unicode for some columns (so I don't have to keep encoding
with every text everywhere...).
And while we are at it, how do you feel -- an extra column with tsvector
and its index -- would it be OK to keep it away from my data (so I can
safely get rid of them if need be)?
[ I intend to keep index of around 2 000 000 records, few KBs of
text each ]...
Regards,
Dawid Kuroczko
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Rules WHERE condition
I have a view that used union all to merge three tables together. I was hoping to create a rule, one for each table using the WHERE condition of the rule to determine which table gets updated. Is this possible? This is what I have, assume the view here is a merge of three tables using union all: CREATE RULE "update_xrf" AS ON UPDATE TO "public"."viewdeterioratedlbp" WHERE ((new.note)::text = 'Unit'::text) DO INSTEAD (UPDATE tblxrf SET deterioration = new.deterioration;); The note column contains a value that can trigger which table needs to be updated. I would like to make one of these rules for each table to update. But when I run the update, it says I have to have an unconditional rule, is that right? Any suggestions? ohc=# update viewdeterioratedlbp set note = 'Unit', deterioration = 'test' where xrf_id = 733; ERROR: cannot update a view HINT: You need an unconditional ON UPDATE DO INSTEAD rule. Thanks, -- Robert ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] TSearch2: Problems with compound words and stop words
Timo,
take a look into .aff file and search 'compoundwords'.
german ispell file I got from http://j3e.de/ispell/igerman98/ has no
support for compound words: 'compoundwords off'
Norwegian, for example, has:
compoundwords controlled z
compoundmin 4
Oleg
On Wed, 17 Nov 2004, Oleg Bartunov wrote:
On Wed, 17 Nov 2004, Timo Haberkern wrote:
sorry for the late answer, i was on holyday,
see my remarks below
Oleg Bartunov wrote:
On Fri, 5 Nov 2004, Timo Haberkern wrote:
Oleg,
i use TSearch2 with PostgreSQL 7.4.6 and i applied the compoundword patch
yesterday. The configuration changed a little bit but the result is the
same. I get no compound words. I'm using the locale de_DE with encoding
ISO8859-1 for the database.
I think i spell is working correctly except the compound words. If i try
SELECT lexize('de_ispell', 'springt')
i get
lexize
{springen,springen}
which seems correct.
But a SELECT lexize('de_ispell', 'Autobahn')
results in
lexize
{autobahn}
i would expect {auto,bahn, autobahn}
Hmm, have you checked 'Autobahn' in ispell dictionary ? Does dictionary
you used supports 'Z' flag for compound words ?
Autobahn is in the ispell dictionary. What does a ispell dictionary need
to support the Z flag???
Try ispell -C Autobahn search 'compound' in 'man ispell' for details. the
problem exists only if ispell *does* splits word correctly while tsearch2
doesn't. You should find correct ispell dictionary for german or create it
yourself. You may consult monzilla.net
http://staff.science.uva.nl/~christof/monzilla/research/project-dr.html
Timo
The new configuration after the compound word patch:
Seems you overestimate my capabilities :)
Actions dict_name
dict_init
dict_initoption
dict_lexize
dict_comment
Edit
Delete
simple dex_init(text) /NULL/
dex_lexize(internal,internal,integer) Simple example of dictionary.
Edit
Delete
en_stem snb_en_init(text) /usr/local/pgsql/share/contrib/english.stop
snb_lexize(internal,internal,integer) English Stemmer. Snowball.
Edit
Delete
Re: [GENERAL] Tsearch2 and Unicode?
Dawid,
unfortunately, tsearch2 doesn't support unicode yet.
If you keep tsvector separately from data than you'll need one more join.
Oleg
On Wed, 17 Nov 2004, Dawid Kuroczko wrote:
I'm trying to use tsearch2 with database which is in 'UNICODE' encoding.
It works fine for English text, but as I intend to search Polish texts I did:
insert into pg_ts_cfg('default_polish', 'default', 'pl_PL.UTF-8');
(and I updated other pg_ts_* tables as written in manual).
However, Polish-specific chars are being eaten alive, it seems.
I.e. doing select to_tsvector('default_polish', body) from messages;
results in list of words but with national chars stripped...
I wonder, am I doing something wrong, or just tsearch2 doesn't grok
Unicode, despite the locales setting? This also is a good question
regarding ispell_dict and its feelings regarding Unicode, but that's
another story.
Assuming Unicode unsupported means I should perhaps... oh, convert
the data to iso8859 prior feeding it to_tsvector()... interesting idea,
but so far I have failed to actually do it. Maybe store the data as
'bytea' and add a column with encoding information (assuming I don't
want to recreate whole database with new encoding, and that I want
to use unicode for some columns (so I don't have to keep encoding
with every text everywhere...).
And while we are at it, how do you feel -- an extra column with tsvector
and its index -- would it be OK to keep it away from my data (so I can
safely get rid of them if need be)?
[ I intend to keep index of around 2 000 000 records, few KBs of
text each ]...
Regards,
Dawid Kuroczko
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Rules WHERE condition
On Wed, Nov 17, 2004 at 11:20:41AM -0500, Robert Fitzpatrick wrote: > I have a view that used union all to merge three tables together. I was > hoping to create a rule, one for each table using the WHERE condition of > the rule to determine which table gets updated. Is this possible? See the CREATE RULE documentation: http://www.postgresql.org/docs/7.4/static/sql-createrule.html In particular, read the last paragraph of the Description section, the one that begins, "There is a catch if you try to use conditional rules for view updates" -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Rules WHERE condition
On Wed, 2004-11-17 at 11:49, Michael Fuhr wrote: > On Wed, Nov 17, 2004 at 11:20:41AM -0500, Robert Fitzpatrick wrote: > > > I have a view that used union all to merge three tables together. I was > > hoping to create a rule, one for each table using the WHERE condition of > > the rule to determine which table gets updated. Is this possible? > > See the CREATE RULE documentation: > > http://www.postgresql.org/docs/7.4/static/sql-createrule.html > Thanks, that explains a lot, but still not able to get my rule to work, this is what I have now: CREATE RULE "update_unconditional" AS ON UPDATE TO "public"."viewdeterioratedlbp" DO INSTEAD NOTHING; CREATE RULE "update_xrf" AS ON UPDATE TO "public"."viewdeterioratedlbp" WHERE ((new.note)::text = 'Unit'::text) DO (UPDATE tblxrf SET deterioration = new.deterioration WHERE (tblxrf.xrf_id = new.xrf_id);); ohc=# update viewdeterioratedlbp set deterioration = 'test' where xrf_id = 143; UPDATE 0 This is the first rule I have tried to setup, I read through the doc, but don't seem to be able to catch what I'm doing wrong. Do I have to update all fields for it to work? -- Robert ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Rules WHERE condition
On Wed, 2004-11-17 at 12:49, Robert Fitzpatrick wrote: > On Wed, 2004-11-17 at 11:49, Michael Fuhr wrote: > > On Wed, Nov 17, 2004 at 11:20:41AM -0500, Robert Fitzpatrick wrote: > > > > > I have a view that used union all to merge three tables together. I was > > > hoping to create a rule, one for each table using the WHERE condition of > > > the rule to determine which table gets updated. Is this possible? > > > > See the CREATE RULE documentation: > > > > http://www.postgresql.org/docs/7.4/static/sql-createrule.html > > > > Thanks, that explains a lot, but still not able to get my rule to work, > this is what I have now: > Forget that last post, it is working even though the UPDATE 0 is returned. The record did update :) Please someone let me know if you see any issues with that? Like I said, my first rule let alone with a WHERE condition. -- Robert ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] User-defined types
Hello, I'm trying to write a PL/PgSQL function whose sole parameter is an array whose element type is a type that I've created. For example: CREATE TYPE test_type AS (x bigint, y bigint); CREATE OR REPLACE FUNCTION array_test (test_type[]) ... According to the 7.4 docs, "Whenever a user-defined base data type is created, PostgreSQL automatically creates an associated array type, whose name consists of the base type's name prepended with an underscore. The parser understands this naming convention, and translates requests for columns of type foo[] into requests for type _foo. The implicitly-created array type is variable length and uses the built-in input and output functions array_in and array_out." However, mydb=# SELECT COUNT(*) FROM pg_type WHERE typname = '_test_type'; count --- 0 (1 row) Am I missing something? Thanks for your time. I'm running PostgreSQL 7.4.5. -- Jonathan Daugherty Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] User-defined types
Jonathan Daugherty <[EMAIL PROTECTED]> writes: > I'm trying to write a PL/PgSQL function whose sole parameter is an > array whose element type is a type that I've created. For example: > According to the 7.4 docs, > "Whenever a user-defined base data type is created, PostgreSQL > automatically creates an associated array type, whose name consists > of the base type's name prepended with an underscore. The key word in that sentence is "base" data type --- ie, not composite. We don't currently support arrays of composite types. (Nor arrays of domain types, either, IIRC.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] User-defined types
# > "Whenever a user-defined base data type is created, PostgreSQL # > automatically creates an associated array type, whose name consists # > of the base type's name prepended with an underscore. # # The key word in that sentence is "base" data type --- ie, not # composite. Ah, I see -- it wasn't obvious to me. # We don't currently support arrays of composite types. (Nor arrays # of domain types, either, IIRC.) Ok, thanks for your time. -- Jonathan Daugherty Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] PROPOSAL: Usenet Discussion Repost
This was posted in news.groups, but it was not posted to the list. REQUEST FOR DISCUSSION (RFD) unmoderated group comp.databases.postgresql.admin unmoderated group comp.databases.postgresql.general unmoderated group comp.databases.postgresql.hackers unmoderated group comp.databases.postgresql.novice unmoderated group comp.databases.postgresql.sql This is a formal Request For Discussion (RFD) for the creation of the following worldwide unmoderated Usenet newsgroups: comp.databases.postgresql.admin, comp.databases.postgresql.general, comp.databases.postgresql.hackers, comp.databases.postgresql.novice, comp.databases.postgresql.sql. This is not a Call for Votes (CFV); you cannot vote at this time. Procedural details are below. CHANGES: The changes from the previous RFD (for comp.databases.postgresql.general) are the inclusion of the following groups to the RFD: unmoderated group comp.databases.postgresql.admin unmoderated group comp.databases.postgresql.hackers unmoderated group comp.databases.postgresql.novice unmoderated group comp.databases.postgresql.sql The charter has been changed for the comp.databases.postgresql.general group to the "official" one located at www.postgresql.org. Charters are added for the admin, hackers, novice and sql groups. The charters are the official ones located at www.postgresql.org RATIONALE: all groups To make the listed postgresql groups proper members of the big eight hierarchy. Currently, they are considered "bogus". A successful vote for this will result in the named PostgreSQL groups being available on most well-managed usenet servers. This will also end potential confusion to new users as to why their well managed news provider does not carry the groups. This will also raise the profile of PostgreSQL by having it listed among the other Relational Database Management Systems in the offical big eight comp.databases.* hierarchy. It will also provide compliance with the usenet rule that those under the big eight hierarchy have gone through RFD and CFV. CHARTER: comp.databases.postgresql.admin This is a general discussion area for users pertaining to the administration, compilation and installation of PostgreSQL. END CHARTER. CHARTER: comp.databases.postgresql.general This is a general discussion area for users. Do not include any SQL related, administration, installation, or compilation questions in this group. There are other groups that can accommodate those needs. END CHARTER. CHARTER: comp.databases.postgresql.hackers The PostgreSQL developer's team lives here. This is for the discussion of current development issues, problems and bugs and the discussion of proposed new features. If people in the other groups don't know the answer to a question and it is likely that only a developer will know the answer, you may re-post that questionhere. You must try elsewhere first! END CHARTER. CHARTER: comp.databases.postgresql.novice This group is for beginners in using the PostgreSQL database system. It serves to supplement the other groups, and help answer basic questions. END CHARTER. CHARTER: comp.databases.postgresql.sql This is a discussion area for users on SQL related matters. END CHARTER. PROCEDURE: This is a request for discussion, not a call for votes. In this phase of the process, any potential problems with the proposed newsgroups should be raised and resolved. The discussion period will continue for a minimum of 21 days (starting from when the first RFD for this proposal is posted to news.announce.newgroups), after which a Call For Votes (CFV) may be posted by a neutral vote taker if the discussion warrants it. Please do not attempt to vote until this happens. All discussion of this proposal should be posted to news.groups. This RFD attempts to comply fully with the Usenet newsgroup creation guidelines outlined in "How to Create a New Usenet Newsgroup" and "How to Format and Submit a New Group Proposal." Please refer to these documents (available in news.announce.newgroups) if you have any questions about the process. DISTRIBUTION: This RFD has been posted to the following newsgroups: news.announce.newgroups, news.groups Additionally, pointers will be posted to the affected groups via another provider: comp.databases.postgresql.admin comp.databases.postgresql.general comp.databases.postgresql.hackers comp.databases.postgresql.novice comp.databases.postgresql.sql Proponent: Mike Cox <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] How to know a record has been updated, then reset the flag?
Hi All... I'm been fighting this problem for a few days now, and it seems like it should be simple. But the solution has eluded me so far... I need to flag a record when it is updated or when it is a new insert. Then I SELECT for the changed records and do something not related to Postgres. Easy enough, I created a trigger procedure and fired it on INSERT OR UPDATE and modify NEW to set the flag field to true. But then the problem is how do I reset the trigger? If I do an UPDATE the trigger fires again. I thought I could check for the flag field being NULL and that works for an INSERT, but apparently if it is an update NEW contains the existing value of the field. I am trying to avoid modifying the cost the needs to set the flags (I can change the schema), but I have full control over the code that has to reset them. Is there a way I can update a record without firing the trigger, or by bypassing it? This is a multi-user environment, so I can't really drop the trigger and readd it. Is there a solution not related to this? I would appreciate some help, thanks very much! ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How to know a record has been updated, then reset the flag?
On Nov 18, 2004, at 2:53 PM, Jim Archer wrote: This is a multi-user environment, so I can't really drop the trigger and readd it. Would it work to drop and readd the trigger within a transaction? Would that make it multi-user safe? I can't think of another way to bypass an update trigger. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] How to know a record has been updated, then reset the
--On Thursday, November 18, 2004 3:15 PM +0900 Michael Glaesemann <[EMAIL PROTECTED]> wrote: Would it work to drop and readd the trigger within a transaction? Would that make it multi-user safe? I can't think of another way to bypass an update trigger. I had that idea too, and I have no idea. :-( ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
