Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
On 29/11/03, Randal L. Schwartz ([EMAIL PROTECTED]) wrote:
Well, since I need 2.5 ideas per month for the three columns I'm still
writing, I'm certainly in a position to write nice things about PG,
although I always have to work it in from a Perl slant.
Actually, I'm sure that any of the magazines I'm in would appreciate
an additional article or two from me.
If you can think of something that fits in 2000 words or so (or 4000
if it needs part 1 and 2), and can have a Perl wrapper, I'd appreciate
some inspiration.
Hi Randal
I think I may have an idea for an article which would address a common
problem for people writing database client interfaces:
The problem is simply explained.
Problem title:
The page of pages problem (!)
The problem:
You want to return a subset of a large number items using some
fairly complex search criteria. You want to make only one database
call, benefit from a cached query, and don't want to have all the
rows in memory. How do you get the total count of pages for the
relevant search criteria?
Why is this relevant?
Moving logic that is inherent to the database to the database
provides a potentially rich yet simple interface to database
queries that can benefit a number of client applications.
Typically this sort of query would be written as at least two
dynamically generated queries in the client program that has to be
parsed by the backend before it is executed. By using functions we
can hide complex joins behind simple field names, and provide
flexible (if limited) search capabilites, as well as caching and
sensible error messages.
Approach:
Using Postgres one can construct a function and then do either
SELECT * from function fn_explore($searchstring, $limit, $offset);
OR
SELECT
*
FROM
function fn_explore()
WHERE
searchterm ~* 'test'
LIMIT
5
OFFSET
10;
What is cool about the second format is that (if the function
returned a type 'explore_result' as below), your PHP/Perl programmer
can at their interface do something like
'... where id 1 AND author IN ('james', 'bill')...'
However I don't know how you get back the total rows in this case,
also maybe the caching effects are minimised?
Type definition:
CREATE TYPE explore_result as (
id INTEGER, -- some sort of row id
total INTEGER, -- total rows for query
author VARCHAR,
image BYTEA
/*
Not needed unless search is done outside db.
, searchterm VARCHAR
*/
);
Sketch function definition:
CREATE OR REPLACE FUNCTION
fn_explore (integer, integer, integer) RETURNS setof explore_result
AS '
DECLARE
searchstring ALIAS for $1;
offsetter ALIAS for $2;
limiter ALIAS for $3;
resulter explore_page%rowtype;
BEGIN
/*
variable verifation section chopped
*/
FOR resulter IN
SELECT
n_id as id,
LOJ.pagetotal as total
pers.t_name as author,
image.b_contents as image
/*
need searchterm returned if we are doing search outside
the database
, COALESCE(t_title || '' '' || t_text, ) as searchterm
FROM
db
/*
- self join on db LOJ for unoffset, unlimited row count
refer to searchterm stuff below
*/
WHERE
/* note, if we are doing a search outside of the
* function and t_title or t_text could be empty then we
* need to coalesce to an empty string
* COALESCE(t_title || '' '' || t_text, ) as searchterm
*/
searchstring ~ t_title || '' '' || t_text
ORDER BY
dt_modified DESC
LIMIT
limiter
OFFSET
offsetter
,
LOOP
RETURN NEXT
resulter;
END LOOP;
RETURN;
END;'
LANGUAGE plpgsql;
--
Rory Campbell-Lange
[EMAIL PROTECTED]
www.campbell-lange.net
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
Chris Travers wrote: Here is a paper I have written for the purposes of providing some additional educational material for the MySQL crowd. Here's my contribution: Why I choose PostgreSQL (PostgreSQL in 21 Seconds) I choose referential integrity, meaning my lookups always work. I choose stored procedures, meaning all my developers - Windows or Unix, Perl, C++ or Java - can access the database in the same way, using the same locking, with the same checking and cleaning I choose subselects and outer joins, which allow me to build complex queries to get exactly the information I want from the database, rather than wasting my time munging data in my code. Even better, I can put those common queries into stored procedures, so other developers can get the same results as I do! I choose partial indexes, so lookups on NULL fields are just as fast if not faster. I choose a user community that believes getting the results right is more important than getting them quickly. I choose getting the right results, right now! I choose funny capitalisation, and a name that can't be pronounced! I choose PostgreSQL. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
I used it first because 1) someone suggested it and I didn't know any better 2) install, setup, maintanance and using it is easier than breathing. You'd be surprised how much of a difference it makes to a newbie to not have to do things like vacuum regularly and the ability to change a column type (I'm not saying this is a good idea, just that it seemed nice at the time), stuff like that. 3) their online documentation was great, learning how to do new stuff was fast and easy 4) It SEEMED to work fine (I say seemed because I never had anything happen to me like an int overflow problem) 5) For the type of work I started off with I didn't badly need the features that mysql lacks I'm betting that this is the case with many mysql users. - Original Message - From: Tom Lane [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, November 29, 2003 10:01 AM Subject: Re: Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments Rod K [EMAIL PROTECTED] writes: Paul Thomas wrote: Much of the populatity of MySQL seems to stem from PHPs out-of-the-box support for it. This is incorrect. The embedded mysql client library was not added until PHP4.0 RC1. PHP's popularity existed long before this. The real culprit causing the popularity of MySQL was it's ubiquity among hosting providers and the virtual non-existence of PG in that arena. If PG had been more friendly to shared hosting environments, perhaps this situation wouldn't have arisen. You are both engaging in the most blatant form of historical revisionism. Of course PHP's support for MySQL didn't drive MySQL adoption --- it was the other way around, PHP adapted to MySQL because that was what was out there. I think friendly to shared hosting environments is a made-up reason as well. The real reason PG lost mindshare to MySQL in the early web days is that at the time, PG was hard to install, somewhat buggy, and poorly documented. (Which was not surprising considering that none of these mattered much in its original academic environment.) MySQL didn't do much, maybe, but what it could do it did pretty well and without install/learning curve hassles. We had mostly caught up on those criteria by perhaps 7.1 or 7.2, but the mindshare gap remains. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
Note: I am a php developer and I love it, but... In dealing with web applications and frontends to database or even just a dynamic web site PHP has every bit the power and ability that Java does and the development time is way down. Uh, how about threads. I know that you don't need them much but it sure would be nice to be able to do background processing. If you need more power IMO Python is the way to go. I am not that familiar with pything, not to get off topic here but what you can do in python that you can't do in PHP? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
Here is a link to the sql for smarties book: http://www.amazon.com/exec/obidos/tg/detail/-/1558603239/102-3995931-726?v=glance by Joe Celko Has some cool ways of handling trees in sql - Original Message - From: Chris Travers [EMAIL PROTECTED] To: Tony [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, November 30, 2003 5:49 AM Subject: Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments Re: [GENERAL] PostgreSQL Advocacy, Thoughts and CommentsRegarding the learning curve issue, maybe people can recommend their favorite books. I recommend SQL Unleashed (I forget the author), pub. Samms. SQL For Smarties also gets recommended often around here, but again, I don't know the author (or in this case, even the publisher). Maybe there are others too that people can recommend. As for the paper-- I think your title is good (PostgreSQL - Ideal for any application development), and I will have to look at how to organize it. I wanted to cover the following topics: 1) Different types of databases, and what is meant by Object Relational 2) Enterprise-ready features (Views, Stored Proceedures, Subselects, etc.) 3) RDBMS tasks and how these features fit in. Perhaps a followup paper could be written as a basic treatise in database design. The other issues I am seing here involve finding a suitable venue for publication. Any suggestions here are welcome also. Best Wishes, Chris Travers ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
Tony [EMAIL PROTECTED] Wrote: Now many consultant/developer/sys-admins like myself are going to client site on a contract (this is especially true in the UK, I can't speak for anywhere else) and finding complex stocktrading systems, inventory systems, CRM systems, and others, all written in PHP backed by MySQL. I started the CRM system I am developing on MySQL before realizing it was the wrong choice. Part of it is simple because people have heard of the software and don't have the time/stamina/patience to do proper research into the benefits of alternatives. There is also a learning curve when going from MySQL to a more standards-compliant RDBMS like PostgreSQL. Heck, I found that going from PostgreSQL to Firebird give me headaches :-P And these RDBMS's have most of the same features! Whether this is right or wrong, good choice or bad choice is not what I'm interested in debating. The point is that when these systems where architected, the developers used MySQL not because they were dumb, but because many of them develop awesome code and can get around most problems in the code, with a little ingenuity. Many simply do not have the insight into the potential benefits of *proper* RDBMS can offer. I would actually venture to say that many of them are using the RDBMS as a sort of object-persistance store, and not really trying to use the *relational* features of the software. They might as well be using Berkeley DB 4. I know that is how I started with MySQL. What most of these programmers do not understand is that an RDBMS is not simply a search-engine for stored persistant objects, but is actually a fully-featured information storage management system. With the right features, this information can be stored, queried, presented in another form, etc. all while ensuring that the stored information is EXACTLY what was intended. The tasks that the RDBMS handles include data storage, integrity enforcement, and data presentation. Most MySQL programmers only use it for data storage. Sadly, this is about all MySQL is good for, and hence the barrier to learning how to USE a REAL RDBMS are a bit higher because of the prevalence of the likes of MySQL and MS Access. The second scenario, is with admin systems, written by people like myself for companies, whether they be simple or complex systems, that are intended as a temporary work around to an immediate problem. In a very short space of time the stop-gap application you had written to sort out the immediate problem quickly becomes a core business application (I recently returned to a site after not being there for two years and the temporary address book/ email system that I knocked up in an afternoon was not only still being used, but now relied upon heavily). But again, if you start with the right tools, it is easier to modify later to adapt to changing needs. I think that this is one of the messages we should be presenting. With updateable views, different applications can even have access to different presentations of the data. So on to my point, MySQL guys will happily say Hey, we're not saying that the features MySQL is missing aren't important, and we're working towards them, but in the meantime these issues can be worked around like this. and happily play the whole thing down. Many LAMP developers aren't aware of the benefits of stored procedures, of triggers and other good stuff. Like myself, if they were aware how much easier life could be if these things were accessible to them, they'd probably be converts too. Agreed completely. Now we just have to sell the PostgreSQL solution. Here is what the MySQL people will say (and we need good evidence to counter): 1: MySQL is faster. 2: MySQL has more community support. 3: MySQL has replication as part of its core distribution. MySQL's replication is better tested... There is not enough emphasis put on the basic importance of these functions in PG. Someone needs to standup and say Hey, look how this can simplify your programming lives until I started using Druid/Postgres, I had no idea why I needed triggers or what a cascade effect did, or why I might want one. The basic issue is that many programmers are not taught to value information management systems, such as RDBMS's. These programmers are interested only in the data storage issues of the database, and not on how to use it to manage the information stored therein. Changing this may take a lot of effort. Also, using an RDBMS to its full extent rubs some OO programmers the wrong way because it strikes them as violating rules of OO design. Of course, then why not use an OO database? ;-) The Linux community has grown at least in part because it has educated potential users and journo's to its benefits. I believe if the PG advocacy team did the same, then it would attract many more serious LAMP developers. I agree. But it will take some time to sell, and will require some extremely
Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
Title: Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments Comments within: Chris Travers wrote: Tony [EMAIL PROTECTED] Wrote: Now many consultant/developer/sys-admins like myself are going to client site on a contract (this is especially true in the UK, I can't speak for anywhere else) and finding complex stocktrading systems, inventory systems, CRM systems, and others, all written in PHP backed by MySQL. I started the CRM system I am developing on MySQL before realizing it was the wrong choice. Part of it is simple because people have heard of the software and don't have the time/stamina/patience to do proper research into the benefits of alternatives. There is also a learning curve when going from MySQL to a more standards-compliant RDBMS like PostgreSQL. Heck, I found that going from PostgreSQL to Firebird give me headaches :-P And these RDBMS's have most of the same features! It's the learning curve part that I'm finding difficult, not because it's a too complicated, but because I can't find a good source of information to learn from. I'm sure I'll get flamed for this, but I seem to be unable to find information on proper design principle, including where and when to use triggers, stored procs, etc, etc, that isn't 20 years old already. In the liquid world of IT, I find it worrying (perhaps incorrectly) learning from a book written 14 years ago. Whether this is right or wrong, good choice or bad choice is not what I'm interested in debating. The point is that when these systems where architected, the developers used MySQL not because they were dumb, but because many of them develop awesome code and can get around most problems in the code, with a little ingenuity. Many simply do not have the insight into the potential benefits of *proper* RDBMS can offer. I would actually venture to say that many of them are using the RDBMS as a sort of object-persistance store, and not really trying to use the *relational* features of the software. They might as well be using Berkeley DB 4. I know that is how I started with MySQL. Agreed... What most of these programmers do not understand is that an RDBMS is not simply a search-engine for stored persistant objects, but is actually a fully-featured information storage management system. With the right features, this information can be stored, queried, presented in another form, etc. all while ensuring that the stored information is EXACTLY what was intended. The tasks that the RDBMS handles include data storage, integrity enforcement, and data presentation. Most MySQL programmers only use it for data storage. Sadly, this is about all MySQL is good for, and hence the barrier to learning how to USE a REAL RDBMS are a bit higher because of the prevalence of the likes of MySQL and MS Access. Indeed, and I believe that the lack of education with regards to this (or even available information presented at the right level) perpetuates the issue, along with the mis-information put forward by MySQL that these aspects aren't really all that important anyway. The second scenario, is with admin systems, written by people like myself for companies, whether they be simple or complex systems, that are intended as a temporary work around to an immediate problem. In a very short space of time the stop-gap application you had written to sort out the immediate problem quickly becomes a core business application (I recently returned to a site after not being there for two years and the temporary address book/ email system that I knocked up in an afternoon was not only still being used, but now relied upon heavily). But again, if you start with the right tools, it is easier to modify later to adapt to changing needs. I think that this is one of the messages we should be presenting. With updateable views, different applications can even have access to different presentations of the data. So on to my point, MySQL guys will happily say "Hey, we're not saying that the features MySQL is missing aren't important, and we're working towards them, but in the meantime these issues can be worked around like this." and happily play the whole thing down. Many LAMP developers aren't aware of the benefits of stored procedures, of triggers and other good stuff. Like myself, if they were aware how much easier life could be if these things were accessible to them, they'd probably be converts too. Agreed completely. Now we just have to sell the PostgreSQL solution. Here is what the MySQL people will say (and we need good evidence to counter): 1: MySQL is faster. 2: MySQL has more community support. 3: MySQL has replication as part of its core distribution. MySQL's replication is better tested... 1. Let's do apples to apples, NOT apples to Oranges as has been done many times in the past. It would be far more useful IMHO to put forward a &
Re: Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
On 29/11/2003 16:24 Jason Tesser wrote: [snip] A programmer that doesn't document stuff needs to find a new job :-) Agreed. So you're replaced him and inherited a documentation-free application. How many favours has he done you by squirrelling away section of business logic in the database? This is more of an issue with management. Anyone who does database apps for on any kind of a large scale will tell you that views, triggers, etc.. are essential. I am currently in teh process of writing a complete solution for the college I develop for. Finance, accounting, pos, registration, student tracking etc... I've worked on stuff for some of the largest companies in the world if that counts. Mind you, I've been in the business 24 years (18 of those as an independent consultant) so maybe I'm just a newbie :) For your accounting, take a look at SQL-Ledger (www.sql-ledger.org). It might save you months of effort. You might not have understood me or I am not understanding you. It feels like we're 2 people divided by a common language... -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
On Sat, 2003-11-29 at 04:37, cnliou wrote: Jason Tesser [EMAIL PROTECTED] MySQL cannot even handle sub-queries yet. Ohh! Really? Allow me to pay my highest respect to the genius mySQL programmers! I completely have no clue on how to construct any single tiny database on a DBMS having no sub-query capability. Being too dumb, I solicit mySQL programmers' help by showing me employee FOO's birthday and his/her latest job title effective on or before 2003-1-1 from the following tables: CREATE TABLE t1 (employee TEXT,BirthDay DATE); CREATE TABLE t2 (employee TEXT,EffectiveDate DATE,JobTitle TEXT); And make the result like this: FOO 1980-1-1 programmer Please do not give me the answer that you will merge these two tables to form one like this: CREATE TABLE t1 (employee TEXT,BirthDay DATE,EffectiveDate DATE,JobTitle TEXT); I have great trouble following your meaning, but I think you are talking about joining two tables in a query: SELECT t1.employee, t1.birthday, t2.jobtitle FROM t1, t2 WHERE t1.employee = t2.employee; That is not the same as using a sub-query: SELECT employee FROM t1 WHERE birthday ( SELECT MIN(effectivedate) FROM t2 ); (select employees who were born after the longest-serving employee started work.) -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Who shall ascend into the hill of the LORD? or who shall stand in his holy place? He that hath clean hands, and a pure heart...Psalms 24:3,4 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
HI All, I'm glad that this thread prompted some thoughtful response. I think one of my main points I was trying to make, Jason hit the nail on the head. The article to which I was referring uses a great example which I have experienced many times before, but in order to grasp this, PHP et al, must be thought of as a scripting language which crosses many corporate boundries, and it is easy to assume that it's primary use (simple web site back ends) are the only thing to discuss. But the situation has changed enourmously since the release of PHP v4. Now many consultant/developer/sys-admins like myself are going to client site on a contract (this is especially true in the UK, I can't speak for anywhere else) and finding complex stocktrading systems, inventory systems, CRM systems, and others, all written in PHP backed by MySQL. Whether this is right or wrong, good choice or bad choice is not what I'm interested in debating. The point is that when these systems where architected, the developers used MySQL not because they were dumb, but because many of them develop awesome code and can get around most problems in the code, with a little ingenuity. Many simply do not have the insight into the potential benefits of *proper* RDBMS can offer. Had they had the benefit of such knowledge the code they have written would be faster (in DB) and more legible. Sadly often the developers are the only source of DBA for some of these companies. The second scenario, is with admin systems, written by people like myself for companies, whether they be simple or complex systems, that are intended as a temporary work around to an immediate problem. In a very short space of time the stop-gap application you had written to sort out the immediate problem quickly becomes a core business application (I recently returned to a site after not being there for two years and the temporary address book/ email system that I knocked up in an afternoon was not only still being used, but now relied upon heavily). So on to my point, MySQL guys will happily say Hey, we're not saying that the features MySQL is missing aren't important, and we're working towards them, but in the meantime these issues can be worked around like this. and happily play the whole thing down. Many LAMP developers aren't aware of the benefits of stored procedures, of triggers and other good stuff. Like myself, if they were aware how much easier life could be if these things were accessible to them, they'd probably be converts too. There is not enough emphasis put on the basic importance of these functions in PG. Someone needs to standup and say Hey, look how this can simplify your programming lives until I started using Druid/Postgres, I had no idea why I needed triggers or what a cascade effect did, or why I might want one. The Linux community has grown at least in part because it has educated potential users and journo's to its benefits. I believe if the PG advocacy team did the same, then it would attract many more serious LAMP developers. Like Linux vs. Windows, PG has an awful lot going for it in respect to MySQL, so why not crow about it. It needs to be pointed at a crowd that are DB novices, they need to be told why PG is worth the time/knowledge investment, because anyone who reads the MySQL site, will come away with the impression that the Trigger, Stored Procs, and other things are a luxurious overhead not necessary for getting the job done. I'd gladly help out with such a paper, but find myself in the sad position of my prose being open to attack due to my newbieness in the DB world and not able to speak authoratatively on the subject. Have a think, I'd like to know if others agree. Cheers T. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
Further to this post, what might actually work is to convince O' Reilly (since they have PostgreSQL book/s) to do some articles like they have for PG, but making full use of the PG database. For instance, building a simple data-warehouse using PG. Articles that show off an OSS product/project in a clearly enterprise light in a step-by-step fashion. There have been so many articles on DB design using MySQL. How about an article on DB design using all the functionality of a real ORDBMS. Just a few thoughts. Cheers T. Tony wrote: HI All, I'm glad that this thread prompted some thoughtful response. I think one of my main points I was trying to make, Jason hit the nail on the head. The article to which I was referring uses a great example which I have experienced many times before, but in order to grasp this, PHP et al, must be thought of as a scripting language which crosses many corporate boundries, and it is easy to assume that it's primary use (simple web site back ends) are the only thing to discuss. But the situation has changed enourmously since the release of PHP v4. Now many consultant/developer/sys-admins like myself are going to client site on a contract (this is especially true in the UK, I can't speak for anywhere else) and finding complex stocktrading systems, inventory systems, CRM systems, and others, all written in PHP backed by MySQL. Whether this is right or wrong, good choice or bad choice is not what I'm interested in debating. The point is that when these systems where architected, the developers used MySQL not because they were dumb, but because many of them develop awesome code and can get around most problems in the code, with a little ingenuity. Many simply do not have the insight into the potential benefits of *proper* RDBMS can offer. Had they had the benefit of such knowledge the code they have written would be faster (in DB) and more legible. Sadly often the developers are the only source of DBA for some of these companies. The second scenario, is with admin systems, written by people like myself for companies, whether they be simple or complex systems, that are intended as a temporary work around to an immediate problem. In a very short space of time the stop-gap application you had written to sort out the immediate problem quickly becomes a core business application (I recently returned to a site after not being there for two years and the temporary address book/ email system that I knocked up in an afternoon was not only still being used, but now relied upon heavily). So on to my point, MySQL guys will happily say Hey, we're not saying that the features MySQL is missing aren't important, and we're working towards them, but in the meantime these issues can be worked around like this. and happily play the whole thing down. Many LAMP developers aren't aware of the benefits of stored procedures, of triggers and other good stuff. Like myself, if they were aware how much easier life could be if these things were accessible to them, they'd probably be converts too. There is not enough emphasis put on the basic importance of these functions in PG. Someone needs to standup and say Hey, look how this can simplify your programming lives until I started using Druid/Postgres, I had no idea why I needed triggers or what a cascade effect did, or why I might want one. The Linux community has grown at least in part because it has educated potential users and journo's to its benefits. I believe if the PG advocacy team did the same, then it would attract many more serious LAMP developers. Like Linux vs. Windows, PG has an awful lot going for it in respect to MySQL, so why not crow about it. It needs to be pointed at a crowd that are DB novices, they need to be told why PG is worth the time/knowledge investment, because anyone who reads the MySQL site, will come away with the impression that the Trigger, Stored Procs, and other things are a luxurious overhead not necessary for getting the job done. I'd gladly help out with such a paper, but find myself in the sad position of my prose being open to attack due to my newbieness in the DB world and not able to speak authoratatively on the subject. Have a think, I'd like to know if others agree. Cheers T. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
On 28/11/2003 17:10 Jason Tesser wrote: [snip] I completely disagree. I do a lot of programming with PHP and the features of Postgres come in handy. Let me give you an example of just some basic things. Triggers! Why should I have to write insert and update triggers in the logic (PHP) if I can handle it at the database level. Sql is 10x as fast as the language. Better to handle what you can at the database level. Same with views and stored procedures. Stored procedures can be a 2-edged sword. They can lead to business logic being scattered between the persistence layer and the business layer. Thats not good for maintaining the application 3 years down the line. Triggers can also cause maintenance problems. Its so easy to forget/fail to document that inserting a record into table x causes column y of table z to be updated. Be careful how and where you use these features as they can come back to bite you! MySQL cannot even handle sub-queries yet. I also use Python for standalone interfaces to the data. Why should I not be able to use the same views and triggers etc in there that I use for my web apps. PHP is quite powerful if used correctly. You are, of course, free to do whatever want. But if you have to use features of the database to compensate for inadequacies in your programming language maybe you should be using another language? Java has its own issues and I am not sure it is as far supiour as you are claming it is. But that is not for this dscussion. I'm not aware of any issues with Java (unless you mean Swing ;)). MySQL may be more popular with (cheap) web hosting places but that doesn't mean it is the best or that Postgres wouldn't serve better even in this area. I am glad to see the article written for PHP mag as Postgres would help alot of PHP guys that are using MySQL. Much of the populatity of MySQL seems to stem from PHPs out-of-the-box support for it. With the MySQL client library license change, this situation will probably change. There was a long thread about this earlier this year. Check the archives. -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
Paul Thomas wrote: On 28/11/2003 17:10 Jason Tesser wrote: [snip] MySQL cannot even handle sub-queries yet. I also use Python for standalone interfaces to the data. Why should I not be able to use the same views and triggers etc in there that I use for my web apps. PHP is quite powerful if used correctly. You are, of course, free to do whatever want. But if you have to use features of the database to compensate for inadequacies in your programming language maybe you should be using another language? This doesn't even make sense in the context of Jasons remark. Java has its own issues and I am not sure it is as far supiour as you are claming it is. But that is not for this dscussion. I'm not aware of any issues with Java (unless you mean Swing ;)). MySQL may be more popular with (cheap) web hosting places but that doesn't mean it is the best or that Postgres wouldn't serve better even in this area. I am glad to see the article written for PHP mag as Postgres would help alot of PHP guys that are using MySQL. Much of the populatity of MySQL seems to stem from PHPs out-of-the-box support for it. With the MySQL client library license change, this situation will probably change. There was a long thread about this earlier this year. Check the archives. This is incorrect. The embedded mysql client library was not added until PHP4.0 RC1. PHP's popularity existed long before this. The real culprit causing the popularity of MySQL was it's ubiquity among hosting providers and the virtual non-existence of PG in that arena. If PG had been more friendly to shared hosting environments, perhaps this situation wouldn't have arisen. Blaming PHP for this situation (and your other comments) show extreme prejudice. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
From: Paul Thomas [EMAIL PROTECTED]: Stored procedures can be a 2-edged sword. They can lead to business logic being scattered between the persistence layer and the business layer. Thats not good for maintaining the application 3 years down the line. Triggers can also cause maintenance problems. Its so easy to forget/fail to document that inserting a record into table x causes column y of table z to be updated. Be careful how and where you use these features as they can come back to bite you! It is all how you organize your app. Stored proceedures are extremely useful when they represent a unified API for accessing parts of the database. Word of advice: Keep the database self-contained. If all you want is object persistance, then why non use Berkeley Database? It is even transactional. The point of having an RDBMS is to provide more flexibility than a simple persistance store. When used sensibly, stored proceedures are extremely simplifying, not the other way arround. Why should I not be able to use the same views and triggers etc in there that I use for my web apps. PHP is quite powerful if used correctly. You are, of course, free to do whatever want. But if you have to use features of the database to compensate for inadequacies in your programming language maybe you should be using another language? I don't think Jason was compensating for weaknesses in the language-- I think that he was asking why he woudln't want to build into the database the universal functions accessed by multiple applications. And he would be right in trying to do so. Let me give you an example: One of the large projects I maintain is HERMES (http://hermes.sourceforge.net). Hermes relies on its own user and permissions catalogs in order to provide a consistant administrative interface across database managers and simplify the task of assigning permissions to users and groups. The differences in syntax can them be handled in wrapper layers, etc. However, it makes sense to try to wrap these catalogs using stored proceedures so that third-party apps don't necessarily need to be aware of the structure of the catalogs when assigning permissions. This way, too, the db users' catalog and the user catalog in the RDBMS can be guaranteed to be in sync. It will also allow me eventually to directly enforce permissions using triggers rather than rely on the RDBMS model (useful in shared hosting environments). Java has its own issues and I am not sure it is as far supiour as you are claming it is. But that is not for this dscussion. I'm not aware of any issues with Java (unless you mean Swing ;)). Every language has issues. This is not the time or place for a development environemnt holy war ;-) But--- PHP and Python all the way ;-) Much of the populatity of MySQL seems to stem from PHPs out-of-the-box support for it. With the MySQL client library license change, this situation will probably change. There was a long thread about this earlier this year. Check the archives. Putting the cart before the horse. MySQL is far easier to administer in a shared hosting environment. Maybe one of these days, I will put together a package for managing PostgreSQL accounts in this way. If there is interest, please email me off-list and we can get started. I don't expect MySQL's dominance to change until we can offer an easy-to-administer alternative for these environments. Best Wishes, Chris Travers ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
On 27/11/2003 09:19 Tony wrote: Hi All, I've just been reading an article in PHP Architect magazine (http://www.phparch.com) which is the cover story for October called Migrating from MySQL to PostgreSQL. I must say that this is a highly compelling article, especially for me, and is aimed at programmers that aren't necessarilly SQL experts or DBAs. For instance, like many PHP Web developers who use MySQL instead of flat files to store stuff! Instead of using a DB as a powerful tool. This article presents reasons as to why a more standards compliant DB is good for programmers, and why in some cases MySQL can be less of a friend to programmers than perhaps PostgreSQL. I honestly believe that if the advocates of PostgreSQL wrote an article or case study along the lines of this article, it would go a long way to attracting many more programmers. In my experience all of the articles and tutorials are written from the perspective of why PG is a better DB as a DB. Rather than emphasise aspects like PG is great because you can move complicated code like this insert complicated PHP/Perl code here . normally dealt programatically to your DB which can be both faster and applied to any other programmers (VB, Java) that you are sharing the important enterprise data with. I've not seen anything in articles aimed at PHP/MySQL users saying, Hey, look at how these triggers can make your life s much easier or Hey, look at how cascading can save you oh so much coding or Hey look at all this programmatical logic that can be put into queries just by writing your own functions I have recently compared the PostgreSQL users to the Debian users (meant as a complement) by the fact that they are in general highly knowledgable of thier own subject and peripheral subjects too. They are passionate and well versed, and happy to nudge people in the direction of enlightenment without spoonfeeding them. But in the same way, the advocacy (IMHO) falls into the same boat as Debian. There is a certain self-assuredness that PostgreSQL is a far superior product and if someone can't see how obvious that is then maybe PG isn't for them (a little harsh I know but I'm trying to illustrate a point). My point is that there are thousands, tens of thousands of programmers out there, that need to know why and how PG is so great. My eyes have now been fully opened by this article, and got rid of my nagging feeling that there was something great about PG that I Just wasn't grasping, and couldn't put my finger on. Maybe the advocacy team should be aiming for all those programmers that desperately need PG, but don't know it yet, and probably don't have time to garner enough DB experience to understand why they need it! Maybe there's not such a need for the advanced features of PostgreSQL amongst PHP programmers as you seem to believe. Most of the PHP stuff I've seen is read-only content display stuff and that doesn't really require a top-notch RDBMS; a more limited database should also be up to the job. For complex transactional web applications, J2EE/Model II is a far superior technology to scripts/Model I and that means a different target audience for the apps where PostgreSQL can offer those essential extra features. Whilst most J2EE developers will be using Oracle/DB2/MSSQL as their back-end, the awareness of PostgreSQL seems quite high and, in the few usenet groups I monitor, I don't recall anyone being flamed for recommending PostgreSQL over MySQL. Maybe seasoned, professional developers don't like being told that they're crap programmers just because they ask for something as fundamental as referential integrity! Coming to your point about advocacy, I certainly don't recognize what you describe. Of course the members of the advocacy group believe in the quality of PostgreSQL (a view shared by most of the subscribers to list). What I think you need to bear in mind is that PostgreSQL is a genuinely open-source product _not_ a commercial product in GPL clothing like MySQL. The developers and advocates are not making $xx per box shifted or trying to seduce users down a supposedly free path into their licensed software lair. That has a big effect on advocacy. Instead of smarmy marketing types who rely on spread FUD and misinformation about every product they consider a competitor, we have a group of people acting with honesty and integrity. Welcome to the real world of open source :-) -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ?
Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
hi, huge snip Maybe there's not such a need for the advanced features of PostgreSQL amongst PHP programmers as you seem to believe. Most of the PHP stuff I've seen is read-only content display stuff and that doesn't really require a top-notch RDBMS; a more limited database should also be up to the job. For complex transactional web applications, J2EE/Model II is a far superior technology to scripts/Model I and that means a different target audience for the apps where PostgreSQL can offer those essential extra features. Whilst most J2EE developers will be using Oracle/DB2/MSSQL as their back-end, the awareness of PostgreSQL seems quite high and, in the few usenet groups I monitor, I don't recall anyone being flamed for recommending PostgreSQL over MySQL. Maybe seasoned, professional developers don't like being told that they're crap programmers just because they ask for something as fundamental as referential integrity! I completely disagree. I do a lot of programming with PHP and the features of Postgres come in handy. Let me give you an example of just some basic things. Triggers! Why should I have to write insert and update triggers in the logic (PHP) if I can handle it at the database level. Sql is 10x as fast as the language. Better to handle what you can at the database level. Same with views and stored procedures. MySQL cannot even handle sub-queries yet. I also use Python for standalone interfaces to the data. Why should I not be able to use the same views and triggers etc in there that I use for my web apps. PHP is quite powerful if used correctly. Java has its own issues and I am not sure it is as far supiour as you are claming it is. But that is not for this dscussion. MySQL may be more popular with (cheap) web hosting places but that doesn't mean it is the best or that Postgres wouldn't serve better even in this area. I am glad to see the article written for PHP mag as Postgres would help alot of PHP guys that are using MySQL. another snip ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
Jason Tesser [EMAIL PROTECTED] MySQL cannot even handle sub-queries yet. Ohh! Really? Allow me to pay my highest respect to the genius mySQL programmers! I completely have no clue on how to construct any single tiny database on a DBMS having no sub-query capability. Being too dumb, I solicit mySQL programmers' help by showing me employee FOO's birthday and his/her latest job title effective on or before 2003-1-1 from the following tables: CREATE TABLE t1 (employee TEXT,BirthDay DATE); CREATE TABLE t2 (employee TEXT,EffectiveDate DATE,JobTitle TEXT); And make the result like this: FOO 1980-1-1 programmer Please do not give me the answer that you will merge these two tables to form one like this: CREATE TABLE t1 (employee TEXT,BirthDay DATE,EffectiveDate DATE,JobTitle TEXT); Regards, CN ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
Jason Tesser [EMAIL PROTECTED] wrote: I completely disagree. I do a lot of programming with PHP and the features of Postgres come in handy. Let me give you an example of just some basic things. Triggers! Why should I have to write insert and update triggers in the logic (PHP) if I can handle it at the database level. Sql is 10x as fast as the language. Better to handle what you can at the database level. Same with views and stored procedures. MySQL cannot even handle sub-queries yet. I also use Python for standalone interfaces to the data. Why should I not be able to use the same views and triggers etc in there that I use for my web apps. PHP is quite powerful if used correctly. I guess I am coming at this from the other direction: MySQL is popular and many people use it for lightweight stuff. Partly this may be because better tools exist for providing hosted solutions, and this is an area we could improve (automatically adding entries to the pg_hba.conf, etc.-- may have to look into doing this). THe real problem I see is that this keeps PHP from being an ideal skill for developing enterprise applications. The features you are mentioning are extremely helpful, even necessary, when you have many applications working against the same database. The triggers, etc. can give you some consistant business logic, and you can use views to present information to the applications in a way that is natural for them. In essence, my point is that for single-use databases, MySQL isn't all that bad (aside from consistancy issues). However, the popularity of the LAMP development environment holds PHP back from being a serious corporate development environment, IMO. Java has its own issues and I am not sure it is as far supiour as you are claming it is. But that is not for this dscussion. MySQL may be more popular with (cheap) web hosting places but that doesn't mean it is the best or that Postgres wouldn't serve better even in this area. I am glad to see the article written for PHP mag as Postgres would help alot of PHP guys that are using MySQL. Again, I think that the most important benefit would be lowering the barrier to entry of serious development. You can start with Linux/Apache/PostgreSQL/PHP for a simple site, and then use your knowledge better to develop more serious applications. But the critical issue to resolve is to make available a tool or set of tools to manage shared hosting environments in an easier way. I would be happy to try to generate such a set of tools. Best Wishes, Chris Travers ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] PostgreSQL Advocacy, Thoughts and Comments
Hi All, I've just been reading an article in PHP Architect magazine (http://www.phparch.com) which is the cover story for October called Migrating from MySQL to PostgreSQL. I must say that this is a highly compelling article, especially for me, and is aimed at programmers that aren't necessarilly SQL experts or DBAs. For instance, like many PHP Web developers who use MySQL instead of flat files to store stuff! Instead of using a DB as a powerful tool. This article presents reasons as to why a more standards compliant DB is good for programmers, and why in some cases MySQL can be less of a friend to programmers than perhaps PostgreSQL. I honestly believe that if the advocates of PostgreSQL wrote an article or case study along the lines of this article, it would go a long way to attracting many more programmers. In my experience all of the articles and tutorials are written from the perspective of why PG is a better DB as a DB. Rather than emphasise aspects like PG is great because you can move complicated code like this insert complicated PHP/Perl code here . normally dealt programatically to your DB which can be both faster and applied to any other programmers (VB, Java) that you are sharing the important enterprise data with. I've not seen anything in articles aimed at PHP/MySQL users saying, Hey, look at how these triggers can make your life s much easier or Hey, look at how cascading can save you oh so much coding or Hey look at all this programmatical logic that can be put into queries just by writing your own functions I have recently compared the PostgreSQL users to the Debian users (meant as a complement) by the fact that they are in general highly knowledgable of thier own subject and peripheral subjects too. They are passionate and well versed, and happy to nudge people in the direction of enlightenment without spoonfeeding them. But in the same way, the advocacy (IMHO) falls into the same boat as Debian. There is a certain self-assuredness that PostgreSQL is a far superior product and if someone can't see how obvious that is then maybe PG isn't for them (a little harsh I know but I'm trying to illustrate a point). My point is that there are thousands, tens of thousands of programmers out there, that need to know why and how PG is so great. My eyes have now been fully opened by this article, and got rid of my nagging feeling that there was something great about PG that I Just wasn't grasping, and couldn't put my finger on. Maybe the advocacy team should be aiming for all those programmers that desperately need PG, but don't know it yet, and probably don't have time to garner enough DB experience to understand why they need it! Sadly the PHP Architect article is not free, I bought the electronic magazine for about $2, but believe it's worth every penny and more. Just my 2 cents. Apologies if the PG articles ARE out there and please notice that my comments do not say that they don't exist, but that I have never seen them. Cheers Tony. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
