[SQL] Does postgresql8.2 supports multithreading?
Hi , Does postgresql8.2 supports multithreading? Thanks, Jyoti Seth
Re: [SQL] Does postgresql8.2 supports multithreading?
am Tue, dem 11.09.2007, um 12:38:38 +0530 mailte Jyoti Seth folgendes: > Hi , > > > > Does postgresql8.2 supports multithreading? No. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Does postgresql8.2 supports multithreading?
A. Kretschmer wrote: > am Tue, dem 11.09.2007, um 12:38:38 +0530 mailte Jyoti Seth folgendes: >> Hi , >> >> >> >> Does postgresql8.2 supports multithreading? > > No. In the bits that (should) matter to end users (ie. libpq), yes, it does. Regards, Dave ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] select sum within transaction problem
Dear all
I have a function like below (simplified). Everything works the way I want it
to except for one thing.
After the select statement sum_revenues is NULL instead of the real sum.
Has this something to do with the earlier deletes and inserts not being
committed yet?
I assumed they would have been committed at the END marked with (1), but
apparently they don't.
How can I fix this?
Thanks to anyone who can enlighten me!
CREATE FUNCTION test(companies IN text[]) returns void AS
$body$
DECLARE
company text;
sum_revenues revenues.revenue%TYPE;
BEGIN
perform general_log('test', 'start');
for idx in array_lower(companies, 1) .. array_upper(companies, 1)
loop
BEGIN
BEGIN
company := lower(companies[idx]);
delete from revenues where ...;
insert into revenues select ..., ..., ... from billing where
...condition1...;
insert into revenues select ..., ..., ... from billing where
...condition2...;
insert into revenues select ..., ..., ... from billing where
...condition3...;
insert into revenues select ..., ..., ... from billing where
...condition4...;
END; --(1)
select sum(revenue) into sum_revenues from revenues;
EXCEPTION
WHEN others
perform general_errlog('test', SQLSTATE, SQLERRM);
END;
end loop;
perform general_log('test', 'end');
EXCEPTION
WHEN others
perform general_errlog('test', SQLSTATE, SQLERRM);
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
Re: [SQL] Does postgresql8.2 supports multithreading?
On 9/11/07, Jyoti Seth <[EMAIL PROTECTED]> wrote: > Hi , > Does postgresql8.2 supports multithreading? > Thanks, > Jyoti Seth This depends entirely on what you mean. No, PostgreSQL isn't a multithreaded application. Yes, you can write multithreaded applications that use PostgreSQL. Consult the documentation for whatever connection library you're interested in using (libpq, JDBC, npgsql, etc.) to find out what thread-related issues you'll need to consider using that library. -Josh ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Simple Query?
I am doing some optimization on our search, but I need some advise... table: item id name -- 1 iPod 2 Zune 3 Walkman table: search_item id_search id_item -- 1 1 1 2 1 3 2 2 2 3 3 1 3 3 Now what I want to have is the items that match with id_search 1 and 2 and 3. Therefore I use the following SQL query. SELECT * FROM item WHERE id IN (SELECT id_item FROM search_item WHERE id_search=1 AND id_item IN (SELECT id_item FROM search_item WHERE id_search=2 AND id_item IN (SELECT id_item FROM search_item WHERE id_search=3))); This should only return id_item 3. Would this be the best SQL query to get this result? I have the feeling there should be something better, but I cannot find it. Anyone has a hint? ---(end of broadcast)--- TIP 1: 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] Simple Query?
On 9/11/07, Koen Bok <[EMAIL PROTECTED]> wrote: > Anyone has a hint? Another way: SELECT i.* FROM item i JOIN search_item s1 ON i.ID = s1.id_item JOIN search_item s2 ON s1.id_item = s2.id_item JOIN search_item s3 ON s2.id_item = s3.id_item WHERE s1.id_search = 1 AND s2.id_search = 2 AND s3.id_search = 3 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Simple Query?
--- Koen Bok <[EMAIL PROTECTED]> wrote: > I am doing some optimization on our search, but I need some advise... > > table: item > > idname > -- > 1 iPod > 2 Zune > 3 Walkman > > table: search_item > > id_search id_item > -- > 1 1 > 1 2 > 1 3 > 2 2 > 2 3 > 3 1 > 3 3 > > > Now what I want to have is the items that match with id_search 1 and > 2 and 3. Therefore I use the following SQL query. SELECT id_search FROM Search_item GROUP BY id_search HAVING Count( * ) = 3 AND Max( id_item ) = 3 AND Min( id_item ) = 1; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Extracting hostname from URI column
Hi,
I'm trying to use substr() and position() functions to extract the full host
name (and later a domain) from a column that holds URLs.
This is what I'm trying, but it clearly doesn't do the job.
=> select substr(href, position('://' in href)+3, position('://' in
href)+3+position('/' in href)), href from url where id <10;
substr | href
+--
texturizer.net | http://texturizer.net/firebird/extensions/
texturizer.net | http://texturizer.net/firebird/themes/
forums.mozilla | http://forums.mozillazine.org/index.php?c=4
www.mozillazin | http://www.mozillazine.org/
devedge.netsca | http://devedge.netscape.com/viewsource/2002/bookmarks/
www.google.com | http://www.google.com/search?&q=%s
groups.google. | http://groups.google.com/groups?scoring=d&q=%s
www.google.com | http://www.google.com/search?q=%s&btnI=I'm+Feeling+Lucky
dictionary.ref | http://dictionary.reference.com/search?q=%s
The 3rd param to the substr function is clearly wrong. Is it even doable
without writing a procedure?
Finally, is this the fastest way to get this data, or is there regex-based
function that might be faster?
Thanks,
Otis
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Extracting hostname from URI column
> I'm trying to use substr() and position() functions to extract the > full host name (and later a domain) from a column that holds URLs. substring( href from '.*://\([^/]*)' ); Pinpoint customers who are looking for what you sell. http://searchmarketing.yahoo.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Extracting hostname from URI column
[EMAIL PROTECTED] wrote:
Hi,
I'm trying to use substr() and position() functions to extract the full host
name (and later a domain) from a column that holds URLs.
This is what I'm trying, but it clearly doesn't do the job.
=> select substr(href, position('://' in href)+3, position('://' in
href)+3+position('/' in href)), href from url where id <10;
substr | href
+--
texturizer.net | http://texturizer.net/firebird/extensions/
texturizer.net | http://texturizer.net/firebird/themes/
forums.mozilla | http://forums.mozillazine.org/index.php?c=4
www.mozillazin | http://www.mozillazine.org/
devedge.netsca | http://devedge.netscape.com/viewsource/2002/bookmarks/
www.google.com | http://www.google.com/search?&q=%s
groups.google. | http://groups.google.com/groups?scoring=d&q=%s
www.google.com | http://www.google.com/search?q=%s&btnI=I'm+Feeling+Lucky
dictionary.ref | http://dictionary.reference.com/search?q=%s
The 3rd param to the substr function is clearly wrong. Is it even doable
without writing a procedure?
Finally, is this the fastest way to get this data, or is there regex-based
function that might be faster?
Thanks,
Otis
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Try this:
test=# select substr(href, position('://' in href)+3, position('/' in
substr(
href,position('://' in href)+3))-1), href from url;
substr | href
--+-
-
devedge.netscape.com |
http://devedge.netscape.com/viewsource/2002/bookmark
s/
texturizer.net | http://texturizer.net/firebird/extensions/
texturizer.net | http://texturizer.net/firebird/themes/
forums.mozillazine.org | http://forums.mozillazine.org/index.php?c=4
www.mozillazine.org | http://www.mozillazine.org/
devedge.netscape.com |
http://devedge.netscape.com/viewsource/2002/bookmark
s/
www.google.com | http://www.google.com/search?&q=%s
groups.google.com| http://groups.google.com/groups?scoring=d&q=%s
www.google.com |
http://www.google.com/search?q=%s&btnI=I'm+Feeling+L
ucky
dictionary.reference.com | http://dictionary.reference.com/search?q=%s
(10 rows)
--
Paul Lambert
Database Administrator
AutoLedgers
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] select sum within transaction problem
"Bart Degryse" <[EMAIL PROTECTED]> writes: > I have a function like below (simplified). Everything works the way I want = > it to except for one thing. > After the select statement sum_revenues is NULL instead of the real sum. > Has this something to do with the earlier deletes and inserts not being = > committed yet? No. I suspect the problem is hidden somewhere in the code you didn't show us. In particular you should check whether the insert/selects are actually finding anything to insert ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Extracting hostname from URI column
chester c young wrote: I'm trying to use substr() and position() functions to extract the full host name (and later a domain) from a column that holds URLs. substring( href from '.*://\([^/]*)' ); Ok, your solution looks better than mine... but I have no idea how to interpret that, time to consult some manuals. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Extracting hostname from URI column
On 9/12/07, Paul Lambert <[EMAIL PROTECTED]> wrote: > > substring( href from '.*://\([^/]*)' ); > Ok, your solution looks better than mine... but I have no idea how to > interpret that, time to consult some manuals. Plain regex The key are the parenthesis () ... basically it will omit ANYTHING + two slashes at the beginning of a string. Then it will match everything BUT a slash, and as much of that as possible since regex are greedy by default (hence the host name he was looking for) ... and everything AFTER a slash will be omitted. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Extracting hostname from URI column
Andrej Ricnik-Bay wrote: Plain regex The key are the parenthesis () ... basically it will omit ANYTHING + two slashes at the beginning of a string. Then it will match everything BUT a slash, and as much of that as possible since regex are greedy by default (hence the host name he was looking for) ... and everything AFTER a slash will be omitted. Cheers, Andrej Thanks - that makes a bit more sense. I'm in the middle of reading chapter 9.3.7 of the manual - POSIX Regular Expressions - which I'm assuming is dealing with this, so it's looking clearer. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Extracting hostname from URI column
Hi, Thanks, perfect! (though I'll have to look into the regex warning): => select substring( href from '.*://\([^/]*)' ) as hostname from url where id<10; WARNING: nonstandard use of escape in a string literal at character 29 HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. So now I have this: hostname -- texturizer.net texturizer.net forums.mozillazine.org www.mozillazine.org devedge.netscape.com www.google.com groups.google.com www.google.com dictionary.reference.com And what I'd like is something that would give me the counts for the number of occurrences of each unique hostname. Something much like `uniq -c'. Can anyone tell me how that's done or where I should look for info? (I'm not sure what to look for, that's the problem). Thanks, Otis - Original Message From: chester c young <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Cc: sql pgsql Sent: Tuesday, September 11, 2007 8:42:46 PM Subject: Re: [SQL] Extracting hostname from URI column > I'm trying to use substr() and position() functions to extract the > full host name (and later a domain) from a column that holds URLs. substring( href from '.*://\([^/]*)' ); Pinpoint customers who are looking for what you sell. http://searchmarketing.yahoo.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Extracting hostname from URI column
> >> I'm trying to use substr() and position() functions to extract the > >> full host name (and later a domain) from a column that holds URLs. > > > > substring( href from '.*://\([^/]*)' ); > > typo: no backslash in front of left paren substring( href from '.*://([^/]*)' ) match up thru // within parens, match anything except / return match within parens Building a website is a piece of cake. Yahoo! Small Business gives you all the tools to get online. http://smallbusiness.yahoo.com/webhosting ---(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] Extracting hostname from URI column
[EMAIL PROTECTED] wrote: And what I'd like is something that would give me the counts for the number of occurrences of each unique hostname. Something much like `uniq -c'. Can anyone tell me how that's done or where I should look for info? (I'm not sure what to look for, that's the problem). Thanks, Otis Just use distinct... test=# select distinct count(*),substring( href from '.*://([^/]*)' ) as domain from url group by domain order by domain; count | domain ---+-- 3 | devedge.netscape.com 1 | dictionary.reference.com 2 | forums.mozillazine.org 1 | groups.google.com 4 | texturizer.net 11 | www.google.com 2 | www.mozillazine.org (7 rows) -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Extracting hostname from URI column
> And what I'd like is something that would give me the counts for the > number of occurrences of each unique hostname. Something much like > `uniq -c'. Can anyone tell me how that's done or where I should look > for info? (I'm not sure what to look for, that's the problem). > select substring( ... ), count(1) from your_table group by 1; Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out. http://answers.yahoo.com/dir/?link=list&sid=396545469 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Extracting hostname from URI column
Ah, I figured out what to look for and found my uniq -c solution: select substring( href from '.*://([^/]*)' ) as hostname, count(substring( href from '.*://([^/]*)' )) from url where id<10 group by hostname order by count desc; hostname | count --+--- texturizer.net | 2 www.google.com | 2 dictionary.reference.com | 1 www.mozillazine.org | 1 devedge.netscape.com | 1 groups.google.com| 1 forums.mozillazine.org | 1 Thanks for the quick help with substring func, people! Otis - Original Message From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Sent: Tuesday, September 11, 2007 11:16:15 PM Subject: Re: [SQL] Extracting hostname from URI column Hi, Thanks, perfect! (though I'll have to look into the regex warning): => select substring( href from '.*://\([^/]*)' ) as hostname from url where id<10; WARNING: nonstandard use of escape in a string literal at character 29 HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. So now I have this: hostname -- texturizer.net texturizer.net forums.mozillazine.org www.mozillazine.org devedge.netscape.com www.google.com groups.google.com www.google.com dictionary.reference.com And what I'd like is something that would give me the counts for the number of occurrences of each unique hostname. Something much like `uniq -c'. Can anyone tell me how that's done or where I should look for info? (I'm not sure what to look for, that's the problem). Thanks, Otis - Original Message From: chester c young <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Cc: sql pgsql Sent: Tuesday, September 11, 2007 8:42:46 PM Subject: Re: [SQL] Extracting hostname from URI column > I'm trying to use substr() and position() functions to extract the > full host name (and later a domain) from a column that holds URLs. substring( href from '.*://\([^/]*)' ); Pinpoint customers who are looking for what you sell. http://searchmarketing.yahoo.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 1: 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] Extracting hostname from URI column
Paul Lambert wrote: Just use distinct... test=# select distinct count(*),substring( href from '.*://([^/]*)' ) as domain from url group by domain order by domain; OK so distinct was redundant there... it gives the same results without it. AutoDRS=# select count(*) as occurances,substring( href from '.*://([^/]*)' ) as domain from url group by domain order by occurances desc,domain; occurances | domain +-- 11 | www.google.com 4 | dictionary.reference.com 4 | texturizer.net 3 | devedge.netscape.com 3 | groups.google.com 2 | forums.mozillazine.org 2 | www.mozillazine.org (7 rows) -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
