Re: [SQL] Update aborted if trigger function fails?
Carlos Moreno wrote: Hi, I just noticed this (odd?) behaviour, and it kind of scares me. Isn't this a little fragile? Is there something I could do to avoid this situation? Should trigger functions be extremely simple as to guarantee that an error would never happen? There's nothing else it can do, really. Far better that the whole update fails than you get an inconsistent database. Imagine you have a banking system, and every time you add a row to the transaction-history, you update the "current_balance" table. Which would you prefer, both updates fail, or the two get out of sync? Now, there is room for improved dependency checking, but functions pose certain difficulties. 1. The body of the function is opaque to PostgreSQL - it's only plpgsql that it handles itself. It knows nothing about Perl/Python/PHP/Java/C. 2. Functions can create queries from text - even if PG understood all these languages, it couldn't determine which tables were accessed. So - how do you deal with this? Well, you test. Ideally, you should have a set of tests and re-run them to ensure all your functions work as desired. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] OpenFTS
Dan, how large is your database ? OpenFTS is what you need because it supports instant indexing and have access to metadata. I heard about some large archives up to 10 mln documents, which use OpenFTS. Proper tuning of database setup in general and OpenFTS is required. In case you want commercial support you ask me in private mail. Oleg On Mon, 11 Apr 2005, Dan Feiveson wrote: Hoping someone can provide feedback on integrating OpenFTS into Postgres. I am running pg 7.3.4 and have a large database of articles that are constantly changing ( being added, updated, removed). I want to perform key word searches across these articles, ranked by relevance and have looked at a number of solutions, including swish-e and ht://dig, but OpenFTS looks like an great solution -- if it works well. Is anybody using OpenFTS with their pg db and if so how does it perform? Did you encounter any problems installing? Any drawbacks? Many thanks to anyone with the time to respond, Dan Dan Feiveson Broomfield, Colorado Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Getting the output of a function used in a where clause
Why not just do: SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes where zipdist($lat1d,$lon1d,lat,long) <= $dist;"; On Mon, 2005-04-11 at 20:25, Bill Lawrence wrote: > Boy I sure thought that would work... I received the following from postgres: > > ERROR: Attribute "distance" not found. > > Started looking into gist Looks complex. > > Any other ideas? > > > -Original Message- > From: PFC [mailto:[EMAIL PROTECTED] > Sent: Monday, April 11, 2005 1:51 AM > To: Bill Lawrence; pgsql-sql@postgresql.org > Subject: Re: [SQL] Getting the output of a function used in a where clause > > > try: > > SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes > where distance <= $dist;"; > > OR you could use a gist index with a geometric datatype to get it a lot > faster. > > > On Sat, 09 Apr 2005 03:43:39 +0200, Bill Lawrence <[EMAIL PROTECTED]> > wrote: > > > HI, > > > > I'm a newbie so please bear with me. I have a function defined (got it > > from > > one of your threads... thanks Joe Conway) which calculates the distance > > between 2 zip code centeroids (in lat,long). This thing works great. > > However, I want to sort my results by distance without incurring the > > additional burden of executing the function twice. A simplified version > > of > > my current SQL (written in a perl cgi) that returns a set of zip codes > > within a given radius is: > > > > > > What I want to write is something like: > > > > $sql = "SELECT zipcode, distance from zipcodes where distance <= $dist > > order > > by distance;"; > > > > But I don't the magic SQL phrase to populate the distance variable using > > my > > nifty function. Do I need to create an output type for distance? > > > > Thanks in advance! > > > > Bill > > > > > > > > > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Update aborted if trigger function fails?
Richard Huxton wrote: I just noticed this (odd?) behaviour, and it kind of scares me. Isn't this a little fragile? Is there something I could do to avoid this situation? Should trigger functions be extremely simple as to guarantee that an error would never happen? There's nothing else it can do, really. Far better that the whole update fails than you get an inconsistent database. Imagine you have a banking system, and every time you add a row to the transaction-history, you update the "current_balance" table. Which would you prefer, both updates fail, or the two get out of sync? Yes, you are absolutely correct. I guess the concern came up as result of a particular situation, in which failing to properly process the trigger function is not that crucial (I wanted to update some additional information that is "optional", and that can be reconstructed easily after discovering that the trigger function had been failing). But in our case, failing to complete the update is rather critical (things can be reconstructed but under certain conditions only, and only by temporarily shutting down the system for a few minutes). So, I was thinking that there may be a way for the user to instruct PG to ignore the fact that the trigger function failed -- that way, we would overcome the difficulties that you mention in improving dependency checking when functions are involved -- PG wouldn't have to determine it: the user would tell it. So - how do you deal with this? Well, you test. Fair enough. Thanks! Carlos -- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Update aborted if trigger function fails?
On Tue, Apr 12, 2005 at 10:55:30AM -0400, Carlos Moreno wrote: > > I guess the concern came up as result of a particular > situation, in which failing to properly process the > trigger function is not that crucial (I wanted to > update some additional information that is "optional", > and that can be reconstructed easily after discovering > that the trigger function had been failing). But in If you can do some things asynchronously, and you don't care about them very much, then you can use LISTEN/NOTIFY to do such processing. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Update aborted if trigger function fails?
Andrew Sullivan wrote: On Tue, Apr 12, 2005 at 10:55:30AM -0400, Carlos Moreno wrote: I guess the concern came up as result of a particular situation, in which failing to properly process the trigger function is not that crucial (I wanted to update some additional information that is "optional", and that can be reconstructed easily after discovering that the trigger function had been failing). But in If you can do some things asynchronously, and you don't care about them very much, then you can use LISTEN/NOTIFY to do such processing. Thanks Andrew for the suggestion -- this may as well be exactly the feature that I needed to begin with. I'm not familiar with the details, but I'll go right away to dig through the docs. Thanks! Carlos -- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] getting count for a specific querry
On Apr 8, 2005, at 4:50 PM, Scott Marlowe wrote: Do you run your 2650s with hyperthreading on? I found that slowed mine down under load, but we never had more than a couple dozen users hitting the db at once, so we may well have had a different load profile than what you're seeing. Yep. Turned off as per various recommendations on this list. The RAID card on this box is a PERC 3/DC. It is a very big disappointment. The Opteron based generic system totally outperforms this Dell box. Vivek Khera, Ph.D. +1-301-869-4449 x806 smime.p7s Description: S/MIME cryptographic signature
Re: [SQL] Query history file
On Apr 5, 2005, at 11:29 AM, Mauro Bertoli wrote: From the server side, if you enable 'log_statement' all queries will go into the server logs. Thank you, I enabled log_statement = all log_duration = true You may also want log_min_error_statement = error else any statement that causes an error (such as a typo) will not be logged -- just the error gets logged. Makes debugging of very complex systems much easier to be able to see the full query that caused the problem. Vivek Khera, Ph.D. +1-301-869-4449 x806 smime.p7s Description: S/MIME cryptographic signature
Re: [SQL] getting count for a specific querry
On Tue, 2005-04-12 at 14:29, Vivek Khera wrote: > On Apr 8, 2005, at 4:50 PM, Scott Marlowe wrote: > > > Do you run your 2650s with hyperthreading on? I found that slowed mine > > down under load, but we never had more than a couple dozen users > > hitting > > the db at once, so we may well have had a different load profile than > > what you're seeing. > > > > Yep. Turned off as per various recommendations on this list. The RAID > card on this box is a PERC 3/DC. It is a very big disappointment. The > Opteron based generic system totally outperforms this Dell box. How much memory is in the box? I've heard horror stories about performance with >2 gigs of ram, which is why I made them order mine with 2 gigs. Does the 3/DC have battery backed cache set to write back? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] getting count for a specific querry
On Apr 12, 2005, at 4:23 PM, Scott Marlowe wrote: How much memory is in the box? I've heard horror stories about performance with >2 gigs of ram, which is why I made them order mine with 2 gigs. Does the 3/DC have battery backed cache set to write back? 4GB RAM and battery backed cache set to write-back mode. FreeBSD 4.11. Vivek Khera, Ph.D. +1-301-869-4449 x806 smime.p7s Description: S/MIME cryptographic signature
Re: [SQL] getting count for a specific querry
On Tue, 2005-04-12 at 15:32, Vivek Khera wrote: > On Apr 12, 2005, at 4:23 PM, Scott Marlowe wrote: > > > How much memory is in the box? I've heard horror stories about > > performance with >2 gigs of ram, which is why I made them order mine > > with 2 gigs. Does the 3/DC have battery backed cache set to write > > back? > > > > 4GB RAM and battery backed cache set to write-back mode. FreeBSD 4.11. If you've got the time, try running it with only 2 gigs and compare the speed. I never really got a chance to run mine with >2 gigs, but I know that I read plenty of posts at the time that the chipset in the 2650 was REALLY slow at using memory over 2 gig. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] ignore single character in SELECT query?
Given select criteria "dont" I would like to return rows with values "don't". Or similarily I want rows with "they're" given input criteria "theyre". So basically I want to ignore a single character (the apostrophe character), anywhere in the middle of my search word, in selecting results. How can I do this? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Query runs very slowly in Postgres, but very fast in other DBMS
You might also try: SELECT dokumnr FROM rid WHERE NOT EXISTS ( SELECT 'd' FROM dok WHERE dok.dokumnr = rid.dokumnr ); Dan Feiveson DataJoe LLC - Original Message - From: "Krasimir Dimitrov" <[EMAIL PROTECTED]> To: "Andrus Moor" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, April 12, 2005 12:33 AM Subject: Re: [SQL] Query runs very slowly in Postgres, but very fast in other DBMS > > try this query : > > SELECT rid.dokumnr as d1 ,dok.dokumnr as d2 FROM rid left join dok on > rid.dokumnr = dok.dokumnr where dok.dokumnr is null; > > > > Tables: > > > > CREATE TABLE dok ( dokumnr NUMERIC(12), > > CONSTRAINT dok_pkey PRIMARY KEY (dokumnr) ); > > CREATE TABLE rid ( dokumnr NUMERIC(12) ); > > CREATE INDEX rid_dokumnr_idx ON rid (dokumnr); > > > > Query: > > > > SELECT dokumnr FROM rid WHERE dokumnr NOT IN > > (select dokumnr FROM dok); > > > > runs VERY slowly in Postgres. It uses the following query plan: > > > > Seq Scan on rid (cost=0.00..28698461.07 rows=32201 width=14) > > Filter: (NOT (subplan)) > > SubPlan > > -> Seq Scan on dok (cost=0.00..864.29 rows=10729 width=14) > > > > In Microsoft Visual FoxPro this query runs fast. FoxPro uses indexes speed > > up the query by comparing bitmaps. > > > > Is it possible to speed up this query is Postgres ? How to force Postgres > > to use indexes for this query ? > > > > Andrus > > > > > > > > ---(end of broadcast)--- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > > > -- > > Krasimir Dimitrov > IT Department > AII Data Processing Ltd., > 16 Ivan Vazov Str, > Sofia 1000, > Bulgaria > Phone: +359 2 9376 352 > E-mail: [EMAIL PROTECTED] > http://www.see-news.com > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] max question
I have the following in a table: oid | citkey | contby | contdate | abcontinue | ccdate -++--+++ 5774835 | 04-0594703 | ||| 5775325 | 04-0594703 | Attorney | 04/06/2005 | 6 | 03/07/2005 5776060 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005 5776067 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005 I am trying to pull rows that have the max. contdate. Why does the following give more than 2 rows? ql "select oid,* from ccontinue where citkey ='04-0594703' group by oid,citkey,contby,contdate,abcontinue,ccdate having max(contdate) = contdate" oid | citkey | contby | contdate | abcontinue | ccdate -++--+++ 5776067 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005 5775325 | 04-0594703 | Attorney | 04/06/2005 | 6 | 03/07/2005 5776060 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005 (3 rows) thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] operating "inet" type
Greetings, I'm confused, that PostgreSQL seems to don't have operators/functions for examining/modifying "inet" data type. No any octet/word-based means (like extract/replace), no even, trivial integer increments. No conversions, except conversion to symbolic string (parsing it is a mess and overhead). No even binary string conversions. You can do integer compare two "inet" values, but you can't do integer increment (i.e. increment inet value by integer). I understand, that developers has more important things to do, but they left "inet" type w/o any ways to work w/ it. Parsing "inet" converted text can't be, seriously, taken as alternative, but using numeric types to store addresses (and ignore native "inet" type) really, can. I'm using PostgreSQL 7.4.3, Can 8.0.1 do more ? Thank you. Ilya A. Kovalenko(mailto:[EMAIL PROTECTED]) SpecialEQ SW section JSC Oganer-Service ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Getting the output of a function used in a where clause
Thanks a bunch! Looks pretty step-by-step at the site for the link you sent. I'll give it a shot and see how it turns out. Thanks again for all your help! Bill -Original Message- From: PFC [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 12, 2005 1:03 AM To: Bill Lawrence Subject: Re: [SQL] Getting the output of a function used in a where clause > Boy I sure thought that would work... I received the following from > postgres: > > ERROR: Attribute "distance" not found. > > Started looking into gist Looks complex. > > Any other ideas? Complex ? CREATE TABLE stuff ( ... coords BOX NOT NULL, ... ) WITHOUT OIDS; CREATE INDEX cities_coords_idx ON geo.cities USING GIST ( coords gist_box_ops ); For some reason you must use BOX instead ot POINT to use the index. CREATE OR REPLACE FUNCTION boxpoint(FLOAT,FLOAT) RETURNS BOXRETURNS NULL ON NULL INPUTLANGUAGE plpgsqlAS $$ DECLARE p POINT; BEGIN p := point($1,$2); IF $1=0 AND $2=0 THEN RETURN NULL; END IF; RETURN box(p,p); END; $$; now use boxpoint(x,y) to select a box : INSERT INTO stuff (...,coords,...) VALUES (...,boxpoint(x,y),...) Now to get all the records whose coords are inside a box using the index : SELECT ... WHERE cords && '((xa,ya),(xb,yb))'::box for all the details look there : http://www.postgis.org/docs/ch04.html#id3530280 it's simple once you're into it. You'll need to install postgis. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Query runs very slowly in Postgres, but very fast in other DBMS
Andrus Moor wrote: SELECT dokumnr FROM rid WHERE dokumnr NOT IN (select dokumnr FROM dok); ... Is it possible to speed up this query is Postgres ? How to force Postgres to use indexes for this query ? Use IN and NOT IN only for small sets. Use JOIN (instead of IN) and LEFT JOIN (instead of NOT IN) for larger sets. e.g.: SELECT rid.dokumnr FROM rid LEFT JOIN dok ON (dok.dokumnr = rid.dokumnr) WHERE dok.dokumnr iS NULL; Cheers, Ezequiel Tolnay ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Query runs very slowly in Postgres, but very fast in other DBMS
>> SELECT dokumnr FROM rid WHERE dokumnr NOT IN >> (select dokumnr FROM dok); > ... >> Is it possible to speed up this query is Postgres ? How to force Postgres >> to use indexes for this query ? > > Use IN and NOT IN only for small sets. Use JOIN (instead of IN) and LEFT > JOIN (instead of NOT IN) for larger sets. e.g.: > > SELECT rid.dokumnr > FROM rid > LEFT JOIN dok ON (dok.dokumnr = rid.dokumnr) > WHERE dok.dokumnr iS NULL; Thank you. How to use this technique to speed up the update statement UPDATE rid SET dokumnr=NULL WHERE dokumnr NOT IN (SELECT dokumnr FROM dok); and DELETE statement DELETE FROM rid WHERE dokumnr NOT IN (SELECT dokumnr FROM dok); Andrus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] max question
"A. R. Van Hook" <[EMAIL PROTECTED]> writes: > I am trying to pull rows that have the max. contdate. Why does the > following give more than 2 rows? > ql "select oid,* from ccontinue where citkey ='04-0594703' group by > oid,citkey,contby,contdate,abcontinue,ccdate having max(contdate) = > contdate" HAVING is going to interpret the max() aggregate separately for each group ... that is, the above query asks for all the rows that have the largest contdate within their group. Given that OID is one of the grouping columns, I'd pretty much expect that to select every single row in the table, because each row will form its own unique group :-( What is it you are trying to accomplish here? In particular, what led to that choice of GROUP BY? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] ignore single character in SELECT query?
[EMAIL PROTECTED] writes: > So basically I want to ignore a single character (the apostrophe > character), anywhere in the middle of my search word, in selecting > results. How can I do this? WHERE replace(name,,'') like '%dont%' Beware of quoting issues if "dont" is coming from user supplied inputs. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] ignore single character in SELECT query?
Use
select replace(quote_literal('don\'t'), '\'', '');
Or
select replace(quote_ident(myColumnName, '\'', '');
Thanks
Dinesh Pandey
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of [EMAIL PROTECTED]
Sent: Wednesday, April 13, 2005 8:16 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] ignore single character in SELECT query?
Given select criteria "dont" I would like to return rows with values
"don't". Or similarily I want rows with "they're" given input criteria
"theyre".
So basically I want to ignore a single character (the apostrophe
character), anywhere in the middle of my search word, in selecting
results. How can I do this?
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] max question
Try "select oid,* from ccontinue where citkey ='04-0594703' group by oid,citkey,contby,contdate,abcontinue,ccdate having contdate= max(contdate)" Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of A. R. Van Hook Sent: Tuesday, April 12, 2005 10:54 PM To: pgsql-sql@postgresql.org Subject: [SQL] max question I have the following in a table: oid | citkey | contby | contdate | abcontinue | ccdate -++--+++ 5774835 | 04-0594703 | ||| 5775325 | 04-0594703 | Attorney | 04/06/2005 | 6 | 03/07/2005 5776060 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005 5776067 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005 I am trying to pull rows that have the max. contdate. Why does the following give more than 2 rows? ql "select oid,* from ccontinue where citkey ='04-0594703' group by oid,citkey,contby,contdate,abcontinue,ccdate having max(contdate) = contdate" oid | citkey | contby | contdate | abcontinue | ccdate -++--+++ 5776067 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005 5775325 | 04-0594703 | Attorney | 04/06/2005 | 6 | 03/07/2005 5776060 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005 (3 rows) thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 8: explain analyze is your friend
