Forgot your password?
typodupeerror
This discussion has been archived. No new comments can be posted.

PostgreSQL 8.4 Out

Comments Filter:
  • by jlechem (613317) on Wednesday July 01, 2009 @03:05PM (#28547255) Homepage Journal
    Does anyone even use mySQL when they have features like this? The only issue I have ever had with this DB was when I was trying to connect a .net app to it and it took me a while to find a workaround.
    • by XanC (644172)

      Not one of these features is remotely compelling enough to switch an existing, working app from MySQL.

      • Re:So why (Score:5, Interesting)

        by ducomputergeek (595742) on Wednesday July 01, 2009 @03:10PM (#28547349)

        No, but Oracle taking over MySQL and the community already showing signs of forking in 4 different directions might be a reason to seriously look at PostgreSQL.

        • by Foofoobar (318279)
          Nope... I was planning on continue to stick with MySQL as MariaDB is going to bring much needed improvements to the project
        • that, and the fact that four years later from the original bug report, stored procedures still can't even find out what caused a thrown exception [mysql.com], with no indication that they even give a damn about it. Working with stored procs under MySQL is a frigging joke.
          • by MBGMorden (803437)

            I'll concede your point, but the original poster did state that his app was working fine as is with MySQL. If the app is simple enough you might simply not have to use stored procedures at all.

            Truthfully though, I'm not sure what keeps drawing users back to MySQL as a default. As the OP stated, it's often not worth the effort to switch a working app to a different database (and I've still got one application hitting a MySQL database because it works fine - all it does it let the company secretaries enter

            • "the original poster did state that his app was working fine as is with MySQL."

              So he thinks. From past experience I'd bet he simply doesn't know better.

              "I've still got one application hitting a MySQL database because it works fine - all it does it let the company secretaries enter in phone calls that their bosses receive in a help desk type fashion)"

              And since you are using MySQL all your referential management is done in code (phone table should have person's id column as a foreign key from persons table,

            • by jadavis (473492)

              I can't fathom why anyone chooses it when starting out from scratch anymore.

              The excuses are quickly running out, that's for sure. Lack of "easy" master/slave replication with readable slave is probably the only serious complaint.

              Even in that case, it's not like you can't do it, but it will be a challenge. Slony is my favorite of the current systems (assuming you have to read from the slave), but "easy" is not one of its strong points. Robust and flexible, but not easy.

              I might say that SQL standard MERGE wou

      • by Morgon (27979)

        I don't know, I think windowing functions are pretty significant,
        Sure, it depends on whether you have an app that benefits from them, but assuming you do, it's pretty significant. Doing sub-sorts on large datasets is a killer without functions like these.

      • by rycamor (194164)

        Not one of these features is remotely compelling enough to switch an existing, working app from MySQL.

        Maybe not the new features, but all the good features that PostgreSQL already had were plenty reason for me to switch my MySQL apps. I suppose it depends on your definition of 'working'. I found that modeling and enforcing constraints in application code instead of in the database was becoming prohibitive for any application of decent complexity. Of course, I know that there are two camps on that issue, and the OOP/Framework crowd (Ruby on Rails, DJango, etc...) tend to take the philosophy that the database

      • by Sxooter (29722)

        I would agree that none of the new features would make switching from MySQL to PostgreSQL compelling. OTOH, the features PostgreSQL has had for quite some time ARE compelling to switch. Like storing the db meta data in transaction safe tables (pgsql) versus storing them in non-crash / non-transaction safe tables (MySQL). Or making sure that what you put in either goes in or throws an error without mangling the data (i.e. MySQL inserts 'stan' into a timestamp field as 0000-00-00 00:00:00 a non-existent an

    • Does it have built in replication yet?

      • Re:So why (Score:5, Informative)

        by Anonymous Coward on Wednesday July 01, 2009 @03:16PM (#28547435)

        No, the replication and hot standby patches were not considered of high enough quality to be a part of 8.4. They will be a high priority during development of 8.5.

        PostgreSQL would never, ever ship something which has a WTF-list as long as MySQL's replication does: http://dev.mysql.com/doc/refman/5.0/en/replication-features.html [mysql.com]

      • by jadavis (473492)

        git repo [postgresql.org]
        Design spec [postgresql.org]

        This is the feature that didn't quite make it in 8.4. Look at how much effort and research went into the design. This isn't a situation where someone threw some code together; this is real design and real code by a highly reputable hacker, that answered feedback from other highly reputable hackers.

        The only reason this didn't make it is that PostgreSQL has a reputation for getting things right (really right), and that's more important than one feature.

        But try it out. If the code hasn't rot

    • Re:So why (Score:5, Interesting)

      by Phroggy (441) <slashdot3&phroggy,com> on Wednesday July 01, 2009 @03:19PM (#28547495) Homepage

      Does anyone even use mySQL when they have features like this? The only issue I have ever had with this DB was when I was trying to connect a .net app to it and it took me a while to find a workaround.

      Because I don't need features like this, and don't know how to use them. At least, as far as I know, I don't need features like this. Since I don't know how to use them, it doesn't really matter, does it?

      MySQL is what I've been running for several years; I'm familiar with the software. I use DBD::mysql in my Perl scripts; I'm sure most things would work fine if I simply switched to DBD::Pg, but would any queries need to be changed? I have no idea. Of course I'd need to migrate my data from MySQL to PostgreSQL; I'm not even sure of the "correct" way to migrate data from one installation of MySQL to another (copying the data files and then fixing whatever's broken usually works well enough). Of course, I'm running a few PHP-based webapps that currently use MySQL; I don't know if it's possible to get them to work with PostgreSQL or not (switching database engines in PHP isn't as simple as it is in Perl).

      I could take the time to do the research and find answers to these questions. Or I could keep using what I know works just fine. Maybe someday I'll have some compelling need to try PostgreSQL and see if switching is practical. Today is not that day.

      Does this answer your question?

      • by zehaeva (1136559)

        PHP supports PostgreSQL its as simple as doing a global find-replace of mysql_ with pg_ there done. Okay a bit too simple but the mysql functions and the pg functions have the same names other than the prefixes and they behave in the same exact manner. You may need to change a little bit of SQL.

        Once you get to start to use the more advanced features of postgresql you'll look at mysql more as a toy rather than a fully featured RDBMS.

        It's rather like when you first started playing with mysql and went "Wow,

        • PHP supports PostgreSQL its as simple as doing a global find-replace of mysql_ with pg_ there done. Okay a bit too simple but the mysql functions and the pg functions have the same names other than the prefixes and they behave in the same exact manner.

          ... or you could just use PDO [php.net], the way God intended.

      • by h4rm0ny (722443)

        You have given what is, I believe, the most common reason for not switching: MySQL works well enough for what you need so it doesn't make sense to expend additional time and effort (you probably don't have much of the former) to learn something that you will only use for the same purposes. It makes sense.

        But in a friendly response, I'll just mention that once you are familiar with PostgreSQL, you will be aware of what more you can do with it and that may lead to the second part of your logic (you will o
        • You have given what is, I believe, the most common reason for not switching: MySQL works well enough for what you need so it doesn't make sense to expend additional time and effort (you probably don't have much of the former) to learn something that you will only use for the same purposes. It makes sense.

          There are two versions of this argument:

          1. The version that applies to a system that already exists, and has been developed with MySQL.
          2. The version that applies to a system that has yet to be built.

          I can bu

          • by Phroggy (441)

            In particular, when people say that MySQL works "well enough" for what they need, I simply do not believe them. They are simply not counting the amount of time they've wasted on data integrity issues over the years, because they just don't know better that with a superior RDBMS, those problems could be solved from day one.

            But I haven't had issues with data integrity. No, I don't have a large database. I simply don't have that much data. If I did, and I had enough users accessing the data that data integrity ever became an issue, then yeah, of course I would want to fix it. That would be a compelling reason to switch, certainly. But my future projects aren't likely to be much bigger than my previous projects, and MySQL has performed well enough in the past that I expect it to work in the future too.

        • "Really, you don't need to learn PostgreSQL if MySQL is meeting your needs."

          The real problem is that MySQL only *seems* to meet some needs because the one with them doesn't know any better.

          Nine out of ten times (to say the least) when somebody has told me "but MySQL covers my needs" it turned out he had no idea what their real needs were and what were the tools he could use (both "real" and logical) so he could pick the best fitted.

          While ignorance is a reason, it's not a very glamurous one.

          • Re: (Score:3, Insightful)

            by h4rm0ny (722443)

            Well I did say that once he learnt PostgreSQL, he'd probably find that MySQL no longer met his needs simply because he realised what more was possible. ;)

            By the sounds of his other posts, he's not doing much more than using databases for a bit of data persistence for websites, in which case, admirer of PostgreSQL that I am, he's still probably right that MySQL meets his needs. It would open more doors for him if he knew PostgreSQL, of course. But equally he could be learning Python, perfecting his golf
      • by Sxooter (29722)

        I think it might be worth examining the differences between the two dbs. I've found some truly compelling things about pgsql. Here's a short list of the things I really like about pgsql:

        Transactional DDL statements. Sounds kinda trivial, but it lets you do things like alter a table, update a field, and then, if things go horribly wrong, you can roll it back. For production updates, this can be a life saver. In pgsql everything except create / drop database and create drop tablespace are transactable.

        The

    • by temojen (678985)
      Because most common web apps are only built on mySQL, because most ISPs only have mySQL, because most common web apps are only built on mySQL...
      • by Dan Ost (415913)

        Fortunately, it's getting much easier to find a hosting provider that offers PostgreSQL. Of course, if you just rent a bare machine and install your own image, you've always been able to install PostgreSQL.

    • ...slashdot!
    • Re: (Score:3, Interesting)

      by rasherbuyer (225625)

      The only reason there are comparisons between MySQL and PostgreSQL is because they are both Open Source. Otherwise there is no comparison.

      PostgreSQL is a fully featured, enterprise ready, RDBMS and stands for comparison with Oracle and DB2 and to a lesser extent Sybase/SQL Server. MySQL is not even in the same league as any of the previously mentioned. However, web developers seem to like it...

      I'll show my colours and say that I've been working with Oracle for over 10 years, and I love it, it gets better al

      • Maybe you can relay back to Oracle please that the eBusiness java interface is god awful slow.

        Thanks. You might have more sway than I do.

  • And more... (Score:5, Informative)

    by jadavis (473492) on Wednesday July 01, 2009 @03:06PM (#28547277)

    Upgrade in place is done via pg_migrator [pgfoundry.org].

    VACUUM now makes use of a "visibility map", which means that it doesn't need to process old data each time VACUUM is run. If you run VACUUM on a large table, and then immediately run it again, the second run will be instant.

    The recursive queries are the SQL standard common table expressions, that is, WITH and WITH RECURSIVE.

    The window functions is a great addition, but with PostgreSQL it's even better because you can define your own custom window functions with CREATE FUNCTION.

    There are also a huge number of little improvements, like "auto explain" which is a module that can automatically log the "EXPLAIN ANALYZE" output when a query takes a long time. This is a great convenience for DBAs, because you don't have to look at long-running queries in the log and attempt to EXPLAIN ANALYZE them manually.

    • by tcopeland (32225)

      > Upgrade in place is done via pg_migrator

      Outstanding. That was kind of painful when I upgraded RubyForge to PostgreSQL 8.3 [blogs.com]; looking forward to a much smaller downtime window for the upgrade to 8.4.

      Too bad replication didn't make it in there... maybe in 8.5.

    • Very impressive. Thanks PosgreSQL developers.
    • by afidel (530433)
      Auto explain sounds like a killer feature, I wish Oracle had that (auto trace is NOT the same).
      • by jadavis (473492)

        That was a feature that caught my eye, too, which is why I mentioned it. It seems like a small thing, but it makes life easier.

        Not only that, but it allows you to explain nested plans, like those from within functions executed in the outer plan.

  • by Foofoobar (318279)
    Can I do a cross database join yet? To date this has still been a feature that has yet to be implemented that I can do in just about every other RDBMS.
    • You have an interesting idea of "database". Why not use schemas instead?
      • Re: (Score:3, Funny)

        by Foofoobar (318279)
        I use the same definition for 'database' everyone else in the industry uses. What the hell are you talking about 'schema' boy?
    • That's not quite fair. mysql doesn't really have them either. mysql cross database joins are simply working around there lack of support for schemas. Schemas are better in virtually every way...

      if you need x database joins in postgres, you can always use dblink of course.
    • Can I do a cross database join yet?

      No. They where not implemented in this version, and the workaround is still the one using contrib/dblink, which allows cross-database queries using function calls.
      Still, even if I sound like a fanboy, this is a very minor annoyance: PG is the best OSS, Free, Gratis, RDBMS available.

    • by Anonymous Coward on Wednesday July 01, 2009 @03:27PM (#28547623)

      If you're coming from MySQL: What MySQL calls "databases" are called "schemas" in PostgreSQL. MySQL has no equivalent of PostgreSQL's "database".

      There is also db-link and dbi-link.

      • Parent is correct (Score:5, Informative)

        by Just Some Guy (3352) <kirk+slashdot@strauser.com> on Wednesday July 01, 2009 @03:54PM (#28548215) Homepage Journal
        I don't know why that got modded troll because it's essentially true. A PostgreSQL database is subdivided into schemas, each schema being much like an entire MySQL database. If you have a database named "mycompany" with schemas "financial" and "hr", you could have something like

        select
        employee.name,
        budgetitems.lineitemamount
        from
        hr.employee,
        financial.budgetitems
        where
        employee.paylevelid = budgetitems.lineitemid

        You can have multiple schemas with the same table names and disambiguate them by referring to schema.table instead of just table. In other words, they're like MySQL databases, and some mod owes the parent an apology.

        • by javilon (99157)

          Thats perfectly fine and well, but can you put two different schemas in two different boxes? no, you canÂt.

          • Thats perfectly fine and well, but can you put two different schemas in two different boxes

            First, give us a use case explaining what it is you're actually wanting to do. Second, do db-link and dbi-link (from the GP post) not do exactly that?

            • by afidel (530433)
              We run into cross database links all the time, from a data consistency perspective it's much better to have one system be the system of record and all others pull from that master server. We even do cross platform links (SQL Server to Oracle) with no problems. Of course db-link and dbi-link seem to fulfill that need just fine. We also have a function where we export a CSV file and an outside vendor uses similar functionality to pull the data in by treating the file as a source table.
          • by Qzukk (229616)

            Thats perfectly fine and well, but can you put two different schemas in two different boxes? no, you can't.

            Depending on what you're trying to achieve with that, you could store the data with partitioning and network file systems (not that I'd risk my data that way). But can you actually do hr.employee.id=job.task.employee on mysql when hr and job are "databases" on different servers?

            Anyway, if you really have an external database (that may be somewhere entirely different) there's DBlink [postgresql.org] and its friend DBI- [pgfoundry.org]

  • Oh, hallelujah! (Score:4, Informative)

    by Just Some Guy (3352) <kirk+slashdot@strauser.com> on Wednesday July 01, 2009 @03:16PM (#28547445) Homepage Journal

    Deadlocked Query Display
    No more log detective work to track down which operations deadlocked; the information is right there.

    That alone is worth the upgrade to me. Currently, if you have two deadlocked transactions, it's a pain in the neck to figure out exactly which ones are involved.

    • by Bovius (1243040)

      Good lord, I didn't see that in the new feature list. Thank you, good sir, for bringing this to our attention. I've spent many frustrating hours trying (and often failing) to track this information down.

      • We're in the same boat. I had a little script that tried to cobble the information together from a bunch of queries while logged in as admin, but that was so far from ideal that I could hardly stand it.
  • Windowing Functions (Score:3, Informative)

    by ProfFalcon (628305) <(moc.yhaclumc) (ta) (gro.todhsals)> on Wednesday July 01, 2009 @03:23PM (#28547553)

    OK, the windowing functions are exactly what I was waiting for. RANK and DENSE_RANK are phenomenal.

    See the presentation by Hitoshi Harada here: PDF Presentation [pgcon.org]

  • What I'd like to say is:

    Access is to Microsoft's Jet Engine while {name it>} is to PostgreSQL. By the way I would like an Open Source implementation.

  • " .... This should help users migrating from MySQL. ... "

    Because we know they are sure to be coming in droves whether they know it or not. :)

  • Is there a significant performance difference between WITH RECURSIVE and a recursive function?

  • SQLite on the low end, Postgres for mid-range apps, and Oracle on the high end.

    Where is the niche for MS SQL and MySQL in this picture?

    • by pembo13 (770295)

      The MS SQL niche is for those that loyal to Microsoft, regardless of cost. For those who will write Microsoft any size check because they are already using all Microsoft's other software, so what's a few more dollars... even though their company just cut staff by a few percent, you can't decide to simply not pay your license fees.

      That said, save for what I consider to be a lack of data types, SQL server is better than MySQL.

      • by afidel (530433)
        SQL Server enterprise is now significantly cheaper than Oracle Enterprise. Back in 2006 we got Oracle for a price that MS wouldn't match, but Oracle is licensed per core while MS is licensed per socket so as hardware advances the MS solution is now much cheaper. Heck the list price for a quad core box (about all you can buy today) is $50k for Oracle vs $25k for MS. SQL Server 2005/2008 are definitely good for all but the biggest of workloads and support all the normal enterprise features.
    • by dave562 (969951)

      The niche for MS SQL comes from all of the developers who have developed apps built on top of it. Most of those apps have been around for longer than a few years. From the point of view of a developer coding a new application from the ground up, it might not make much sense to saddle customers with the cost of MS SQL licenses when something like Postgres could get the job done.

      Another issue might come from scalability. MS SQL scales pretty well. Postgres is still waiting into introduce it. Once they in

  • I use PostgreSQL daily and I love it. The window functions are an enormous boon! It's still the best.

    We need some distribution happening a la Netezza, Greenplum, etc:

    • pgpool-II [postgresql.org]: old Oracle-style partitioning on a single column with fixed partition values
    • GridSQL [sourceforge.net]: stuck in 1.1beta for a long time but promises Teradata-style shared-nothing parallelism

    Anyone know better?

How many hardware guys does it take to change a light bulb? "Well the diagnostics say it's fine buddy, so it's a software problem."

Working...