Re: [SQL] Efficient Searching of Large Text Fields
Don't invent a wheel and use contrib/tsearch2 for that. On Tue, 13 Jun 2006, Aaron Bono wrote: In another post on a different topic, Rod Taylor said the following: "A\tcat in the\nhat" might be stored as ARRAY['A', 'cat', 'in', 'the', 'hat']. This got me thinking. I have a discussion forum for gamers and want to provide searching capabilities so the user can type in a phrase like "magical bow" and get all posts, sorted by relevance that contain these words. My questions are: 1. Will storing the posts in an ARRAY help improve performance of these searches? If so, by how much? 2. What functions or libraries are available to make such searching easy to implement well? 3. What is the best way to sort by relevance? Thanks, Aaron Bono ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Efficient Searching of Large Text Fields
O Oleg Bartunov έγραψε στις Jun 14, 2006 : > Don't invent a wheel and use contrib/tsearch2 for that. Hi Oleg, i just wanted to ask if anything close to exact phrase matching could be deployed/implemented with tsearch2. > > On Tue, 13 Jun 2006, Aaron Bono wrote: > > > In another post on a different topic, Rod Taylor said the following: > > > > "A\tcat in the\nhat" might be stored as ARRAY['A', 'cat', 'in', 'the', > > 'hat']. > > > > This got me thinking. I have a discussion forum for gamers and want > > to provide searching capabilities so the user can type in a phrase > > like "magical bow" and get all posts, sorted by relevance that contain > > these words. > > > > My questions are: > > 1. Will storing the posts in an ARRAY help improve performance of > > these searches? If so, by how much? > > 2. What functions or libraries are available to make such searching > > easy to implement well? > > 3. What is the best way to sort by relevance? > > > > Thanks, > > Aaron Bono > > > > ---(end of broadcast)--- > > TIP 3: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > > > Regards, > Oleg > _ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > -- -Achilleus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Efficient Searching of Large Text Fields
On Tue, 13 Jun 2006, Aaron Bono wrote: I will look that tsearch (at . It appears their 8.1.x version is still in development and I use PostgreSQL 8.1.3 but it is worth trying - I'm not in a hurry for that feature anyway. I also looked at PHPBB a little - it appears their database stores words but the code is so difficult to dig through I was not sure about their implementation or even what they used it for. Would it be worth the work to save the text into a separate searchable table that kept individual words and word counts or would that be more work and eat up more space than it is worth? You could actually index the words that way and get much quicker searches. Then again, as I read through tsearch, it may make this approach unnecessary... I have also seen what looks like people using search results tables that, after a search is performed, save a list of the results. For example, if I were doing a search of a forum, I could save the search in a table like this: forum_topic forum_topic_id (PK) forum_topic_name etc... forum_topic_search forum_topic_search_id (PK) forum_topic_search_dt forum_topic_search_desc forum_topic_search_results forum_topic_search_results_id (PK) forum_topic_search_id (FK) sort_index (int to tell us the order the results are returned in) forum_topic_id (FK) This way you can allow users to page through the results without having to constantly research or cache the results somewhere in memory. Has anyone tried an approach like this? When do you clean these search tables out? They could get quite large after a while. You might be surprized, but queries in general are very similar. Analyze your search log, after normalization you could estimate the total number of distinct queries. We developed search daemon for the big blog and it worked quite well. Thanks! Aaron On 6/13/06, Rod Taylor <[EMAIL PROTECTED]> wrote: It won't help at all. Fast partial matches against arrays is nearly impossible. You might take a look at tsearch though. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] how to replace 0xe28093 char with another one?
hi. When I execute "SELECT specification FROM armature WHERE id = 96;" query I get WARNING: ignoring unconvertible UTF-8 character 0xe28093. How can I replace this (0xe28093) char with another one? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Prepared statements in PGSQL functions
Hi Listers,
I want to use prepared statement in a function. Here is my code:
create or replace function generate_data
( integer, integer )
returns integer
as
$BODY$
declare
p_count alias for $1;
p_max_value_id1 alias for $2;
v_max_value_id1 integer ;
v_id1 int;
v_id2 int;
v_filler varchar(200) := repeat('BIGSTRING', 3);
begin
v_id1:= round( (random()* v_max_value_id1)::bigint,0);
v_id2:= round( (random()* v_max_value_id1)::bigint,0);
prepare mystmt( int, int, varchar) as insert into part
values ($1,$2,$3);
execute mystmt(v_id1, v_id2, v_filler );
deallocate mystmt;
end;
$BODY$
language plpgsql ;
Definition of table part is :
CREATE TABLE part (
id1int not null,
id2int not null,
filler varchar(200)
);
When I try to call my function I am getting the following errors :
postgres=# select * from gen (10, 10 );
ERROR: function mystmt(integer, integer, character varying) does not exist
HINT: No function matches the given name and argument types. You may need to
add explicit type casts.
CONTEXT: SQL statement "SELECT mystmt( $1 , $2 , $3 )"
PL/pgSQL function "gen" line 12 at execute statement
How to solve my problem ? Is it possible at all to call prepared statement
inside a function at all?
Regards. MILEN
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Prepared statements in PGSQL functions
am 14.06.2006, um 15:12:36 +0200 mailte Milen Kulev folgendes: > How to solve my problem ? Is it possible at all to call prepared statement > inside a function at all? Yes, i have a example: create or replace function foo() returns text as $$ declare sql text; begin sql := 'prepare bla(int) as select now();'; execute sql; sql := 'execute bla(1);'; execute sql; return 'ready'; end $$ language plpgsql; test=*# select foo(); foo --- ready (1 row) You should execute strings in plpgsql, not prepared statements. HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] how to replace 0xe28093 char with another one?
That character is the EN Dash. Are you by chance copying and pasting from MS Word or some other program that does smart replace while you type? I don't see this character in your select. Is there something else that is running that may be causing this problem? -Aaron On 6/14/06, Sergey Levchenko <[EMAIL PROTECTED]> wrote: hi. When I execute "SELECT specification FROM armature WHERE id = 96;" query I get WARNING: ignoring unconvertible UTF-8 character 0xe28093. How can I replace this (0xe28093) char with another one? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Prepared statements in PGSQL functions
"Milen Kulev" <[EMAIL PROTECTED]> writes: > I want to use prepared statement in a function. Why? You seem not to be aware that plpgsql implicitly prepares statements behind the scenes. > prepare mystmt( int, int, varchar) as insert into part > values ($1,$2,$3); > execute mystmt(v_id1, v_id2, v_filler ); > deallocate mystmt; If that worked it would be *exactly* the same as just doing insert into part values (v_id1, v_id2, v_filler); except for being slower due to re-preparing each time through the function. So don't waste your time trying to outsmart the language. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Prepared statements in PGSQL functions
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Wednesday, June 14, 2006 4:35 PM To: Milen Kulev Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Prepared statements in PGSQL functions "Milen Kulev" <[EMAIL PROTECTED]> writes: >> I want to use prepared statement in a function (your comments below). Wanted just to test the difference ... Why? You seem not to be aware that plpgsql implicitly prepares statements behind the scenes. &&>> I already have a version with "direct" insert ( just as you say a couple of lines below) > prepare mystmt( int, int, varchar) as insert into part > values ($1,$2,$3); > execute mystmt(v_id1, v_id2, v_filler ); > deallocate mystmt; If that worked it would be *exactly* the same as just doing insert into part values (v_id1, v_id2, v_filler); except for being slower due to re-preparing each time through the function. So don't waste your time trying to outsmart the language. >> My idea was to prepare the statment once and execute it in a loop many >> times (within a procedure/function). Anyway, obviously there is no performance gain in using prepared statement in functions. Regards. Milen regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Good examples of calling slony stored procedures
Hi Can somebody direct me to the mailing list for slony. I couldn't find it anywhere on the postgres.org website (which is where I found this list to begin with). Thanks Mark -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 13, 2006 20:27 To: Mark Adan Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Good examples of calling slony stored procedures "Mark Adan" <[EMAIL PROTECTED]> writes: > I was wondering if where can I find some examples of calling the slony > stored procedures instead of using slonik? I want to be able to for > example add a table into slony. Thanks This is likely the wrong bunch to ask --- there's a slony project mailing list where the right people to ask hang out. Don't have the address at hand. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Good examples of calling slony stored procedures
try www.slony.info -- Original Message --- From: "Mark Adan" <[EMAIL PROTECTED]> To: Sent: Wed, 14 Jun 2006 08:50:23 -0700 Subject: Re: [SQL] Good examples of calling slony stored procedures > Hi > > Can somebody direct me to the mailing list for slony. I couldn't find > it anywhere on the postgres.org website (which is where I found this > list to begin with). Thanks > > Mark > > -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: Tuesday, June 13, 2006 20:27 > To: Mark Adan > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Good examples of calling slony stored procedures > > "Mark Adan" <[EMAIL PROTECTED]> writes: > > I was wondering if where can I find some examples of calling the slony > > stored procedures instead of using slonik? I want to be able to for > > example add a table into slony. Thanks > > This is likely the wrong bunch to ask --- there's a slony project > mailing list where the right people to ask hang out. Don't have > the address at hand. > > regards, tom lane > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq --- End of Original Message --- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Good examples of calling slony stored procedures
Hi Jim I looked there already and didn't find what I needed. I saw this web page from cbbrowne and he briefly talked about using "bare metal" slony functions, but doesn't have any examples. Mark -Original Message- From: Jim Buttafuoco [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 14, 2006 08:53 To: Mark Adan; pgsql-sql@postgresql.org Subject: Re: [SQL] Good examples of calling slony stored procedures try www.slony.info -- Original Message --- From: "Mark Adan" <[EMAIL PROTECTED]> To: Sent: Wed, 14 Jun 2006 08:50:23 -0700 Subject: Re: [SQL] Good examples of calling slony stored procedures > Hi > > Can somebody direct me to the mailing list for slony. I couldn't find > it anywhere on the postgres.org website (which is where I found this > list to begin with). Thanks > > Mark > > -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: Tuesday, June 13, 2006 20:27 > To: Mark Adan > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Good examples of calling slony stored procedures > > "Mark Adan" <[EMAIL PROTECTED]> writes: > > I was wondering if where can I find some examples of calling the slony > > stored procedures instead of using slonik? I want to be able to for > > example add a table into slony. Thanks > > This is likely the wrong bunch to ask --- there's a slony project > mailing list where the right people to ask hang out. Don't have > the address at hand. > > regards, tom lane > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq --- End of Original Message --- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Good examples of calling slony stored procedures
On Wed, Jun 14, 2006 at 08:55:21AM -0700, Mark Adan wrote: > Hi Jim > > I looked there already and didn't find what I needed. I saw this web But the mailing list link is at the top of that page: http://gborg.postgresql.org/mailman/listinfo/slony1 A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Good examples of calling slony stored procedures
Excellent. I will subscribe to that one. Thanks Mark -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan Sent: Wednesday, June 14, 2006 09:14 To: pgsql-sql@postgresql.org Subject: Re: [SQL] Good examples of calling slony stored procedures On Wed, Jun 14, 2006 at 08:55:21AM -0700, Mark Adan wrote: > Hi Jim > > I looked there already and didn't find what I needed. I saw this web But the mailing list link is at the top of that page: http://gborg.postgresql.org/mailman/listinfo/slony1 A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Efficient Searching of Large Text Fields
On Wed, 14 Jun 2006, Achilleus Mantzios wrote: O Oleg Bartunov ?? Jun 14, 2006 : Don't invent a wheel and use contrib/tsearch2 for that. Hi Oleg, i just wanted to ask if anything close to exact phrase matching could be deployed/implemented with tsearch2. not yet, but doable, since we have inverted index support now. Looking for sponsorship. On Tue, 13 Jun 2006, Aaron Bono wrote: In another post on a different topic, Rod Taylor said the following: "A\tcat in the\nhat" might be stored as ARRAY['A', 'cat', 'in', 'the', 'hat']. This got me thinking. I have a discussion forum for gamers and want to provide searching capabilities so the user can type in a phrase like "magical bow" and get all posts, sorted by relevance that contain these words. My questions are: 1. Will storing the posts in an ARRAY help improve performance of these searches? If so, by how much? 2. What functions or libraries are available to make such searching easy to implement well? 3. What is the best way to sort by relevance? Thanks, Aaron Bono ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] About sequences that works BAD !!!!
Hello everyone: We are working with serials fields and we found a problem with then: When we insert in a table that has e unique restrict, and this makes insert fails, the sequence increments anyway…¿What we can do about it? We hope you can help us….. Alexis Palma Espinosa. Ingeniero en Informática. "If you are not part of the solution...you are part of the problem"
Re: [SQL] About sequences that works BAD !!!!
Alexis Palma Espinosa wrote: Hello everyone: We are working with serials fields and we found a problem with then: When we insert in a table that has e unique restrict, and this makes insert fails, the sequence increments anyway...¿What we can do about it? Nothing. The whole point of sequences is that they don't lock. They *do* guarantee unique numbers, but they *do not* guarantee no gaps. If you really want a series of ID numbers with no gaps you'll want to do something like: 1. Begin transaction 2. Lock table exclusively 3. Find highest existing ID (...ORDER BY id DESC LIMIT 1) 4. Add one to it 5. Store new row. 6. commit transaction, freeing the lock -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] About sequences that works BAD !!!!
On Wednesday 14 June 2006 02:02 pm, "Alexis Palma Espinosa" <[EMAIL PROTECTED]> thus communicated: --> Hello everyone: --> --> --> --> We are working with serials fields and we found a problem with then: When we insert in a table that has e unique restrict, and this makes insert fails, the sequence increments anyway...¿What we can do about it? --> --> --> --> We hope you can help us. --> --> --> This is doing exactly what it is supposed to do. See the docs: http://www.postgresql.org/docs/7.4/static/functions-sequence.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Requirement for PostgreSQL Database Developer
Hi , This is Mark with ProV International, This email is in regards to the requirement we have with one of our direct client in San Diego, CA. PostgreSQL Database Developer This position involves creating tables, views, functions and stored procedures to support front end OLTP and reporting applications. The ideal developer will have thorough knowledge of SQL (PL/pgSQL), experience with at least one other PostgreSQL language (e.g. PL/Perl), and extensive experience with complex stored procedures, code optimization, and index tuning in PostgreSQL. Ideal candidate will have the following qualifications: 5+ years database development with PostgreSQL Knowledge of at least one other language in addition to PL/pgSQL, such as PL/Perl or PL/Java. Experience implementing PostgreSQL replication using Slony-I. Some experience with either SQL Server 2000 or Oracle 9i/10g. Significant background in creating complex stored procedures and SQL scripts Understanding of database normalization concepts Some experience in logical and physical database design and implementation Prior experience working in a project oriented environment and meeting deadlines under tight time constraints Strong analytical skills Capable of working independently with minimal supervision. Location: San Diego, CA Duration: 6+ months. If you find yourself comfortable with this job profile & find it interesting please send me your resume in MS Word Format. Kindest Regards,Mark,ProV InternationalTampa, FL 33607Tel 408-241-7795 Ext: - 27[EMAIL PROTECTED]www.provintl.com
Re: [SQL] Requirement for PostgreSQL Database Developer
"Mark" <[EMAIL PROTECTED]> writes: > This is Mark with ProV International, This email is in regards = > to the requirement we have with one of our direct client in San Diego, = > CA. This is off-topic for pgsql-sql. Please use pgsql-jobs for this type of message in future. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Good examples of calling slony stored procedures
Martha Stewart called it a Good Thing when [EMAIL PROTECTED] ("Mark Adan")
wrote:
> I looked there already and didn't find what I needed. I saw this web
> page from cbbrowne and he briefly talked about using "bare metal" slony
> functions, but doesn't have any examples.
If you look at the source code for the slonik utility, it shows how
the functions actually get used.
In most cases, slonik.c submits fairly simple requests using the
functions in slony1-funcs.sql.
For instance, the slonik MOVE SET (id=1, old origin=11, new origin=22);
command runs a bunch of C "deteriorata" that is a wrapper for:
select _slony_schema.moveset(1, 22);
Plenty of the commands are about as simple as that.
And this means that, for these "simple" operations, if you want to
submit them via SQL queries, there's a very thin veiling you need to
do to submit the functions as SQL selects.
There are more complex cases, such as FAIL OVER, SET ADD TABLE,
EXECUTE SCRIPT, UPDATE FUNCTIONS, and WAIT FOR EVENT, where there is
considerably complex logic in addition to what is in the stored procs.
Looking at slonik.c is the best thing I can suggest you do...
--
"cbbrowne","@","cbbrowne.com"
http://linuxdatabases.info/info/x.html
Signs of a Klingon Programmer #6: "Debugging? Klingons do not
debug. Our software does not coddle the weak."
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
