PostgreSQL 8.1 Available 261
atani writes "PostgreSQL 8.1 has been posted, though not officially announced as of this moment. This release includes two-phased commits, improved SMP and overall performance, a new role system replaces the older user/group, autovacuum is now within the backend rather than a separate contrib module, and various improvements, performance enhancements, and bugfixes. " You can also read the developer notes for the popular database. One thing is clear- with the newest Postresql and MySql, you have much to choose from.
Congratulations to the PostgreSQL Team! (Score:5, Informative)
nevermind - I'm just hard of reading this morning (Score:5, Informative)
http://www.postgresql.org/ftp/binary/v8.1.0/win32
Clicking the '8.1' link on the 'new releases' section on the front page takes you to the 'source' files only. That was the problem I had!
PostgreSQL 8.1 Release Notes (Score:2, Informative)
Re:Too big? (Score:3, Informative)
RC1/RC2 (Score:5, Informative)
My only beef with PgSQL has been there since before the 7's. There is still no way to not show the list of databases to users who have no right or access to those databases. Why should userA with rights to databaseA see that there is a databaseB or databaseC? This really seems like a simple feature, yet nobody will accept it into the release.
-M
Re:Just installed Win32 version (Score:4, Informative)
1.3 was the development series for 1.4, the new stable release as of yesterday. There are a heap of new features over 1.2 (which shipped with PostgreSQL 8.0) - see http://www.postgresql.org/about/news.419 [postgresql.org] for details.
Indeed, SQLite is an excellent choice. (Score:3, Informative)
I recently set up a small site for a local business. Basically they wanted to be able to upload and edit product information, in addition to other such tasks.
Using a combination of OpenBSD, Python and SQLite, it was very easy to get a system set up for them. Best of all for them, it turned an otherwise obsolete computer into a working server.
Making backups of the database is quite easy too, considering a single file (or three, in their case) can be copied and archived.
While I did not benchmark the system, there is a good chance that the SQLite solution is far more efficient than that of MySQL, and most likely moreso than PostgreSQL. But then again, SQLite doesn't offer many of the more advanced features of larger database systems. If you don't need those features, you might as well trade them off for faster execution and reduced RAM consumption.
Re:Much to choose from? (Score:2, Informative)
http://firebird.sourceforge.net/ [sourceforge.net]
So that's three open source SQL database systems; MySQL, Firebird, and PostgreSQL.
Here is a comparision between aviable open source relational databases.
http://www.geocities.com/mailsoftware42/db/ [geocities.com] (it's a bit dated)
Here is the migration guide for MS-SQL to Firebird, in which I doubt your interested.
http://firebird.sourceforge.net/manual/migration-
Re:Congratulations to the PostgreSQL Team! (Score:3, Informative)
I'm using PostgreSQL for many things (custom app, Drupal, Apache logging, back-office) and it's great- this new release adds lots of useful bits.
Probably the most useful new feature for those who use it to run mini web sites will be the MAX() and MIN() functions now using indices- this has been a point of contention for some time.
XA will be useful for bigger stuff.
I'm also really pleased auto-vacuum is now part of the DB.
Now, Gentoo- let's get cracking!
Re:Too big? (Score:5, Informative)
sqlite is good. But it does require setup.
You still need to use CREATE TABLE, INSERTs and the like.
You need to connect to the thing in your code, with the same attendent problems as other connection strings. sqlite has a poor jdbc element, if java's your gig.
There are a couple of versions of sqlite. Be aware of this. There is sqlite and sqlite3. Note the jump there from 1 straight to 3. You'll need different DBD drivers there in Perl.
Also, there is no multiple write capability, so you have to synchrinise on this yourself. Who do you trust to write code for ACID commits? Yourself or say, the Postgres team?.
Where sqlite rocks is where the data is entirely read only, which eats out Mysqls lunch.
Also remember that sqlite and Postgres are not mutually exclusive.
In my sqlite setup, I deploy a sqlite database as a file for the fast read only webservers. Where is this data drawn from? My Postgres database with perl to generate the file. So you can get the UPDATE integrity of Postgres with the blazing speed and compactness of sqlite. This works where your application has discreet state where everything is consistent and you can batch update your sqlite.
Re:at last "SELECT ... FOR UPDATE NOWAIT" (Score:5, Informative)
Postgres docs actually have a chapter on this: Concurrency Control [postgresql.org]. Like most high-end databases Postgres can handle this situation in different ways, depending on how it is set up...
A database should never tell you that the data is not avalible. It should always give you the best version of the data it has.
Re:Congratulations to the PostgreSQL Team! (Score:2, Informative)
Re:RC1/RC2 (Score:2, Informative)
Chris
Re:Congratulations to the PostgreSQL Team! (Score:1, Informative)
For real multi-master clustering, I think there are commercial options availabile; but yes, it'll be nice when it's included.
Re:Embedded version? (Score:2, Informative)
Re:RC1/RC2 (Score:1, Informative)
If I understand the talk on the postgresql archvies, you have the SQL Standard to thank for that when they defined how the INFORMATION_SCHEMA works. For almost everything the PostgreSQL team defers to the standard for questions of what will be accepted or not. If you can get the SQL-201X standard to support alternative definitions for the INFORMATION_SCHEMA views, I'm sure the postgresql community will accept your changes.
Re:Much to choose from? (Score:3, Informative)
http://firebird.sourceforge.net/ [sourceforge.net]
Firebird is kind of a bastard step brother of PostgreSQL, since they share the common Ingres heritage... it's got foreign keys, triggers and stored procedures for far longer than MySQL, for instance. It's Borland Delphi's Interbase code gone open-source. However, it's a relative newcomer and most who care are Delphi developers.
Its license is neither BSD nor GNU, which i think also makes up for some caution people have against it.
Re:Too big? (Score:1, Informative)
You don't _have_ to make your own concurrent-write implementation. SQLite is already ACID compliant; you'll just have to live with blocking writes, which is perfectly fine for most blogs/home pages and that kind of stuff.
And for heavy load you'd always pick postgres over mysql. Bad news for poor mysql ab..
Postgresql has multimaster replication. (Score:3, Informative)
because only one was actually late (Score:3, Informative)
Re:The inherit bug? (Score:3, Informative)
"do features like that really get used in the real world?"
I'm with you on that question. These, well at least Postgres, are Relational Database Management Systems. They're not object databases...and IMHO, it isn't worth messing with them in that fashion. Set it up as a relational database, and let your middle tier deal with the object to relational conversions as needed if you use object oriented programming. If not...then, use an object oriented database...if you can find one that is up to par...which I've not run across yet.
Re:Article text for your convenience (Score:2, Informative)
Wooww... This looks more and more like Oracle version 7 released in the early 90's. Oracle has had 2PC (2 Phase Commit) for about 15 years now. Nice catchup
So has Firebird... See: http://firebird.sourceforge.net/index.php?op=guide &id=ib6_overview [sourceforge.net].
Re:The inherit bug? (Score:5, Informative)
In fact, the PostgreSQL team has been musing for years on whether to drop object inheritance completely, because it just muddies the waters of relational design, and causes implementation details to affect logical design.
But interestingly, table inheritance has become a solution to a lower-level implementation problem: "horizontal" table partitioning. Using inheritance together with tablespaces (available since 8.0), you can break a large table into multiple subtables, each on a different physical storage device, then make a table that inherits from them all to present unified querying to the whole range of data. This is a *big* advantage for very large databases.
Re:Upgrading between 8.0 and 8.1 (Score:2, Informative)
From the Release notes:
E.1.2. Migration to version 8.1
A dump/restore using pg_dump is required for those wishing to migrate data from any previous release.
Autovacuum Integration Details (Score:2, Informative)
Re: there are more tools available for MS SQL (Score:4, Informative)
I take issue with the number of tools.
Postgresql has a great variety of tools, both OSS and commercial that work great. I've been working on an updated list of all the tools. Here are a few of the most popular admin tools:
PGadminIII
http://www.sqlmanager.net/products/postgresql/man
DBvisualizer
http://www.minq.se/products/dbvis/ [www.minq.se] [www.minq.se]
EMS Postgresql Manager
http://www.sqlmanager.net/products/postgresql/man
PHPpgadmin
http://sourceforge.net/projects/phppgadmin [sourceforge.net] [sourceforge.net]
Sybase Power Designer
http://www.sybase.com/products/enterprisemodeling
ERWIN data modeller
http://www3.ca.com/Solutions/Product.asp?ID=260 [ca.com] [ca.com]
CASE Studio 2
http://www.casestudio.com/enu/default.aspx [casestudio.com] [casestudio.com]
Postgresql has a vibrant tool community. If you want more info on Postgresql tools see
http://techdocs.postgresql.org/v2/Guides/PostgreS
Zero downtime Upgrading between 8.0 and 8.1 (Score:5, Informative)
MySQL is more popular (Score:4, Informative)
- MySQL is very simple to learn
- MySQL is supported by virtually all webhosting companies, whereas you have to look pretty hard to find a host that provides PostgreSQL.
- MySQL historically has performed much better than Postgres for both reads and writes. Nowadays, MySQL only outperforms Postgres on reads. So for a website, where most database calls are SELECTs, MySQL will often (but not always) give better performance.
- Nearly all blog, forum, photo gallery, etc. packages support MySQL. They do not all support Postgres, but some do.
Now, of course, Postgres has many advantages over MySQL, but for a small hobby website, you should just use whatever you know best and your host supports and your software supports. The answer is normally MySQL for your type of website.Postgres is generally better than MySQL (though MySQL is closing the gap) in terms of SQL standards compliance and RDBMS-type features. If you're implementing a true DB app, you should definitely give PostgreSQL a second look. But for a web forum, stick with MySQL.
P.S. Did you go to Wisconsin?
Read the writeup, dipshit (Score:3, Informative)
When Debian Sarge was finally released, the slashdot writeup went so far to assert that "Hell has officially frozen over!". So the sword cuts both ways.
Nice kneejerk reaction though, buddy.
thank you #postgresql IRC channel (Score:2, Informative)
Re:at last "SELECT ... FOR UPDATE NOWAIT" (Score:3, Informative)
If I understand your question, you're talking about data entry applications doing a "select ... for update" before making changes, waiting for the user to make them, then doing the update and commit. You want to know how applications avoided the "Modify record" page hang until another user was done prior to NOWAIT.
This scheme actually isn't that common. You don't want to block read-only operations on user input, and this would do that. Besides, in a web application, you don't even want to keep a database connection between hits. (You have no way of knowing if the user has closed the browser and isn't coming back. Keeping a connection open for an hour just in case is undesirable, to say the least.)
One way would be to hold a modification lock in an application table or in-memory on the application side. But the most common scheme is called "optimistic locking", which is sort of a misnomer. There's no lock. It works like this:
When you present the modification page to the user, do a select with all of the columns. Keep two copies - the user's modified version and the original. When making the changes, execute an UPDATE like this:
update table set col1 = newval1, col2 = newval2 where id = id and col1 = oldval1 and col2 = oldval2;
If this statement returns a row count of 1, you know that no other changes have been made in this time and you can safely go on. If it returns a row count of 0, the record is missing or changed. Send a "Sorry, another user beat you to it" page back to the user. If you want to get fancy, you can show a three-way diff sort of thing to make their changes anyway.
In the vast majority of cases, this is totally transparent. Only if two people happen to be updating the same columns of the same record in the same table at the same time do you ever get an error. It's almost as simple as completely ignoring concurrency, and it has much better behavior - the user's change being rejected while they still remember what it was, rather than someone's change being silently clobbered.
Re:Much to choose from? (Score:1, Informative)
Re:MySQL is more popular (Score:3, Informative)
That's certainly not true in the general case. There are many situations in which PostgreSQL will outperform MySQL on reads. Particularly if you need one of PostgreSQL's features to achieve reasonable performance.