[GENERAL] tsearch2 questions
1. What is the advantage of the tsearch2() trigger? Why can't I write my own trigger which does approximately: UPDATE manuscript set manuscript_vector = setweight(to_tsvector(manuscript_genre), 'A') || setweight(to_tsvector(manuscript_title), 'B') || to_tsvector(manuscript_abstract); 2. Is there a way to know in advance the maximum return value of the rank function? I have lots of other information to include in the goodness-of-match score besides the fulltext match rank so I would prefer a tsearch2 rank score between 0 and 1. Do I need to write my own rank function? -- Make April 15 just another day, visit http://fairtax.org ---(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] How do I aggregate data from multiple rows into a delimited list?
Berend Tober wrote: D. Dante Lorenso wrote: I want to select several rows of data and have them returned in a single record with the rows joined by a delimiter. Review the User Comments at http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html; for some ideas. I found a better solution using ARRAY_TO_STRING and ARRAY. My question email was originally blocked because I sent it to the list from the wrong email address. Unfortunately it was later unfrozen and sent on to the list (sorry) because I did more searching and had solved the problem on my own: http://archives.postgresql.org/pgsql-general/2007-07/msg00075.php Thanks for all your help, though! -- Dante ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] tsearch2 questions
On Wed, 4 Jul 2007, Joshua N Pritikin wrote: 1. What is the advantage of the tsearch2() trigger? Why can't I write my own trigger which does approximately: no advantage, it's just an example. UPDATE manuscript set manuscript_vector = setweight(to_tsvector(manuscript_genre), 'A') || setweight(to_tsvector(manuscript_title), 'B') || to_tsvector(manuscript_abstract); 2. Is there a way to know in advance the maximum return value of the rank function? I have lots of other information to include in the goodness-of-match score besides the fulltext match rank so I would prefer a tsearch2 rank score between 0 and 1. Do I need to write my own rank function? what's about simple normalization formulae, like rank/(rank+1) ? Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Design Tool
On 03.07.2007 21:43, Gabriele wrote: Free or not so costly license. If i use postgresql is also to save money, as you might expect. A one hundred dollars software might be my solution, a one thousand dollars is probably not. Casestudio.. or Toad Data Modeler, as it is named these days, is about the only one. -- Regards, Hannes Dorbath ---(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] Reasonable way to backup cluster Windows
On 03.07.2007 17:18, Andrus wrote: Which is reasonable way to backup cluster in Windows in compressed format ? Is the windows pg_dump different from the *nix one? If not, what is the problem with -Fc? -- Regards, Hannes Dorbath ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Stored Procedure: Copy table from; path = text variable
Richard Huxton wrote: Dave Page wrote: Richard Huxton wrote: Charles Pare wrote: Wow, it works great Thank's for the quick answer 12 minutes? I've seen bug-patches turned around quicker than that by Tom ;-) Yeah, that's really quite disappointing Richard - you need to pull your socks up :-) Would have been under the 10, but it took me 2 minutes to make love to my wife. I'm getting faster all the time though :-) OK, now thats a response I wasn't expecting - and a mental image I could have done without! /D ---(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] tsearch2 questions
On Wed, Jul 04, 2007 at 10:59:46AM +0400, Oleg Bartunov wrote: On Wed, 4 Jul 2007, Joshua N Pritikin wrote: 1. What is the advantage of the tsearch2() trigger? Why can't I write my own trigger which does approximately: no advantage, it's just an example. Please mention that in the documentation: tsearch2() trigger used to automatically update vector_column_name, my_filter_name is the function name to preprocess text_column_name. There are can be many functions and text columns specified in tsearch2() trigger. The following rule used: function applied to all subsequent text columns until next function occurs. Example, function dropatsymbol replaces all entries of @ sign by space. tsearch2() is an example. You are welcome to write your own trigger. 2. Is there a way to know in advance the maximum return value of the rank function? I have lots of other information to include in the goodness-of-match score besides the fulltext match rank so I would prefer a tsearch2 rank score between 0 and 1. Do I need to write my own rank function? what's about simple normalization formulae, like rank/(rank+1) ? I think you are suggesting that I use the best rank as the denominator for the rank column. Yes, I suppose that will work. Thanks. -- Make April 15 just another day, visit http://fairtax.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] tsearch2 questions
On 7/4/07, Joshua N Pritikin [EMAIL PROTECTED] wrote: Please mention that in the documentation: dont you think this is perfeclty clear? If you want to do something specific with columns, you may write your very own trigger function using plpgsql or other procedural languages (but not SQL, unfortunately) and use it instead of tsearch2 trigger. what's about simple normalization formulae, like rank/(rank+1) ? I think you are suggesting that I use the best rank as the denominator for the rank column. Yes, I suppose that will work. actually oleg supposed not to use best rank, but just use the formula as given - rank/(rank+1) to get rank in range of 0 to 1. depesz
Re: [GENERAL] Date for a week day of a month
On 7/3/07, Emi Lu [EMAIL PROTECTED] wrote:
Can I know how to get the date of each month's last Thursday please?
Query: select getDateBaseOnWeekday('2007-04-01', 'Last Thursday');
Result: 2007-04-26
you can easily do it without functions.
for example, this select:
SELECT
cast(d.date + i * '1 day'::interval as date)
FROM
(select '2007-04-01'::date as date) d,
generate_series(0, 30) i
WHERE
to_char(d.date, 'MM') = to_char( cast(d.date + i * '1 day'::interval as
date), 'MM')
AND to_char(cast(d.date + i * '1 day'::interval as date), 'D') = '5'
ORDER BY 1 DESC
LIMIT 1
;
does what you need.
to get last-thursday for another month, just change: (select
'2007-04-01'::date as date) d, to be 1st of any other month.
depesz
Re: [GENERAL] tsearch2 questions
On Wed, Jul 04, 2007 at 10:40:11AM +0200, hubert depesz lubaczewski wrote: On 7/4/07, Joshua N Pritikin [EMAIL PROTECTED] wrote: Please mention that in the documentation: dont you think this is perfeclty clear? If you want to do something specific with columns, you may write your very own trigger function using plpgsql or other procedural languages (but not SQL, unfortunately) and use it instead of tsearch2 trigger. From where are you quoting? I was quoting from: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-ref.html what's about simple normalization formulae, like rank/(rank+1) ? I think you are suggesting that I use the best rank as the denominator for the rank column. Yes, I suppose that will work. actually oleg supposed not to use best rank, but just use the formula as given - rank/(rank+1) to get rank in range of 0 to 1. OK, then what does the +1 mean in your formulae? Consider these results from [1]. rank/(rank+1): 0.19/.1 = 1.9, .1/.1 = 1, etc. That doesn't make sense. The reciprocal also doesn't make sense. So what does Oleg mean? I was guessing that Oleg meant to divide the rank column by the first rank, that is, by 0.19 so you would get 1, .52, .52, etc. id | headline| rank +---+-- 3 | bcrawling/b over cobbles in a low bpassage/b. | 0.19 1 | bcrawl/b over cobbles leads inward to the west. | 0.1 4 | bpassages/b lead east, north, and south. | 0.1 5 | bcrawl/b slants up. | 0.1 7 | bpassage/b here is blocked by a recent cave-in. | 0.1 Am I being stupid? [1] http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-guide.html -- Make April 15 just another day, visit http://fairtax.org ---(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] tsearch2 questions
On 7/4/07, Joshua N Pritikin [EMAIL PROTECTED] wrote: From where are you quoting? I was quoting from: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-ref.html i was quoting file http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html or actually - it's copy provided with sources of postgresql in contrib/tsearch2/docs directory. actually oleg supposed not to use best rank, but just use the formula as given - rank/(rank+1) to get rank in range of 0 to 1. OK, then what does the +1 mean in your formulae? Consider these results from [1]. rank/(rank+1): 0.19/.1 = 1.9, .1/.1 = 1, etc. That doesn't make sense. The reciprocal also doesn't make sense. So what does Oleg mean? I was guessing that Oleg meant to divide the rank column by the first rank, that is, by 0.19 so you would get 1, .52, .52, etc. +1 means: add one to. for example: for rank = 0.1 you get: 0.1/(0.1+1) = 0.1/1.1 = 0.0909 for rank = 0.5 you get: 0.5/(0.5+1) = 0.5/1.5 = 0. i think that notation: rank+1 is pretty readable. additionally - sorry but i dont understand your calculations. what is 0.19/.1 ? how did you get the .1? depesz
Re: [GENERAL] tsearch2 questions
On Wed, Jul 04, 2007 at 11:08:21AM +0200, hubert depesz lubaczewski wrote: On 7/4/07, Joshua N Pritikin [EMAIL PROTECTED] wrote: From where are you quoting? I was quoting from: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-ref.html i was quoting file http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html So that one is fine. Only the reference could use some clarification. actually oleg supposed not to use best rank, but just use the formula as given - rank/(rank+1) to get rank in range of 0 to 1. OK, then what does the +1 mean in your formulae? Consider these results from [1]. rank/(rank+1): 0.19/.1 = 1.9, .1/.1 = 1, etc. That doesn't make sense. The reciprocal also doesn't make sense. So what does Oleg mean? I was guessing that Oleg meant to divide the rank column by the first rank, that is, by 0.19 so you would get 1, .52, .52, etc. +1 means: add one to. for example: for rank = 0.1 you get: 0.1/(0.1+1) = 0.1/1.1 = 0.0909 for rank = 0.5 you get: 0.5/(0.5+1) = 0.5/1.5 = 0. D'oh! I see. i think that notation: rank+1 is pretty readable. additionally - sorry but i dont understand your calculations. what is 0.19/.1 ? how did you get the .1? I was imagining that rank+1 was the second row of the rank column. Sorry for the confusion. -- Make April 15 just another day, visit http://fairtax.org ---(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] tsearch2 in multilingual database?
Sometime in the future, I anticipate storing other languages in addition to English in my database to be indexed with tsearch2. set_curcfg() seems to be per-session. Will I need to call set_curcfg() every time I switch languages? -- Make April 15 just another day, visit http://fairtax.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] how to covert a column?
Dimitrius Weddington wrote: I have a dataset (90GBs worth) that contains 3 columns of unix timestamps in seconds. Ideally, I want to use copy to load the data into the DB (the disk i/o of trying to process this volume of data vi perl takes a couple of days). I was hoping to convert the unix timestamps inside the DB to pg timestamp creating a view or create table as... however no success so far. The to_timestamp() function works fine to convert the data but for some reason I can't get it to work in creating a view with the fields that are timestamps instead of INT. The following works well on my PostgreSQL 8.2.4 which is configured with --enable-integer-datetimes: SHOW TIME ZONE; TimeZone --- Europe/Vienna (1 row) CREATE TABLE t (id integer NOT NULL PRIMARY KEY, ts integer); CREATE VIEW v (id, ts) AS (SELECT t.id, to_timestamp(t.ts) FROM t); INSERT INTO t (id, ts) VALUES (1, 0), (2, 3600); SELECT * FROM v; id | ts + 1 | 1970-01-01 01:00:00+01 2 | 1970-01-01 02:00:00+01 (2 rows) So your problem must be something else. Maybe you can describe your problem in greater detail? Yours, Laurenz Albe ---(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] Mugs 'n stuff
A. Kretschmer wrote: am Tue, dem 03.07.2007, um 23:37:57 +0100 mailte Raymond O'Donnell folgendes: Hi all, Is it still possible to get PostgreSQL merchandise? A friend of mine is looking for some, but I can't seem to find where its available. Can you or your fried visit the pgday.it at Prato, Italy? http://www.pgday.it/en/ , maybe there. We, the german postgresql user group, have some stuff like blue plush elephants, shirts and coffee-cups, see also http://ads.wars-nicht.de/blog/ (scroll a little bit down), this will be available in Prato. I've always had luck finding such items at http://www.cafepress.com/ Here's the tinyurl to a search for postgresql, which found a few items as well as some semi-related: http://tinyurl.com/27onuq I found all kinds of clothing as well as coffee cups, license plate frames and clocks. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin ---(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] Date for a week day of a month
generate_series that's a good one!
Thank you!
On 7/3/07, Emi Lu [EMAIL PROTECTED] wrote:
Can I know how to get the date of each month's last Thursday please?
Query: select getDateBaseOnWeekday('2007-04-01', 'Last Thursday');
Result: 2007-04-26
you can easily do it without functions.
for example, this select:
SELECT
cast(d.date + i * '1 day'::interval as date)
FROM
(select '2007-04-01'::date as date) d,
generate_series(0, 30) i
WHERE
to_char(d.date, 'MM') = to_char( cast(d.date + i * '1 day'::interval as
date), 'MM')
AND to_char(cast(d.date + i * '1 day'::interval as date), 'D') = '5'
ORDER BY 1 DESC
LIMIT 1
;
does what you need.
to get last-thursday for another month, just change: (select
'2007-04-01'::date as date) d, to be 1st of any other month.
depesz
---(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] Mugs 'n stuff
On 04/07/2007 14:20, Geoffrey wrote: A. Kretschmer wrote: We, the german postgresql user group, have some stuff like blue plush elephants, shirts and coffee-cups, see also http://ads.wars-nicht.de/blog/ (scroll a little bit down), this will be available in Prato. I've always had luck finding such items at http://www.cafepress.com/ Here's the tinyurl to a search for postgresql, which found a few items as well as some semi-related: http://tinyurl.com/27onuq I found all kinds of clothing as well as coffee cups, license plate frames and clocks. Thanks for your replies! - I'll pass them on. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Design Tool
On 04.07.2007 10:44, Gabriele wrote: Anyway it doesn't support SQLite. Casestudio is a script based framework, there is lot of user contributed stuff. I remember having seen SQLite support somewhere, if not it's not so hard to add support yourself. -- Regards, Hannes Dorbath ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Reasonable way to backup cluster Windows
On 04.07.2007 10:42, Andrus wrote: I use pg_dumpall -g but in this case I have two backup files: one regular backup and second contains sql scripts for user creation. How to force pg_dump to backup users also ? There is no way around that. pg_dumpall -g pg_dump -Fc for each DB. -- Regards, Hannes Dorbath ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] blobs
On Jul 4, 2007, at 11:34 , Cesar Alvarez wrote: is there a way to store pictures or executables in postgres?? From the PostgreSQL FAQ 4.10) What is the difference between the various character types? http://www.postgresql.org/docs/faqs.FAQ.html#item4.10 From the PostgreSQL Documentation 8.4. Binary Data Types http://www.postgresql.org/docs/8.2/interactive/datatype-binary.html Chapter 30. Large Objects http://www.postgresql.org/docs/8.2/interactive/largeobjects.html Hope this helps. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] blobs
hello every one is there a way to store pictures or executables in postgres?? Cesar Alvarez. begin:vcard fn:Cesar Alvarez n:;Cesar Alvarez title:Web Development Asesor and Software Enginner version:2.1 end:vcard ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] blobs
On 04/07/2007 17:34, Cesar Alvarez wrote: is there a way to store pictures or executables in postgres?? http://www.postgresql.org/docs/8.2/static/datatype-binary.html --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(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] What O/S or hardware feature would be useful for databases?
On 7/4/07, Ron Johnson [EMAIL PROTECTED] wrote: Enterprise-level tapes can sit in storage for 7-15 years and then still be readable. Can a disk drive sit un-used for 7 years? Would the motor freeze up? Will we still be able to connect SATA drives in 7 years? Same with a tape-drive, no? I've seen so many standard changes in drives and SCSI connectors ... if you don't keep spares of all the equipment involved you'll face the same issue with tapes that you'd face with SATA disks. -- Ron Johnson, Jr. Jefferson LA USA -- Cheers, Andrej Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [general] growing disk usage problem: alternative solution?
Thanks all for the replies. Mr. Wu Zong-min ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Problem with autovacuum and pg_autovacuum
Hello, we got a small problem with auto_vacuum: since we have some big tables which have heavy read/write access, we tried to exclude this tables from autovacuum: database1=# select vacrelid,enabled,(select relname from pg_class where oid=vacrelid) as relname from pg_autovacuum; vacrelid | enabled | relname --+-+-- 42041 | f | guestbook 42344 | f | forum_threads 42406 | f | forum_thread_entries 41937 | f | user_online 42255 | f | forum_fora 41570 | f | users 41694 | f | user_data (7 rows) Now it seems, that autovacuum is processing exactly this tables: 2007-07-04 22:37:05 CEST DEBUG: autovacuum: processing database database1 2007-07-04 22:37:06 CEST DEBUG: autovac: will VACUUM ANALYZE users 2007-07-04 22:37:06 CEST DEBUG: autovac: will VACUUM ANALYZE user_stats 2007-07-04 22:37:06 CEST DEBUG: autovac: will VACUUM ANALYZE guestbook 2007-07-04 22:37:06 CEST DEBUG: autovac: will VACUUM ANALYZE forum_fora 2007-07-04 22:37:06 CEST DEBUG: autovac: will VACUUM ANALYZE forum_threads 2007-07-04 22:37:06 CEST DEBUG: autovac: will VACUUM ANALYZE forum_thread_entries 2007-07-04 22:37:06 CEST DEBUG: autovac: will VACUUM ANALYZE user_data 2007-07-04 22:37:06 CEST DEBUG: autovac: will VACUUM ANALYZE user_online 2007-07-04 22:37:06 CEST DEBUG: vacuuming schema1.users 2007-07-04 22:38:39 CEST DEBUG: vacuuming pg_toast.pg_toast_41570 2007-07-04 22:38:47 CEST DEBUG: vacuuming schema1.user_stats 2007-07-04 22:49:06 CEST DEBUG: vacuuming pg_toast.pg_toast_43602 2007-07-04 22:51:51 CEST DEBUG: vacuuming schema1.guestbook 2007-07-04 23:00:38 CEST DEBUG: vacuuming pg_toast.pg_toast_42041 2007-07-04 23:00:43 CEST DEBUG: vacuuming schema1.forum_fora 2007-07-04 23:00:50 CEST DEBUG: vacuuming pg_toast.pg_toast_42255 2007-07-04 23:00:50 CEST DEBUG: vacuuming schema1.forum_threads 2007-07-04 23:01:06 CEST DEBUG: vacuuming schema1.forum_thread_entries 2007-07-04 23:01:50 CEST DEBUG: vacuuming pg_toast.pg_toast_42406 2007-07-04 23:01:54 CEST DEBUG: vacuuming schema1.user_data 2007-07-04 23:05:36 CEST DEBUG: vacuuming pg_toast.pg_toast_41694 2007-07-04 23:05:45 CEST DEBUG: vacuuming schema1.user_online database1=# select version(); version - PostgreSQL 8.2.4 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21) (1 row) Any idea, what's wrong here? Kind regards -- Andreas 'ads' Scherbaum Failure is not an option. It comes bundled with your Microsoft product. (Ferenc Mantfeld) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] perpetual dump/restore problem
Dear List, As many of you know every time one wants to upgrade to a major server version, as I want to do now, there is a need for pg_dumpall and restore. This is a huge undertaking and requires lots of testing and planning. I do hope that in the future this requirement will be dropped. I am now upgrading from 8.1.9 to 8.2.4 and pg_dumpall gives a dump file which has a command \connect template0 Normally template0 does not accept connection but I found a way to make that happen in the documentation on postgresql website. But is it generally safe to accept the connection for template0 all the time? Why won't pg_dump include a line to accept connection for template0 just before it writes \connect template0 and then remove the permission after it is done with template0? thanks Wu Z-m ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Problem with autovacuum and pg_autovacuum
Andreas 'ads' Scherbaum wrote: Hello, we got a small problem with auto_vacuum: since we have some big tables which have heavy read/write access, we tried to exclude this tables from autovacuum: database1=# select vacrelid,enabled,(select relname from pg_class where oid=vacrelid) as relname from pg_autovacuum; vacrelid | enabled | relname --+-+-- 42041 | f | guestbook 42344 | f | forum_threads 42406 | f | forum_thread_entries 41937 | f | user_online 42255 | f | forum_fora 41570 | f | users 41694 | f | user_data (7 rows) Most likely it is worried about XID wraparound, and those are precisely the tables that need urgent vacuumed because they haven't been vacuumed in a long time. What do you do to keep them clear of dead tuples? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Problem with autovacuum and pg_autovacuum
Hello, On Wed, 4 Jul 2007 18:04:35 -0400 Alvaro Herrera wrote: Most likely it is worried about XID wraparound, and those are precisely the tables that need urgent vacuumed because they haven't been vacuumed in a long time. No, autovacuum is doing this with every run. Beside this, the database has only some 10k changes per day. The wraparound was my first idea, but i don't see a reason, why this should be happen with every autovacuum run. What do you do to keep them clear of dead tuples? Most of this tables are just big (guestbook or forum entries as example). But there will be no dead tuples, since the entries are inserted and never changed. The main reason for putting this tables into the pg_autovacuum table was to avoid the locks at all with normal autovacuum processing and analyze the tables in a nightly maintenance window. Kind regards -- Andreas 'ads' Scherbaum Failure is not an option. It comes bundled with your Microsoft product. (Ferenc Mantfeld) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problem with autovacuum and pg_autovacuum
Andreas 'ads' Scherbaum wrote:
Hello,
On Wed, 4 Jul 2007 18:04:35 -0400 Alvaro Herrera wrote:
Most likely it is worried about XID wraparound, and those are precisely
the tables that need urgent vacuumed because they haven't been vacuumed
in a long time.
No, autovacuum is doing this with every run. Beside this, the database has
only some 10k changes per day. The wraparound was my first idea, but i
don't see a reason, why this should be happen with every autovacuum run.
Ok a new weird scenario. Could you please let us look at
select relname, relfrozenxid, age(relfrozenxid) from pg_class where
relkind in ('r', 't') order by 3 desc;
and
select datfrozenxid, age(datfrozenxid) from pg_database where
datname = 'your database';
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
---(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] Problem with autovacuum and pg_autovacuum
Hello,
On Wed, 4 Jul 2007 18:40:15 -0400 Alvaro Herrera wrote:
Andreas 'ads' Scherbaum wrote:
On Wed, 4 Jul 2007 18:04:35 -0400 Alvaro Herrera wrote:
Most likely it is worried about XID wraparound, and those are precisely
the tables that need urgent vacuumed because they haven't been vacuumed
in a long time.
No, autovacuum is doing this with every run. Beside this, the database has
only some 10k changes per day. The wraparound was my first idea, but i
don't see a reason, why this should be happen with every autovacuum run.
Ok a new weird scenario. Could you please let us look at
select relname, relfrozenxid, age(relfrozenxid) from pg_class where
relkind in ('r', 't') order by 3 desc;
Thats a bit more information ...
http://rafb.net/p/xJ4W6W43.html
select datfrozenxid, age(datfrozenxid) from pg_database where
datname = 'your database';
database1=# select datfrozenxid, age(datfrozenxid) from pg_database where
datname = 'database1';
datfrozenxid | age
--+--
524 | 35952722
(1 row)
Kind regards
--
Andreas 'ads' Scherbaum
Failure is not an option. It comes bundled with your Microsoft product.
(Ferenc Mantfeld)
---(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] Problem with autovacuum and pg_autovacuum
Andreas 'ads' Scherbaum wrote:
Hello,
On Wed, 4 Jul 2007 18:40:15 -0400 Alvaro Herrera wrote:
Andreas 'ads' Scherbaum wrote:
On Wed, 4 Jul 2007 18:04:35 -0400 Alvaro Herrera wrote:
Most likely it is worried about XID wraparound, and those are precisely
the tables that need urgent vacuumed because they haven't been vacuumed
in a long time.
No, autovacuum is doing this with every run. Beside this, the database has
only some 10k changes per day. The wraparound was my first idea, but i
don't see a reason, why this should be happen with every autovacuum run.
Ok a new weird scenario. Could you please let us look at
select relname, relfrozenxid, age(relfrozenxid) from pg_class where
relkind in ('r', 't') order by 3 desc;
Thats a bit more information ...
http://rafb.net/p/xJ4W6W43.html
Oh. It's not the age. Please let us look at the pg_stat_user_tables
entries for the involved tables? If it's picking the same tables maybe
pgstats has stale info, but why is it not updating it?
--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
The problem with the facetime model is not just that it's demoralizing, but
that the people pretending to work interrupt the ones actually working.
(Paul Graham)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] What O/S or hardware feature would be useful for databases?
On 07/04/07 16:00, Andrej Ricnik-Bay wrote: On 7/4/07, Ron Johnson [EMAIL PROTECTED] wrote: Enterprise-level tapes can sit in storage for 7-15 years and then still be readable. Can a disk drive sit un-used for 7 years? Would the motor freeze up? Will we still be able to connect SATA drives in 7 years? I was a bit harsh about connecting to SATA drives. IDE has been around for 21 years and ATA-133 is backwards compatible with 20MB drives of that era, so I predict that you'll be able to plug SATA-1 drives into machines with SATA-9 interfaces. But then, the motor might still not spin up... :( Same with a tape-drive, no? I've seen so many standard changes in drives and SCSI connectors ... if you don't keep spares of all the equipment involved you'll face the same issue with tapes that you'd face with SATA disks. No. Enterprise tape drives are not flavor of the month, and can always read the previous one or two generations of tape. And if you've switched from, for example, SuperDLT to LTO, then you'll still be able to buy some drives on the used market (either eBay or from a dealer). -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] What O/S or hardware feature would be useful for databases?
Andrej Ricnik-Bay [EMAIL PROTECTED] writes: On 7/4/07, Ron Johnson [EMAIL PROTECTED] wrote: Enterprise-level tapes can sit in storage for 7-15 years and then still be readable. Can a disk drive sit un-used for 7 years? Would the motor freeze up? Will we still be able to connect SATA drives in 7 years? Same with a tape-drive, no? Uh, no, because the tape is removable. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
