[GENERAL] knowing which table/schema is going to be chosen
I may have several tables with the same name in different schema. pina.mytable, test.mytable, import.mytable I have a search_path that may not just consist of $user, public. eg. $user, public, test, import I'd like to know which table is going to be chosen if I do a select * from mytable; In this case test.mytable will be chosen. Is there a way to ask postgresql the schema of the table that will be chosen? -- 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
Re: [GENERAL] [pgeu-general] pgday.eu
On Thu, Nov 12, 2009 at 12:18 AM, Thom Brown thombr...@gmail.com wrote: I second that. I wasn't sure quite what to expect, but it was very well organised and executed. And thanks to our French hosts whose hard work really paid off too! The talks were excellent, especially Gavin M. Roy's lightning talk ;) : http://vimeo.com/7561950 Meh - that one could have gone better. Possibly if I was slightly less hungover for example (whose idea was it to have Cognac after beer, red wine and scotch anyway?) :-) Do you have any more videos? -- 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] [pgeu-general] pgday.eu
On Thu, Nov 12, 2009 at 09:41, Dave Page dp...@pgadmin.org wrote: On Thu, Nov 12, 2009 at 12:18 AM, Thom Brown thombr...@gmail.com wrote: I second that. I wasn't sure quite what to expect, but it was very well organised and executed. And thanks to our French hosts whose hard work really paid off too! The talks were excellent, especially Gavin M. Roy's lightning talk ;) : http://vimeo.com/7561950 Meh - that one could have gone better. Possibly if I was slightly less hungover for example (whose idea was it to have Cognac after beer, red wine and scotch anyway?) Wasn't that actually your idea? I would blame JD but I think he had left by then? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] [pgeu-general] pgday.eu
On Thu, Nov 12, 2009 at 8:44 AM, Magnus Hagander mag...@hagander.net wrote: On Thu, Nov 12, 2009 at 09:41, Dave Page dp...@pgadmin.org wrote: On Thu, Nov 12, 2009 at 12:18 AM, Thom Brown thombr...@gmail.com wrote: I second that. I wasn't sure quite what to expect, but it was very well organised and executed. And thanks to our French hosts whose hard work really paid off too! The talks were excellent, especially Gavin M. Roy's lightning talk ;) : http://vimeo.com/7561950 Meh - that one could have gone better. Possibly if I was slightly less hungover for example (whose idea was it to have Cognac after beer, red wine and scotch anyway?) Wasn't that actually your idea? I would blame JD but I think he had left by then? Certainly not mine (I would have stuck with the scotch) - I have a vague recollection that it may have been Simon. -- 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
[GENERAL] duplicate key violates unique constraint error
Hi All I have a cluster setup with one master and one slave . Replication from master to slave is not taking place. I am getting this error duplicate key violates unique constraint in my slon.log on slave . This error is thrown while Slon is inserting a row in a table on slave. This must be because of the reason that duplicate rows are being returned while querying sl_log_1 table. I googled about the same problem and found that there is some bug in postgres due to which some table or index on that table gets corrupted. Due to this duplicate rows are either stored in the table or there are no duplicate rows but duplicates are returned while querying the table. I am using postgres 8.1.2 and slony 1.1.5 . Please suggest which version of postgres has fix for above mentioned problem. Thanks... Tamanna
Re: [GENERAL] PostgreSQL 8.3.8 on AIX5.3 : compilation failed
Hi Tom, Laurenz and all, Adding #include postgres.h in src/bin/psql/psqlscan.c fixes the problem ! I tested the fix with PostgreSQL 8.3.8 and I would check the 8.3.9 as soon as possible. Thank you so much for your help and your rapidity to solve this question / problem !!! Have a nice day. Regards, Alexandra Tom Lane a écrit : Albe Laurenz laurenz.a...@wien.gv.at writes: Alexandra Roy wrote: Why the -disable-largefile option is necessary to do a PostgreSQL 8.3.8 32-build ? The problem will be fixed in the upcoming 8.5 release. ... and the next minor releases, eg 8.3.9. regards, tom lane -- Alexandra ROY Database Competence Center Bull, Architect of an Open World TM Phone: 04 76 29 77 01 http://www.bull.com This e-mail contains material that is confidential for the sole use of the intended recipient. Any review, reliance or distribution by others or forwarding without express permission is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies. -- 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] [pgeu-general] pgday.eu
2009/11/12 Dave Page dp...@pgadmin.org: On Thu, Nov 12, 2009 at 12:18 AM, Thom Brown thombr...@gmail.com wrote: I second that. I wasn't sure quite what to expect, but it was very well organised and executed. And thanks to our French hosts whose hard work really paid off too! The talks were excellent, especially Gavin M. Roy's lightning talk ;) : http://vimeo.com/7561950 Meh - that one could have gone better. Possibly if I was slightly less hungover for example (whose idea was it to have Cognac after beer, red wine and scotch anyway?) :-) Do you have any more videos? Yes, I've got loads, but with their bitrate being 5 megabytes per second (no, not megabits!), I have to re-encode them with a lower resolution and much lower bitrate just so I can upload them. Unfortunately some are better than others since my attention was torn between trying to record it and actually watching the talks. Thom -- 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] [pgeu-general] pgday.eu
On Thu, Nov 12, 2009 at 11:57:59AM +0200, Devrim GÜNDÜZ wrote: On Thu, 2009-11-12 at 08:41 +, Dave Page wrote: (whose idea was it to have Cognac after beer, red wine and scotch anyway?) **Perfect** idea -- but it was not me ;) YOU wasn't there! ;-) -- Andreas 'ads' Scherbaum German PostgreSQL User Group European PostgreSQL User Group - Board of Directors Volunteer Regional Contact, Germany - PostgreSQL Project PGDay.eu 2009 in Paris, Nov. 6/7, http://www.pgday.eu/ signature.asc Description: Digital signature
Re: [GENERAL] duplicate key violates unique constraint error
tamanna madaan wrote: I am getting this error duplicate key violates unique constraint in my slon.log on slave . This error is thrown while Slon is inserting a row in a table on slave. This must be because of the reason that duplicate rows are being returned while querying sl_log_1 table. And are there any errors in the PostgreSQL logs? Does this table contain duplicates? I googled about the same problem and found that there is some bug in postgres due to which some table or index on that table gets corrupted. Due to this What bug? Do you have a number or mailing-list reference? I am using postgres 8.1.2 and slony 1.1.5 . Please suggest which version of postgres has fix for above mentioned problem. I'm not sure it's clear what problem we're talking about here. However, you are missing *16* updates for PostgreSQL (8.1.18) and four for slony (1.1.9). First step - get a fresh cup of tea or coffee, read through the release notes between 8.1.2 and 8.1.18 and once you are happy upgrade. Do the same for slony. Then, if the slony sl_log table(s) have errors, I'd probably restart the whole replication from scratch. I'd never be happy that I'd found all the problems and corrected them. If you are starting the replication from base, it might make sense to upgrade slony to 1.2.17 while you're doing so. HTH -- 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] knowing which table/schema is going to be chosen
Ivan Sergio Borgonovo wrote: I have a search_path that may not just consist of $user, public. eg. $user, public, test, import I'd like to know which table is going to be chosen if I do a select * from mytable; Is there a way to ask postgresql the schema of the table that will be chosen? Hmm - I don't know of a specific function. You could do something like this though: SELECT nspname FROM pg_namespace WHERE oid = ( SELECT relnamespace FROM pg_class WHERE oid = 'mytable'::regclass::oid ); -- 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] DB Restart
Sam Jas wrote: Hi, We are facing issue with the RES memory. Below is the o/p of top command. It shows that writer process reserved 3.8g. We have observed that if it increased to 3.9g we need to restart the db. Otherwise it hangs. Kindly suggest us the good way to figure it out this issue. shared_buffer is 3 GB. Are you saying that you're running out of memory overall, or do you have a ulimit issue for the postgres user? -- 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] knowing which table/schema is going to be chosen
On Thu, 12 Nov 2009 10:38:27 + Richard Huxton d...@archonet.com wrote: Ivan Sergio Borgonovo wrote: I have a search_path that may not just consist of $user, public. eg. $user, public, test, import I'd like to know which table is going to be chosen if I do a select * from mytable; Is there a way to ask postgresql the schema of the table that will be chosen? Hmm - I don't know of a specific function. You could do something like this though: SELECT nspname FROM pg_namespace WHERE oid = ( SELECT relnamespace FROM pg_class WHERE oid = 'mytable'::regclass::oid ); This surely meet my needs, and I'm going to place it in my toolbox still... is there a way that could use information_schema? My need was caused by a compromise with 2 immature API... so I'm not surprised that a solution looks like an hack but I was wondering if in other cases knowing in advance which table postgresql is going to pick up could be a legit interest. BTW I think I've spotted an error in the docs: http://www.postgresql.org/docs/8.3/interactive/ddl-schemas.html http://www.postgresql.org/docs/8.4/interactive/ddl-schemas.html 5.7.2. The Public Schema In the previous sections we created tables without specifying any schema names. By default, such tables (and other objects) are automatically put into a schema named public. Every new database contains such a schema. Thus, the following are equivalent: ... CREATE TABLE products ( ... ); and: CREATE TABLE public.products ( ... ); I think they are not equivalent if the search_path contains the name of an existing schema. Is there anything equivalent to search_path in the SQL standard? 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
Re: [GENERAL] DB Restart
Thanks for your reply. No we are not running OOM. ulimit o/p is as below. [postg...@server1 ~]$ ulimit unlimited --- On Thu, 12/11/09, Richard Huxton d...@archonet.com wrote: From: Richard Huxton d...@archonet.com Subject: Re: [GENERAL] DB Restart To: Sam Jas samja...@yahoo.com Cc: general pgsql-general@postgresql.org Date: Thursday, 12 November, 2009, 10:39 AM Sam Jas wrote: Hi, We are facing issue with the RES memory. Below is the o/p of top command. It shows that writer process reserved 3.8g. We have observed that if it increased to 3.9g we need to restart the db. Otherwise it hangs. Kindly suggest us the good way to figure it out this issue. shared_buffer is 3 GB. Are you saying that you're running out of memory overall, or do you have a ulimit issue for the postgres user? -- 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 Connect more, do more and share more with Yahoo! India Mail. Learn more. http://in.overview.mail.yahoo.com/
Re: [GENERAL] DB Restart
Sam Jas wrote: Thanks for your reply. No we are not running OOM. ulimit o/p is as below. [postg...@server1 ~]$ ulimit unlimited Unless you are running on a 32-bit system you should be alright then. What precisely is the problem? What do you mean by the DB hangs? -- 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
[GENERAL] What is the simpliest text search configuration?
Hi all, I'd like to implement a full text search with postgresql, and I can't find a text search configuration that would just: map unicode accentuated letters to an un-accentuated equivalent tokenize the words (and skip any non word characters) no stopwords lower case the tokens How can I achieve this? I'm particularly interested in deactivating the stopwords filtering. I tried pg_catalog.simple, but despite its name, it still considers stop words. Thanks for your help! Jerome. -- Jerome Eteve. http://www.eteve.net jer...@eteve.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [pgeu-general] pgday.eu
On Thu, 2009-11-12 at 08:41 +, Dave Page wrote: (whose idea was it to have Cognac after beer, red wine and scotch anyway?) **Perfect** idea -- but it was not me ;) -- Devrim GÜNDÜZ , RHCE Professional Services, Training, 24x7 Support Authors: PostgreSQL Replicator, ODBCng, PostgreSQL RPMs, PITRTools http://www.commandprompt.com/ XMPP: dgun...@jabber.commandprompt.com Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] DB Restart
We are running on 64 - bit. Whenever the reserved memory in top command it crosses 3.9g it hangs. If we try to kill process (using pg_cancel_backend()) it won't kill. At last either we have to kill all process at OS level or we have to reboot the server. After rebooting server everything worked perfectly fine. BTW we are using postgreSQL 8.3.2. PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 4822 postgres 15 0 4045m 3.8g 3.8g S 0.7 12.1 2:09.63 postgres: writer process Thanks Sam --- On Thu, 12/11/09, Richard Huxton d...@archonet.com wrote: From: Richard Huxton d...@archonet.com Subject: Re: [GENERAL] DB Restart To: Sam Jas samja...@yahoo.com Cc: general pgsql-general@postgresql.org Date: Thursday, 12 November, 2009, 12:56 PM Sam Jas wrote: Thanks for your reply. No we are not running OOM. ulimit o/p is as below. [postg...@server1 ~]$ ulimit unlimited Unless you are running on a 32-bit system you should be alright then. What precisely is the problem? What do you mean by the DB hangs? -- Richard Huxton Archonet Ltd The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Re: [GENERAL] What is the simpliest text search configuration?
Dear Jerome, from personal experience full-text searching in PostgreSQL can be quite powerful but it's not simple, it requires thought, planning and coding. PostgreSQL mainly provides an efficient token matching mechanism supporting positional information and weights, but natural language processing and normalization is pretty basic. If you don't mind writing a couple of user-defined functions to take control of lexeme normalization, then tsvector/tsquery support can be a very powerful tool for custom search engines. regards, Michael 2009/11/12 Jérôme Etévé jerome.et...@gmail.com Hi all, I'd like to implement a full text search with postgresql, and I can't find a text search configuration that would just: map unicode accentuated letters to an un-accentuated equivalent tokenize the words (and skip any non word characters) no stopwords lower case the tokens How can I achieve this? I'm particularly interested in deactivating the stopwords filtering. I tried pg_catalog.simple, but despite its name, it still considers stop words. Thanks for your help! Jerome.
[GENERAL] re-using RETURNING
Hi,
just to be sure, it is still (8.4) not possible to use RETURNING within an
other INSERT?
To show what i mean:
test=*# insert into foo2 (id1, id2) (insert into foo(n) values
('a'),('b'),('c') returning currval('some_sequence'), id);
ERROR: syntax error at or near insert
LINE 1: insert into foo2 (id1, id2) (insert into foo(n) values ('a')...
I know, it is intended in 8.5 with the 'WITH' - clause, but not yet in 8.4,
right?
(and there are no other RDBMS which can do that?)
Thanks, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
--
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] re-using RETURNING
On Thu, Nov 12, 2009 at 1:41 PM, A. Kretschmer andreas.kretsch...@schollglas.com wrote: Hi, just to be sure, it is still (8.4) not possible to use RETURNING within an other INSERT? it is being discussed for 8.5, but as far as features go - stable versions don't change (at least change is very very very very very unlikely), so 8.4 will stay the same. -- GJ
Re: [GENERAL] DB Restart
(Please refrain from html email on the list) On Thu, Nov 12, 2009 at 12:10 AM, Sam Jas samja...@yahoo.com wrote: Hi, We are facing issue with the RES memory. Below is the o/p of top command. It shows that writer process reserved 3.8g. We have observed that if it increased to 3.9g we need to restart the db. Otherwise it hangs. Kindly suggest us the good way to figure it out this issue. shared_buffer is 3 GB. Are you sure this is what your problem really is? It's quite normal for the bgwriter to show a high res / shr number because it touches all the shared_buffers eventually. On my main db at work, where we have 8G shared_buffers, it looks like this: PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 32284 postgres 15 0 8446m 7.5g 7.5g S 0.0 23.8 2:35.10 postgres: writer process 337 postgres 15 0 8475m 7.0g 7.0g S 0.0 22.2 116:15.04 postgres: slony www 10.0.0.104(56186) idle 336 postgres 16 0 8455m 4.7g 4.7g S 4.1 14.8 148:57.29 postgres: slony www 10.0.0.104(56184) COMMIT 335 postgres 18 0 8457m 3.9g 3.9g S 0.0 12.5 598:04.23 postgres: slony www 10.0.0.104(56183) idle Note that the amount of memory used by a process on its own is RES-SHR, so that none of these processes are actually using 7.5, 7.0, 4.7 or 3.9 Gigs by themselves. PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 4822 postgres 15 0 4045m 3.8g 3.8g S 0.7 12.1 2:09.63 postgres: writer process Generally this isn't a problem. Is this 100% reproduce-able? Are you sure there's no other problem, or that maybe a checkpoint kicks in at the same time and your hung database isn't just unresponsive for a minute or so? -- 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] DB Restart
On Thu, Nov 12, 2009 at 12:10 AM, Sam Jas samja...@yahoo.com wrote: Hi, We are facing issue with the RES memory. Below is the o/p of top command. It shows that writer process reserved 3.8g. We have observed that if it increased to 3.9g we need to restart the db. Otherwise it hangs. Kindly suggest us the good way to figure it out this issue. shared_buffer is 3 GB. Oh yeah, how much memory does this machine have on it? -- 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] re-using RETURNING
2009/11/12 A. Kretschmer andreas.kretsch...@schollglas.com:
Hi,
just to be sure, it is still (8.4) not possible to use RETURNING within an
other INSERT?
To show what i mean:
test=*# insert into foo2 (id1, id2) (insert into foo(n) values
('a'),('b'),('c') returning currval('some_sequence'), id);
ERROR: syntax error at or near insert
LINE 1: insert into foo2 (id1, id2) (insert into foo(n) values ('a')...
I know, it is intended in 8.5 with the 'WITH' - clause, but not yet in 8.4,
right?
(and there are no other RDBMS which can do that?)
Well there is a little trik in actual versions...
use these options:
\a
\t
\o /tmp/archivo.csv
then
DELETE FROM tabla WHERE entero =13 RETURNING *;
(remember that you can throught this query from shell command line, with
psql options)
(next step, delete the last line of the file archivo.csv: DELETE 9890)
create a clon - void table (this will be your log table, if you have
already created this
, avoid this step):
postgres=# CREATE TABLE tabla_2 AS SELECT * FROM tabla WHERE 1=0;
SELECT
Then you can go with this
postgres=# COPY tabla_2 FROM '/tmp/returnes.csv' DELIMITER '|';
COPY 19780
postgres=# SELECT * from tabla_2 limit 1;
entero
13
(1 row)
Obviusly, you can automatize these steps in a shell script.
Hope you enjoy it.
--
Emanuel Calvo Franco
DBA at: www.siu.edu.ar
www.emanuelcalvofranco.com.ar
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is the simpliest text search configuration?
Hi Michael, I actually found that the 'simple' dictionary doesn't enforce a stopword list by default. so i defined my search conf like this and it works: create text search configuration sbsimple ( parser = 'default' ) ; alter text search configuration sbsimple ALTER MAPPING FOR word,hword,asciiword,asciihword WITH simple Cheers! J. 2009/11/12 Michael Nacos m.na...@gmail.com: Dear Jerome, from personal experience full-text searching in PostgreSQL can be quite powerful but it's not simple, it requires thought, planning and coding. PostgreSQL mainly provides an efficient token matching mechanism supporting positional information and weights, but natural language processing and normalization is pretty basic. If you don't mind writing a couple of user-defined functions to take control of lexeme normalization, then tsvector/tsquery support can be a very powerful tool for custom search engines. regards, Michael 2009/11/12 Jérôme Etévé jerome.et...@gmail.com Hi all, I'd like to implement a full text search with postgresql, and I can't find a text search configuration that would just: map unicode accentuated letters to an un-accentuated equivalent tokenize the words (and skip any non word characters) no stopwords lower case the tokens How can I achieve this? I'm particularly interested in deactivating the stopwords filtering. I tried pg_catalog.simple, but despite its name, it still considers stop words. Thanks for your help! Jerome. -- Jerome Eteve. http://www.eteve.net jer...@eteve.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] duplicate key violates unique constraint error
On Wed, Nov 11, 2009 at 10:12 PM, tamanna madaan tamanna.ma...@globallogic.com wrote: Hi All I have a cluster setup with one master and one slave . Replication from master to slave is not taking place. I am getting this error “duplicate key violates unique constraint” in my slon.log on slave . This error is thrown while Slon is inserting a row in a table on slave. This must be because of the reason that duplicate rows are being returned while querying sl_log_1 table. I googled about the same problem and found that there is some bug in postgres due to which some table or index on that table gets corrupted. Due to this If your underlying file system is unreliable and results in a corrupted index that allows postgresql to insert duplicate rows, that is NOT a bug in postgresql, it is a failing in your hardware / OS that you need to get fixed. Pgsql can't be blamed for errors created by bad hardware, and no amount of coding can overcome that deficit. -- 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] re-using RETURNING
On 12/11/2009 9:41 PM, A. Kretschmer wrote: Hi, just to be sure, it is still (8.4) not possible to use RETURNING within an other INSERT? Not directly, now. However, if I recall correctly in 8.4 you *CAN* use a ... RETURNING statement within an SQL function, and use the results of that in another query. -- Craig Ringer -- 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] DB Restart
Sam Jas wrote: We are running on 64 - bit. Whenever the reserved memory in top command it crosses 3.9g it hangs. If we try to kill process (using pg_cancel_backend()) it won't kill. At last either we have to kill all process at OS level or we have to reboot the server. After rebooting server everything worked perfectly fine. BTW we are using postgreSQL 8.3.2. PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 4822 postgres 15 0 4045m 3.8g 3.8g S 0.7 12.1 2:09.63 postgres: writer process Well, this process won't respond to pg_cancel_backend() because it's not a normal backend. It's the background-writer process. Do you see the same problem with ordinary backend processes, or is it always the writer? Also - are you familiar with strace? -- 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] knowing which table/schema is going to be chosen
Ivan Sergio Borgonovo m...@webthatworks.it writes: BTW I think I've spotted an error in the docs: http://www.postgresql.org/docs/8.3/interactive/ddl-schemas.html http://www.postgresql.org/docs/8.4/interactive/ddl-schemas.html 5.7.2. The Public Schema In the previous sections we created tables without specifying any schema names. By default, such tables (and other objects) are automatically put into a schema named public. Every new database contains such a schema. Thus, the following are equivalent: ... By default means assuming you're using the default search_path. However saying that here, where we haven't explained search_path yet, wouldn't be an improvement IMO. 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] What is the simpliest text search configuration?
=?UTF-8?B?SsOpcsO0bWUgRXTDqXbDqQ==?= jerome.et...@gmail.com writes: I'd like to implement a full text search with postgresql, and I can't find a text search configuration that would just: map unicode accentuated letters to an un-accentuated equivalent tokenize the words (and skip any non word characters) no stopwords lower case the tokens How can I achieve this? I'm particularly interested in deactivating the stopwords filtering. I tried pg_catalog.simple, but despite its name, it still considers stop words. What's wrong with specifying an empty stopword list? (To me, removing accents is already past what I'd expect of a simple configuration, so I doubt you're going to find a dictionary that provides exactly that set of features and no other ones.) 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] What is the simpliest text search configuration?
We submitted unaccent dictionary for 8.5 See http://www.sai.msu.su/~megera/wiki/unaccent for some information Oleg On Thu, 12 Nov 2009, Jrme Etv wrote: Hi Michael, I actually found that the 'simple' dictionary doesn't enforce a stopword list by default. so i defined my search conf like this and it works: create text search configuration sbsimple ( parser = 'default' ) ; alter text search configuration sbsimple ALTER MAPPING FOR word,hword,asciiword,asciihword WITH simple Cheers! J. 2009/11/12 Michael Nacos m.na...@gmail.com: Dear Jerome, from personal experience full-text searching in PostgreSQL can be quite powerful but it's not simple, it requires thought, planning and coding. PostgreSQL mainly provides an efficient token matching mechanism supporting positional information and weights, but natural language processing and normalization is pretty basic. If you don't mind writing a couple of user-defined functions to take control of lexeme normalization, then tsvector/tsquery support can be a very powerful tool for custom search engines. regards, Michael 2009/11/12 JЪЪrЪЪme EtЪЪvЪЪ jerome.et...@gmail.com Hi all, I'd like to implement a full text search with postgresql, and I can't find a text search configuration that would just: map unicode accentuated letters to an un-accentuated equivalent tokenize the words (and skip any non word characters) no stopwords lower case the tokens How can I achieve this? I'm particularly interested in deactivating the stopwords filtering. I tried pg_catalog.simple, but despite its name, it still considers stop words. Thanks for your help! Jerome. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] re-using RETURNING
A. Kretschmer andreas.kretsch...@schollglas.com wrote: Hi, just to be sure, it is still (8.4) not possible to use RETURNING within an other INSERT? Thx for all replies. It is not a really problem, i will write a benchmark to compare the new writeable CTE (in 8.5 alpha) with the old style (8.4). That's all ;-) And yes, i will publish the result, of course. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] [pgeu-general] pgday.eu
Devrim GÜNDÜZ escribió: On Thu, 2009-11-12 at 08:41 +, Dave Page wrote: (whose idea was it to have Cognac after beer, red wine and scotch anyway?) **Perfect** idea -- but it was not me ;) Maybe JD? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [pgeu-general] pgday.eu
2009/11/12 Alvaro Herrera alvhe...@commandprompt.com: Devrim GÜNDÜZ escribió: On Thu, 2009-11-12 at 08:41 +, Dave Page wrote: (whose idea was it to have Cognac after beer, red wine and scotch anyway?) **Perfect** idea -- but it was not me ;) Maybe JD? Hahahahaha. He went back to his hotel early, unable to cope with a European party :-p http://img132.yfrog.com/i/3e9o.jpg/ -- 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] [pgeu-general] pgday.eu
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ok a *bit* off-topic here... ;-) But who's this? He reminds me after a programming maraton! :-D Or after a cognac after red wine! :-P Whoever your are... Cheers Mate! ;) BR, Pedro On 11/12/2009 05:24 PM, Dave Page wrote: 2009/11/12 Alvaro Herrera alvhe...@commandprompt.com: Devrim GÜNDÜZ escribió: On Thu, 2009-11-12 at 08:41 +, Dave Page wrote: (whose idea was it to have Cognac after beer, red wine and scotch anyway?) **Perfect** idea -- but it was not me ;) Maybe JD? Hahahahaha. He went back to his hotel early, unable to cope with a European party :-p http://img132.yfrog.com/i/3e9o.jpg/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/ iEYEARECAAYFAkr8SIoACgkQ2FH5GXCfxAtxpwCgrN7s4a1hGMa2MfBeYQoYREYR ixEAnRWuvs6D1KIB+96ZPUTVDgOzpZ5q =BZI1 -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [pgeu-general] pgday.eu
On Thu, 2009-11-12 at 17:24 +, Dave Page wrote: **Perfect** idea -- but it was not me ;) Maybe JD? Hahahahaha. He went back to his hotel early, unable to cope with a European party :-p http://img132.yfrog.com/i/3e9o.jpg/ *sigh* I really should have been there :-) -- Devrim GÜNDÜZ , RHCE Professional Services, Training, 24x7 Support Authors: PostgreSQL Replicator, ODBCng, PostgreSQL RPMs, PITRTools http://www.commandprompt.com/ XMPP: dgun...@jabber.commandprompt.com Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] upgrade to 8.4 with new character set
Kevin Duffy wrote: We originally built our database with a character set of SQL_ASCII. We now know the error of our foolish ways. We are trying to upgrade to 8.4 and the windows binaries would like us to use UTF8. I would like to use a backup to restore onto the new installation, however I am getting warnings and errors about how I am trying to restore a SQL_ASCII character set to a UTF8 Database. I need to have confidence that my data is intact before I can continue with the upgrade. I was wondering what would be the best course of action. I currently have the 8.4 database installed on a separate machine for testing. Thank you for all your help Jonathan Schindler This is what I did: Download install iconv for windows from gnuwin32.sourceforge.net Backup the 8.3 database using the plain format e.g. pg_dump -F p -f backup.ascii -U postgres mydb Run Iconv on the backup to convert to your new dataset (you may need to use a different source encoding - but ISO-8859-1 works for me!) e.g. c:\program files\gnuwin32\bin\iconv.exe -f ISO-8859-1 -t UTF-8 backup.ascii backup.utf-8 Create a new db in UTF-8 encoding (newdb) Now restore to your 8.4 database e.g. psql -U postgres -f backup.utf-8 newdb The only problem I've had so far is that some of my data was a streamed xml file into a text variable, which stupidly had some byte order marks at the beginning which became nonsense after converting to UTF-8. These were removed with a SQL query but hopefully this shouldn't happen to most people. Hope this helps. Howard Cole www.selestial.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] upgrade to 8.4 with new character set
Kevin Duffy wrote: We are trying to upgrade to 8.4 and the windows binaries would like us to use UTF8. Jonathan Schindler I would advise caution upgrading to 8.4 on windows as it seems to cause more of the random winsock 10061 error. Which in my experience happens hundreds of times a day. Howard Cole www.selestial.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] DB Restart
Sam Jas wrote: ... BTW we are using postgreSQL 8.3.2. red flag. 8.3 is up to 8.3.8, lots of updates since 8.3.2 I attach the release notes for these incremental revisions. note there are gobs of fixes in many of these releases.you can upgrade 8.3.2 directly to 8.3.8, with only the caveat that you should reindex any GiST indexes due to a fix in 8.3.5 E.6. Release 8.3.3 *Release date: *2008-06-12 This release contains one serious and one minor bug fix over 8.3.2. For information about new features in the 8.3 major release, see Section E.9 release-8-3.html. E.6.1. Migration to Version 8.3.3 A dump/restore is not required for those running 8.3.X. However, if you are upgrading from a version earlier than 8.3.1, see the release notes for 8.3.1. E.6.2. Changes * Make |pg_get_ruledef()| parenthesize negative constants (Tom) Before this fix, a negative constant in a view or rule might be dumped as, say, -42::integer, which is subtly incorrect: it should be (-42)::integer due to operator precedence rules. Usually this would make little difference, but it could interact with another recent patch to cause PostgreSQL to reject what had been a valid SELECT DISTINCT view query. Since this could result inpg_dump output failing to reload, it is being treated as a high-priority fix. The only released versions in which dump output is actually incorrect are 8.3.1 and 8.2.7. * Make ALTER AGGREGATE ... OWNER TO update pg_shdepend (Tom) This oversight could lead to problems if the aggregate was later involved in a DROP OWNED or REASSIGN OWNED operation. E.5. Release 8.3.4 *Release date: *2008-09-22 This release contains a variety of fixes from 8.3.3. For information about new features in the 8.3 major release, see Section E.9 release-8-3.html. E.5.1. Migration to Version 8.3.4 A dump/restore is not required for those running 8.3.X. However, if you are upgrading from a version earlier than 8.3.1, see the release notes for 8.3.1. E.5.2. Changes * Fix bug in btree WAL recovery code (Heikki) Recovery failed if the WAL ended partway through a page split operation. * Fix potential use of wrong cutoff XID for HOT page pruning (Alvaro) This error created a risk of corruption in system catalogs that are consulted by VACUUM: dead tuple versions might be removed too soon. The impact of this on actual database operations would be minimal, since the system doesn't follow MVCC rules while examining catalogs, but it might result in transiently wrong output from pg_dump or other client programs. * Fix potential miscalculation of datfrozenxid (Alvaro) This error may explain some recent reports of failure to remove old pg_clog data. * Fix incorrect HOT updates after pg_class is reindexed (Tom) Corruption of pg_class could occur if REINDEX TABLE pg_class was followed in the same session by an ALTER TABLE RENAME orALTER TABLE SET SCHEMA command. * Fix missed combo cid case (Karl Schnaitter) This error made rows incorrectly invisible to a transaction in which they had been deleted by multiple subtransactions that all aborted. * Prevent autovacuum from crashing if the table it's currently checking is deleted at just the wrong time (Alvaro) * Widen local lock counters from 32 to 64 bits (Tom) This responds to reports that the counters could overflow in sufficiently long transactions, leading to unexpected lock is already held errors. * Fix possible duplicate output of tuples during a GiST index scan (Teodor) * Regenerate foreign key checking queries from scratch when either table is modified (Tom) Previously, 8.3 would attempt to replan the query, but would work from previously generated query text. This led to failures if a table or column was renamed. * Fix missed permissions checks when a view contains a simple UNION ALL construct (Heikki) Permissions for the referenced tables were checked properly, but not permissions for the view itself. * Add checks in executor startup to ensure that the tuples produced by an INSERT or UPDATE will match the target table's current rowtype (Tom) This situation is believed to be impossible in 8.3, but it can happen in prior releases, so a check seems prudent. * Fix possible repeated drops during DROP OWNED (Tom) This would typically result in strange errors such as cache lookup failed for relation NNN. * Fix several memory leaks in XML operations (Kris Jurka, Tom) * Fix |xmlserialize()| to raise error properly for unacceptable target data type (Tom) * Fix a couple of places that mis-handled multibyte characters in text search
Re: [GENERAL] [pgeu-general] pgday.eu
It's Joshua Drake, aka JD. On 11/12/09, Pedro Doria Meunier pdo...@netmadeira.com wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ok a *bit* off-topic here... ;-) But who's this? He reminds me after a programming maraton! :-D Or after a cognac after red wine! :-P Whoever your are... Cheers Mate! ;) BR, Pedro On 11/12/2009 05:24 PM, Dave Page wrote: 2009/11/12 Alvaro Herrera alvhe...@commandprompt.com: Devrim GÜNDÜZ escribió: On Thu, 2009-11-12 at 08:41 +, Dave Page wrote: (whose idea was it to have Cognac after beer, red wine and scotch anyway?) **Perfect** idea -- but it was not me ;) Maybe JD? Hahahahaha. He went back to his hotel early, unable to cope with a European party :-p http://img132.yfrog.com/i/3e9o.jpg/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/ iEYEARECAAYFAkr8SIoACgkQ2FH5GXCfxAtxpwCgrN7s4a1hGMa2MfBeYQoYREYR ixEAnRWuvs6D1KIB+96ZPUTVDgOzpZ5q =BZI1 -END PGP SIGNATURE- -- Sent via pgeu-general mailing list (pgeu-gene...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgeu-general -- 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] [pgeu-general] pgday.eu
On Thu, 2009-11-12 at 18:47 +, Dave Page wrote: It's Joshua Drake, aka JD. On Thu, 2009-11-12 at 08:41 +, Dave Page wrote: (whose idea was it to have Cognac after beer, red wine and scotch anyway?) **Perfect** idea -- but it was not me ;) Maybe JD? Hahahahaha. He went back to his hotel early, unable to cope with a European party :-p http://img132.yfrog.com/i/3e9o.jpg/ I had no idea that European beer was uhmm, twice as strong as US counterparts. I knew I was in trouble when people (Dave Page) poured Scotch into my water. It was time to go before I got into a LOT of trouble :P Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander -- 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] [pgeu-general] pgday.eu
On 12/11/2009 19:01, Joshua D. Drake wrote: I had no idea that European beer was uhmm, twice as strong as US counterparts. I knew I was in trouble when people (Dave Page) poured Scotch into my water. It was time to go before I got into a LOT of trouble :P Hmmmit's usually supposed to be the other way around - water into Scotch, I mean. Mind you, a lot of people would say that mixing Scotch and water is a mortal sin to begin with. :-) Dave seemed to have an endless supply of those little drinks tickets - it would have been *very* easy to get into trouble! Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Full Text Search 101?
Hi everyone, I posted to this forum once before and was able to receive help. Thanks again! I'm trying to implement full text search capabilities. Basically, I have a very simple data catalog type of website (http:// gis.drcog.org/datacatalog), where the user can type in a word or words to search for records that match the criteria. I have a table with a few fields, but I want to be able to search/index two fields -- the name of the dataset and the description. (or more, if I can index fields from other tables too, that would be great). I'd like to be able to use a full text search to rank the results in terms of relevance. What is the best way to handle this? I've read through the PostgreSQL documentation and don't quite understand it although I'm trying to understand and am 'playing around' with this on a development server. Is the default text search configuration enough? I've tried setting my own configuration but get errors about not finding .dict dictionary files? I have a default install of PostgreSQL 8.4. Also, I've created a tsvector column and created indexes, but it didn't seem to delete stop words from the indexes. Should the stop words be indexed? Also, I don't quite understand how to create indexes but rank certain words as being more important than others, for instance, maybe having words that come from the name column carrying more importance than words coming from the description column. Finally, I'd like watersheds to come up when someone searches for water so I don't think I have this configured properly, because this record is not returned. Is there a good tutorial or maybe documentation that is a bit easier to understand? I know my database is far from complicated but I can't seem to find a configuration that works well. When I try ranking my results, most of the results end up with the same rank, so something must be wrong? Can someone point me in the right direction? Thanks for the help. I appreciate it. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Full Text Search 101?
there's a lot of material on this site:
http://www.sai.msu.su/~megera/wiki/tsearch2slides
how do you create your tsvectors? If all your results have the same score
(assuming you are using two or more tokens in your tsquery) it might be that
your tsvector lacks positional information ('a b'::tsvector vs 'a:1,5
b:3'::tsvector) and/or weights. I have experienced similar behaviour when
doing searches against such tsvectors. Using rank_cd instead of rank might
help you even then, I think. My understanding is that if you want to use
weights in your tsvectors you have to set them yourself (you may concatenate
tsvectors from different locations if you want, after using set_weight on
some of them).
hope this helps a bit, Michael
2009/11/12 Jonathan jharah...@gmail.com
Hi everyone,
I posted to this forum once before and was able to receive help.
Thanks again!
I'm trying to implement full text search capabilities. Basically, I
have a very simple data catalog type of website (http://
gis.drcog.org/datacatalog), where the user can type in a word or words
to search for records that match the criteria. I have a table with a
few fields, but I want to be able to search/index two fields -- the
name of the dataset and the description. (or more, if I can index
fields from other tables too, that would be great). I'd like to be
able to use a full text search to rank the results in terms of
relevance.
What is the best way to handle this? I've read through the PostgreSQL
documentation and don't quite understand it although I'm trying to
understand and am 'playing around' with this on a development server.
Is the default text search configuration enough? I've tried setting my
own configuration but get errors about not finding .dict dictionary
files? I have a default install of PostgreSQL 8.4.
Also, I've created a tsvector column and created indexes, but it
didn't seem to delete stop words from the indexes. Should the stop
words be indexed?
Also, I don't quite understand how to create indexes but rank certain
words as being more important than others, for instance, maybe having
words that come from the name column carrying more importance than
words coming from the description column. Finally, I'd like
watersheds to come up when someone searches for water so I don't
think I have this configured properly, because this record is not
returned.
Is there a good tutorial or maybe documentation that is a bit easier
to understand? I know my database is far from complicated but I can't
seem to find a configuration that works well. When I try ranking my
results, most of the results end up with the same rank, so something
must be wrong?
Can someone point me in the right direction?
Thanks for the help. I appreciate it.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Full Text Search 101?
Hi Jonathan;
I posted to this forum once before and was able to receive help.
Thanks again!
I'm trying to implement full text search capabilities. Basically, I
have a very simple data catalog type of website (http://
gis.drcog.org/datacatalog), where the user can type in a word or words
to search for records that match the criteria. I have a table with a
few fields, but I want to be able to search/index two fields -- the
name of the dataset and the description. (or more, if I can index
fields from other tables too, that would be great). I'd like to be
able to use a full text search to rank the results in terms of
relevance.
You can set up an index per table, but I've found that having a single
vector table with bridge tables to the data I want to search works well.
This would be in the style of:
create table vectors (
id serial primary key,
vector tsvector not null
);
create table interesting_data (
id serial primary key,
textual text not null
);
CREATE TABLE vector_to_interesting (
v_id int not null references vectors(id),
i_id int not null references interesting_data(id)
);
And then
SELECT id.*
FROM interesting_data itd,
vectors v,
vector_to_interesting itv
WHERE v.id = itv.v_id
AND itv.i_id = itd.id
AND v.vector @@ to_tsquery('searchpattern');
Allowing for some procedures around that that return multiple row types,
to the client software.
What is the best way to handle this? I've read through the PostgreSQL
documentation and don't quite understand it although I'm trying to
understand and am 'playing around' with this on a development server.
Is the default text search configuration enough? I've tried setting my
own configuration but get errors about not finding .dict dictionary
files? I have a default install of PostgreSQL 8.4.
Also, I've created a tsvector column and created indexes, but it
didn't seem to delete stop words from the indexes. Should the stop
words be indexed?
The default english configuration in PostgreSQL should have done this.
Building your own configuration tends towards being a bit more advanced,
and english should suit most needs.
Also, I don't quite understand how to create indexes but rank certain
words as being more important than others, for instance, maybe having
words that come from the name column carrying more importance than
words coming from the description column.
For part of this, there's ts_rank(tsvector, tsquery), as well as the
relative weighting system in the textsearch modules. Unfortunately, I
don't have much experience with the relative weighting, but, ts_rank()
should get you partly there.
I also found an interesting page on relative weighting @
http://www.postgresonline.com/special_feature.php?sf_name=postgresql83tsearch_cheatsheetoutputformat=html
that might be useful for you.
Finally, I'd like
watersheds to come up when someone searches for water so I don't
think I have this configured properly, because this record is not
returned.
Well, there's two ways to achieve this.
The first is to manually the stopwords, which is getting into the custom
configuration and a bit on the more advanced side.
The other is that in PG8.4, you can do
to_tsquery('water:*')
which will tell the search parser to do a partial match, which would
return watershed, in this instance.
My testing has shown it to be a bit slower (30ms, vs 15ms for a
non-partial search), but not egregiously slow.
Is there a good tutorial or maybe documentation that is a bit easier
to understand? I know my database is far from complicated but I can't
seem to find a configuration that works well. When I try ranking my
results, most of the results end up with the same rank, so something
must be wrong?
Can someone point me in the right direction?
Thanks for the help. I appreciate it.
Hope this helps. :)
Regards,
--
Aurynn Shaw
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 103
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
as...@commandprompt.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] [pgeu-general] pgday.eu
I had no idea that European beer was uhmm, twice as strong as US counterparts. I knew I was in trouble when people (Dave Page) poured Scotch into my water. It was time to go before I got into a LOT of trouble :P Joshua D. Drake btw, the normal beer is twice as strong as the US one (around 5.4% here usually). you can also get bock bier (as we call it in austria), however. it is in the area of 12% :). THIS is beer *g*. you need just the fingers of one hand to count if you had more than enough *g*. i can send you a bottle, if you want. hans -- Cybertec Schoenig Schoenig GmbH Reyergasse 9 / 2 A-2700 Wiener Neustadt Web: www.postgresql-support.de -- 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] [pgeu-general] pgday.eu
On Thu, Nov 12, 2009 at 4:26 PM, Hans-Juergen Schoenig h...@cybertec.at wrote: I had no idea that European beer was uhmm, twice as strong as US counterparts. I knew I was in trouble when people (Dave Page) poured Scotch into my water. It was time to go before I got into a LOT of trouble :P Joshua D. Drake btw, the normal beer is twice as strong as the US one (around 5.4% here usually). you can also get bock bier (as we call it in austria), however. it is in the area of 12% :). THIS is beer *g*. you need just the fingers of one hand to count if you had more than enough *g*. Not that I'd want to discourage anyone sending beer to anyone, but... Though perhaps well outside the mainstream US beer drinkers normal experience there are a good variety of barley wines, Trappist type ales, bocks and doppelbocks available in the US. The whole category of high test beer is a fav of mine, and I'd agree you shouldn't need more than a couple of fingers to know when you've had enough. Personally, I'd say it's more like: select limit from beer where type in ( 'Barley wine', 'Trappist', 'Bock', 'Doppelbock' ) limit -- 1 -- Peter Hunsberger -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Full Text Search 101?
Just wanted to thank everyone for the input so far. I do appreciate it! I'm going to read through some of this tonight and see what I can do! Thanks! Jonathan -- 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] Postgres Clustering Options
On Thu, Nov 12, 2009 at 07:50:06AM +0200, Mikko Partio wrote: - On Wed, Nov 11, 2009 at 7:28 PM, David Kerr d...@mr-paradox.net wrote: - basically point to using a replication based solution, which i don't think - would meet my - performance demands. - - Does anyone have expereince with this or a similar setup that they could - share with me? - - - - We have done a setup like this with Red Hat Cluster Suite. - - We are quite happy with the setup in general, and it has been working well - even in 'unexpected circumstances' (power outages etc). The only thing I'd - change in this setup if I could is the cluster software: RHCS is not mature - enough and it seems every release contains new critical bugs, and sometimes - even mission-critical components such as quorum disk do not work after an - upgrade. - - Regards - - Mikko Hi Mikko, In your enviornment, are the applications able to recover automatically after a DB failover? For exmaple, we're using Java/JDBC connections +Geronimo we're researching whether or not JDBC/Geronimo would be able to retry in the case of losing a connection to the DB vs failing and crashing the app. Dave -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgmemcache vs pgmemcached
Hi, Have anyone tried pgmemcache and pgmemcached? What is the difference betweent he two? They are both non-persistent, so I have difficult time deciding which one to use. Any suggestion? Thanks, jb
Re: [GENERAL] Postgres Clustering Options
On Wednesday 11 November 2009, David Kerr said something like: I'm trying to meet a very high uptime requirement in a high performance environment. If you don't mind Xen, have you considered: http://dsg.cs.ubc.ca/remus/ System mirroring/hot standby, with instant failover, complete with any open TCP/IP connections, etc. With paravirtualization, the performance hit shouldn't be very big. j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0x14EA086E -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Libpq binary mode SELECT ... WHERE ID IN ($1) Question
Hello,
Suppose I have a table:
create table foo ( id int4, name varchar(50) );
I want to prepare and execute in binary mode:
select name from foo where id in ($1);
Execute works when I have a single value for $1, and treat it as a
normal INT4. However, when I attempt to send multiple values for the
parameter, I get no meaningful results.
My code snippets are below.
When setting up an array of numbers to pass as a parameter, is there
something special that needs to be done? The documentation is quite
vague; I did poke around the source and found in contrib some int_array
code, but don't fully understand it.
I suspect I need to do something like ...
(a) when I prepare, do something to tell postgres that I will have an
array of values, instead of a single value, and/or
(b) perhaps encapsulate the array of integers in some manner.
If I could be pointed to an example or documentation, it would be much
appreciated.
Thanks,
Matt Sanchez
The prepare code snippet:
Oid oids[1] = { 23 }; //INT4OID
result = PQprepare( pgconn, getname,
select name from foo where id in ($1)
1, oids );
The execute code snippet:
int ids[4] = { 3, 5, 6, 8 };// param values we want to setup
/* convert numbers to network byte order ... */
char * paramvalues[1] = (char *) ids;
int paramlengths[1];
int paramformats[1] = { 1 };
paramlengths[0] = = 4 * sizeof( int );
result = PQexecPrepared( pgconn,
getname, // statement name
1, // number of params
paramvalues,
paramlenths,
paramformats,
1 );
--
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] Postgres Clustering Options
On Fri, Nov 13, 2009 at 1:47 AM, David Kerr d...@mr-paradox.net wrote: In your enviornment, are the applications able to recover automatically after a DB failover? For exmaple, we're using Java/JDBC connections +Geronimo we're researching whether or not JDBC/Geronimo would be able to retry in the case of losing a connection to the DB vs failing and crashing the app. Since the system is active/passive, a failover *will* disconnect all sessions. We have coded our applications so that if they lose connection to the database, they will automatically try to reconnect. Regards Mikko
Re: [GENERAL] Libpq binary mode SELECT ... WHERE ID IN ($1) Question
You could try to prepare a query like this:
select name from foo where id=any($1);
and then pass the array of integers as $1 (although, I don't know how
you can do that as I've never used the C interface of libpq).
On Fri, Nov 13, 2009 at 1:34 AM, Matt Sanchez matt-sanc...@comcast.net wrote:
Hello,
Suppose I have a table:
create table foo ( id int4, name varchar(50) );
I want to prepare and execute in binary mode:
select name from foo where id in ($1);
Execute works when I have a single value for $1, and treat it as a
normal INT4. However, when I attempt to send multiple values for the
parameter, I get no meaningful results.
My code snippets are below.
When setting up an array of numbers to pass as a parameter, is there
something special that needs to be done? The documentation is quite
vague; I did poke around the source and found in contrib some int_array
code, but don't fully understand it.
I suspect I need to do something like ...
(a) when I prepare, do something to tell postgres that I will have an
array of values, instead of a single value, and/or
(b) perhaps encapsulate the array of integers in some manner.
If I could be pointed to an example or documentation, it would be much
appreciated.
Thanks,
Matt Sanchez
The prepare code snippet:
Oid oids[1] = { 23 }; //INT4OID
result = PQprepare( pgconn, getname,
select name from foo where id in ($1)
1, oids );
The execute code snippet:
int ids[4] = { 3, 5, 6, 8 }; // param values we want to setup
/* convert numbers to network byte order ... */
char * paramvalues[1] = (char *) ids;
int paramlengths[1];
int paramformats[1] = { 1 };
paramlengths[0] = = 4 * sizeof( int );
result = PQexecPrepared( pgconn,
getname, // statement name
1, // number of params
paramvalues,
paramlenths,
paramformats,
1 );
--
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
