Re: [GENERAL] Slow Vacuum was: vacuum output question
well, I can confirm problems is caused by indices here as well - I do reindex twice a month because of that. (and general performance problems caused by large index) - maybe it is time to focus on index re usability - since HOT is already there, autovacuum does the job too. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] RCA for MemoryContextAlloc: invalid request size(Known Issue)
That I'll do but that still won't answer the question : What is the reason for the data corruption leading to MemoryContextAlloc: invalid request size -Original Message- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: January 08, 2009 8:18 PM To: Yogvinder Singh Cc: Stefan Kaltenbrunner; pgsql-general@postgresql.org Subject: Re: [GENERAL] RCA for MemoryContextAlloc: invalid request size(Known Issue) On Thu, Jan 8, 2009 at 4:10 AM, Yogvinder Singh yogvin...@newgen.co.in wrote: Correcting myself: Here is the version details template1=# select version(); version --- PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.3 20041212 (Red Hat 3.4.3-9.EL4) So why are you running a version missing years upon years of bug fixes? Just update to the last 7.3.x first Disclaimer :- This e-mail and any attachment may contain confidential, proprietary or legally privileged information. If you are not the original intended recipient and have erroneously received this message, you are prohibited from using, copying, altering or disclosing the content of this message. Please delete it immediately and notify the sender. Newgen Software Technologies Ltd (NSTL) accepts no responsibilities for loss or damage arising from the use of the information transmitted by this email including damages from virus and further acknowledges that no binding nature of the message shall be implied or assumed unless the sender does so expressly with due authority of NSTL. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] RCA for MemoryContextAlloc: invalid request size(Known Issue)
On Fri, Jan 9, 2009 at 2:53 PM, Yogvinder Singh yogvin...@newgen.co.in wrote: That I'll do but that still won't answer the question : What is the reason for the data corruption leading to MemoryContextAlloc: invalid request size That's because you may read the corrupt data and do further operations which leads to failure. For example, the tuple length may be corrupted and you try to copy that tuple to memory. That's when the palloc can fail with the error. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] RCA for MemoryContextAlloc: invalid request size(Known Issue)
Pavan, What is the possible reason for the data corruption? Regards, Yogvinder -Original Message- From: Pavan Deolasee [mailto:pavan.deola...@gmail.com] Sent: January 09, 2009 2:59 PM To: Yogvinder Singh Cc: Scott Marlowe; Stefan Kaltenbrunner; pgsql-general@postgresql.org Subject: Re: [GENERAL] RCA for MemoryContextAlloc: invalid request size(Known Issue) On Fri, Jan 9, 2009 at 2:53 PM, Yogvinder Singh yogvin...@newgen.co.in wrote: That I'll do but that still won't answer the question : What is the reason for the data corruption leading to MemoryContextAlloc: invalid request size That's because you may read the corrupt data and do further operations which leads to failure. For example, the tuple length may be corrupted and you try to copy that tuple to memory. That's when the palloc can fail with the error. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com Disclaimer :- This e-mail and any attachment may contain confidential, proprietary or legally privileged information. If you are not the original intended recipient and have erroneously received this message, you are prohibited from using, copying, altering or disclosing the content of this message. Please delete it immediately and notify the sender. Newgen Software Technologies Ltd (NSTL) accepts no responsibilities for loss or damage arising from the use of the information transmitted by this email including damages from virus and further acknowledges that no binding nature of the message shall be implied or assumed unless the sender does so expressly with due authority of NSTL. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Where do I find crypto installer for Mac platform
I am looking for a mac platform installer for what I was told I needed, pgcrypto. Assistance finding this would be appreciated... Steve Henry San Diego Mac IT http://www.sdmacit.com 760.751.4292 Office - 760.546.8863 Cell -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about COPY command
On fim, 2009-01-08 at 08:39 -0500, Josh Harrison wrote:
Hi,
A basic question about the COPY command syntax
This is the syntax in the postgres manual.
COPY tablename [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
..
.
What is the difference between copying from 'filename' and copying from
'stdin' ???
The most important distinction is that 'filename' refers to a file
residing on the *server*, but STDIN is clientside.
For security, the file variant requires superuser privileges.
gnari
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] RCA for MemoryContextAlloc: invalid request size(Known Issue)
On Fri, Jan 9, 2009 at 4:17 PM, Yogvinder Singh yogvin...@newgen.co.in wrote: Pavan, What is the possible reason for the data corruption? It could be anything from buggy hardware to bug in the code. As many have pointed out earlier, you are running a very old release and missing several bug fixes. If you are willing, you could read the release notes of those missing releases to check if there was any fix related to data corruption. I think the best thing to upgrade to the latest point release as soon as possible. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] RCA for MemoryContextAlloc: invalid request size(Known Issue)
Yogvinder Singh wrote: Pavan, What is the possible reason for the data corruption? Yogvinder - you haven't provided any information for people to identify what is corrupted let alone why it is corrupted. Like I said in my reply on the 7th - it's either hardware problems or a bug in the code. Tracking down precisely what happened is going to be a lengthy and complicated process even assuming you have all the tools in place, good logging etc. Given that you're running 7.3.2 it's unlikely that that is the case. Since you're running 7.3.2 and the last release for 7.3 is 7.3.21 you've chosen to run without 18 sets of bug-fixes issued over a period of five years. Have you read the release-notes for those versions and satisfied yourself that none of the changes affect you? http://www.postgresql.org/docs/8.3/static/release.html If you are not 100% certain that those changes are useless to you, follow the advice everyone is giving and upgrade to the 7.3.21 as soon as is practical. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] dblink between oracle and postgres?
2009/1/8 Filip Rembiałkowski plk.zu...@gmail.com: 2009/1/8 Joshua D. Drake j...@commandprompt.com On Thu, 2009-01-08 at 15:12 -0500, Josh Harrison wrote: Hi, Is there any utility like (oracle's dblink etc) that can establish connection between oracle and postgres database? dbi-link Ora-link In the opposite direction, you can use Oracle's feature described here: http://lists.mysql.com/mysql/189287 (change to psqlodbc driver should be easy). -- Filip Rembiałkowski -- Emanuel Calvo Franco ArPUG / AOSUG Member Postgresql Support Admin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Adding Arabic dictionary for TSearch2.. to_tsvector('arabic'...) doesn't work..
Hi Mohammed, See my answers below, and hopefully they won't lead you too far astray. Note though, it has been a long time since I have done this and there are doubtless more knowledgeable people in this forum who will be able to correct anything I say that may be misleading or incorrect. Cheers, Andy Mohamed wrote: no one ? / Moe On Thu, Jan 8, 2009 at 11:46 AM, Mohamed mohamed5432154...@gmail.com mailto:mohamed5432154...@gmail.com wrote: Ok, thank you all for your help. It has been very valuable. I am starting to get the hang of it and almost read the whole chapter 12 + extras but I still need a little bit of guidance. I have now these files : * A arabic Hunspell rar file (OpenOffice version) wich includes : o ar.dic o ar.aff * An Aspell rar file that includes alot of files * A Myspell ( says simple words list ) * And also Andrews two files : o ar.affix o ar.stop I am thinking that I should go with just one of these right and that should be the Hunspell? Hunspell is based on MySpell, extending it with support for complex compound words and unicode characters, however Postgresql cannot take advantage of Hunspell's compound word capabilities at present. Aspell is a GNU dictionary that replaces Ispell and supports UTF-8 characters. See http://aspell.net/test/ for comparisons between dictionaries, though be aware this test is hosted by Aspell... I will leave it to others to argue the merits of Hunspell vs. Aspell, and why you would choose one or the other. There is an ar.aff file there and Andrews file ends with .affix, are those perhaps similiar? Should I skip Andrews ? The ar.aff file that comes with OpenOffice Hunspell dictionary is essentially the same as the ar.affix I supplied. Just open the two up, compare them and choose the one that you feel is best. A Hunspell dictionary will work better with a corresponding affix file. Use just the ar.stop file ? The ar.stop file flags common words from being indexed. You will want a stop file as well as the dictionary and affix file. Feel free to modify the stop file to meet your own needs. On the Arabic / English on row basis language search approach, I will skip and choose the approach suggested by Oleg : if arabic and english characters are not overlaped, you can use one index. The Arabic letters and English letters or words don't overlap so that should not be an issue? Will I be able to index and search against both languages in the same query? If you want to support multiple language dictionaries for a single table, with each row associated to its own dictionary, use the tsvector_update_trigger_column trigger to automatically update your tsvector indexed column on insert or update. To support this, your table will need an additional column of type regconfig that contains the name of the dictionary to use when searching on the tsvector column for that particular row. See http://www.postgresql.org/docs/current/static/textsearch-features.html#TEXTSEARCH-UPDATE-TRIGGERS for more details. This will allow you to search across both languages in the one query as you were asking. And also 1. What language files should I use ? 2. How does my create dictionary for the arabic language look like ? Perhaps like this : CREATE TEXT SEARCH DICTIONARY arabic_dic( TEMPLATE = ? , // Not sure what this means DictFile = ar, // referring to ar.dic (hunspell) AffFile = ar , // referring to ar.aff (hunspell) StopWords = ar // referring to Andrews stop file. ( what about Andrews .affix file ? ) // Anything more ? ); From psql command line you can find out what templates you have using the following command: \dFt or looking at the contents of the pg_ts_template table. If choosing a Hunspell or Aspell dictionary, I believe a value of TEMPLATE = ispell should be okay for you - see http://www.postgresql.org/docs/current/static/textsearch-dictionaries.html#TEXTSEARCH-ISPELL-DICTIONARY. The template provides instructions to postgresql on how to interact with the dictionary. The rest of the create dictionary statement appears fine to me. Thanks again! / Moe No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.176 / Virus Database: 270.10.3/1879 - Release Date: 1/6/2009 5:16 PM
Re: [GENERAL] Where do I find crypto installer for Mac platform
Steve Henry wrote: I am looking for a mac platform installer for what I was told I needed, pgcrypto. Assistance finding this would be appreciated... It is part of /contrib. I am not sure where it is in the mac installer. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Where do I find crypto installer for Mac platform
On Fri, Jan 9, 2009 at 2:31 PM, Bruce Momjian br...@momjian.us wrote:
Steve Henry wrote:
I am looking for a mac platform installer for what I was told I
needed, pgcrypto.
Assistance finding this would be appreciated...
It is part of /contrib. I am not sure where it is in the mac installer.
It should be in ${INSTALL_DIR}/lib and ${INSTALL_DIR}/share, exactly
as it would be for a source install.
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Thanx for 8.3
On Fri, 2009-01-09 at 08:17 +0100, Reg Me Please wrote: On Friday 09 January 2009 00:10:53 Jeremiah Jahn wrote: Just wanted to say thank you for version 8.3. The ordered indexing has dropped some of my search times from over 30 seconds to 3. I've been beating my head against this issue for over 8 years. I will drink to you tonight. thanx again, -jj- -- When you're dining out and you suspect something's wrong, you're probably right. Give also CLUSTER a try. And partial indexes also. I've had clusters going since they became available. They still required massive sequential scans and with a dedicated disk array w/ a sustained I/O rate of 600MB/s it still took 30 seconds. My data has about 25 new/updated entries per day, so the clusters just couldn't keep up. 70% of my problem was sorting, followed by a complex join. Now that the sorting is O(n), I've modified things to use a search table that is basically a select into of the join I always had to do. Had I done this before, I wouldn't have had the improvements to justify the added complexity to my system. I use partial indexes in other places, but these are name searches where someone wants all the 'SMITHS%' in half the state of Illinois who've been 'convicted' of 'aggravated battery' 'in the last 5 years' and have traffic tickets'; It's difficult to come up with partials when the queries are not predictable. Nor have I ever had the budget to get enough memory to keep these tables in memory. There just always been a limit to the amount of hardware(money) I can throw as something. Of course that's what makes it fun and challenging. Now if there was just simple way to make some sort of persistent view that could have indexes on it, so that complex joins could be sped up, in stead of making non-normal tables. (hint hint :) Prosit! -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Thanx for 8.3
On Friday 09 January 2009 15:46:51 Jeremiah Jahn wrote: On Fri, 2009-01-09 at 08:17 +0100, Reg Me Please wrote: On Friday 09 January 2009 00:10:53 Jeremiah Jahn wrote: Just wanted to say thank you for version 8.3. The ordered indexing has dropped some of my search times from over 30 seconds to 3. I've been beating my head against this issue for over 8 years. I will drink to you tonight. thanx again, -jj- -- When you're dining out and you suspect something's wrong, you're probably right. Give also CLUSTER a try. And partial indexes also. I've had clusters going since they became available. They still required massive sequential scans and with a dedicated disk array w/ a sustained I/O rate of 600MB/s it still took 30 seconds. My data has about 25 new/updated entries per day, so the clusters just couldn't keep up. 70% of my problem was sorting, followed by a complex join. Now that the sorting is O(n), I've modified things to use a search table that is basically a select into of the join I always had to do. Had I done this before, I wouldn't have had the improvements to justify the added complexity to my system. I use partial indexes in other places, but these are name searches where someone wants all the 'SMITHS%' in half the state of Illinois who've been 'convicted' of 'aggravated battery' 'in the last 5 years' and have traffic tickets'; It's difficult to come up with partials when the queries are not predictable. Nor have I ever had the budget to get enough memory to keep these tables in memory. There just always been a limit to the amount of hardware(money) I can throw as something. Of course that's what makes it fun and challenging. Now if there was just simple way to make some sort of persistent view that could have indexes on it, so that complex joins could be sped up, in stead of making non-normal tables. (hint hint :) Prosit! -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand For materialized view just google it. Or just jump here: http://www.jonathangardner.net/tech/w/PostgreSQL/Materialized_Views -- -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Adding Arabic dictionary for TSearch2.. to_tsvector('arabic'...) doesn't work..
Thank you for you detailed answer. I have learned alot more about this stuff now :) As I see it accordingly to the results it's between Hunspell and Aspell. My Aspell version is 0.6 released 2006. The Hunspell was released in 2008. When I run the Postgres command \dFt I get the following list : - ispell - simple - snowball - synonym - thesaurus So I set up my dictionary with the ispell as a template and Hunspell/Aspell files. Now I just have one decision to make :) Just another thing: If you want to support multiple language dictionaries for a single table, with each row associated to its own dictionary Not really, since the two languages don't overlap, couldn't I set up two separate dictionaries and index against both on the whole table ? I think that's what Oleg was refering to. Not sure... Thanks for all the help / Moe Ps. I can't Arabic so I can't have a look on the files to decide :O On Fri, Jan 9, 2009 at 2:14 PM, Andrew ar...@pacific.net.au wrote: Hi Mohammed, See my answers below, and hopefully they won't lead you too far astray. Note though, it has been a long time since I have done this and there are doubtless more knowledgeable people in this forum who will be able to correct anything I say that may be misleading or incorrect. Cheers, Andy Mohamed wrote: no one ? / Moe On Thu, Jan 8, 2009 at 11:46 AM, Mohamed mohamed5432154...@gmail.comwrote: Ok, thank you all for your help. It has been very valuable. I am starting to get the hang of it and almost read the whole chapter 12 + extras but I still need a little bit of guidance. I have now these files : - A arabic Hunspell rar file (OpenOffice version) wich includes : - ar.dic - ar.aff - An Aspell rar file that includes alot of files - A Myspell ( says simple words list ) - And also Andrews two files : - ar.affix - ar.stop I am thinking that I should go with just one of these right and that should be the Hunspell? Hunspell is based on MySpell, extending it with support for complex compound words and unicode characters, however Postgresql cannot take advantage of Hunspell's compound word capabilities at present. Aspell is a GNU dictionary that replaces Ispell and supports UTF-8 characters. See http://aspell.net/test/ for comparisons between dictionaries, though be aware this test is hosted by Aspell... I will leave it to others to argue the merits of Hunspell vs. Aspell, and why you would choose one or the other. There is an ar.aff file there and Andrews file ends with .affix, are those perhaps similiar? Should I skip Andrews ? The ar.aff file that comes with OpenOffice Hunspell dictionary is essentially the same as the ar.affix I supplied. Just open the two up, compare them and choose the one that you feel is best. A Hunspell dictionary will work better with a corresponding affix file. Use just the ar.stop file ? The ar.stop file flags common words from being indexed. You will want a stop file as well as the dictionary and affix file. Feel free to modify the stop file to meet your own needs. On the Arabic / English on row basis language search approach, I will skip and choose the approach suggested by Oleg : if arabic and english characters are not overlaped, you can use one index. The Arabic letters and English letters or words don't overlap so that should not be an issue? Will I be able to index and search against both languages in the same query? If you want to support multiple language dictionaries for a single table, with each row associated to its own dictionary, use the tsvector_update_trigger_column trigger to automatically update your tsvector indexed column on insert or update. To support this, your table will need an additional column of type regconfig that contains the name of the dictionary to use when searching on the tsvector column for that particular row. See http://www.postgresql.org/docs/current/static/textsearch-features.html#TEXTSEARCH-UPDATE-TRIGGERSfor more details. This will allow you to search across both languages in the one query as you were asking. And also 1. What language files should I use ? 2. How does my create dictionary for the arabic language look like ? Perhaps like this : CREATE TEXT SEARCH DICTIONARY arabic_dic( TEMPLATE = ? , // Not sure what this means DictFile = ar, // referring to ar.dic (hunspell) AffFile = ar , // referring to ar.aff (hunspell) StopWords = ar // referring to Andrews stop file. ( what about Andrews .affix file ? ) // Anything more ? ); From psql command line you can find out what templates you have using the following command: \dFt or looking at the contents of the pg_ts_template table. If choosing a Hunspell or Aspell dictionary, I believe a value of TEMPLATE = ispell should be okay for you - see
[GENERAL] Adding Arabic dictionary for TSearch2.. to_tsvector('arabic'...) doesn't work..
Thank you for you detailed answer. I have learned alot more about this stuff now :) As I see it accordingly to the results it's between Hunspell and Aspell. My Aspell version is 0.6 released 2006. The Hunspell was released in 2008. When I run the Postgres command \dFt I get the following list : - ispell - simple - snowball - synonym - thesaurus So I set up my dictionary with the ispell as a template and Hunspell/Aspell files. Now I just have one decision to make :) Just another thing: If you want to support multiple language dictionaries for a single table, with each row associated to its own dictionary Not really, since the two languages don't overlap, couldn't I set up two separate dictionaries and index against both on the whole table ? I think that's what Oleg was refering to. Not sure... Thanks for all the help / Moe Ps. I can't read Arabic so I can't have a look on the files to decide :O On Fri, Jan 9, 2009 at 2:14 PM, Andrew ar...@pacific.net.au wrote: Hi Mohammed, See my answers below, and hopefully they won't lead you too far astray. Note though, it has been a long time since I have done this and there are doubtless more knowledgeable people in this forum who will be able to correct anything I say that may be misleading or incorrect. Cheers, Andy Mohamed wrote: no one ? / Moe On Thu, Jan 8, 2009 at 11:46 AM, Mohamed mohamed5432154...@gmail.comwrote: Ok, thank you all for your help. It has been very valuable. I am starting to get the hang of it and almost read the whole chapter 12 + extras but I still need a little bit of guidance. I have now these files : - A arabic Hunspell rar file (OpenOffice version) wich includes : - ar.dic - ar.aff - An Aspell rar file that includes alot of files - A Myspell ( says simple words list ) - And also Andrews two files : - ar.affix - ar.stop I am thinking that I should go with just one of these right and that should be the Hunspell? Hunspell is based on MySpell, extending it with support for complex compound words and unicode characters, however Postgresql cannot take advantage of Hunspell's compound word capabilities at present. Aspell is a GNU dictionary that replaces Ispell and supports UTF-8 characters. See http://aspell.net/test/ for comparisons between dictionaries, though be aware this test is hosted by Aspell... I will leave it to others to argue the merits of Hunspell vs. Aspell, and why you would choose one or the other. There is an ar.aff file there and Andrews file ends with .affix, are those perhaps similiar? Should I skip Andrews ? The ar.aff file that comes with OpenOffice Hunspell dictionary is essentially the same as the ar.affix I supplied. Just open the two up, compare them and choose the one that you feel is best. A Hunspell dictionary will work better with a corresponding affix file. Use just the ar.stop file ? The ar.stop file flags common words from being indexed. You will want a stop file as well as the dictionary and affix file. Feel free to modify the stop file to meet your own needs. On the Arabic / English on row basis language search approach, I will skip and choose the approach suggested by Oleg : if arabic and english characters are not overlaped, you can use one index. The Arabic letters and English letters or words don't overlap so that should not be an issue? Will I be able to index and search against both languages in the same query? If you want to support multiple language dictionaries for a single table, with each row associated to its own dictionary, use the tsvector_update_trigger_column trigger to automatically update your tsvector indexed column on insert or update. To support this, your table will need an additional column of type regconfig that contains the name of the dictionary to use when searching on the tsvector column for that particular row. See http://www.postgresql.org/docs/current/static/textsearch-features.html#TEXTSEARCH-UPDATE-TRIGGERSfor more details. This will allow you to search across both languages in the one query as you were asking. And also 1. What language files should I use ? 2. How does my create dictionary for the arabic language look like ? Perhaps like this : CREATE TEXT SEARCH DICTIONARY arabic_dic( TEMPLATE = ? , // Not sure what this means DictFile = ar, // referring to ar.dic (hunspell) AffFile = ar , // referring to ar.aff (hunspell) StopWords = ar // referring to Andrews stop file. ( what about Andrews .affix file ? ) // Anything more ? ); From psql command line you can find out what templates you have using the following command: \dFt or looking at the contents of the pg_ts_template table. If choosing a Hunspell or Aspell dictionary, I believe a value of TEMPLATE = ispell should be okay for you - see
[GENERAL] Seeking PG Booth Voleenteers for 2009 SCALE
The call is going out for PostgreSQL enthusiasts. We are seeking volunteers to assist the PostgreSQL booth for this years Southern California Linux Expo: http://scale7x.socallinuxexpo.org The Exhibit hall will be open on Saturday the February 21st and Sunday the 22nd between the hours of 10am and 5pm. The time that the booth will need attendants is between the hours of 10 a.m. and 5 p.m. http://scale7x.socallinuxexpo.org/conference-info/hotel_info This will be a great opportunity to meet PostgreSQL community leaders and local LAPUG members. For anyone what would like to volunteer, please email me with your contact information so that we can make arrangements. Also on a side note, LAPUG will be meeting at SCALE's PostgreSQL BOF. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Seeking PG Booth Volunteers for 2009 SCALE
Sorry about the typo in the subject line. On Fri, Jan 9, 2009 at 9:05 AM, Richard Broersma richard.broer...@gmail.com wrote: The call is going out for PostgreSQL enthusiasts. We are seeking volunteers to assist the PostgreSQL booth for this years Southern California Linux Expo: http://scale7x.socallinuxexpo.org The Exhibit hall will be open on Saturday the February 21st and Sunday the 22nd between the hours of 10am and 5pm. The time that the booth will need attendants is between the hours of 10 a.m. and 5 p.m. http://scale7x.socallinuxexpo.org/conference-info/hotel_info This will be a great opportunity to meet PostgreSQL community leaders and local LAPUG members. For anyone what would like to volunteer, please email me with your contact information so that we can make arrangements. Also on a side note, LAPUG will be meeting at SCALE's PostgreSQL BOF. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [EXPLAIN] Nested loops
Hi. For an INNER JOINed query, EXPLAIN says that a nested loop is responsible for the big part of the time needed to run. The 2 tables JOINed are: T1: multi-million rows T2: few dozens rows The join is though a single column in both sides and it's NOT a PK in either table. But I have indexes in both T1 and T2 for that column. I've read in the Explaining EXPLAIN by Rober Treat (at http://wiki.postgresql.org/wiki/Image:OSCON2005-ExplainingExplain.sxi) that this nested loop can be slow because of lacking of indexes. Is there any hint to try to speed that query up? As of now, only a REINDEX can help thanks to caching, I presume. But the EXPLAIN still says there's a slow nested loop. -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [EXPLAIN] Nested loops
Could you provide the output of EXPLAIN ANALYZE with your query? On Fri, Jan 9, 2009 at 7:06 PM, Reg Me Please regmeple...@gmail.com wrote: Hi. For an INNER JOINed query, EXPLAIN says that a nested loop is responsible for the big part of the time needed to run. The 2 tables JOINed are: T1: multi-million rows T2: few dozens rows The join is though a single column in both sides and it's NOT a PK in either table. But I have indexes in both T1 and T2 for that column. I've read in the Explaining EXPLAIN by Rober Treat (at http://wiki.postgresql.org/wiki/Image:OSCON2005-ExplainingExplain.sxi) that this nested loop can be slow because of lacking of indexes. Is there any hint to try to speed that query up? As of now, only a REINDEX can help thanks to caching, I presume. But the EXPLAIN still says there's a slow nested loop. -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [EXPLAIN] Nested loops
Here it comes: Aggregate (cost=227.59..227.61 rows=1 width=8) - Nested Loop (cost=0.00..227.34 rows=49 width=8) - Seq Scan on T2 (cost=0.00..1.07 rows=6 width=4) Filter: (fld1 = 'VEND'::text) - Index Scan using i_T1_partial on T1 (cost=0.00..37.61 rows=8 width=8) Index Cond: ((T1.prod_id = 42) AND (T1.fk1 = T2.fk1)) On Friday 09 January 2009 19:22:28 Victor Nawothnig wrote: Could you provide the output of EXPLAIN ANALYZE with your query? On Fri, Jan 9, 2009 at 7:06 PM, Reg Me Please regmeple...@gmail.com wrote: Hi. For an INNER JOINed query, EXPLAIN says that a nested loop is responsible for the big part of the time needed to run. The 2 tables JOINed are: T1: multi-million rows T2: few dozens rows The join is though a single column in both sides and it's NOT a PK in either table. But I have indexes in both T1 and T2 for that column. I've read in the Explaining EXPLAIN by Rober Treat (at http://wiki.postgresql.org/wiki/Image:OSCON2005-ExplainingExplain.sxi) that this nested loop can be slow because of lacking of indexes. Is there any hint to try to speed that query up? As of now, only a REINDEX can help thanks to caching, I presume. But the EXPLAIN still says there's a slow nested loop. -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [EXPLAIN] Nested loops
Reg Me Please regmeple...@gmail.com writes: Aggregate (cost=227.59..227.61 rows=1 width=8) - Nested Loop (cost=0.00..227.34 rows=49 width=8) - Seq Scan on T2 (cost=0.00..1.07 rows=6 width=4) Filter: (fld1 = 'VEND'::text) - Index Scan using i_T1_partial on T1 (cost=0.00..37.61 rows=8 width=8) Index Cond: ((T1.prod_id = 42) AND (T1.fk1 = T2.fk1)) If those rowcount estimates are accurate, then this is a perfectly good plan; in fact probably the best you could hope for. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [EXPLAIN] Nested loops
On Friday 09 January 2009 20:00:36 Thomas Pundt wrote: Reg Me Please wrote: Here it comes: Aggregate (cost=227.59..227.61 rows=1 width=8) - Nested Loop (cost=0.00..227.34 rows=49 width=8) - Seq Scan on T2 (cost=0.00..1.07 rows=6 width=4) Filter: (fld1 = 'VEND'::text) - Index Scan using i_T1_partial on T1 (cost=0.00..37.61 rows=8 width=8) Index Cond: ((T1.prod_id = 42) AND (T1.fk1 = T2.fk1)) That's the EXPLAIN output, _not_ EXPLAIN ANALYZE as requested. Probably statistics aren't up-to-date? Thomas Correct! I changed a value in the WHERE condition to avoid some caching (maybe useless in this case). QUERY PLAN --- Aggregate (cost=227.59..227.61 rows=1 width=8) (actual time=151.722..151.723 rows=1 loops=1) - Nested Loop (cost=0.00..227.34 rows=49 width=8) (actual time=25.157..151.507 rows=44 loops=1) - Seq Scan on T2 (cost=0.00..1.07 rows=6 width=4) (actual time=0.015..0.032 rows=6 loops=1) Filter: (fld1 = 'VEND'::text) - Index Scan using i_T1_partial on T1 (cost=0.00..37.61 rows=8 width=8) (actual time=5.435..25.226 rows=7 loops=6) Index Cond: ((T1.prod_id = 4242) AND (T1.fk1 = T2.fk1)) Total runtime: 151.818 ms (7 rows) -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [EXPLAIN] Nested loops
On Friday 09 January 2009 20:00:57 Tom Lane wrote: Reg Me Please regmeple...@gmail.com writes: Aggregate (cost=227.59..227.61 rows=1 width=8) - Nested Loop (cost=0.00..227.34 rows=49 width=8) - Seq Scan on T2 (cost=0.00..1.07 rows=6 width=4) Filter: (fld1 = 'VEND'::text) - Index Scan using i_T1_partial on T1 (cost=0.00..37.61 rows=8 width=8) Index Cond: ((T1.prod_id = 42) AND (T1.fk1 = T2.fk1)) If those rowcount estimates are accurate, then this is a perfectly good plan; in fact probably the best you could hope for. regards, tom lane Rowcounts seems to be quite accurate as the tables get VACUUM FULL ANALYZEd regularly. This query plan comes from index optimization. It is the cost estimate for the nested loop that scares me a little. As of now only file system caching seems to help the timing (by a factor 10) but this in turn is a transitory effect as I have little control over FS cache. -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [EXPLAIN] Nested loops
Reg Me Please wrote: Here it comes: Aggregate (cost=227.59..227.61 rows=1 width=8) - Nested Loop (cost=0.00..227.34 rows=49 width=8) - Seq Scan on T2 (cost=0.00..1.07 rows=6 width=4) Filter: (fld1 = 'VEND'::text) - Index Scan using i_T1_partial on T1 (cost=0.00..37.61 rows=8 width=8) Index Cond: ((T1.prod_id = 42) AND (T1.fk1 = T2.fk1)) That's the EXPLAIN output, _not_ EXPLAIN ANALYZE as requested. Probably statistics aren't up-to-date? Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] refactoring: changing tables names
I need to change some table names, substitute some with views and duplicate others. As a first experiment I was planning to create a view that clone a table and then rename the occurrences of the table name with the view where needed finally change the definition of the view. Am I going to incur in any significant slowdown accessing the table indirectly? Later I'll have to rename tables that have associated sequences, pk/fk and are referenced in functions etc... etc... Any good list of advices? Is there any tools that works with postgresql that can help me? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
