[GENERAL] Get the number of records of a result set
Hello, I'm writing a function in PL/pgSQL and I would like to know if there is a method to get the number of records in a result set, after a select query, without executing the same query using COUNT(*). Thanks. Regards, Eugenio. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Getting the count(*) from two tables and two date ranges in same query
Adam, I don't get the correct row counts when running this SQL. It seems to produce the correct count when there are no rows found,but not when at least one row is found (I get a much higher count than when running: SELECT count(*) FROM table2 WHERE date BETWEEN.AND table1.id = n ,which is the count I want.) Might be some problem with the case statement? Or with the from table2, table3 where table2.table1id = table3.table1id part? Very grateful for help! Håkan Jacobsson - System Developer RELEVANT TRAFFIC EUROPE AB, Riddarg 17D, SE-114 57 Sthlm, Sweden Mobile (+46) 736 56 97 58 Direct (+46) 8 56 24 98 05 Phone to office (+46) 8 678 97 50 || Fax (+46) 8 661 19 22 -Ursprungligt meddelande- Från: Adam Rich [mailto:[EMAIL PROTECTED] Skickat: den 28 januari 2008 16:27 Till: Håkan Jacobsson; pgsql-general@postgresql.org Ämne: Re: [GENERAL] Getting the count(*) from two tables and two date ranges in same query Håkan, You can add as many date ranges as you need: Select t1.id, sum(case when t2.date between d1 and d2 then 1 else 0 end) as sum1, sum(case when t2.date between d3 and d4 then 1 else 0 end) as sum2 sum(case when t3.date between d1 and d2 then 1 else 0 end) as sum3, sum(case when t3.date between d3 and d4 then 1 else 0 end) as sum4 from t1, t2, t3 where t1.id=t2.id and t2.id = t3.id group by t1.id If you know the ID, you can modify slightly: Select sum(case when t2.date between d1 and d2 then 1 else 0 end) as sum1, sum(case when t2.date between d3 and d4 then 1 else 0 end) as sum2 sum(case when t3.date between d1 and d2 then 1 else 0 end) as sum3, sum(case when t3.date between d3 and d4 then 1 else 0 end) as sum4 from t2, t3 where t2.id = t3.id and t2.id = 123456 --- Håkan Jacobsson [EMAIL PROTECTED] wrote: Hi Adam and all, I don't get it=). How do I input the second daterange in this query? Also, I have the ID from table1. Its known in the query. Wouldn't I need to use a UNION for this kind of query? Håkan Jacobsson - System Developer RELEVANT TRAFFIC EUROPE AB, Riddarg 17D, SE-114 57 Sthlm, Sweden Mobile (+46) 736 56 97 58 Direct (+46) 8 56 24 98 05 Phone to office (+46) 8 678 97 50 || Fax (+46) 8 661 19 22 -Ursprungligt meddelande- Från: Adam Rich [mailto:[EMAIL PROTECTED] Skickat: den 28 januari 2008 15:22 Till: Håkan Jacobsson; pgsql-general@postgresql.org Ämne: RE: [GENERAL] Getting the count(*) from two tables and two date ranges in same query Resulting in 4 columns in the ResultSet like: count(*)_from_table2_between_fromdate1_and_todate1 = X count(*)_from_table2_between_fromdate2_and_todate2 = Y count(*)_from_table3_between_fromdate1_and_todate1 = Z count(*)_from_table3_between_fromdate2_and_todate2 = V Is this possible? Select t1.id, sum(case when t2.date between d1 and d2 then 1 else 0 end) as sum1, sum(case when t3.date between d1 and d2 then 1 else 0 end) as sum2 from t1, t2, t3 where t1.id=t2.id and t2.id = t3.id group by t1.id ---(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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Get the number of records of a result set
Hello http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS regards Pavel Stehule On 30/01/2008, Eugenio Tacchini [EMAIL PROTECTED] wrote: Hello, I'm writing a function in PL/pgSQL and I would like to know if there is a method to get the number of records in a result set, after a select query, without executing the same query using COUNT(*). Thanks. Regards, Eugenio. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Get the number of records of a result set
On Wed, 2008-01-30 at 09:14 +0100, Eugenio Tacchini wrote: Hello, I'm writing a function in PL/pgSQL and I would like to know if there is a method to get the number of records in a result set, after a select query, without executing the same query using COUNT(*). not sure what exactly you mean, but perhaps this could help? del_stime := timeofday(); execute del_qry; del_etime := timeofday(); GET DIAGNOSTICS del_rows = ROW_COUNT; This would get you the # of rows inserted into the destination table etc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Getting the count(*) from two tables and two date ranges in same query
Adam, I just realised that issuing the SQL on one table produces the correct count. SELECT sum(case when table2.date between '2007-07-13' and '2007-07-13' then 1 else 0 end) as sumx FROM table2 WHERE id = n; This is working alright. So the problem should lie in the last part: from table2, table3 where table2.id = table3.id and table2.id = n; I think I need the UNION statement instead? Håkan Jacobsson - System Developer RELEVANT TRAFFIC EUROPE AB, Riddarg 17D, SE-114 57 Sthlm, Sweden Mobile (+46) 736 56 97 58 Direct (+46) 8 56 24 98 05 Phone to office (+46) 8 678 97 50 || Fax (+46) 8 661 19 22 ---(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: [GENERAL] Get the number of records of a result set
At 16.35 30/01/2008 +0800, Ow Mun Heng wrote: not sure what exactly you mean, but perhaps this could help? del_stime := timeofday(); execute del_qry; del_etime := timeofday(); GET DIAGNOSTICS del_rows = ROW_COUNT; This would get you the # of rows inserted into the destination table etc. This is exactly what I mean, thanks a lot to you and Pavel. Eugenio. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgresql book - practical or something newer?
On Jan 30, 2008 11:35 AM, Raymond O'Donnell [EMAIL PROTECTED] wrote: On 30/01/2008 11:27, Gregory Stark wrote: In fact I think most of the features you'll look for examples of will be from the last 1-2 years. When 8.3 comes out people will be looking for whole books on XML functionality, tsearch implementations, etc, and there will be nothing aside from the manual since they're all brand new features. Isn't this the idea of the interactive online docs? People can add stuff they find useful for others. The PHP docs have tons of extra snippets added by users - some dross, granted, but there's a lot of good stuff there too. It most certainly is, please, add away! The comments are moderated, so hopefully there's not too much dross in ours! /D ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgresql book - practical or something newer?
On 30/01/2008 11:27, Gregory Stark wrote: In fact I think most of the features you'll look for examples of will be from the last 1-2 years. When 8.3 comes out people will be looking for whole books on XML functionality, tsearch implementations, etc, and there will be nothing aside from the manual since they're all brand new features. Isn't this the idea of the interactive online docs? People can add stuff they find useful for others. The PHP docs have tons of extra snippets added by users - some dross, granted, but there's a lot of good stuff there too. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql book - practical or something newer?
Ow Mun Heng [EMAIL PROTECTED] writes: rant One of the worst aspect of PG is the documentation, or the lack of it in terms of traditional house. The Manual is fine and all, but in most cases, what I find that it lacks is actually examples. Either examples to show what it a particular field/query means but also as a way to show exactly how a particular problem can be solved. I always thought one of the best things about the manual was that it has tons of examples. Arguably too many examples for a reference manual but personally I find it easier to learn from examples than reference text anyways so I appreciate it. When I played with both MSSQL and MySQL, I had loads of books (and I bought a bit of it too, didn't bother subscribing to safari, it just ain't a book!) to be used as reference and what not. In PG, all there is, is the manual, a book by Robert Treat, the Book from Joshua, 1 or 2 other books authored by someone I can't remember etc and that's about it. Actually there are several other books, but they're mostly out of date. This is the biggest source of the problem you're complaining about I think. Most of the features you're looking for documentation for will be from the last 2-3 years and it takes about that long for books to get into print. In fact I think most of the features you'll look for examples of will be from the last 1-2 years. When 8.3 comes out people will be looking for whole books on XML functionality, tsearch implementations, etc, and there will be nothing aside from the manual since they're all brand new features. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning ---(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: [GENERAL] postgresql book - practical or something newer?
On Wed, 2008-01-30 at 15:54 +0800, Ow Mun Heng wrote: rant One of the worst aspect of PG is the documentation, or the lack of it in terms of traditional house. The Manual is fine and all, but in most cases, what I find that it lacks is actually examples. Either examples to show what it a particular field/query means but also as a way to show exactly how a particular problem can be solved. With respect, I have to disagree here. The strength of PG's documentation is, in fact, one of the key reasons I switched my company completely off a commercial RDBMS and onto PostgreSQL. In my opinion, PostgreSQL has, hands-down, the best documentation of any FOSS package I've used, and it's better than much commercial documentation too. The development group seems to be be uncompromising in its dedication to keeping the documentation up-to-date, accurate, and thorough. You should see what some of these commercial vendors try to pass off as documentation! It's awful. I don't disagree with your point that it's not robust with examples of exactly how a particular problem can be solved. But I think there are enough, and more importantly, I don't think problem-solving is an important focus for a manual (that's why 3rd party books exist). The manual needs to be *the* reference document so that end users don't need to read source code in order to understand how the system works. Example-oriented documentation has a tendency to skimp on the reference material and leave big gaping holes, in my experience. I like the reference focus of the existing PostgreSQL manual very much. -- Jason Topaz [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgresql book - practical or something newer?
On Wed, 30 Jan 2008 11:27:20 + Gregory Stark [EMAIL PROTECTED] wrote: Ow Mun Heng [EMAIL PROTECTED] writes: rant One of the worst aspect of PG is the documentation, or the lack of it in terms of traditional house. The Manual is fine and all, but in most cases, what I find that it lacks is actually examples. Either examples to show what it a particular field/query means but also as a way to show exactly how a particular problem can be solved. I always thought one of the best things about the manual was that it has tons of examples. Arguably too many examples for a reference manual but personally I find it easier to learn from examples than reference text anyways so I appreciate it. Evil is in the details. Some examples don't really show off the power of postgresql. Sometimes you look at an example, you know other related stuff and say... mmm I know I can push this further but how? How/where is it possible to submit doc patches? Even for older versions? There were things I didn't find so easy to understand/guess in the manual, no rocket science, I took note of them or I just found external pages that actually explained how to do that and I think their place should actually be in the manual. BTW examples are a sort of specification too. I wouldn't underestimate their more formal value. So I think they should be part of *the* reference documentation with example output as well. They shouldn't be of the kind how-to but of the kind you can't push the syntax further and this is what you'd expect as an output. Many things are already there in the VI Reference section but some are not, especially in the V Server programming part. -- Ivan Sergio Borgonovo http://www.webthatworks.it ---(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: [GENERAL] postgresql book - practical or something newer?
On 30/01/2008 12:12, Ivan Sergio Borgonovo wrote: Many things are already there in the VI Reference section but some are not, especially in the V Server programming part. +1 The Server Programming section is where we really need lots of examples. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgresql book - practical or something newer?
Joshua D. Drake wrote: Tom Hart [EMAIL PROTECTED] wrote: [...] I find that the best way to get what you need, is to read the fine manual from postgresql. Yes, its massive, unwieldy and in a lot of ways counter-intuitive (to a newbie) but if you have the terminology down you aren't going to find a more comprehensive text. I find the manual answers just about everything I've needed to answer. Personally though I find the on-line version somewhat slow/cumbersome to find what I'm looking for. Using Windows as my desktop machine (servers running Linux) I found the most accessible form of the manual was that distributed with pgAdminIII. Until recently they shipped a fully searchable Windows Help version of the latest manual which was fantastic. Unfortunately pgAdmin has now removed that section of the manual and simply links to the Postgres web-site. I can understand it was some work to put it in each time - but it was very useful. So much so when I get a chance I intend to find an older copy of pgAdmin and install just the manual from it. Has anyone else generated a Windows Help version of the manual? Is there a source version of the files used to generate it (pgAdmin people?)? I'd be interested in the amount of work needed to create the file - if not excessive I might volunteer to get it done again if people other than me might find it useful. Pete Plus, when you find things that don't quite make sense you can submit a doc patch to make the docs that much better. Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHn2oKATb/zqfZUUQRAtgCAKCOigLpBd9/EcYVPF/QsDvCYS3JugCggtS9 9QK5xte33f0/2+N/0pWOQvY= =ioXq -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Peter Wilson T: 01707 891840 M: 07796 656566 http://www.yellowhawk.co.uk The information in this email is confidential and is intended for the addressee/s only. Access to this email by anyone else is unauthorised. If you are not the intended recipient, you must not read, use or disseminate the information contained in or attached to this email. ---(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: [GENERAL] [OT] Slony + Alter table using pgadmin
On Jan 29, 2008, at 23:42 , Ow Mun Heng wrote: This is OT for this list and I don't have access to I-net (only email) and I'm not subscribed to the Slony list. To see your options for the slony1-general mailing list by email, send a message to: [EMAIL PROTECTED] with the word `help' in the subject or body (don't include the quotes), and you will get back a message with instructions. Michael Glaesemann grzm seespotcode net ---(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: [GENERAL] postgresql book - practical or something newer?
On Jan 30, 2008 12:45 PM, Peter Wilson [EMAIL PROTECTED] wrote: Has anyone else generated a Windows Help version of the manual? We distribute it with PostgreSQL - it's just not integrated with the pgAdmin help any more. You can even tell pgAdmin to use that if you don''t wish to use the online help. Regards, Dave. ---(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: [GENERAL] postgresql book - practical or something newer?
On Wed, 30 Jan 2008 11:27:20 + Gregory Stark [EMAIL PROTECTED] wrote: BTW examples are a sort of specification too. I wouldn't underestimate their more formal value. So I think they should be part of *the* reference documentation with example output as well. They shouldn't be of the kind how-to but of the kind you can't push the syntax further and this is what you'd expect as an output. In the manual yes, but I think there's definately a need for a howto document, something that demonstrates how to handle typical database functionality in PgSQL. Many of the people I've convinced to start using PostgeSQL spend the first week or so asking me questions on how to do basic things in PostgreSQL. When I say that there's a manual, the complaint usually is what I've noticed myself: the manual is great for looking up individual facts, but your problem may consist of 15 facts and it's up to you to connect the dots. This can be very confusing and discouraging to the average MySQL migrator (ugh, I said the M word :) ) What people like about the books is that the books usually tackle reallife problems from start to finish. Shurely the PgSQL community must be able to piece together something like that? It doesn't have to be a paper-book, although there are companies that print on demand and ship directly to the customer. Isn't there a wiki somewhere that we can fill with reallife stuff? Then all 8.3 stuff could be added there to, even before 8.3 is released. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql book - practical or something newer?
Am Mittwoch, 30. Januar 2008 schrieb Raymond O'Donnell: Isn't this the idea of the interactive online docs? People can add stuff they find useful for others. Well, not really, for better or worse. Each release, we take the comments and either fold them into the main documentation or delete them. So the interactive feature is more of an easier way to submit additions or corrections; it is not meant to add a user-edited extra dimension to the documentation material. The well-hidden techdocs section of the web site is supposed to allow users to submit tips, articles, and the like, but I'm not sure how accessible that is. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgresql book - practical or something newer?
Dave Page wrote: On Jan 30, 2008 1:34 PM, Peter Wilson [EMAIL PROTECTED] wrote: Dave Page wrote: On Jan 30, 2008 12:45 PM, Peter Wilson [EMAIL PROTECTED] wrote: Has anyone else generated a Windows Help version of the manual? Is it only distributed with the Window distribution? Yes, at present. I guess it's something we could add to the website though. /D That would get my vote - maybe as an extra column on the following page http://www.postgresql.org/docs/manuals/ Pete ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] ECPG problem with 8.3
Michael Meskes wrote: On Mon, Jan 14, 2008 at 10:57:45AM -0500, Tom Lane wrote: I'm concerned about this too. We'll at least have to call this out as an incompatibility in 8.3, and it seems like a rather unnecessary step backwards. Given that people seem to use this feature I'm more than willing to implement it, although it might become a bit hackish. Given that fetch is not a preparable statement we can live with the slightly inconsistent variable handling I think. Expect a patch soon. Michael I've just tested my original un-tweaked ECPG application code against 8.3RC2 and everything compiles and runs fine - including the variable count argument. Thanks very much Michael Pete ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgresql book - practical or something newer?
On Jan 30, 2008 1:34 PM, Peter Wilson [EMAIL PROTECTED] wrote: Dave Page wrote: On Jan 30, 2008 12:45 PM, Peter Wilson [EMAIL PROTECTED] wrote: Has anyone else generated a Windows Help version of the manual? We distribute it with PostgreSQL - it's just not integrated with the pgAdmin help any more. You can even tell pgAdmin to use that if you don''t wish to use the online help. Hi Dave, good to know it still exists. Is it only distributed with the Window distribution? Yes, at present. I guess it's something we could add to the website though. /D ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql book - practical or something newer?
Dave Page wrote: On Jan 30, 2008 12:45 PM, Peter Wilson [EMAIL PROTECTED] wrote: Has anyone else generated a Windows Help version of the manual? We distribute it with PostgreSQL - it's just not integrated with the pgAdmin help any more. You can even tell pgAdmin to use that if you don''t wish to use the online help. Hi Dave, good to know it still exists. Is it only distributed with the Window distribution? I only run Postgres on Linux boxes, but use a Windows desktop machine. Is there a place where I can just download the .chm file without having to install Postgres on Windows? All the best Pete Regards, Dave. ---(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 -- Peter Wilson T: 01707 891840 M: 07796 656566 http://www.yellowhawk.co.uk The information in this email is confidential and is intended for the addressee/s only. Access to this email by anyone else is unauthorised. If you are not the intended recipient, you must not read, use or disseminate the information contained in or attached to this email.
Re: [GENERAL] postgresql book - practical or something newer?
Am Mittwoch, 30. Januar 2008 schrieb Peter Wilson: Has anyone else generated a Windows Help version of the manual? It can be built from the source code using the make htmlhelp target in doc/src/sgml/. I don't know how to get from there to the final format, though. I understand it is proprietary. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql book - practical or something newer?
Am Mittwoch, 30. Januar 2008 schrieb Ivan Sergio Borgonovo: How/where is it possible to submit doc patches? [EMAIL PROTECTED] -- The process is mostly the same as for normal code. The Developer section of the web site gives you more information. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Clustering/Partitioning tables from existing tables?
Hello, Last night I was working realy hard (10 hours) while reinstalling some servers in Freiburg and now I have a big problem/question to tables... My customer had used PostgreSQL 7.4 and we have dumped all tables into separated dumps because the tables are too big!!! Formerly, it was the software accessing the Database which handled the searches in the over 8000 tables which have between 50 and 2000 MByte each! We have now installed PostgreSQL 8.2 and imported all tables but since this tables are generaly ONE table, I like to make a partitioned table from it. Q 1: How to create the master table now? Q 2: Can anyone provide me a script whic do that. (I am NOT THE GENIE) Note: The tables have only names like data_N where N is a number from 0 (yes, with leading zeros) to 9. Thanks, Greetings and nice Day Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSN LinuxMichi 0033/6/6192519367100 Strasbourg/France IRC #Debian (irc.icq.com) signature.pgp Description: Digital signature
Re: [GENERAL] Can we have 2 databases on same server
Hello Suresh, Suresh Gupta VG wrote: Hi Team, I have Solaris 9 machine with Pgsql 7.4 and want to upgrade to 8.2.5. I don't want to disturb the first version now. I want to install second one separately and need to test with my application. If it works fine, I will activate only the latest one and other version 7.4 will be discarded. This is my idea. 1) Can I please know, if we can run 2 databases on the same server with out any conflicts? It looks like you already got the answer on this one.. however, your terminology is a bit off. Unlike Oracle (where we think in terms of databases), in PostgreSQL we think in terms of clusters, where a cluster (often used interchangeably with the word server) is a collection of databases. A cluster listens on a single port, has a single data directory, and manages a single list of roles (accounts and groups) that span all of the databases that are managed by the cluster. Hope that helps. When you read the documentation you will see references to Clusters, not databases... -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 919-463-0999/877-258-8987 http://www.otg-nc.com Ask me about Expert PostgreSQL PostGIS Training Delivered Worldwide.
Re: [GENERAL] PGCon vs Postgresql Conference
On Jan 30, 2008, at 12:18 AM, Sim Zacks wrote: Anyway my suggestion to Sim is to read about each conference on the respective conference websites: http://www.postgresqlconference.org/why/ http://www.pgcon.org/2008/ I read those, I was just confused as to why there were 2 conferences on the same topics one right after the other. Someone mentioned that one is commercial and the other is not-for- profit, so someone might define PGCon as proprietary and say the postgresqlconference is open source, but that would start a flame war and would be inappropriate being that both of them are supporting open source software, and nobody is against making a profit (except Communists, and they're not against profit as long as the guy who worked hard to earn it doesn't get to keep it). So, at which should one wear red? Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Getting the count(*) from two tables and two date ranges in same query
=?iso-8859-1?Q?H=E5kan_Jacobsson?= [EMAIL PROTECTED] writes: I just realised that issuing the SQL on one table produces the correct count. SELECT sum(case when table2.date between '2007-07-13' and '2007-07-13' then 1 else 0 end) as sumx FROM table2 WHERE id = n; This is working alright. So the problem should lie in the last part: from table2, table3 where table2.id = table3.id and table2.id = n; It sounds like there are multiple rows in table3 matching the id of (at least some of) the rows in table2. You'll get an increment to the sum for each join pair. regards, tom lane ---(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: [GENERAL] postgresql book - practical or something newer?
vincent [EMAIL PROTECTED] writes: In the manual yes, but I think there's definately a need for a howto document, something that demonstrates how to handle typical database functionality in PgSQL. Many of the people I've convinced to start using PostgeSQL spend the first week or so asking me questions on how to do basic things in PostgreSQL. When I say that there's a manual, the complaint usually is what I've noticed myself: the manual is great for looking up individual facts, but your problem may consist of 15 facts and it's up to you to connect the dots. Surely even a book that's a little out-of-date can serve fine for that kind of introduction? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql book - practical or something newer?
Tom Lane wrote: vincent [EMAIL PROTECTED] writes: In the manual yes, but I think there's definately a need for a howto document, something that demonstrates how to handle typical database functionality in PgSQL. Many of the people I've convinced to start using PostgeSQL spend the first week or so asking me questions on how to do basic things in PostgreSQL. When I say that there's a manual, the complaint usually is what I've noticed myself: the manual is great for looking up individual facts, but your problem may consist of 15 facts and it's up to you to connect the dots. Surely even a book that's a little out-of-date can serve fine for that kind of introduction? regards, tom lane I agree that it would be useful as an introduction, but I have 4 years of mySQL experience (I know, I'm sorry) and I've been working with postgres for the past 3-4 months during which time I've built a data mine by hand, and set up a few different web apps running against it (drupal, openreports, etc.) so I think I'm past the introduction phase. What I was looking for was an intermediate level (call me presumptuous) book with more performance tips and advanced techniques/functions. Even though this book may have some sort of this information in it, it's going to be based on 7.x and the entire thing is available online (as well as the docs, which personally I like). And on the subject of beginner's documentation, I think I learned a lot more playing/hacking/reading docs/posting here (of course that's always been my preferred learning method) then I would have with a book. Everybody has their own learning style and different things work well for different people. The key here is that when it's up to you to connect the dots then you learn what the dots are, how they relate to each other, and what each of them is for. That gives you a lot better understanding then Just run SELECT count(*) FROM a LEFT JOIN Of course that's just my opinion, I could be wrong :-) -- Tom Hart IT Specialist Cooperative Federal 723 Westcott St. Syracuse, NY 13210 (315) 471-1116 ext. 202 (315) 476-0567 (fax) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgresql book - practical or something newer?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 30 Jan 2008 13:20:58 -0500 Tom Hart [EMAIL PROTECTED] wrote: regards, tom lane I agree that it would be useful as an introduction, but I have 4 years of mySQL experience (I know, I'm sorry) and I've been working with postgres for the past 3-4 months during which time I've built a data mine by hand, and set up a few different web apps running against it (drupal, openreports, etc.) so I think I'm past the introduction phase. What I was looking for was an intermediate level (call me presumptuous) book with more performance tips and advanced techniques/functions. Even though this book may have some sort of this information in it, it's going to be based on 7.x and the entire thing is available online (as well as the docs, which personally I like). The above sounds like you want a cookbook not a manual. In proper open source fashion perhaps you could start documenting the things you learn and post them to Techdocs :) Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHoMJ4ATb/zqfZUUQRAh5mAKCsjIbE7dw+fljZitcMpw0t7vd1vQCdHh9g i/I1lcXst6i+FfO5+JpKVrs= =cSfc -END PGP SIGNATURE- ---(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
[GENERAL] XML-support
Hi, I noticed the upcoming support for xml in 8.3: Does anybody know if this allows updates, inserts,removes and renames of nodes to a XML-document (as for Xindice)? Regards Sigurd Nes ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql book - practical or something newer?
Tom Lane wrote: vincent [EMAIL PROTECTED] writes: Surely even a book that's a little out-of-date can serve fine for that kind of introduction? I guess the point is that using older books is the only option, there simple are no uptodate books available. People who want to use a book (and many do) are forced to learn PgSQL the way it was a few years ago. regards, tom lane The key here is that when it's up to you to connect the dots then you learn what the dots are, how they relate to each other, and what each of them is for. True, but that only works for experienced 'nerds' who get a kick out of connecting dots. Joe Average want's a bit more assistance, a bit more guidance. In short; I think PgSQL needs a beginnersbook, an advanced-nerdy book, and a bible... oh yeah, we need a PgSQL bible, no doubt! :) Cookbooks are also nice, but I guess from a growth point of view a beginnersbook is definately a must-have. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgresql book - practical or something newer?
--- vincent [EMAIL PROTECTED] wrote: On Wed, 30 Jan 2008 11:27:20 + Gregory Stark [EMAIL PROTECTED] wrote: BTW examples are a sort of specification too. I wouldn't underestimate their more formal value. So I think they should be part of *the* reference documentation with example output as well. They shouldn't be of the kind how-to but of the kind you can't push the syntax further and this is what you'd expect as an output. In the manual yes, but I think there's definately a need for a howto document, something that demonstrates how to handle typical database functionality in PgSQL. Many of the people I've convinced to start using PostgeSQL spend the first week or so asking me questions on how to do basic things in PostgreSQL. When I say that there's a manual, the complaint usually is what I've noticed myself: the manual is great for looking up individual facts, but your problem may consist of 15 facts and it's up to you to connect the dots. More documentation would be nice, but surely it's more down to getting the type of user base that write your average how to books? The O'Reilly books seem to cover postgres quite nicely, however I've only had a flick through in shops. One thing's for sure, 2 months ago I signed up to the most common postgresql and m*sql lists when I was trying to decide what was best for our backend. At the time m*sql was my 1st choice, and it took me less than a day to drop those toys in the street and decide postgresql was the way forward. ___ Support the World Aids Awareness campaign this month with Yahoo! For Good http://uk.promotions.yahoo.com/forgood/ ---(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: [GENERAL] postgresql book - practical or something newer?
Glyn Astill wrote: More documentation would be nice, but surely it's more down to getting the type of user base that write your average how to books? The O'Reilly books seem to cover postgres quite nicely, however I've only had a flick through in shops. One thing's for sure, 2 months ago I signed up to the most common postgresql and m*sql lists when I was trying to decide what was best for our backend. At the time m*sql was my 1st choice, and it took me less than a day to drop those toys in the street and decide postgresql was the way forward. ___ Support the World Aids Awareness campaign this month with Yahoo! For Good http://uk.promotions.yahoo.com/forgood/ I definitely think that the lists are one of the shining stars for postgresql support. I've learned some good reference stuff from online docs/google but the really tricky questions were only answered here, and amazingly enough, quickly and with good humor. Perhaps what we really need is somebody to comb through the archives looking for common problems or exceptional solutions and compile them into a book. -- Tom Hart IT Specialist Cooperative Federal 723 Westcott St. Syracuse, NY 13210 (315) 471-1116 ext. 202 (315) 476-0567 (fax) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql book - practical or something newer?
vincent wrote: True, but that only works for experienced 'nerds' who get a kick out of connecting dots. Joe Average want's a bit more assistance, a bit more guidance. Have you read the Tutorial section of the docs? What do you feel it is missing? Can you contribute to it? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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: [GENERAL] postgresql book - practical or something newer?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 30 Jan 2008 13:55:12 -0500 Tom Hart [EMAIL PROTECTED] wrote: I definitely think that the lists are one of the shining stars for postgresql support. I've learned some good reference stuff from online docs/google but the really tricky questions were only answered here, and amazingly enough, quickly and with good humor. Perhaps what we really need is somebody to comb through the archives looking for common problems or exceptional solutions and compile them into a book. /me looks hard at Tom Hart... Yep, looks like a volunteer to me said Bob. Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHoMnOATb/zqfZUUQRAjKPAJsEnY/OHS74AcRM3WoEdkIXWwChaACgnbcU VkU7J4iZfCiwcL8k0OqicgU= =bg/L -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] aggregate hash function
I'm in need of an aggregate hash function. Something like select md5_agg(someTextColumn) from (select someTextColumn from someTable order by someOrderingColumn). I know that there is an existing MD5 function, but it is not an aggregate. I have thought about writing a concat aggregate function that would concatenate the input into a long string and then using MD5() on that, but that seems like it would have some bad performance implications (memory consumption, possibly spilling to disk, many large memory copies, etc) as it would buildup the entire concatenated string first before hashing it. I also thought about making a aggregate function that works by keeping the MD5 result as a string in the state, then concatenating the new input with the current state, hashing that and using it as the new state. This solves the problem of building up a giant string to just traverse over at the end to get the MD5 sum. This approach would actually work for me, but it doesn't give me the actual MD5 sum of the data which is what I really want. comments/ideas/suggestions?
Re: [GENERAL] postgresql book - practical or something newer?
vincent wrote: True, but that only works for experienced 'nerds' who get a kick out of connecting dots. Joe Average want's a bit more assistance, a bit more guidance. Have you read the Tutorial section of the docs? What do you feel it is missing? Can you contribute to it? Yes I have. What's missing... well there are quite a few relatively basic things like sequences and it doesn't touch stored-procedures and triggers. I guess I could write something up in the 2.76 seconds of spare time I have every week :) But what I think would be really helpful is to get some organisation in the sources of information. Techdocs for example. Can't we gather all that information, validate it agains 8.2/8.3 and stick it into one big document and call it the PgSQL big book of wonders? Give it an exhaustive intro on every major aspect of PgSQL and that should setup users for life. ---(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: [GENERAL] postgresql book - practical or something newer?
Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 30 Jan 2008 13:55:12 -0500 Tom Hart [EMAIL PROTECTED] wrote: I definitely think that the lists are one of the shining stars for postgresql support. I've learned some good reference stuff from online docs/google but the really tricky questions were only answered here, and amazingly enough, quickly and with good humor. Perhaps what we really need is somebody to comb through the archives looking for common problems or exceptional solutions and compile them into a book. /me looks hard at Tom Hart... Yep, looks like a volunteer to me said Bob. Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHoMnOATb/zqfZUUQRAjKPAJsEnY/OHS74AcRM3WoEdkIXWwChaACgnbcU VkU7J4iZfCiwcL8k0OqicgU= =bg/L -END PGP SIGNATURE- /me misses the good old days :-) I'm definitely willing to participate in this, or maybe just start it and pass it off, but as much as I'd love to put something like this together, I currently have no internet at home (I thought nerds weren't supposed to be dead poor) and doing this all at work wouldn't be my boss's idea of high productivity, though admittedly he is the one who got me into postgreSQL and is definitely open-source friendly. I just don't think he'll want to be paying me wages to create postgreSQL docs. Is there anybody else out there who is interested in working with me on a project like this? I think it'd be an excellent way to contribute back to the list/community for the assistance we've received here that wouldn't have been given anywhere else (especially not free of charge). My e-mail is [EMAIL PROTECTED] if you're interested. -- Tom Hart IT Specialist Cooperative Federal 723 Westcott St. Syracuse, NY 13210 (315) 471-1116 ext. 202 (315) 476-0567 (fax) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] expression index on date_trunc
Hi,
is is possible to create an expression index based on the date_trunc function?
Working with PostgreSQL 8.2, I'm trying to create an index using:
CREATE INDEX request_day_idx ON moksha_sm_request (date_trunc('day',
request_received));
I get the error message:
ERROR: functions in index expression must be marked IMMUTABLE
SQL state: 42P17
I'd like to use the index to speed up a query that does a group by on the day
part of a timestamp to lump all the stuff that happens in the same day together
(I have a timestamp because the information in the table is coming from an
appserver logfile and the date/time component of each line provides a full
timestamptz (always the same tz!)).
I'd be grateful if someone could point out what part of the statement is not
IMMUTABLE or how I could mark my create index statement as being immutable.
Thanks,
Andy
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Mailing list archives/docs project
Hello everybody. What started as a question about Practical PostgreSQL has ballooned into a project to create another documentation resource, compiled entirely from mailing list archives. While discussing documentation in the general list I realized that the resource I had learned the most from and had been the most helpful to me was the mailing lists themselves. It was from this thought that an idea was born. What if we compile a book of hand-picked mailing list archives to address some of the intermediate/advanced and less-used/documented features of postgresql along with well-written solutions to not-so-common problems? Well I've decided to run with it, both to further my own knowledge of postgreSQL but also to contribute back to the community in the first way that I thought of. The purpose of this message is a call for help. I'm looking for anybody willing to help comb the archives for exceptional excerpts or submit their own list questions/experiences. Anybody willing to help in any way is more than welcome, but the first (and arguably most arduous) stage of this project will be the information gathering itself, hence the plea. If anybody is interested in contributing, email me at [EMAIL PROTECTED] and we can go from there. And of course a big thanks to anybody that takes their time to answer or assist another person. I don't know about anybody being paid to be here so our thanks is all the compensation we can offer. Sorry it's not a BMW :-) -- Tom Hart IT Specialist Cooperative Federal 723 Westcott St. Syracuse, NY 13210 (315) 471-1116 ext. 202 (315) 476-0567 (fax) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Oracle Analytical Functions
I'm trying to replicate the use of Oracle's 'lag' and 'over partition by' analytical functions in my query. I have a table (all_client_times) such as: client_id, datetime 122, 2007-05-01 12:00:00 122, 2007-05-01 12:01:00 455, 2007-05-01 12:02:00 455, 2007-05-01 12:03:00 455, 2007-05-01 12:08:00 299, 2007-05-01 12:10:00 299, 2007-05-01 12:34:00 and I would like to create a new view that takes the first table and calculates the time difference in minutes between each row so that the result is something like: client_id,datetime, previousTime, difftime 122,2007-05-01 12:01:00, 2007-05-01 12:00:00, 1 455,2007-05-01 12:03:00, 2007-05-01 12:02:00, 1 455,2007-05-01 12:08:00, 2007-05-01 12:03:00, 5 299,2007-05-01 12:34:00, 2007-05-01 12:10:00, 24 In Oracle I can achieve this with: CREATE OR REPLACE VIEW client_time_diffs AS SELECT client_id,datetime, LAG(datetime, 1) OVER (partition by client_id ORDER BY client_id,datetime) AS previoustime from all_client_times; Any idea how I could replicate this in SQL from PG. Would this be an easy thing to do in Pl/pgSQL? If so could anyone give any directions as to where to start? Appreciate the help, Willem ---(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: [GENERAL] Oracle Analytical Functions
-Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Willem Buitendyk Sent: Wednesday, January 30, 2008 1:15 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Oracle Analytical Functions I'm trying to replicate the use of Oracle's 'lag' and 'over partition by' analytical functions in my query. I have a table (all_client_times) such as: client_id, datetime 122, 2007-05-01 12:00:00 122, 2007-05-01 12:01:00 455, 2007-05-01 12:02:00 455, 2007-05-01 12:03:00 455, 2007-05-01 12:08:00 299, 2007-05-01 12:10:00 299, 2007-05-01 12:34:00 and I would like to create a new view that takes the first table and calculates the time difference in minutes between each row so that the result is something like: client_id,datetime, previousTime, difftime 122,2007-05-01 12:01:00, 2007-05-01 12:00:00, 1 455,2007-05-01 12:03:00, 2007-05-01 12:02:00, 1 455,2007-05-01 12:08:00, 2007-05-01 12:03:00, 5 299,2007-05-01 12:34:00, 2007-05-01 12:10:00, 24 In Oracle I can achieve this with: CREATE OR REPLACE VIEW client_time_diffs AS SELECT client_id,datetime, LAG(datetime, 1) OVER (partition by client_id ORDER BY client_id,datetime) AS previoustime from all_client_times; Any idea how I could replicate this in SQL from PG. Would this be an easy thing to do in Pl/pgSQL? If so could anyone give any directions as to where to start? You could certainly create a cursor and then just use age() or other time difference extraction method as appropriate: http://www.postgresql.org/docs/8.2/static/sql-declare.html http://www.postgresql.org/docs/8.2/static/functions-datetime.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Oracle Analytical Functions
and I would like to create a new view that takes the first table and calculates the time difference in minutes between each row so that the result is something like: client_id,datetime, previousTime, difftime 122,2007-05-01 12:01:00, 2007-05-01 12:00:00, 1 455,2007-05-01 12:03:00, 2007-05-01 12:02:00, 1 455,2007-05-01 12:08:00, 2007-05-01 12:03:00, 5 299,2007-05-01 12:34:00, 2007-05-01 12:10:00, 24 Any idea how I could replicate this in SQL from PG. Would this be an easy thing to do in Pl/pgSQL? If so could anyone give any directions as to where to start? You can create a set-returning function, that cursors over the table, like this: CREATE OR REPLACE FUNCTION lagfunc( OUT client_id INT, OUT datetime timestamp, OUT previousTime timestamp, OUT difftime interval) RETURNS SETOF RECORD as $$ DECLARE thisrow RECORD; last_client_id INT; last_datetime timestamp; BEGIN FOR thisrow IN SELECT * FROM all_client_times ORDER BY client_id, datetime LOOP IF thisrow.client_id = last_client_id THEN client_id := thisrow.datetime; datetime := thisrow.datetime; previousTime := last_datetime; difftime = datetime-previousTime; RETURN NEXT; END IF; last_client_id := thisrow.client_id; last_datetime := thisrow.datetime; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; select * from lagfunc() limit 10; select * from lagfunc() where client_id = 455; Here I used an interval, but you get the idea. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Mailing list archives/docs project
On Wed, 30 Jan 2008 16:10:07 -0500 Tom Hart [EMAIL PROTECTED] wrote: Hello everybody. What started as a question about Practical PostgreSQL has ballooned into a project to create another [snip] I generally tend to write stuff that was not clear on my website. http://www.webthatworks.it/d1/taxonomy_menu/2/3/10/11 At this moment very few info... I was planning to publish some more notes that now are just local txt files. Other sources of info are: http://www.network-theory.co.uk/docs/postgresql/vol1/index.html and the other volumes http://www.postgresql.org/files/documentation/books/aw_pgsql/15467.html here there are good tech articles but hard to spot in all non reference/example material http://people.planetpostgresql.org/xzilla/ great resource for how-tos http://www.varlena.com/ I downloaded a: annotated postgresq.conf guide for postgres in pdf but I can't find the source. I'm a dev not a DBA so I generally don't collect info about management and tuning. BTW nice drupal website. I'm looking for a drupal web designer. -- Ivan Sergio Borgonovo http://www.webthatworks.it ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Oracle Analytical Functions
How about something like this: SELECT client_id , datetime , lagged as previoustime , datetime - lagged difftime FROM ( SELECT client_id ,datetime ,(SELECT MAX(datetime) FROM all_client_times def WHERE def.client_id = abc.client_id AND def.datetime abc.datetime) as lagged FROM all_client_times abc ) WHERE lagged is not null If you have records with no previous data or multiple rows, you'll need to play with this to get it to work but it should point in the right direction. Hope that helps, LewisC --- Willem Buitendyk [EMAIL PROTECTED] wrote: I'm trying to replicate the use of Oracle's 'lag' and 'over partition by' analytical functions in my query. I have a table (all_client_times) such as: client_id, datetime 122, 2007-05-01 12:00:00 122, 2007-05-01 12:01:00 455, 2007-05-01 12:02:00 455, 2007-05-01 12:03:00 455, 2007-05-01 12:08:00 299, 2007-05-01 12:10:00 299, 2007-05-01 12:34:00 and I would like to create a new view that takes the first table and calculates the time difference in minutes between each row so that the result is something like: client_id,datetime, previousTime, difftime 122,2007-05-01 12:01:00, 2007-05-01 12:00:00, 1 455,2007-05-01 12:03:00, 2007-05-01 12:02:00, 1 455,2007-05-01 12:08:00, 2007-05-01 12:03:00, 5 299,2007-05-01 12:34:00, 2007-05-01 12:10:00, 24 In Oracle I can achieve this with: CREATE OR REPLACE VIEW client_time_diffs AS SELECT client_id,datetime, LAG(datetime, 1) OVER (partition by client_id ORDER BY client_id,datetime) AS previoustime from all_client_times; Any idea how I could replicate this in SQL from PG. Would this be an easy thing to do in Pl/pgSQL? If so could anyone give any directions as to where to start? Appreciate the help, Willem ---(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 Lewis R Cunningham An Expert's Guide to Oracle Technology http://blogs.ittoolbox.com/oracle/guide/ LewisC's Random Thoughts http://lewiscsrandomthoughts.blogspot.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Mailing list archives/docs project
Ivan Sergio Borgonovo wrote: On Wed, 30 Jan 2008 16:10:07 -0500 Tom Hart [EMAIL PROTECTED] wrote: Hello everybody. What started as a question about Practical PostgreSQL has ballooned into a project to create another [snip] I generally tend to write stuff that was not clear on my website. http://www.webthatworks.it/d1/taxonomy_menu/2/3/10/11 At this moment very few info... I was planning to publish some more notes that now are just local txt files. Other sources of info are: http://www.network-theory.co.uk/docs/postgresql/vol1/index.html and the other volumes http://www.postgresql.org/files/documentation/books/aw_pgsql/15467.html here there are good tech articles but hard to spot in all non reference/example material http://people.planetpostgresql.org/xzilla/ great resource for how-tos http://www.varlena.com/ I downloaded a: annotated postgresq.conf guide for postgres in pdf but I can't find the source. I'm a dev not a DBA so I generally don't collect info about management and tuning. BTW nice drupal website. I'm looking for a drupal web designer. Thanks for the response. The book/document itself will focus on the mailing list archives but I have forwarded the information onto the team that has assembled so far to look at the possibility of creating an appendix with extra information such as what's above. Also, I'm glad you like our site :-) The external site was developed by my supervisor, and I'm currently involved in developing our intranet on a separate drupal install. Unfortunately neither of us is very talented :-) but I know there's about 10 billion drupal developers and theme designers out there so it shouldn't be too hard to find somebody who can help you out. -- Tom Hart IT Specialist Cooperative Federal 723 Westcott St. Syracuse, NY 13210 (315) 471-1116 ext. 202 (315) 476-0567 (fax) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] XML-support
Sigurd Nes wrote: I noticed the upcoming support for xml in 8.3: Does anybody know if this allows updates, inserts,removes and renames of nodes to a XML-document (as for Xindice)? No, it doesn't support that directly. I guess you could achieve it by using XSLT. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [pgsql-advocacy] PostgreSQL Certification
Josh, Myself and a small team of PostgreSQL contributors have started a new community project for PostgreSQL Certification. It is just launching but we wanted to get it out there so that people can join in on the discussion now :). Who else is in this? Have you talked to the Venezualan folks? SRA? As you know, I'm strongly in favor of a good, generally respected certification. Let's get all of the interested folks on one project. --Josh ---(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: [GENERAL] [pgsql-advocacy] PostgreSQL Certification
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 30 Jan 2008 14:17:43 -0800 Josh Berkus [EMAIL PROTECTED] wrote: Current broadcast members are: Myself Magnus Robert Chander (need to get him on the website) Bruce has a pending invitation (which I didn't send yet) I have not spoken with SRA or the Venezualan folks but am more than happy to have them involved. Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHoPiMATb/zqfZUUQRAqMHAJsHop8kUqHkHRLJMjNFBIny+dIiYQCfXz19 fXELUEQ3khSifVR6JJaI3K8= =N1BL -END PGP SIGNATURE- ---(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: [GENERAL] Oracle Analytical Functions
create table data ( client_id integer, datetime timestamp not null ); create index data_client_id on data(client_id); copy data from STDIN DELIMITER ','; 122,2007-05-01 12:00:00 122,2007-05-01 12:01:00 455,2007-05-01 12:02:00 455,2007-05-01 12:03:00 455,2007-05-01 12:08:00 299,2007-05-01 12:10:00 299,2007-05-01 12:34:00 \. CREATE OR REPLACE FUNCTION visits ( OUT client_id INTEGER, OUT datetime_1 TIMESTAMP, OUT datetime_2 TIMESTAMP, OUT dur INTERVAL ) RETURNS SETOF RECORD LANGUAGE plpgsql AS $_$ DECLARE rp data%ROWTYPE;-- previous data table record r data%ROWTYPE; -- data table record, more recent than rp BEGIN rp = (NULL,NULL); FOR r IN SELECT * FROM data ORDER BY client_id,datetime LOOP IF rp.client_id = r.client_id THEN client_id = r.client_id; datetime_1 = r.datetime; datetime_2 = rp.datetime; dur = r.datetime-rp.datetime; RETURN NEXT; END IF; rp = r; END LOOP; RETURN; END; $_$; [EMAIL PROTECTED] select * from visits() order by client_id,datetime_1; client_id | datetime_1 | datetime_2 | dur ---+-+-+-- 122 | 2007-05-01 12:01:00 | 2007-05-01 12:00:00 | 00:01:00 299 | 2007-05-01 12:34:00 | 2007-05-01 12:10:00 | 00:24:00 455 | 2007-05-01 12:03:00 | 2007-05-01 12:02:00 | 00:01:00 455 | 2007-05-01 12:08:00 | 2007-05-01 12:03:00 | 00:05:00 (4 rows) -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 create table data ( client_id integer, datetime timestamp not null ); create index data_client_id on data(client_id); copy data from STDIN DELIMITER ','; 122,2007-05-01 12:00:00 122,2007-05-01 12:01:00 455,2007-05-01 12:02:00 455,2007-05-01 12:03:00 455,2007-05-01 12:08:00 299,2007-05-01 12:10:00 299,2007-05-01 12:34:00 \. CREATE OR REPLACE FUNCTION visits ( OUT client_id INTEGER, OUT datetime_1 TIMESTAMP, OUT datetime_2 TIMESTAMP, OUT dur INTERVAL ) RETURNS SETOF RECORD LANGUAGE plpgsql AS $_$ DECLARE rp data%ROWTYPE;-- previous data table record r data%ROWTYPE; -- data table record, more recent than rp BEGIN rp = (NULL,NULL); FOR r IN SELECT * FROM data ORDER BY client_id,datetime LOOP IF rp.client_id = r.client_id THEN client_id = r.client_id; datetime_1 = r.datetime; datetime_2 = rp.datetime; dur = r.datetime-rp.datetime; RETURN NEXT; END IF; rp = r; END LOOP; RETURN; END; $_$; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] PostgreSQL Certification
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hey guys, Myself and a small team of PostgreSQL contributors have started a new community project for PostgreSQL Certification. It is just launching but we wanted to get it out there so that people can join in on the discussion now :). For more information please visit: http://www.postgresqlcertification.org/ Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHoPdMATb/zqfZUUQRAqhlAJ92rMzYpn+k4rGDXpd4WiZwJQcBNACfWNeg 0zPBFRb4yc6Idpj99PCcFbY= =Spdr -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] expression index on date_trunc
A Redhead wrote:
CREATE INDEX request_day_idx ON moksha_sm_request
(date_trunc('day', request_received));
I get the error message:
ERROR: functions in index expression must be marked IMMUTABLE
[...]
I'd be grateful if someone could point out what part of the statement
is not IMMUTABLE
or how I could mark my create index statement as being immutable.
The retrieved value of request_received depends on your current
timezone, and so does the result of date_trunc, that would be why it's
not immutable.
If you don't need that behavior, you can shift your timestamptz to a
fixed timezone, both in your index and in your queries, as in:
CREATE INDEX request_day_idx ON moksha_sm_request
(date_trunc('day', request_received at time zone 'Europe/Paris'));
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] expression index on date_trunc
[EMAIL PROTECTED] writes:
CREATE INDEX request_day_idx ON moksha_sm_request (date_trunc('day',
request_received));
...
I'd be grateful if someone could point out what part of the statement is not
IMMUTABLE or how I could mark my create index statement as being immutable.
date_trunc(timestamp with time zone) is not immutable because it depends what
your current time zone is. That is, if you change what time zone you're in a
timestamp with time zone could appear to change from one day to another.
However date_trunc(timestamp without time zone) is immutable. So I think what
you have to do is build your index on:
date_trunc('day', request_received AT TINE ZONE 'GMT')
or whatever time zone you're interested in. That will get you the day part of
that timestamp at that time zone (because it first casts it to a timestamp
without time zone for the time zone you specify).
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL
training!
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] aggregate hash function
Most implementations of md5 internally consist of 3 functions: md5_init - which initializes internal context, md5_update - which accepts portions of data and processes them and md5_final - which finalizes the hash and releases the context. These roughly suit aggregate's internal functions (SFUNC and FINALFUNC, md5_init is probably to be called on first actual input). Since performance is important for you the functions should be written in low-level language as C, to me it doesn't look difficult to take some C md5 module and adapt it to be an aggregate... though it's not like I would do this easily myself :)
Re: [GENERAL] [pgsql-advocacy] PostgreSQL Certification
Josh, I have not spoken with SRA or the Venezualan folks but am more than happy to have them involved. OK, I'll get you some contact info. --Josh ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Is PostGreSql's Data storage mechanism inferior?
Hi all, I'm new to PostGreSql. http://searchyourwebhost.com/web-hosting/articles/insight-database-hosting-using-sql Check out the link. I am starting out on a new personal project had zeroed in on PostGreSql with Mono-ASP.NET as ideal for my needs, mainly owing to a PostGreSql whitepaper. Now, I chanced upon the article above. I've pasted the cons as mentioned in the article, and would like the community feedback on it, especially with regards to the inferior Data Storage mechanism. The cons of PostgreSql Hosting * Performance considerations: Inserts and Updates into the PostgreSql database is much slower compared to MySql. PostgreSql hosting thus might slow down the display of the web page online. * BSD license issues: Since PostgreSql comes under the Berkeley license scheme, this is again considered to be too open. * Availability of inferior Data Storage mechanism: PostgreSql uses Postgres storage system, which is not considered to be transaction sae during PostgreSql hosting. * Its not far-flung: While MySql hosting and MSSql hosting have deeply penetrated into the market, PostgreSql hosting still remains to be passive in the database hosting market. * Non-availability of required assistance for PostgreSql hosting: Assistance is being provided via mailing lists. However there is no guarantee that the issue faced during PostgreSql hosting would be resolved. Thanks!
Re: [GENERAL] aggregate hash function
On Jan 30, 2008 4:40 PM, Vyacheslav Kalinin [EMAIL PROTECTED] wrote: Most implementations of md5 internally consist of 3 functions: md5_init - which initializes internal context, md5_update - which accepts portions of data and processes them and md5_final - which finalizes the hash and releases the context. These roughly suit aggregate's internal functions (SFUNC and FINALFUNC, md5_init is probably to be called on first actual input). Since performance is important for you the functions should be written in low-level language as C, to me it doesn't look difficult to take some C md5 module and adapt it to be an aggregate... though it's not like I would do this easily myself :) Yes, thank you, I'm aware of how MD5 works - that's precisely why I don't like the idea of concatenating everything together first. I was hoping that because PG already exposed an MD5 function that it used a stdlib and also exposed the constituent functions and I just wasn't looking in the right place for them. Assuming it did, it would be pretty trivial to use them for SFUNC and FFUNC in creating an aggregate. Thanks for the help.
Re: [GENERAL] enabling autovacuum
Chander Ganesan wrote: Jeremy Harris wrote: Version: PostgreSQL 8.2.4 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070418 (Red Hat 4.1.2-10) We have one problematic table, which has a steady stream of entries and a weekly mass-delete of ancient history. The bloat query from Greg Sabino Mullane (thanks to Greg Smith for pointing it out) returns: Inserts don't generate dead tuples, and AVD looks at obsolete tuples.. As such, I wouldn't expect AVD to kick off until after you did a mass delete...assuming that delete was sizable enough to trigger a vacuum. Ah, that would explain it - thankyou. So I need to retreat to the question of why the weekly vacuum permits the observed bloat. Any ideas? More information that I could gather? Thanks, Jeremy ---(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: [GENERAL] [pgsql-advocacy] PostgreSQL Certification
Can you show us the goals of the PostgreSQL Certification ? I always voted for the united PostgreSQL Certification program (amin, developer) we could promote with the help of commercial companies. In my opinion, common certificate, valid in all countries will be much more useful than buttons. We have several good authors who can be sponsored to write certification courses with the help of developers. Oleg On Wed, 30 Jan 2008, Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hey guys, Myself and a small team of PostgreSQL contributors have started a new community project for PostgreSQL Certification. It is just launching but we wanted to get it out there so that people can join in on the discussion now :). For more information please visit: http://www.postgresqlcertification.org/ Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHoPdMATb/zqfZUUQRAqhlAJ92rMzYpn+k4rGDXpd4WiZwJQcBNACfWNeg 0zPBFRb4yc6Idpj99PCcFbY= =Spdr -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Oracle Analytical Functions
I tried this function but it keeps returning an error such as: ERROR: invalid input syntax for integer: 2007-05-05 00:34:08 SQL state: 22P02 Context: PL/pgSQL function lagfunc line 10 at assignment I checked and there are no datetime values in the client_id field anywhere in my table 'all_client_times' I have no idea what is going on here ... Thanks for the code though - it has taught me a lot all ready; such as using, OUT and SETOF Record Willem Adam Rich wrote: and I would like to create a new view that takes the first table and calculates the time difference in minutes between each row so that the result is something like: client_id,datetime, previousTime, difftime 122,2007-05-01 12:01:00, 2007-05-01 12:00:00, 1 455,2007-05-01 12:03:00, 2007-05-01 12:02:00, 1 455,2007-05-01 12:08:00, 2007-05-01 12:03:00, 5 299,2007-05-01 12:34:00, 2007-05-01 12:10:00, 24 Any idea how I could replicate this in SQL from PG. Would this be an easy thing to do in Pl/pgSQL? If so could anyone give any directions as to where to start? You can create a set-returning function, that cursors over the table, like this: CREATE OR REPLACE FUNCTION lagfunc( OUT client_id INT, OUT datetime timestamp, OUT previousTime timestamp, OUT difftime interval) RETURNS SETOF RECORD as $$ DECLARE thisrow RECORD; last_client_id INT; last_datetime timestamp; BEGIN FOR thisrow IN SELECT * FROM all_client_times ORDER BY client_id, datetime LOOP IF thisrow.client_id = last_client_id THEN client_id := thisrow.datetime; datetime := thisrow.datetime; previousTime := last_datetime; difftime = datetime-previousTime; RETURN NEXT; END IF; last_client_id := thisrow.client_id; last_datetime := thisrow.datetime; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; select * from lagfunc() limit 10; select * from lagfunc() where client_id = 455; Here I used an interval, but you get the idea. ---(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: [GENERAL] Oracle Analytical Functions
Thanks Reece, I got this to work for me. The only problem was with the ORDER BY clause which did not seem to work properly. I took it out and instead used a sorted view for the data table. Cheers, Willem Reece Hart wrote: create table data ( client_id integer, datetime timestamp not null ); create index data_client_id on data(client_id); copy data from STDIN DELIMITER ','; 122,2007-05-01 12:00:00 122,2007-05-01 12:01:00 455,2007-05-01 12:02:00 455,2007-05-01 12:03:00 455,2007-05-01 12:08:00 299,2007-05-01 12:10:00 299,2007-05-01 12:34:00 \. CREATE OR REPLACE FUNCTION visits ( OUT client_id INTEGER, OUT datetime_1 TIMESTAMP, OUT datetime_2 TIMESTAMP, OUT dur INTERVAL ) RETURNS SETOF RECORD LANGUAGE plpgsql AS $_$ DECLARE rp data%ROWTYPE;-- previous data table record r data%ROWTYPE; -- data table record, more recent than rp BEGIN rp = (NULL,NULL); FOR r IN SELECT * FROM data ORDER BY client_id,datetime LOOP IF rp.client_id = r.client_id THEN client_id = r.client_id; datetime_1 = r.datetime; datetime_2 = rp.datetime; dur = r.datetime-rp.datetime; RETURN NEXT; END IF; rp = r; END LOOP; RETURN; END; $_$; [EMAIL PROTECTED] select * from visits() order by client_id,datetime_1; client_id | datetime_1 | datetime_2 | dur ---+-+-+-- 122 | 2007-05-01 12:01:00 | 2007-05-01 12:00:00 | 00:01:00 299 | 2007-05-01 12:34:00 | 2007-05-01 12:10:00 | 00:24:00 455 | 2007-05-01 12:03:00 | 2007-05-01 12:02:00 | 00:01:00 455 | 2007-05-01 12:08:00 | 2007-05-01 12:03:00 | 00:05:00 (4 rows) -Reece ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Oracle Analytical Functions
Found the error: client_id := thisrow.datetime; should be client_id := thisrow.client_id; All works well now, Thanks very much, Willem Willem Buitendyk wrote: I tried this function but it keeps returning an error such as: ERROR: invalid input syntax for integer: 2007-05-05 00:34:08 SQL state: 22P02 Context: PL/pgSQL function lagfunc line 10 at assignment I checked and there are no datetime values in the client_id field anywhere in my table 'all_client_times' I have no idea what is going on here ... Thanks for the code though - it has taught me a lot all ready; such as using, OUT and SETOF Record Willem Adam Rich wrote: and I would like to create a new view that takes the first table and calculates the time difference in minutes between each row so that the result is something like: client_id,datetime, previousTime, difftime 122,2007-05-01 12:01:00, 2007-05-01 12:00:00, 1 455,2007-05-01 12:03:00, 2007-05-01 12:02:00, 1 455,2007-05-01 12:08:00, 2007-05-01 12:03:00, 5 299,2007-05-01 12:34:00, 2007-05-01 12:10:00, 24 Any idea how I could replicate this in SQL from PG. Would this be an easy thing to do in Pl/pgSQL? If so could anyone give any directions as to where to start? You can create a set-returning function, that cursors over the table, like this: CREATE OR REPLACE FUNCTION lagfunc( OUT client_id INT, OUT datetime timestamp, OUT previousTime timestamp, OUT difftime interval) RETURNS SETOF RECORD as $$ DECLARE thisrow RECORD; last_client_id INT; last_datetime timestamp; BEGIN FOR thisrow IN SELECT * FROM all_client_times ORDER BY client_id, datetime LOOP IF thisrow.client_id = last_client_id THEN client_id := thisrow.datetime; datetime := thisrow.datetime; previousTime := last_datetime; difftime = datetime-previousTime; RETURN NEXT; END IF; last_client_id := thisrow.client_id; last_datetime := thisrow.datetime; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; select * from lagfunc() limit 10; select * from lagfunc() where client_id = 455; Here I used an interval, but you get the idea. ---(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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Is PostGreSql's Data storage mechanism inferior?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 30 Jan 2008 15:11:05 -0800 Swaminathan Saikumar [EMAIL PROTECTED] wrote: Hi all, I'm new to PostGreSql. http://searchyourwebhost.com/web-hosting/articles/insight-database-hosting-using-sql The cons of PostgreSql Hosting * Performance considerations: Inserts and Updates into the PostgreSql database is much slower compared to MySql. PostgreSql hosting thus might slow down the display of the web page online. Not when data integrity comes into play. * BSD license issues: Since PostgreSql comes under the Berkeley license scheme, this is again considered to be too open. Exactly how is too open a bad thing? * Availability of inferior Data Storage mechanism: PostgreSql uses Postgres storage system, which is not considered to be transaction sae during PostgreSql hosting. Uhmm that is a blatant lie. * Its not far-flung: While MySql hosting and MSSql hosting have deeply penetrated into the market, PostgreSql hosting still remains to be passive in the database hosting market. I know many postgresql hosting companies. * Non-availability of required assistance for PostgreSql hosting: Assistance is being provided via mailing lists. However there is no guarantee that the issue faced during PostgreSql hosting would be resolved. Say what? http://www.postgresql.org/support/professional_support Sincerely, Joshua D. Drake Thanks! - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHoREhATb/zqfZUUQRAkRpAJ9gdQAwmWsXPNlut0DJ8/mNgzmytQCdEd8M 0kBugrvVLkPSH4VWBtKYUUE= =vqnc -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Oracle Analytical Functions
I tried this function but it keeps returning an error such as: ERROR: invalid input syntax for integer: 2007-05-05 00:34:08 SQL state: 22P02 Context: PL/pgSQL function lagfunc line 10 at assignment Whoops, this line: client_id := thisrow.datetime; Should be: client_id := thisrow.client_id; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Is PostGreSql's Data storage mechanism inferior?
--- On Wed, 1/30/08, Swaminathan Saikumar [EMAIL PROTECTED] wrote: The cons of PostgreSql Hosting * Performance considerations: Inserts and Updates into the PostgreSql database is much slower compared to MySql. PostgreSql hosting thus might slow down the display of the web page online. ... might slow down ... I guess they didn't check to know one way or ther other whether It does slow down a web page. The real answer depends upon your they kind of uses you have. * BSD license issues: Since PostgreSql comes under the Berkeley license scheme, this is again considered to be too open. What does too open mean? Is too open a bad thing? * Availability of inferior Data Storage mechanism: PostgreSql uses Postgres storage system, which is not considered to be transaction sae during PostgreSql hosting. It seems they misspelled safe. But in either case they also seemed to have confused MySQL with PostgreSQL on this point. Being transactionally safe is one of PostgreSQL's strongest points. * Its not far-flung: While MySql hosting and MSSql hosting have deeply penetrated into the market, PostgreSql hosting still remains to be passive in the database hosting market. My gut says that this is probably true. But there are very good hosting sites that provide PostgreSQL. * Non-availability of required assistance for PostgreSQL hosting: Assistance is being provided via mailing lists. However there is no guarantee that the issue faced during PostgreSQL hosting would be resolved. If you have a highly critical application that requires guaranteed assistance you are more than able to purchase it from some top notch consultant companies that support PostgreSQL. Just check out the PostgreSQL home page. So I don't this this point is correct either. If you don't get too much feed back on this subject, just remember that topics like this come up frequently to the point of list member exhaustion. You can find such discussions if you search the list archive. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Is PostGreSql's Data storage mechanism inferior?
Swaminathan Saikumar wrote: Hi all, I'm new to PostGreSql. http://searchyourwebhost.com/web-hosting/articles/insight-database-hosting-using-sql What a wonderful article - it's almost worth keeping a copy. It's so bad it's difficult to know where to start. I think my favourite has to be : + MSSql being massive is considered to have the maximum scalability for database hosting. And I just love the comment on both MySQL and Postgres about GPL and BSD being 'too open' - meaning? Pete Check out the link. I am starting out on a new personal project had zeroed in on PostGreSql with Mono-ASP.NET as ideal for my needs, mainly owing to a PostGreSql whitepaper. Now, I chanced upon the article above. I've pasted the cons as mentioned in the article, and would like the community feedback on it, especially with regards to the inferior Data Storage mechanism. The cons of PostgreSql Hosting * Performance considerations: Inserts and Updates into the PostgreSql database is much slower compared to MySql. PostgreSql hosting thus might slow down the display of the web page online. * BSD license issues: Since PostgreSql comes under the Berkeley license scheme, this is again considered to be too open. * Availability of inferior Data Storage mechanism: PostgreSql uses Postgres storage system, which is not considered to be transaction sae during PostgreSql hosting. * Its not far-flung: While MySql hosting and MSSql hosting have deeply penetrated into the market, PostgreSql hosting still remains to be passive in the database hosting market. * Non-availability of required assistance for PostgreSql hosting: Assistance is being provided via mailing lists. However there is no guarantee that the issue faced during PostgreSql hosting would be resolved. Thanks! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Is PostGreSql's Data storage mechanism inferior?
On Jan 30, 2008 5:11 PM, Swaminathan Saikumar [EMAIL PROTECTED] wrote: Hi all, I'm new to PostGreSql. http://searchyourwebhost.com/web-hosting/articles/insight-database-hosting-using-sql I just skimmed through that page and honestly, it's wrong on LOTS of counts, again and again. For instance, about mysql it has these two contradicting points: QUOTE: Pros: Availability of Superior Data Storage mechanism: Though prior versions of MySql supported ISAM/MyISAM mechanisms, later versions starting from 4.x have started using the InnoDB mechanism, which is considered to be transaction safe for database hosting. Cons: Presence of Inferior Data Integrity mechanism: Though MySql is ACID (Atomic, Consistent, Isolated, Durable) complaint, when dealing with deadlocks in database hosting, it uses row-level locking which is considered to be inferior when compared to Multi Version Concurrency Control (MVCC). UNQUOTE: OK, while I hardly consider InnoDB to be superior to PostgreSQL's storage engine, it uses MVCC for its locking mechanism! But, MySQL really isn't capable of true ACID compliance because it as a whole doesn't support check constraints, but it accepts the syntax without actually implementing the feature. I read one, and it seems like many of the entries don't even make sense. This one for MSSQL for instance: QUOTE: Pros: Remarkable Reliability: MSSQL hosting offers high reliability by having a data manager for reading and writing data to the database. Even if the client machine crashes, the read and write is not committed in the database by the data manager. The transaction logs also help in rollbacks thus paving way to commendable reliability in MSSql hosting. UNQUOTE: WHAT? What does that mean? And how does it imply superior reliability? I can't see any of that meaning MSSQL stays up longer than any other database. Seriously, if I were interviewing someone for a db job, and they pointed out that they wrote that page I would politely decline to hire them. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [pgsql-advocacy] PostgreSQL Certification
Josh Berkus wrote: Josh, Myself and a small team of PostgreSQL contributors have started a new community project for PostgreSQL Certification. It is just launching but we wanted to get it out there so that people can join in on the discussion now :). Who else is in this? Have you talked to the Venezualan folks? SRA? As you know, I'm strongly in favor of a good, generally respected certification. Let's get all of the interested folks on one project. You may know that I'm part of the BSD Certification Group. Proper certification is not a trivial project. I joined up. -- Dan Langille - http://www.langille.org/ BSDCan - The Technical BSD Conference: http://www.bsdcan.org/ PGCon - The PostgreSQL Conference:http://www.pgcon.org/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [pgsql-advocacy] PostgreSQL Certification
Josh Berkus wrote: Josh, Myself and a small team of PostgreSQL contributors have started a new community project for PostgreSQL Certification. It is just launching but we wanted to get it out there so that people can join in on the discussion now :). Who else is in this? Have you talked to the Venezualan folks? SRA? As you know, I'm strongly in favor of a good, generally respected certification. Let's get all of the interested folks on one project. Am i automatically disqualified by asking who the Venezualan folks are? b ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Is PostGreSql's Data storage mechanism inferior?
On Jan 30, 2008, at 6:22 PM, Richard Broersma Jr wrote: If you don't get too much feed back on this subject, just remember that topics like this come up frequently to the point of list member exhaustion. You can find such discussions if you search the list archive. Too true. There's only so many times people can be confronted with Defend yourselves! before they start ignoring it. On the flip side, when you approach with Tell me, what advantages does Postgres have to offer? you'll find many people all too willing to step up with pride. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Is PostGreSql's Data storage mechanism inferior?
Thanks everyone. After all the good things I heard about Postgres, I was surprised to see this article; and the point on storage concerned me. I am glad to see that the article was wrong, not only on the storage engine count, but also on others. Thanks for the feedback. On 1/30/08, Erik Jones [EMAIL PROTECTED] wrote: On Jan 30, 2008, at 6:22 PM, Richard Broersma Jr wrote: If you don't get too much feed back on this subject, just remember that topics like this come up frequently to the point of list member exhaustion. You can find such discussions if you search the list archive. Too true. There's only so many times people can be confronted with Defend yourselves! before they start ignoring it. On the flip side, when you approach with Tell me, what advantages does Postgres have to offer? you'll find many people all too willing to step up with pride. Erik Jones DBA | Emma(R) [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com
Re: [GENERAL] Is PostGreSql's Data storage mechanism inferior?
On Jan 30, 2008, at 5:11 PM, Swaminathan Saikumar wrote: Hi all, I'm new to PostGreSql. http://searchyourwebhost.com/web-hosting/articles/insight-database- hosting-using-sql Check out the link. I am starting out on a new personal project had zeroed in on PostGreSql with Mono-ASP.NET as ideal for my needs, mainly owing to a PostGreSql whitepaper. Now, I chanced upon the article above. I've pasted the cons as mentioned in the article, and would like the community feedback on it, especially with regards to the inferior Data Storage mechanism. The cons of PostgreSql Hosting * Performance considerations: Inserts and Updates into the PostgreSql database is much slower compared to MySql. PostgreSql hosting thus might slow down the display of the web page online. Not for inserts. For updates, nder some workloads, possibly. A typical website run on a hosted server, not likely. While you might be able to clock single instances of these operations on each of those databases against each other (and, I emphasize *might*) and have MySQL come out on top, MySQL is the often demonstrated loser when you want to scale out and process hundreds to thousands at once. Isn't that what you're shooting for with a web app? * BSD license issues: Since PostgreSql comes under the Berkeley license scheme, this is again considered to be too open. What does that even mean? * Availability of inferior Data Storage mechanism: PostgreSql uses Postgres storage system, which is not considered to be transaction sae during PostgreSql hosting. What I *think* they're getting at there is pure nonsense. In fact, the sentence itself is nonsensical. * Its not far-flung: While MySql hosting and MSSql hosting have deeply penetrated into the market, PostgreSql hosting still remains to be passive in the database hosting market. While I'll admit that MySQL hosting is more widespread, calling Postgres hosting passive has no meaning whatsoever. * Non-availability of required assistance for PostgreSql hosting: Assistance is being provided via mailing lists. However there is no guarantee that the issue faced during PostgreSql hosting would be resolved. You pay for what you get, i.e there are numerous commercial companies that offer both paid support and consulting. For free, as mentioned, you have the lists which are, incidentally, much better that the free options available to most other technologies. Watch this list for a bit, hardly a week goes by without at least one or two people professing the support and general help gleaned from it's participants as their primary decision to go with Postgres. Basically, the person who made that list pulled a bunch of bullet points out of the FUD-mosphere that barely make sense as English sentences and offered them up without a shred of evidence or reference. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] enabling autovacuum
Jeremy Harris wrote: Chander Ganesan wrote: Inserts don't generate dead tuples, and AVD looks at obsolete tuples.. As such, I wouldn't expect AVD to kick off until after you did a mass delete...assuming that delete was sizable enough to trigger a vacuum. Ah, that would explain it - thankyou. So I need to retreat to the question of why the weekly vacuum permits the observed bloat. Any ideas? More information that I could gather? Autovacuum will kick off an analyze if you do enough inserts however. ---(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: [GENERAL] postgresql book - practical or something newer?
On Wed, 2008-01-30 at 20:47 +0900, Jason Topaz wrote: I don't disagree with your point that it's not robust with examples of exactly how a particular problem can be solved. But I think there are enough, and more importantly, I don't think problem-solving is an important focus for a manual (that's why 3rd party books exist). Which is also the cause of the original rant. There is very few 3rd party books. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] MySQL [WAS: postgresql book...]
On Wed, 30 Jan 2008 13:20:58 -0500 Tom Hart [EMAIL PROTECTED] wrote: I have 4 years of mySQL experience (I know, I'm sorry) Why is this something to apologize for? I used to use MySQL for everything and now use PostgreSQL for the majority of my DB needs. I certainly advocate PG now to anyone who will listen, but I don't think it helps to portray MySQL as a POS or regret its existence. It's a very useful tool if used correctly. IMO, people who are able to effectively use PG/MySQL/Oracle/XYZ appropriately are more valuable than those that blindly use the same one for every single task. Sorry - I just noticed this in a couple recent posts and I felt like voicing my $0.02. Josh ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] MySQL [WAS: postgresql book...]
On Wed, 2008-01-30 at 20:14 -0600, Josh Trutwin wrote: On Wed, 30 Jan 2008 13:20:58 -0500 Tom Hart [EMAIL PROTECTED] wrote: I have 4 years of mySQL experience (I know, I'm sorry) Why is this something to apologize for? I used to use MySQL for everything and now use PostgreSQL for the majority of my DB needs. I certainly advocate PG now to anyone who will listen, but I don't think it helps to portray MySQL as a POS or regret its existence. It's a very useful tool if used correctly. IMO, people who are able to effectively use PG/MySQL/Oracle/XYZ appropriately are more valuable than those that blindly use the same one for every single task. Best tool for the JOB in my books. Heck, I'm even considering running MySQL through it's MyISAM backend as a slave to my PG master. Should(emphasis!) be plenty Fast ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] MySQL [WAS: postgresql book...]
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 30 Jan 2008 20:14:15 -0600 Josh Trutwin [EMAIL PROTECTED] wrote: On Wed, 30 Jan 2008 13:20:58 -0500 Tom Hart [EMAIL PROTECTED] wrote: I have 4 years of mySQL experience (I know, I'm sorry) Why is this something to apologize for? I think he was apologizing to himself. After that much self inflicted pain, the inner psyche has to start to revolt. :P Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHoTOqATb/zqfZUUQRAoW5AJ0erekd/h9hln5C9KWMnmX6x36jxACfdeuX 28rj8nLZawFIl2R/5o+klsY= =Roxf -END PGP SIGNATURE- ---(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
[GENERAL] Performance problems with Postgresql/ZFS/Non-global zones on Solaris?
In the hopes that someone has already blazed this trail ... I'm running Postgresql (v8.1.10) on Solaris 10 (Sparc) from within a non-global zone. I originally had the database storage in the non-global zone (e.g. /var/local/pgsql/data on a UFS filesystem) and was getting performance of X (e.g. from a TPC-like application: http://www.tpc.org). I then wanted to try relocating the database storage from the zone (UFS filesystem) over to a ZFS-based filesystem (where I could do things like set quotas, etc.). When I do this, I get roughly half the performance (X/2) I did on the UFS system. I did try to run some low level I/O tests (i.e. http://www.iozone.org/) to see if there was a filesystem performance difference, but there doesn't seem to be any between the UFS and ZFS numbers I'm seeing. So, I was hoping someone might have already tried this type of setup and can provide some suggestions for helping boost the ZFS performance numbers (and save me a truss debugging session). Are there any known issues with using Postgresql and ZFS? Things I have already tried: - setting the record size of the ZFS filesystem to be 8K (vs 128K ) default -- no noticeable performance difference - addind the ZFS filesystem as a loopback (i.e. lofs) filesystem in the non-global zone -- no noticeable difference - adding the ZFS filesystem as a dataset filesystem in the non-global zone -- no noticeable difference Bob ---(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: [GENERAL] postgresql book - practical or something newer?
Tom Hart wrote: I definitely think that the lists are one of the shining stars for postgresql support. I've learned some good reference stuff from online docs/google but the really tricky questions were only answered here, and amazingly enough, quickly and with good humor. Perhaps what we really need is somebody to comb through the archives looking for common problems or exceptional solutions and compile them into a book. The good and bad news is that the best way to do things often changes from release to release, hence the need to get the most current information from the mailing lists. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql book - practical or something newer?
Bruce Momjian said: Tom Hart wrote: I definitely think that the lists are one of the shining stars for postgresql support. I've learned some good reference stuff from online docs/google but the really tricky questions were only answered here, and amazingly enough, quickly and with good humor. Perhaps what we really need is somebody to comb through the archives looking for common problems or exceptional solutions and compile them into a book. The good and bad news is that the best way to do things often changes from release to release, hence the need to get the most current information from the mailing lists. Although I have solved almost every problem I have come up against in learning, partly with archives, I've often had to resort to asking the list because finding relevant missives in the archives can be hard if you don't know what month to look at, and even then the search results can produce a lot incidental wanderings to get to the solutions. It seems that some intermediate ground (TWIKI or a document in some format) might help with some of these questions, perhaps with sections based on release. Personally, I found it very hard to get going with PL/pgSQL based on existing documentation; an older book on PostgreSQL had enough examples that I got over that hump and can usually find my way now with the documentation, lists and archives. My $0.02 (inflating ? devalued 'cause it's US currency ?) ... Greg Williamson Senior DBA GlobeXplorer LLC, a DigitalGlobe company
Re: [GENERAL] [pgsql-advocacy] PostgreSQL Certification
I suggest to explicitly invite the Russian folks too. Oleg showed strong interest in a global certification thing. we can contribute some material and so on if needed. it is currently in german but it should not be a big problem. many thanks, hans On Jan 30, 2008, at 11:22 PM, Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 30 Jan 2008 14:17:43 -0800 Josh Berkus [EMAIL PROTECTED] wrote: Current broadcast members are: Myself Magnus Robert Chander (need to get him on the website) Bruce has a pending invitation (which I didn't send yet) I have not spoken with SRA or the Venezualan folks but am more than happy to have them involved. Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/ donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHoPiMATb/zqfZUUQRAqMHAJsHop8kUqHkHRLJMjNFBIny+dIiYQCfXz19 fXELUEQ3khSifVR6JJaI3K8= =N1BL -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Cybertec Schönig Schönig GmbH PostgreSQL Solutions and Support Gröhrmühlgasse 26, 2700 Wiener Neustadt Tel: +43/1/205 10 35 / 340 www.postgresql.at, www.cybertec.at
