Follow Slashdot stories on Twitter

 


Forgot your password?
Close
typodupeerror
×
Databases Programming Data Storage Software IT

PostgreSQL 8.0 Released 556

Posted by timothy from the slots-for-your-tabs dept.
Christopher Cashell writes "The PostgreSQL project has released version 8.0 of their well known Object-Relational Database. New features include: Win32 Native Server, Savepoints, Point-In-Time Recovery, Tablespaces, and lots more. Downloads are available via bittorrent for Unix/Linux, and the much anticipated Win32 version, or via ftp (use a mirror!)." (Here's the official announcement.)
This discussion has been archived. No new comments can be posted.

PostgreSQL 8.0 Released More

PostgreSQL 8.0 Released

Comments Filter:

  • Great (Score:5, Informative)

    by Anonymous Coward on Wednesday January 19, 2005 @08:20AM (#11407022)
    Great, but why should I use PostgreSQL when I already have a database, you might ask? Here's why. [sql-info.de]

    • For example, exceptionally poor performance of aggregates like COUNT(*), relatively weak optimizer.

      See Wiki entry [wikipedia.org] for more.

      • Re:PGSQL has its own gotchas (Score:5, Interesting)

        by JohanV ( 536228 ) on Wednesday January 19, 2005 @09:06AM (#11407367) Homepage
        I would consider the PostgreSQL gotchas to be of an entirely different category as the MySQL gotchas.
        In PostgreSQL the gotchas are all about performance. And while it may be bad that the database crawls if I didn't schedule a job with vacuum and analyze, that does not have any long term effects. I just run the maintenance tasks manually, add them to a cronjob and I am good to go.
        In MySQL however, the gotchas are mostly about data integrity. And that means that they can be disasterous because they can lead to dataloss. Unless you can explain to me how to get back whatever was silently truncated by MySQL if I discover the problems a few days later.

        I do very much prefer PostgreSQLs focus on data integrity. But of course I actually read the manual so I won't be bitten by them in either database.
        • Have you used PostgreSQL recently?

          Performance hasn't been an issue since at least version 7.

          • Re:PGSQL has its own gotchas (Score:5, Interesting)

            by JohanV ( 536228 ) on Wednesday January 19, 2005 @11:29AM (#11408973) Homepage
            Performance is still an issue when you don't read the manual and never perform maintenance on your database. No matter how good the planner is, if it doesn't have acurate statistics because nobody ever runs ANAYZE your database will crawl. No matter which version you use, if you never VACUUM dead row versions will accumulate and eventually kill your performance.

            I use PostgreSQL on a daily basis and when I change a database schema I will make sure that I run a VACUUM ANALYZE after committing the changes. But many of the people that we host don't bother to read the manual and don't do this. Usually this is no big deal because it gets picked up by the scheduled jobs. But every now and then somebody calls in a panic because their website is slow. And it always turns out to be major schema changes throwing the planner off. A quick VACUUM ANALYZE gets the performance up again and the customer is happy and has (hopefully) learned a valuable lesson.

            This is a real gotcha in PostgreSQL. It has made a few of our customers' websites crawl for a few hours on occasion. But it has never caused dataloss.

            • No matter which version you use, if you never VACUUM dead row versions will accumulate and eventually kill your performance.

              Actually, for 7.4 and newer that isn't true, since they include the aptly named "autovacuum" daemon. And yes, autovacuum also does "VACUUM ANALYZE", so no need to worry about that either.
              • But the autovacuum daemon isn't enabled or even compiled by default. You have to go into contrib, compile and install it, and then turn it loose.

                Soon, 8.1 or so, it will be integrated into the backend. It almost made it into 8.0, but missed it by "" - that much.

        • Almost accurate (Score:4, Informative)

          by einhverfr ( 238914 ) <[moc.liamg] [ta] [srevart.sirhc]> on Wednesday January 19, 2005 @12:34PM (#11409716) Homepage Journal
          While I think that PostgreSQL is the best open source database out there, there is one very important gotcha wrt: MVCC which can cause data integrity. This only happens when autovacuum is not running, of course.

          If you run enough transactions between vacuum runs (iirc a billion), the transaction counter will wrap around and suddenly your data does not have a consistant point of reference regarding visible transactions. Now, if you wait for a billion transactions to run VACUUM, you either:
          1) Have extremely poor performance anyway (not to mention having all your stats off so the planner is doing seq scans when it should use an index)
          2) Are doing something with the database which I cannot imagine (I guess a huge number of select statements could cause this, but updates cause old tuples to sit around, so you would have bad performance).

          Now, I am not aware of this ever actually having happened, but it is in the documentation, so I figure I should point it out. Of course if you let the database get to this point, then you have bigger problems than your data (chief among them being the IT staff and/or management)..

          In general, PostgreSQL focuses on data integrity to a degree not seen elsewhere in the open source database world. Even Firebird does not have such a heavy focus in this area, though to be fair it is a different enough product that their focus works well in their target markets.

          My company offers application development, remote administration, and implimentation services for PostgreSQL, MySQL, and Firebird. I am very excited about this release because it will enable us to do more with the database manager which makes us most productive.

          As a side note, PostgreSQL-Win32 will not run on Win9x because it requires an NTFS filesystem, iirc. So it is not a perfect solution for Windows development yet (until Win9x fades into the distance or until they decide that they should port it to FAT). Of course you could still use the Cygwin installation, I think. But it is better, IMO, to run it on a arguably stable system anyway.

      • Re:PGSQL has its own gotchas (Score:5, Interesting)

        by StormReaver ( 59959 ) on Wednesday January 19, 2005 @09:17AM (#11407458)
        I have several tables with over 3 million rows, and most aggregates return immediately (or nearly immediately). count(*) is the only dog since it reads every row to get the count, but it's acceptable since I rarely count all rows in the table. I've often wondered why the developers couldn't keep an internal count of all active rows, so count(*) would return immediately, but I'm sure they have their reasons.

        You've got to be joking about PostgreSQL having a weak optimizer. If it's weak, only the computer can tell.

        Your Wiki link spent most of its space praising PostgreSQL for its advanced features, while your intent is clearly to denigrate it. If that represents PosgreSQL's worst facets, then I am very, very happy.
        • If your RDBMS is doing a full table scan just to do a count(*), then it (or your code) is badly broken.

          • Re:PGSQL has its own gotchas (Score:5, Informative)

            by amorsen ( 7485 ) <benny+slashdot@amorsen.dk> on Wednesday January 19, 2005 @10:09AM (#11408050)
            If your RDBMS is doing a full table scan just to do a count(*), then it (or your code) is badly broken.

            Postgres knows how many rows are in the table, but it does not know how many of those you can see. Some of them may be inserted speculatively by another transaction. Postgres needs to go through each row to determine whether or not that row is actually visible to you. It is possible to turn this into an O(1) operation if you're willing to do sufficient work on inserts and deletes. Whether this is a good tradeoff depends on how often you do count(*) compared to how often you do inserts and deletes.

        • Re:PGSQL has its own gotchas (Score:5, Informative)

          by Cajal ( 154122 ) on Wednesday January 19, 2005 @09:48AM (#11407782)
          In MySQL (with MyISAM tables), the reason things like count(*) are fast is that MyISAM pre-computes those values. It can do this because it locks the table on insert and update. PostgreSQL doesn't lock the table on modifications -- it allows concurrent access via Multi-Version Concurrency Control (MVCC). Basically, each row in the DB has additional information (used internally by PostgreSQL), which stores which transaction created and last modified the row. PG uses this to determine if any given row should be "visible" to the current transaction. Because this informaiton is constantly changing (and varies from transaction to transaction), you can't precompute things like count(*) and sum(*). See http://developer.postgresql.org/pdf/internalpics.p df for more info (start around page 56).

          • That's really dumb. (Score:4, Informative)

            by emil ( 695 ) on Wednesday January 19, 2005 @12:40PM (#11409783)

            You're telling me that MySQL locks the entire table when I insert a row? You must be joking - that would bring a database to it's knees.

            I'm an Oracle guy, and this is how they do it:

            • If you have an index on a NOT NULL column, count(*) will count the index, and it will be fast(er). If you don't have a suitable index, you will incur a full table scan. This is reasonable.
            • An Oracle table can have one or more "FREELISTS" - an insert will attempt to find a block from an available freelist, and a row lock will be applied to the transaction header of that block. If you have high insert activity, you can (re)build your table with more FREELISTS. You can have multiple transactions hitting a block if you don't exceed MAXTRANS. If you decided to shove the block clear full of data and you set INITTRANS to 1, you effectively have block-level locking, not row-level locking.
            • Oracle NEVER escalates a lock, and there is no table of locked rows in memory (and you should run away from any database that has such a structure).

            Oracle has the best technology in the industry, hands down (DB2 didn't even get triggers until v5). Postgres appears to be paying much more attention to Oracle's methods than MySQL.

            Guess which database I'd use if I had no money to spend?

            • You're telling me that MySQL locks the entire table when I insert a row? You must be joking - that would bring a database to it's knees.

              Why do you think MySQL performs so poorly with large number of concurrent users?

            • Re:That's really dumb. (Score:5, Informative)

              by Cajal ( 154122 ) on Wednesday January 19, 2005 @01:44PM (#11410729)
              You're telling me that MySQL locks the entire table when I insert a row? You must be joking - that would bring a database to it's knees.

              MySQL can use different "storage engines" for different tables. One of their older (and arguably, more widely deployed) engines in MyISAM, which does table-level locking (their newer engine, InnoDB, does row-level locking). From the MySQL Manual (sec 7.3.1) [mysql.com]:

              Currently, MySQL supports table-level locking for ISAM, MyISAM, and MEMORY (HEAP) tables, page-level locking for BDB tables, and row-level locking for InnoDB tables.

              and from Section 7.3.2 [mysql.com]:

              To achieve a very high lock speed, MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB and BDB.

              This is one of the many problems with MySQL. I agree with you that Oracle (and PostgreSQL) are much better designed.

      • Another garage product also fares badly on COUNT(*) however if you do COUNT() they both do real well. It has to do with the fact that when you do COUNT(*) you have to count every row and that takes a fair bit of time. When you do COUNT(key) you count rows in the index file and that is real quick :-)

  • plperl (Score:3, Informative)

    by michaelhood ( 667393 ) on Wednesday January 19, 2005 @08:21AM (#11407028)
    One of the most exciting features of 8.0 is plperl, their Perl-based server side language, allowing for triggers and persistent storage [postgresql.org]. On another note, I wish MySQL would catch up to PgSQL. Even if you don't like MySQL, the competition keeps them innovating. If PgSQL is light years ahead, what's pushing them?
    • What's pushing PostgreSQL to keep innovating? The competition obviously. Oracle.
    • Oracle and MS-SQL are pushing them...

      That's what the point-in-time recovery and tablespaces are about.

    • Re:plperl (Score:4, Insightful)

      by StrawberryFrog ( 67065 ) on Wednesday January 19, 2005 @08:30AM (#11407095) Homepage Journal
      One of the most exciting features of 8.0 is plperl, their Perl-based server side language

      I'm not convinced. SQL is supposed to a standard, so you can move from one database server to another with not much effort. This is a big step away from that. Much like the features you'd find in Oracle or MS SQL.
      • If the SQL standard weren't woefully inadequate, inconsistent, unclear, and primitive, then maybe we wouldn't have people fixing it. Open standards are great, but when the standard sucks it's not real usefull. It's not a big step away from database independence because there's never been any to begin with.

      • Re:plperl (Score:5, Informative)

        by AhBeeDoi ( 686955 ) on Wednesday January 19, 2005 @09:00AM (#11407317)
        One of the most exciting features of 8.0 is plperl, their Perl-based server side language

        I'm not convinced. SQL is supposed to a standard, so you can move from one database server to another with not much effort. This is a big step away from that. Much like the features you'd find in Oracle or MS SQL.

        Hardly.

        First of all, SQL is supposed to be a sub-language used by other languages to access an RDMS. PL/Perl is a server side procedural language which allows you to write PgSQL functions in Perl. How standard is PL/SQL for Oracle? It's proprietary. In fact, Oracle and MS SQL are not entirely compliant with SQL 92 as they have their own extensions.

  • Magical upgrade needed (Score:5, Insightful)

    by inflex ( 123318 ) on Wednesday January 19, 2005 @08:21AM (#11407034) Homepage Journal
    Now all I need is a magical installer which will convert my existing 7.2.4 systems to 8.x without missing a single beat or disrupting the existing programs written against the old libs.... I'm serious!

    Converting 200Gb of data is not something you want to try do without downtime.

    All that said, it's good to see PostgreSQL always improving. Good one guys.

    • Re:Magical upgrade needed (Score:3, Informative)

      by Dom2 ( 838 )
      You need to look at setting up a second system and running slony-I [slony.info].

      -Dom

    • Now all I need is a magical installer which will convert my existing 7.2.4 systems to 8.x without missing a single beat or disrupting the existing programs written against the old libs.... I'm serious!

      Even though I can feel your pain I' d like to ask a question: Do you know of any database (free or commercial) that supports such a feature (auto)magically?

      See, I thought not.

      Migrating a data server to a new major release is a pain by definiton and while you might be able to trick the users into believing

    • For smaller databases (the one I'm working with is in the 200MB range, so much smaller than what you're talking about), I haven't noticed any bumps when testing conversion to 8.0 (at least the betas). Data/schema, queries, etc. all were loaded in just fine, and apps so far have behaved as though nothing has changed. That all said, I won't begin to roll this into production until Summer due to app testing.

      I am curious about the query optimizer and understanding what sort of improvements may have been mad

  • .. but the key issue on Windows is:

    Does is have a native Microsoft SQL Server import feature?

    The thing is, getting a plain SQL dump out of MS SQL Server isn't possible with version 2000, haven't checked since then though. I'm sure this is a deliberate thing tough...
    • PostgreSQL has had an ODBC driver for quite some time. You could use that in conjunction with SQL Servers DTS tools to copy data from a SQL Server DB to Pg. There might be some pain involved, particular with indices and constraints, but it shouldn't be too awful.
    • Intelligent Converters [convert-in.com] makes conversion tools to dump a variety of databases to other formats.

      Off the top of my head snagging their MS SQL to MySQL converter might do the trick for you?

      Hope that helps a little...
    • If you want to migrate away from SQL-Server then you have MS DTS (at least for the time being).

      DTS can pump your MS-SQL-database into postgresql with little problem i'd expect. Now getting the logic (triggers, functions) transferred is a whole other question.

      Siggy.
    • The thing is, getting a plain SQL dump out of MS SQL Server isn't possible with version 2000, haven't checked since then though. I'm sure this is a deliberate thing tough...

      It can be done, just isn't as obvious as people would like.

      Secondly Version 2000 is the latest version of MS SQL Server.
  • Can anyone explain to me how it is "Object Relational" as opposed to just straight "Relational" or is this just another case of /. bullshit?

    • Re:Erm? (Score:5, Informative)

      by Erik Hensema ( 12898 ) on Wednesday January 19, 2005 @08:34AM (#11407131) Homepage
      In PostgresQL you can make a table inherit parts of another table. This is what's meant by object relational. Example (from the docs [postgresql.org]):
      CREATE TABLE cities (
      name text,
      population float,
      altitude int -- (in ft)
      );

      CREATE TABLE capitals (
      state char(2)
      ) INHERITS (cities);
      • That's not really all that object relational, it's kind of like early iterations of c++ which were just preprocessor macro's. To see a true object oriented DB that still allows standard SQL access see Intersystems CACHÉ [intersystems.com]. Everything is an object and can be manipulated as such, it's a truely different way of looking at SQL.

    • Re:Erm? (Score:4, Informative)

      by Delirium Tremens ( 214596 ) on Wednesday January 19, 2005 @08:41AM (#11407192) Journal
      From http://www.postgresql.org/docs/7.3/interactive/use r-preface.html [postgresql.org]:

      PostgreSQL offers substantial additional power by incorporating the following additional concepts in such a way that users can easily extend the system:

      • inheritance
      • data types
      • functions

      Other features provide additional power and flexibility:

      • constraints
      • triggers
      • rules
      • transactional integrity

      These features put PostgreSQL into the category of databases referred to as object-relational. Note that this is distinct from those referred to as object-oriented, which in general are not as well suited to supporting traditional relational database languages. So, although PostgreSQL has some object-oriented features, it is firmly in the relational database world.

    • Re:Erm? (Score:3, Informative)

      by quigonn ( 80360 )
      The difference is explained in this wonderful Wikipedia article on object-relational databases [wikipedia.org].
  • Thanks to the postgresql team for providing this great product. I've been using postgresql for a couple of years now, and the release candidates of version 8 for the last month or so. (It's good to finally have a windows port!)

    Apart from the initial learning curve, I have exactly zero complaints with postgres - it always does exactly what it says it will. I have no qualms trusting vital data to it. Combined with the excellent pqxx [pqxx.tk] c++ bindings library you can build robust applications with very tightl

  • Web Server Difficulties (Score:5, Informative)

    by Reality_X ( 23422 ) on Wednesday January 19, 2005 @08:24AM (#11407053) Homepage
    Hi Folks,

    Please take it easy on 'wwwmaster'.

    'www' fell over a couple of hours ago, and a couple of mirrors are coming online to round-robin the address.

    Can someone please change the the first link ("PostgreSQL project") in the story to point to 'www'?

    Thanks.

  • I've been waiting for this (Score:5, Informative)

    by wackysootroom ( 243310 ) on Wednesday January 19, 2005 @08:24AM (#11407055) Homepage
    Goodbye Oracle, hello PostgreSQL. Now I can have a mostly SQL92 compliant database with ACID, transactions and now PITR and tablespaces that I can use on the server and on a win32 desktop.

    For those of you wanting a great frontend, try PGAdmin3 [pgadmin.org]. It works on Win32 and Linux.

  • Vital Data (Score:3, Funny)

    by _Hellfire_ ( 170113 ) on Wednesday January 19, 2005 @08:31AM (#11407100)
    I wrote a PHP program that tracks my gf's and I's finances with a Postgresql database backend.

    It's up to about 600 records now and although I wouldn't say it's mission critical it certainly is vital.

    I have to say I'm extremely impressed with pgsql. It's easy to use and consistent in what it does. I have no complaints whatsoever. What I like most (although I'm not sure this is exclusive to pgsql) is the fact that I can at any time get a plain text dump of everything in the database in a format that makes sense. If the worst comes to the absolute worst, I can always mess around with awk and write a script which can convert a pg dump to another format. It gives me piece of mind that my data can always be read.

    I've heard that ms sql users are not so fortunate - ie no plain text dumps. Correct me if I'm wrong.

    Kudos to the pgsql team for such a fine product and keep up the good work!

    • SQL Server may have some missing functionality, but plain text dumps work perfectly well (albeit you might need to do them one table at a time). They're also incredibly simple to do.

      Most of Microsoft's products may be crap, but SQL Server does seem to be an exception to the rule. All in all it works pretty well in my experience. However 9/10ths of SQL Server deployments would probably be just as well off with PostgreSQL. :-)

      • You're probably referring to BCP or a similar function, which dumps a single table of data at a time to a text file in a specified format. What the original poster is probably referring to is the ability to dump a database into an SQL script that, when run, completely re-creates the schema, data and other features of the database. It's a very simple backup/restore mechanism and makes various conversions and other operations much easier.

  • Everyone keeps saying PostgreSQL is better than mySQL, but where does it stand in comparison to Oracle? Anyone know?

    • Re:How does it compare to Oracle? (Score:5, Informative)

      by nemesisj ( 305482 ) on Wednesday January 19, 2005 @08:50AM (#11407246) Homepage
      It's got a long way to go as far as enterprise features.

      There is no clustering support in PostgreSQL (and I mean real clustering, not some Java hack where transactions are shipped off to two separate DB servers, both of which don't know they're part of a cluster). This is pretty much a show stopper as far as using PostgreSQL in the company I work for, as high availability is a large concern, and any downtime would be serious.

      In previous versions of PostgreSQL, the pg_dump and pg_restore tools were not very good - dumps that included tables or views often would fail on reimport because PostgreSQL wouldn't know the order in which to import everything. You also had to pass in a number of options on the command line just to get a dump that made sense, and large object support was kind of clunky.

      That said, I still use PostgreSQL for many many projects and have used PostgreSQL for many years. It's a great product, but it isn't near Oracle in terms of enterprise level features.
    • I'll preface this with stating that I generally stick to ANSI SQL 92 and limit my use of database features to that.

      From that perspective, PostgreSQL performs within 5% either way of Oracle for me in pretty much all cases, save one:

      PostgreSQL performs much worse if I didn't create an index I needed. Oracle will automatically index based on queries that are run - this can yield HUGELY better performance if you screw up and don't create your physical model to support how you use the database in your applica
    • Oracle is bigger, they have lots of enterprise features and apps, and trained people to support you, plus a big company behind.
      It must be the sane choice for big corps. In my workplace, we develop for oracle, and postgres, and we are very pleased with both performances.
      Programming for postgres is nicer, because they are more conformant to standars (the version of ORacle we are using doesn't have "LIMIT" and "OFFSET", for example). Administering it is easier, backups/restores are easier, mirroring is easier.

    • Re:How does it compare to Oracle? (Score:5, Informative)

      by kpharmer ( 452893 ) * on Wednesday January 19, 2005 @10:05AM (#11407995)
      Postgresql is ready to take on the smaller, non-critical databases that oracle used to get. This is significant proportion of the databases out there, and will take revenue away from oracle. (Mysql will actually probably take more revenue away, but it has too many quality problems and functionality gaps to really deserve to.)

      But there are many other, more demanding databases that postgresql isn't yet ready for. Oracle, DB2, and even SQL Server 2005 all have very mature & solid: optimizers, replication, partitioning solutions, parallelism, failover/clustering support, etc.

      Here are two examples:

      Using db2 for example, you can create a view which is automatically populated by the database like a table (MQT). Then any queries against the base tables that could be sped up by hitting this view will be rewritten by the engine to hit the view. Now, this might seem like needless fluff if you're just writing a hobby php app. But if you need to implement a commercial app like SAP with its 6,000 tables - and you have performance issues - you can make adjustments in the database layer this way. Also, if you're provoding adhoc reporting for hundreds of users on a terrabyte of data - this technique can provide *dramatic* performance benefits.

      Another example is partitioning. Back to db2 (which I work with the most): you can spread a database across a dozen separate servers using a hashkey. Now, every query will have all dozen servers working independently on its own fraction of the data. On each of those servers, you can then partition again, this time using ranges or values (MDC) - so that data that doesn't apply to a query will be skipped in tablescans of that table. Using these techniques you can get sub-second response to *adhoc* queries against a terrabyte of data - without indexes (notoriously unreliable here).

      Lots more examples where the above came from. Sure, you will pay real money for licensing, hardware, and labor to implement these. Then again, the two above features actually save you in hardware costs. Additionally, some problems are big enough that they can easily justify the cost of licensing a product like this. I've seen these techniques used to save companies million, even hundreds of millions of dollars.
  • Congratulations to all developers and beta testers who made PostgreSQL 8.0 possible! Those who download via BitTorrent please keep your downloaders open for as long as you can, so that they can seed the torrent to others.
  • I have heard great things about PostgreSQL, and a buddy of mine recently told me that this release was pending.

    One thing I'm not keen on though is tarball distributions. I don't want to have to compile the thing, I just want to grab a package for my platform and install it (just like I do with MySQL).

    My favoured platform though is Mac OS X. There's a whole list of platforms in the FAQ, but Mac isn't amongst them.

    So my questions are is Mac OS X supported, and if so are there any plans to make a Mac OS X
    • To answer the first part of my question, I just found the Supported Platforms part of the manual, and sure enough Mac OS X is there.

      The question remains though - are there plans for a Mac OS X installer package?

    • Re:Mac OS X support? (Score:2, Informative)

      by DrZZ ( 138100 )
      This guy [entropy.ch] usually isn't too far behind in creating .pkg for the stable PostgreSQL releases. I have run it on OS X for a number of years and I have been very happy with it.
    • PostgreSQL 7.4 for the mostpart* works fine on Mac OS X. In terms of installation, I do it with fink [sourceforge.net]. I'm not sure when a PostgreSQL 8.0 source package will be available for fink, but hopefully not too long.

      *If you want to support more connections or more shared memory per connection, you need to set your sysv shm settings in /etc/rc something similar to FreeBSD 5's defaults and then reboot. They are set once on boot and cannot be changed with sysctl after they are first set.

      Here's what I'm currently u

    • Re:Mac OS X support? (Score:4, Informative)

      by SuperBanana ( 662181 ) on Wednesday January 19, 2005 @09:25AM (#11407535)
      My favoured platform though is Mac OS X. There's a whole list of platforms in the FAQ, but Mac isn't amongst them. So my questions are is Mac OS X supported, and if so are there any plans to make a Mac OS X .pkg installation file?

      7.x compiles right out of the box- in fact, Apple's Remote Desktop system actually installs and uses PostgreSQL for all its data storage (client system data and whatnot; ARD can collect a lot of per-system data). Very slick.

  • Java Stored Procedures? (Score:2, Interesting)

    by SQLz ( 564901 )
    What happened to the big news about the Java stored prodcedures? Wasn't there some news not to long ago about adding support for this in Postgres? To me that would been a more welcome feature than the rest of the stuff.

  • Typical (Score:2, Interesting)

    by Anonymous Coward
    I hate it when MySQL fanboys jump into threads like this only to show their ignorance of relational algebra and predicate calculus saying that no one should ever bother with PostgreSQL and ACID-compliance, because MySQL is somehow a "better tool for the job" in the "real world". We already have comments saying that, so people, please read this first: [1] [wikipedia.org] [2] [wikipedia.org] [3] [wikipedia.org] [4] [wikipedia.org] [5] [wikipedia.org] [6] [wikipedia.org] [7] [sql-info.de] before you post yet another misleading plug for your favorite toy. Thank you. A real relational database is more than just a data s

  • Looking good! (Score:2, Interesting)

    by unixMafiA ( 851276 )
    Good to see they're back in the race, coming up with something serious - and Thanks, Oracle! for being a tough competitor providing a solid standard to keep up with. PostgreSQL is, in my opinion, the most presentable of not-too-much-$ database solutions, I sure like to present clients with a new version of P rather than the crummy image of MySQL (by no means an inferior product but not profiled in the same, serious way). Ah and let's see how it runs on Mac OS/X - Darwin, too. Maybe my little 12" will beco

  • Two more features... (Score:5, Interesting)

    by teqo ( 602844 ) on Wednesday January 19, 2005 @08:58AM (#11407308) Journal

    ... and it would really really rock!

    • Hot tablespace-based backups, combined with write-ahead log backups, as seen in Oracle since version 8 (or even 7?) This is an extremely nice feature when you have large databases and no chance for regular scheduled downtime and still want backups, both complete and incremental ones. Compared to the export-based feature of PostgreSQL, it would put way less load onto the server, because tablespace-based and WAL-based backup bypasses the SQL engine, so it is copying a (for example) 200 GB files vs. 200 GB query-based export
    • Better and more integrated replication. There are a number of independent projects that want to create replication add-ons, like pgreplication [postgresql.org] and the older, more academic Postgres-R, but that's not really production quality so far. According to some consultant that is working tightly with PostgreSQL and the developers, they are working on it, but he was really hedging when asked about advanced features and was theorizing how practically impossible and/or expensive multi-master realtime replication would be... An optional feature for many users, granted, but still something you might want for scaling beyond certain limits.

    Said that, PostgreSQL is a really great thing, and being FOSS, I could of course always go ahead and add the named features... .)

    • Check out the new Slony replication engine:

      http://www.slony.info/ [slony.info]

      It is probably the best master->slave data replication engine for PostgreSQL at the moment. It is free and developed by one of the core developers.

    • There are a number of independent projects that want to create replication add-ons, like pgreplication [postgresql.org] and the older, more academic Postgres-R, but that's not really production quality so far.

      Well, the gig was up after everyone figured out that Postgres-R was just Postgres with a big muffler and wing.

    • Re:Two more features... (Score:5, Informative)

      by JohanV ( 536228 ) on Wednesday January 19, 2005 @09:23AM (#11407516) Homepage
      Most of your first request is already implemented in PostgreSQL 8. You can combine a hot backup of the files on the filesystem with the WAL-archiver to have the backup feature you want. It is not per tablespace (yet) so you have to backup your entire database.

      For the second request, keep a close eye on the mailinglists. Affilias has hired a core developer to make it happen.
      The first stage, master-slave replication, has been released in the form of Slony-1. Yes, it is an add on. No, it is not integrated. But you can add Slony-1 to a running system and add slaves without ever taking the master down, and it is backwards compatible so you can even use it to upgrade running 7.3.x installations to PostgreSQL 8.
      The second stage, Slony-2, will be a full multi-master replication solution. (I read something about a 'kickoff' meeting today hosted by Affilias.) The goal is to be able to take a single, out of the box installation of PostgreSQL, plug Slony-1 into it, replicate the database to another box and when that box has caught up switch to full multi-master mode under Slony-2.
      The code won't fall out of the sky tomorrow, but people are working on it.
  • I've done a little bit of work with different DBs (Oracle 9i/10g, db2, Ms SQL 2000, MySQL, and some will laugh, Access) ... and as far as I know, they're not OOP DBs. If they're not, then whats the advantage of an OOP Db compared to everyone else?
  • I've been running 8.0 RC1 for a while because of a few features I missed...Bare in mind my database projects are for fun so my featuresets may be considered low profile :)

    Especially the ability to ALTER TABLE "x" RENAME TO "Y"; (!)

    Yes mySQL has been able to do this for a while, I got sick of exporting out the data and re-importing, but now we dont have too!

    It might be also a good time to mention that EMS have released Lite versions of their product that are free. (pgsql, mysql, mssql and interbase)

  • It's easier to install and admin than mysql (Score:5, Insightful)

    by hqm ( 49964 ) on Wednesday January 19, 2005 @09:13AM (#11407429)
    The dirty secret is that Postgres is actually easier to install and administer than mysql. I don't want to get into a religious argument with mysql users but ... oh hell yes I do.

    I have worked with self-educated programmers who did not know how to do simple table joins or even modestly complex SQL queries or transactions, because they had learned what they thought was SQL by using Mysql. There is a whole generation of developers who now think that transactions aren't really necessary in a database application.

    Postgres is really an Oracle killer at this point, and I know, having used Oracle. There is quite simply no reason to use any other relational database at this point, especially to back a live web site.
    • It's just a shame that PHP doesn't include the same level of support for PostgreSQL as they do for MySQL. I like PostgreSQL very much, but whenever I look into the capabilities of a client's hosting facility, they always have MySQL and never PostgreSQL.

      I don't know that PostgreSQL is an Oracle killer yet. Maybe for a single server/single database solution. But for mission-critical enterprise apps that need 100% uptime (4 nines anyway), you have to have active-active databases, backups while the app is r
    • Postgres is really an Oracle killer at this point, and I know, having used Oracle. There is quite simply no reason to use any other relational database at this point, especially to back a live web site.

      I'd be careful with statements like this. Postgres competes well with Oracle for a variety of applications. However, Oracle still has some VLDB and high availability features that aren't yet available for Postgres. Also, for a lot of folks, what matters isn't the database, but stuff like the availability of

  • awesome (Score:2, Insightful)

    by idlake ( 850372 )
    Some people say that providing open source applications on Windows helps proprietary software and the Windows monopoly, but I don't think so. Software like Apache, PHP, PostgreSQL on the server, and Mozilla Firefox, Thunderbird, and OpenOffice on the client lets Windows users gradually move over to open source applications. I think that's a much better way of getting people to switch than to ask them to do an all-or-nothing switch.

  • And my favorite features are (Score:3, Informative)

    by plopez ( 54068 ) on Wednesday January 19, 2005 @10:12AM (#11408083) Journal
    playing around with the beta and RCs several items impressed me:

    1) GIS support. Very important for what I do. This is probably due to how closely they work with GRASS (which I haven't used yet).

    2) Ability to define and bind operators. Very flexible.

    3) Much more relationally compliant while also supporting OOP.

    4) PGAdmin II is very handy. A few rough points but now there is no excuse for those afraid of a command line.

    It just gets better every release. I am currently porting a MMSQL database over and so far so good.

  • the Oracle comparison (Score:5, Insightful)

    by justins ( 80659 ) on Wednesday January 19, 2005 @10:16AM (#11408127) Homepage Journal
    Oracle will always have more features than any other given database product you care to compare it to, and there will always be those who find some of those Oracle-unique features irresistable. That's what it means to be the central product of a very large, wealthy and reasonably effective software company.

    It is not a very meaningful comparison, though. Oracle also has a lot of stuff that nobody would be happy to see tacked on to PostgreSQL. Oracle is notoriously difficult to administer. The download of Oracle's database product is probably 500 times larger than PostgreSQL. (and 20 times larger than Oracle itself was just a few versions ago, which is interesting, and yes I'm guesstimating but still...)

    In short, PostgreSQL doesn't need all the features of the Oracle db to be successful, as *nix and NT did not need all the features of a mainframe OS to be successful.

  • Replication (Score:3, Interesting)

    by dmadole ( 528015 ) on Wednesday January 19, 2005 @11:09AM (#11408760)
    If only a simple and integrated replication mechanism would appear, I would consider switching back to PostgreSQL from MySQL.

    I miss PostgreSQL, but too many things are made easier by replication.

  • Change Column Types - finally (Score:3, Informative)

    by EugeneK ( 50783 ) on Wednesday January 19, 2005 @12:39PM (#11409779) Homepage Journal
    from the release notes : [postgresql.org]

    Change Column Types


    A column's data type can now be changed with ALTER TABLE.



    Before, to do this, you had to create a temporary table with the changed column type, copy all the data over to it, and then rename the temp table as the old table.

    Thank you Postgres team! Now, if we can rename a column, that would be a nice bonus.

  • It looks like a great year for PostgreSQL (Score:4, Informative)

    by smchris ( 464899 ) on Wednesday January 19, 2005 @03:44PM (#11412242)

    Oxford University announced a while back that they will be scrapping most of their proprietary DBs for PostgreSQL over the course of '05:

    http://news.zdnet.co.uk/software/applications/0% 2C 39020384%2C39173013%2C00.htm

Slashdot Top Deals

Genius is ten percent inspiration and fifty percent capital gains.

Close