Re: [GENERAL] FTS phrase searches
I guess no response means it's not possible. I ended up doing a
manual substring match for quoted strings, but that's a poor hack.
Maybe I'll take a poke at implementing something like
tsvector_contains_phrase; it seems like a natural extension of what's
in there now.
On Mon, Nov 1, 2010 at 4:35 PM, Glenn Maynard gl...@zewt.org wrote:
How are adjacent word searches handled with FTS? tsquery doesn't do
this, so I assume this has to be done as a separate filter step, eg.:
# large house sales
SELECT * FROM data WHERE fts @@ to_tsquery('large house sales')
AND tsvector_contains_phrase(fts, to_tsvector('large house')));
to do an indexed search for large house sales and then to narrow
the results to where large house actually appears as a phrase (eg.
adjacent positions at the same weight). I can't find any function to
do that, though. (Presumably, it would return true if all of the
words in the second tsvector exist in the first, with the same
positions relative to each other.)
tsvector @ tsvector seems logical, but isn't supported.
This isn't as simple as using LIKE, since that'll ignore stemming,
tokenization rules, etc. If the language rules allow this to match
larger house or large-house, then a phrase restriction should,
too. It's also painful when the FTS column is an aggregate of several
other columns (eg. title and body), since a LIKE match needs to know
that and check all of them separately.
Any hints? This is pretty important to even simpler search systems.
--
Glenn Maynard
--
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] Getting number of affected rows after DELETE FROM
On 2010-12-17, Raimon Fernandez co...@montx.com wrote: Hi, I'm trying to solve what I think must be a real trivial question. When I use psql after every DELETE FROM table WHERE id= I get how many rows were affected, in this case, deleted. Also I've implemented the full FrontEnd/BackEnd Protocol v3 and there after a CommandComplete also I receive how many rows were affected. But now, I'm using REALstudio www.realsoftware.com with their plugin, and I can't get the rows affected. I can send a simple DELETE FROM table WHERE id= and all what I get is nothing, no rows, no set, no info, even if the action didn't delete any row because the id was wrong. They say that if the DELETE gives an empty string, means that PostgreSQL isn't returning nothing and that I have to get those values with some special values, like return parameters. In pg/plsql I've used sometimes the GET DIAGNOSTICS variable = ROW_COUNT or FOUND with great success, but I really can't make them work outside their main function. There is something like select lastval(); but for rows affected ? thanks in advance, Easiest work-around is to add returning true on the end of your delete then the delete will return some row(s) when it succeeds. Best solution is to get realstudio to fix their plugin or use a different framework. -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: INSERT INTO...RETURNING with partitioned table based on trigger function
On 2010-12-16, pgsql.30.miller_2...@spamgourmet.com pgsql.30.miller_2...@spamgourmet.com wrote: --0015174c1e4aaf077604977d7e62 Content-Type: text/plain; charset=ISO-8859-1 Hi - Issue: How to return a sequence value generated upon INSERT of records into a partitioned table using trigger functions (without having to insert into the child table directly). partitioning doesn't work with INSERT ... RETURNING ... and trigger based partitioning. use a rule instead have the rule FOR EACH ROW DO INSTEAD SELECT insertfunc(NEW) and have insertfunc do the insert and return the id column. for declaring the function the type of NEW is table_name%ROWTYPE 2) multiple instances of the application may be running, so generation of the sequence number in the application is not feasible (moreover, the application is multi-threaded and additional summary data insertions may occur between the insertion of summary data and detailed data in the two partitioned tables. another option is the application could call nextval itself or call lastval after the insert. both of these SQL functions are thread safe. 3) is there a technical reason as to why the return values of trigger functions are ignored when TG_OP=AFTER for INSERT/ UPDATE operations, because you can't change history. -- ⚂⚃ 100% natural -- 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] Maximum size for char or varchar with limit
On 2010-12-08, Tom Lane t...@sss.pgh.pa.us wrote: Adrian Klaver adrian.kla...@gmail.com writes: On 12/08/2010 08:04 AM, Tom Lane wrote: The rationale for having a limit of this sort is (a) we *don't* want the upper limit of declarable length to be encoding-dependent; and (b) if you are trying to declare an upper limit that's got more than a few digits in it, you almost certainly ought to not be declaring a limit at all. Well that explains it :) Would it be possible to change the below section in the docs to state that the declared max value of n is limited to a max string size of 10Mb? I don't really see any point in that. The value is meant to be an order of magnitude or so more than anything that's sane according to point (b). If you think you need to know what it is, you're already doing it wrong. I have some values of perhaps 20Mb that I might want to store samples of in a partitioned table. (so I can delete them easily) what's the right way? I guess I could just keep them as disk files and rotate the directories as I rotate partitions. -- ⚂⚃ 100% natural -- 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] FTS phrase searches
You might be interested in http://www.sai.msu.su/~megera/wiki/2009-08-12
Oleg
On Sun, 19 Dec 2010, Glenn Maynard wrote:
I guess no response means it's not possible. I ended up doing a
manual substring match for quoted strings, but that's a poor hack.
Maybe I'll take a poke at implementing something like
tsvector_contains_phrase; it seems like a natural extension of what's
in there now.
On Mon, Nov 1, 2010 at 4:35 PM, Glenn Maynard gl...@zewt.org wrote:
How are adjacent word searches handled with FTS? tsquery doesn't do
this, so I assume this has to be done as a separate filter step, eg.:
# large house sales
SELECT * FROM data WHERE fts @@ to_tsquery('large house sales')
AND tsvector_contains_phrase(fts, to_tsvector('large house')));
to do an indexed search for large house sales and then to narrow
the results to where large house actually appears as a phrase (eg.
adjacent positions at the same weight). I can't find any function to
do that, though. (Presumably, it would return true if all of the
words in the second tsvector exist in the first, with the same
positions relative to each other.)
tsvector @ tsvector seems logical, but isn't supported.
This isn't as simple as using LIKE, since that'll ignore stemming,
tokenization rules, etc. If the language rules allow this to match
larger house or large-house, then a phrase restriction should,
too. It's also painful when the FTS column is an aggregate of several
other columns (eg. title and body), since a LIKE match needs to know
that and check all of them separately.
Any hints? This is pretty important to even simpler search systems.
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
[GENERAL] unable to write inside TEMP...
Hi. When installing PostrgeSQL (no mater 32 or 64-bit) on Windows Vista Home Premium (64-bit) i a get error message Unable to write inside TEMP environment variable path. Any idea? -- 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] unable to write inside TEMP...
On 19/12/2010 16:53, tbazadaykin wrote: Hi. When installing PostrgeSQL (no mater 32 or 64-bit) on Windows Vista Home Premium (64-bit) i a get error message Unable to write inside TEMP environment variable path. Any idea? Permissions maybe? What is TEMP set to? 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
Re: [GENERAL] unable to write inside TEMP...
No. I'm Administrator. About TEMP: - %USERPROFILE%\AppData\Local\Temp (this is for current user) - %SystemRoot%\TEMP (system var) Im elso check disk free space - it's ok. - Original Message - From: Raymond O'Donnell r...@iol.ie To: tbazadaykin tbazaday...@yandex.ru Cc: pgsql-general@postgresql.org Sent: Sunday, December 19, 2010 8:04 PM Subject: Re: [GENERAL] unable to write inside TEMP... On 19/12/2010 16:53, tbazadaykin wrote: Hi. When installing PostrgeSQL (no mater 32 or 64-bit) on Windows Vista Home Premium (64-bit) i a get error message Unable to write inside TEMP environment variable path. Any idea? Permissions maybe? What is TEMP set to? 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unable to kill local COPY
version: 8.3 The other day, my DB stop processing request. It still accepts connections but not processing those. So I quit all client connections from client yet those process still alive on SQL Server. I tried to Stop DB by issuing pt_ctl STOP -m fast but failed to shut down database. Next I issued pg_ctl stop -m immediate. I think this one killed all processes that were from client. The returning message says the server was stopped. So I issued ps -ef | grep postgres but master and 4 child processes were still there... 502 150 1 0 0:10.70 ?? 0:12.69 /Library/PostgresPlus/8.3/bin/postgres -D /data 502 165 150 0 0:03.71 ?? 0:05.15 postgres: logger process 502 171 150 0 0:44.15 ?? 1:06.28 postgres: writer process 502 72026 150 0 0:00.15 ?? 0:00.87 postgres: postgres mydata [local] VACUUM 502 81413 150 0 0:00.48 ?? 0:06.52 postgres: postgres mydata 127.0.0.1(56760) COPY I then tried to kill the following process 502 81413 150 0 0:00.48 ?? 0:06.52 postgres: postgres mydata 127.0.0.1(56760) COPY even with -s 15 I could not kill So what I end up doing was to hard reboot the machine... I have questions: 1) Did I have better choice than hard-reboot the machine? (soft reboot didn't work). 2) The COPY command I was tying to kill is local request. Is this the process postgres trying to write changes to the disk? If so, what could I have done to data? Thank you very much for your time.
Re: [GENERAL] Table both does not and does exist! wth?
On Dec 17, 2010, at 10:34 AM, Melvin Davidson wrote: please don't name your constraint using the same name you named your table. I can't believe I missed the obvious, but that's why it's better to have someone else take a look. Thanks for spotting that. I've tacked on _pk to the constraint. Fixed! FWIW, I find it useful to provide some kind of indication of what fields are in the index in the index name, ie: xtmp_changed_ids__pk_seq_all. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.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] DB files, sizes and cleanup
On Dec 17, 2010, at 4:22 PM, Gauthier, Dave wrote: max_fsm_pages = 20 Don't know that this is related, but that count is pretty low. If you run a vacuumdb -av and capture the output, the very end will tell you how many FSM pages you actually need; it's very possible 200,000 isn't enough. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.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] Getting number of affected rows after DELETE FROM
On 19dic, 2010, at 10:33 , Jasen Betts wrote: On 2010-12-17, Raimon Fernandez co...@montx.com wrote: Hi, I'm trying to solve what I think must be a real trivial question. When I use psql after every DELETE FROM table WHERE id= I get how many rows were affected, in this case, deleted. Also I've implemented the full FrontEnd/BackEnd Protocol v3 and there after a CommandComplete also I receive how many rows were affected. But now, I'm using REALstudio www.realsoftware.com with their plugin, and I can't get the rows affected. I can send a simple DELETE FROM table WHERE id= and all what I get is nothing, no rows, no set, no info, even if the action didn't delete any row because the id was wrong. They say that if the DELETE gives an empty string, means that PostgreSQL isn't returning nothing and that I have to get those values with some special values, like return parameters. In pg/plsql I've used sometimes the GET DIAGNOSTICS variable = ROW_COUNT or FOUND with great success, but I really can't make them work outside their main function. There is something like select lastval(); but for rows affected ? thanks in advance, Easiest work-around is to add returning true on the end of your delete then the delete will return some row(s) when it succeeds. thanks for your idea ... I've tried to add after my DELETE FROM x the RETURNING TRUE and when it succeeds I get only a True, when postgre can't delete the row because can't find it, I've get NIL. Just to be sure, but the RETURNING TRUE is not a mandatory option to get how many rows were affected after an insert,delete,update ? I'm not sure if REALstudio uses the libpq in their plugin. The libpq returns how many rows were affected or at least has some option to return those values ? Last year I made some postgreSQL for iPhone and I compiled the libpq but only for SELECTS, not inserts or delete, and I'm not sure of this option. Best solution is to get realstudio to fix their plugin or use a different framework. Of course, but those 'commercial frameworks' that are not really interested in doing professional front ends for profressional databases like postgreSQL, are lazy, first I have to demonstrate that they are doing something wrong in their plugin or at least that they have a simple option to implement this, wich I consider, a mandatory for professional databases. Also I'm open to other frameworks but there are few that can deploy the same code to Windows, OS X and Linux. Thanks again, regards, r. -- 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] FTS phrase searches
2010/12/19 Oleg Bartunov o...@sai.msu.su: You might be interested in http://www.sai.msu.su/~megera/wiki/2009-08-12 Thanks, that looks pretty much like what I had in mind. Hopefully that'll get merged for 9.0+1; phrases are a major part of all text searches. -- Glenn Maynard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Locale setting advice
Hi, I'm about to upgrade a 700GB 8.4 db to version 9.0.2, and I'm curious if there is any benefit to switching from my current lc_collate setting of en_US.UTF-8 to C. I read over the docs a couple times, but couldn't really find anything conclusive other than the statement that settings other than C have negative performance impacts. We do store UTF8 data, but I assume that that is handled by creating the db with the encoding of UTF8. Are there any other details I can provide to elicit feedback? Cheers, FT
[GENERAL] Need More Case Studies on PostGIS
Hi All, I'd like to read more case studies on PostGIS. I know some case studies are listed in http://postgis.refractions.net/ . Are there more case studies that you can refer me to? Thanks in advance. Mary
Re: [GENERAL] DB files, sizes and cleanup
I recreated the 2 bad DBs (the ones with millions of files in them). So far, they're behaving. Thanks for the help. -Original Message- From: Merlin Moncure [mailto:mmonc...@gmail.com] Sent: Saturday, December 18, 2010 1:29 PM To: Gauthier, Dave Cc: Bill Moran; pgsql-general@postgresql.org Subject: Re: [GENERAL] DB files, sizes and cleanup On Fri, Dec 17, 2010 at 5:22 PM, Gauthier, Dave dave.gauth...@intel.com wrote: max_fsm_pages = 20 max_fsm_relations = 12000 There are 12 DBs with roughly 30 tables+indexes each. There are apparently 2 bad DBs. Both identical in terms of data models (clones with different data). I've pg_dummped one of them to a file, dropped the DB (took a long time as millions of files were deleted) and recreated it. It now has 186 files. ls -1 | wc took a while for the other bad one but eventually came up with exactly 7,949,911 files, so yes, millions. The other one had millions too before I dropped it. Something is clearly wrong. But, since the DB recreate worked for the other one, I'll do the same thing to fix this one too. What I will need to know then is how to prevent this in the future. It's very odd because the worst of the 2 bad DBs was a sister DB to one that's no problem at all. Here's the picture... I have a DB, call it foo, that gets loaded with a ton of data at night. The users query the thing readonly all day. At midnight, an empty DB called foo_standby, which is identical to foo in terms of data model is reloaded from scratch. It takes hours. But when it's done, I do a few rename databases to swap foo with foo_standby (really just a name swap). foo_standby serves as a live backup of yesterday's data. Come the next midnight, I truncate all the tables and start the process all over again. maybe something in this process is leaking files. if I was in your shoes, I'd recreate the database from scratch, then watch the file count carefully and look for unusual growth. this is probably not the case, but if it is in fact a backend bug it will turn up again right away. anything else interesting jump out about these files? for example, are there a lot of 0 byte files? merlin -- 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] Need More Case Studies on PostGIS
For exaple this http://www.postgresql.org/files/documentation/pdf/9.0/postgresql-9.0-US.pdf20.12.2010, 04:05, "Wang, Mary Y" mary.y.w...@boeing.com:Hi All, I'd like to read more case studies on PostGIS. I know some case studies are listed in http://postgis.refractions.net/. Are there more case studies that you can refer me to? Thanks in advance. Mary
