

PostgreSQL 9.0 Released 344
poet writes "Today the PostgreSQL Global Development Group released PostgreSQL 9.0. This release marks a major milestone in the PostgreSQL ecosystem, with added features such as streaming replication (including DDL), Hot Standby and other nifty items like DO. The release notes list all the new features, and the guide explains them in greater detail. You can download a copy now."
Waiting for a capable PostgreSQL front-end (Score:3, Informative)
Yes first, congratulations to those folks. I am still waiting for a front-end to PostgreSQL that is as functional and easy to program as Microsoft's Access.
I might be flamed here but there is nothing that bests Access in the open source world. Being able to program business logic into a form is something that Access and VB are pretty good at.
What open source program can replace these two Microsoft beasts?
Re:Has the Documentation Been Improved? (Score:5, Informative)
Err, have you actually used the PostgreSQL manual? It's one of the best manuals I've ever seen for a software product.
Re:Meh (Score:3, Informative)
I think you've got your databases backward when it comes to integrity and verification...
Re:"Great leap forward" (Score:5, Informative)
Yes, but MySQL has a shoddy parser (needs a space after the -- comment tag), poor trigger failing (you have to do a kludge and dump a varchar into an int to get it to fail), apparent lack of direction (how many forks and engines?!), no CTE support and the list goes on. I am constantly banging my head against a wall with MySQL. I use MSSQL for work, Postgres at home and MySQL on hosting.
I am truly surprised that most web hosting companies do not offer Postgres. Postgres also allows writing of DB functions in C, Java, PHP etc. like Oracle, which is useful for bundling code into the DB (making the DB the application) without everyone having to see your SQL source for functions. It is also licensed on BSD which is good for using their libpq library in commercial apps; MySQL's C API is GPLd or licensed expensively from Oracle, although there are moves toward making it free for use in commercial apps (as far as I can tell from the mishmash of info coming from their sales rep via email).
Also, as far as I know, MySQL puts all of its indexes in memory for replication which is a problem if the node goes down. Can anyone enlighten me?
In any case, well done to the Postgres team. Not only is their software package neat, their documentation is some of the best I have ever seen.
Re:Cool (Score:2, Informative)
Re:Waiting for a capable PostgreSQL front-end (Score:4, Informative)
Why not just use .NET with PostgreSQL? You can put whatever you want on the back end.
Or you could use Once:Radix or Servoy, both of which integrate with PostgreSQL.
https://sourceforge.net/projects/onceradix/ [sourceforge.net]
http://www.servoy.com/ [servoy.com]
Re:Has the Documentation Been Improved? (Score:5, Informative)
Re:Has the Documentation Been Improved? (Score:5, Informative)
It's actually one of the best manuals for SQL in general - at least, in my experience, it has the most clear explanations of many more advanced SQL constructs that are common between various RDBMSes.
Re:Cool (Score:2, Informative)
Re:Cool (Score:5, Informative)
What's the difference between logic in the trigger determining when to issue the payload logic, and the logic outside the trigger...
No! Its far more than syntactic sugar. Performance, readability, and maintainability are what this brings to the table.
The difference between PostgreSQL's new column trigger feature and traditional triggers is they are only called when the column is modified rather than when any row is modified. This means, in many cases, the trigger will never be called and therefore, the DB isn't having to run at PL/SQL interpreter speeds during the execution of the trigger, to then determine there is nothing for it to do. Furthermore, a big headache which is extremely common to trigger code are IF/THEN/ELSE or long CASE statements to determine which columns are modified, or to determine if the trigger even cares that the row in question (example, columns which the trigger doesn't care about) has been modified.
The above combination of traditional triggers means lots of overhead, lots of needless PL/SQL code execution, and hard to read/maintain triggers for non-trivial actions. Whereas with the new feature, you can now have a single trigger relate to specific column, which is only ever executed when the trigger should actually execute. Its a win, win, win for all PostgreSQL users. And best of all, this means you can have smaller triggers when you need to perform different actions based on different column changes.
Re:Waiting for a capable PostgreSQL front-end (Score:3, Informative)
Indeed. About 0.0005 seconds of Googling brought me this as the first link:
http://www.postgresonline.com/journal/archives/24-Using-MS-Access-with-PostgreSQL.html [postgresonline.com]
Though I think there are probably a grand total of 3 people on Earth who have used MS Access in any serious capacity and don't loathe it.
Re:Waiting for a capable PostgreSQL front-end (Score:5, Informative)
You know that you can point your MS Access client to any supported back-end right? Just create an ODBC connection on your Windows machine to your PostgreSQL server and you can use Access with pretty much all the features that work for the Microsoft JetEngine (PostgreSQL has ODBC drivers here; http://www.postgresql.org/ftp/odbc/versions/ [postgresql.org])
Earlier this year we converted a huge Access application from MSSQL to PostgreSQL and the technical conversion, using ODBC to PostgreSQL instead of connecting to MSSQL, was a piece of cake.
Re:Cool (Score:3, Informative)
You clearly have no idea what you're talking about. Granularity is widely accepted for such linguistic applications. The reason being, its completely applicable.
Basically you're foolishly arguing that monster triggers are preferred over small, terse, highly readable, and much more maintainable triggers. Lots and lots of code is good. Less code which does the same thing is bad. That's simply ignorant, foolish, and FUD. "Scattering" is clearly being used to imply a negative connotation. Given there isn't a single negative with their use and that "granularity" is accepted and proper, making such statements squarely implies you're trolling.
Re:Thank you! (Score:5, Informative)
Thirded. There is absolutely no reason for anyone to be using MySQL any more other than the old silly excuse "my hosting provider doesn't have anything else". PostgreSQL is now faster than MySQL in all but the most trivial of contrived cases, doesn't require you to choose between table types for different load types, is just as easy to use and install, has all the features that MySQL has and runs on a Windows server (for those idiots who think that is a good thing). Also, the PG community is vastly more helpful and knowledgeable than the rabble that is the MySQL user base.
Finally, PostgreSQL is a proper independent open source project with a structure that all other open source projects should be judged by. MySQL has gone from hand to hand in the corporate world and has a future that is far from certain.
Down with the joke that is MySQL, and down with all the idiots that make me work with it.
Re:Meh (Score:5, Informative)
Um, yeah. MySQL, out of the box, using the defaults, doesn't support foreign keys now. You have to specifically create the tables with a non-standard SQL code to get them to use the right database backend to get foreign key support.
Unless you mean by 'support' 'Will silently accept and throw away'...
Foreign keys have been enabled and working by default in Postgres since version 7. (There was no version 5...) That was released just over ten years ago at this point.
Re:"Great leap forward" (Score:4, Informative)
| Please tell me you're kidding. Anyone suggesting MySql for real work should just be laughed at.
I'm not sure how you got modded to +5 with this statement, but your statement is uninformed and completely false. While MySQL isn't in the class of Oracle for HA, MySQL with InnoDB is damn well is a competent database and I don't just mean for LAMP.
Eh. It's *okay* for lightweight work where you don't care about data integrity or don't add or modify a lot of data. Beyond that it falls apart quickly.
At a previous job we used ActiveRecord hooked up to MySQL to handle an influx of temporal data that was meant to be quickly processed and usable for reading back in real time. ActiveRecord uses sequences (so, auto increment fields in MySQL -- since proper sequences are lacking in MySQL) for the primary key. With Postgres this is not a problem at all. InnoDB, OTOH, locks *the entire table* to update an auto increment field. The sysadmin/dba was averse to using Postres, so the result was a series of complex and tedious to debug performance problems and queues. We spent countless hours dancing around the performance problems inherent to table level locking.
Of course we could have gone with MyISAM... but data integrity was important. There were other seemingly basic features that were lacking in MySQL (timezone support and a useful explain command come to mind). As far as I can tell there aren't a lot of good reasons to actively choose MySQL. The lightweight cases are well handled by SQLite, and the heavier stuff will almost certainly benefit from what Postgres has to bring to the table.
Re:Has the Documentation Been Improved? (Score:3, Informative)
AFAIC, it is the standard by which other software manuals should be judged. Good call.
Re:Cool (Score:3, Informative)
Re:"Great leap forward" (Score:3, Informative)
It's not 2000 anymore. 99% of the problems people have historically with MySQL are simply not present in recent production versions. PostgreSQL and MySQL roughly have feature parity nowadays, Stop treating MySQL as if it's some toy. WikiVS has a good, up-to-date comparison: http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL [wikivs.com]
I also find it amusing that an AC below complains about "how many storage engines"? Whoosh, that's the sound of the point flying over his head.
By the way, I'm not dissing PostgreSQL in any way, I think it's great. But it's about time some meaningless mantras stop being chanted.
Re:I think Sun was giving them access to some... (Score:4, Informative)
The servers Sun supplied that Oracle recently yanked were for the regular PostgreSQL build farm, used to run basic regression tests. They've since been replaced, the project is unmoved. As I mention in more detail in my upthread post, work on the PostgreSQL performance farm continues unaffected by that. It is expected that some build farm machines will also run the performance farm client periodically too, that's the only overlap there ever was between the two pieces of work. If Oracle still had hardware in the build farm it could have been used for performance tests too eventually. But they don't, and we in the PostgreSQL community don't care; we don't need their contributions.
Re:"Great leap forward" (Score:3, Informative)
Re:Haha (Score:2, Informative)
Re:"Great leap forward" (Score:3, Informative)
The Feb 30 issue gets *even better*. For years I used that as a prime example of what's wrong with MySQL, so I was a bit disappointed when I found out they'd fixed it.
Then I discovered that Feb 35th is *STILL* a valid date! The only thing they fixed was Feb 30th and 31st!
MySQL clearly just doesn't get it.