Slashdot Mirror
PostgreSQL 8.0 Enters Beta
gavinroy writes "As announced in pgsql-announce, PostgreSQL 8.0 Beta is now available. New features include native win32 support, Point in Time Recovery, Tablespaces, and much more! here is the beta history if you want more information."
...and jump right to the beta announcement message.
The Army reading list
http://developer.postgresql.org/beta.php
the windows installer is at
http://pgfoundry.org/projects/pginstaller
http://bt.postgresql.org
http://bt.postgresql.org
:)
Join the torrent!
What part of "A well regulated militia" do you not understand?
it is the most advanced Open Source database there is. If anything pissed you off with MySQL chances are Postgresql will have a solution for you.
Die IT theme.
On the other hand, I've seen both Oracle and DB2 corrupt indexes and database table data in various circumstances (Usually the failing of a DBA in some capacity or other.) I'd be curious to see how the various databases stack up against each other without the hype that most of the parties that publish such studies usually bring to the table.
I'm trying to teach myself to set people on fire with my mind... Is it hot in here?
(MySQL|MSSQL|Oracle|DB2) is (cheaper|better|faster|ACID-compliant|'1337) and Postgres is (slower|buggier|missing features|has broken features|sucky)!!!
I want to delete my account but Slashdot doesn't allow it.
I think this was a major stumbling block for postgreSQL's adoption. I'd love to use it here at work for some small projects but unfortunately were getting more and more windows servers. PITR recovery is a must for any production database these days. Maybe there are some 3rd party packages but I don't think mysql supports this yet. This is great news and I hope it spurs a new round of adoption for pgsql!
"Thanks to the remote control I have the attention span of a gerbil."
Apologies to Ziff-Davis...
... to go from Beta -> Release, we need as many people out there to put it through her paces as possible, on as many platforms as possible. We urge anyone, and everyone, to download a copy and run her through her regression tests, and report any/all problems, and bugs, to
...
From: "Marc G. Fournier"
To: pgsql-announce ( at ) postgresql ( dot ) org
Subject: PostgreSQL 8.0.0 Officially Goes Beta
Date: Mon, 9 Aug 2004 21:36:52 -0300 (ADT)
After almost 9 months of development, the PostgreSQL Global Development Group is proud to announce that development on PostgreSQL 8.0.0 has now finished, and is ready for some serious testing.
For those wondering about the 8.0.0 designation on this release, there have been several *very* large features included in this release that we felt warranted the jump. As with all of our releases, we aim to have this one as rock solid as possible, but *at least* one of the features added to this release involved such changes that may warrant a bit extra testing post-release before deploying it in production.
Although the list of new features in 8.0.0 is extensive, with both SMB (Win32 Native Support) and Enterprise (Nested Transactions and Point in Time Recory) features being added, there is one thing that hasn't been included as part of the core distribution, and that is a Windows Installer, which can be found at:
http://pgfoundry.org/projects/pginstaller
For a complete list of changes/improvements since 7.4.0 was released, please see:
http://developer.postgresql.org/beta-history.txt
That said, and without further ado, Beta 1 is currently available for download on all mirrors:
http://www.postgresql.org/mirrors-ftp.html
And, thanks to David Fetter, the Beta is also available via BitTorrent at:
http://bt.postgresql.org
As with all releases, the success of this release falls in the your hands
pgsql-bugs ( at ) postgresql ( dot ) org
The more bugs we can find, and eliminate, during Beta, the more successful the Release will be...
On behalf of all of the developers, Happy Bug Hunting
d a v e
"Hmmm...upgrades."
I think Windows support is the only reason MySQL is so popular. PostgreSQL has always been ahead of MySQL in terms of everything but speed. But everybody is familiar with MySQL because, when you want to pick something up, you pick the one that will work with your system, and most people are on Windows.
Up until this point, you have had to install hundreds of MB of cygwin to get PostgreSQL to work on Windows. I think it's a little late to usurp MySQL's market share, especially as MySQL is now entrenched in the cheap web hosting market, but at least PostgreSQL might get the respect it deserves.
I guessed you missed OpenFTS, which has been out for a couple years now.
Doesn't even know about dBase III. That thing stopped development not 10 years ago, but 20 years ago. Maybe the guy was working so hard on dBase III that for the last 10 years of the project he didn't notice for a decade that all his fellow programmers weren't coming to work, his paychecks weren't arriving, Ashton-Tate was no longer in business, and a new company had moved into the office space that he was working in.
No weapon in the arsenals of the world is so formidable as the will and moral courage of free men.-Ronald Reagan
As far as I'm concerned, the only truly missing feature is distributed transactions. Are there any plans to add them any time soon?
___
If you think big enough, you'll never have to do it.
The cross-datatype comparison indexing is very important (ex. '1' = 1), as well as index usage on OR clauses. Both of these before would cause full table scans, which is very costly on VLDBs (Very Large DataBases).
The improvement to the VACUUM I/O processor is important for Postgre to be used on a multi-app server. The 'play nice' feature will allow one server to house the DB AND web servers (albeit at a performance hit to the DB processes).
Overall, a nice improvement.
I just wanted to say thanks to Fujitsu for helping pay for this
And thanks to Afilias (the guys who run the"what does a wheel-barrow... have... to do with DBs??"
Nothing. It's even an incorrect category. A database is the data you collect. PostgreSQL is a DataBase Management System (DBMS). They should rename the category to reflect this.
Thanks,
--
Matt
Back in the 6.x days postgresql had a well-deserved reputation for being, well, slow. That was back in the '90s, though.
In the case of postgres there are three digits in the version - a.b.c
If your upgrade increases either a or b then the on-disk structure of the database has changed, and as part of the upgrade you'll need to dump the database out to a backup file and restore it.
If the only change is in the final digit c then there's no on-disk change and you can upgrade just by upgrading the binaries. That tends to mean that final digit upgrades are bug fixes, and you should always do the upgrade.
So if you're running 7.4.1 you can easily, and definitely should, upgrade to 7.4.3. But upgrading to 8.0 is a marginally more time-consuming upgrade that you may not want to make on a production system unless you want the new features in 8.0
There's no 'technical' difference between a first digit change upgrade and a second digit upgrade. There's a difference in expectations though, and the version following 7.4.* has enough major new features to justify a major version jump to 8.0.0, with all the positives and negatives that jump implies. The upgrade path would have been identical had it been called 7.5.0 though.
PostgreSQL supports replication BUT replication is absolutely useless as a failover mechanism because it is asynchronous. That is, when you commit a transaction, you cannnot be sure if/when it gets propagated to the slaves. For true failover you need distributed transactions. Neither MySQL nor PostgreSQL support them, but curiously, Firebird does.
___
If you think big enough, you'll never have to do it.
Probably not of interest to you, but certainly worth mentioning is phpPgAdmin, a web based PostgreSQL administration tool. Works well for me.
It's supported 64-bit for as long as I can remember.
steve
Oh, you're not stuck, you're just unable to let go of the onion rings.
Try PGAdmin III.
You'll like this, actually...
If it can be cast directly (integer to numeric) it will do so implicitly.
So, integer to smallint will do part of the work -- but if it fails (comes across a number that won't fit in the smallint) then it will rollback -- nothing lost but a little time (most PostgreSQL commands are atomic and transactional).
However, you can use an expression to do the conversion if you have something stranger in mind -- it's essentially run as an UPDATE.
Excuse the crappy formatting.
BEGIN;
SAVEPOINT altertab;
ALTER TABLE tab
ALTER COLUMN text_col TYPE bool
EXPRESSION (CASE WHEN text_col = 'SOMETHING'
THEN TRUE
ELSE THEN FALSE
END);
ROLLBACK TO altertab;
ALTER TABLE tab
ALTER COLUMN text_col TYPE bool
EXPRESSION (CASE WHEN text_col IN ('SOMETHING', 'OR', 'ANOTHER')
THEN TRUE
ELSE THEN FALSE
END);
COMMIT;
Rod Taylor
Maybe it is because MySQL treats TEXT fields as binary since it has no Unicode support (at least, the current stable, non beta release).
But then again... a man's bug is another man's feature.
you might be thinking of ingres... recently open-sourced by ca.
Tablespaces are also a key feature. The nicest thing about tablespaces is: each schema can have its own tablespace. This makes maintenance much, much easier, allowing you to isolate the data for each of multiple applications or developers. You can also use it to isolate mission-critical data within the same schema, which in many cases can keep your app running, even if you lose a non-critical portion of your database.
Savepoints are nice, but I've never had to use them. And altering column data types is nifty, but not really useful in the real world.
Btw, does PostgreSQL have row-level locking yet?
Gentoo Sucks
PostgreSQL adheres to the standards more than MySQL, so you're using a language with broader industry adoption if you use PostgreSQL. Especially from someone who says, "If X then we'll use Oracle", you ought to know that PostgreSQL would make migration to/from Oracle easier.
It depends on which user you are talking about.
A database system has many users. Among them, database administrators and programmers. Programmers' goal is to hack applications as easily and quickly as possible. Database administrators' goal is to make sure the database keeps its integrity (i.e. the data stored there is correct)
I would say that PostgreSQL is more friendly towards database administrators and Mysql is more friendly towards programmers. And, no, you can't have a DBMS which makes both happy: there are opposing roles.
IMO what counts at the end of the day (I mean, from a business point of view) is that the organization as a whole can trust its data, and not if a hacker wrote 50 or 100 fewer lines of code.
Of course, if you are talking about a situation in wich the same person is the database administrator *and* the only programmer, well... I think MySQL is OK for your league.
To the second question: Nobody in the open source world.
Microsoft's on the way to doing it, and there was a small project that existed for a few months that allowed GNOME to access a database as a file system (It was very nasty; involved a kernel patch (a CORBA orb) that nobody was too happy about, so the project never took off.)
I've thought about the problem a few times. It requires the kernel pass information back to user space, unless the database was actually incorporated into kernel space (and that won't blow over well for a number of reasons). Passing the data back to user space requires a messaging system. The problem is, there are very few messaging systems out there designed specifically for kernelspace-userspace communication. CORBA was one developers answer; my answer would to be to ground up a protocol (because I feel that a network messaging solution, i.e. TCP/IP, can't be secured well enough in the long run).
Lastly, a daemon needs to exist to listen to the calls from the kernel and interpret them into SQL. This could be built into the kernel itself, but once again you have to question the security of the kernel building an SQL query that would go directly to the database server. Also, one of the better parts about this daemon would be metadata extraction; since the daemon is virtually transparent to both the user and the database server, the metadata can be completely ripped from the data and stored in a seperate table to allow for much faster, more optimised searches. EXIF Tags can be copied from JPEGs, ID3 tags copied from MP3s, etc.
Ideally, the daemon would be pluggable, allowing for anyone's metadata extension to be added after compiliation, but I believe that it's important to have a functional system before having a featureful system.
If you'd like to talk more about it, I'm really open to the idea of finally having an SQL-based file system. A relational database file system is the future; if we get there before Windows, we can add yet another example of the speed of open source development.
"Victory means exit strategy, and it's important for the President to explain to us what the exit strategy is." G.W.Bush
You really need to update your "known facts".
... except for MySQL's outstanding marketing.
Using a TPC-W style benchmark suite implemented with Apache, PHP4 and either MySQL 4.1.1 or PostgreSQL 7.4.2, I get more or less the same performance. Because of the transactional requirements and the update concurrency, all tables are InnoDB, of course. Based on that I cannot but contradict your claims about MySQL's scalability (and I am a PostgreSQL CORE developer). It keeps well up and is stable even under heavy load. Where the test uses a stored procedure in PostgreSQL, it must use a bunch of PHP code and separate query calls in the MySQL case, but that is exactly what developers do today and since the Apache server is part of the benchmarked system, this is as fair as possible.
That said, Apache+PHP+DB is the environment most people are talking about when they speak about simple to medium complex Web applications. With the scalability and performance being head to head, why would someone voluntarily miss stored procedures, views, triggers and all the other yet to be done for MySQL features? And while the (new in 4.1) subselect support makes it possible to get all of the TPC-W functionality implemented at all, to get it running fast enough in MySQL one has to rewrite some queries in a manner that I would call unmaintainable code. These complex features are not something where you can say "Transactions, checkmark". You have to look at how complete the implementation is and how well the query optimizer can deal with queries that use that feature.
So looking at the two right now, with the performance advantage gone, and the Win32 support knocking at the door, replication available and tons of well settled features in the HISTORY that are still on MySQL's ROADMAP, PostgreSQL is not just the better choice in some cases. It is ahead
Sincerely, Jan
It takes a real man to ride a scooter
..Mysql is more friendly towards programmers.
:)....MySQL is more friendly to the programmer who hasn't got a clue about databases maybe, if you know what a real DBMS can do it becomes a pain in the ass to work with.
:) I'd still take Postges over MySQL any day.
Are you kidding me? If you like to do everything in your app that _should_ have been handled by the DBMS then yeah, sure
you can't have a DBMS which makes both happy: there are opposing roles.
Well, I don't think I'd agree with that, but if anything I'd say MySQL is the easier one to administer. Postgres needs a lot more thought with regards to tuning and user management.
Especially user management in Postgres is, in my view, horrible and a lot better in MySQL. Well, can't have it all I guess
What a rotten party, have we run out of beer or something?
Mysql is still able to read and write ancient databases (the ISAM format was defined in 1986 and can still be read).
The biggest problem that I face with Postgres 7.4 is that its referential integrity's locks block INSERTS and UPDATES that should go through fine.
For example, set up these two tables:
CREATE TABLE car_type (
id serial primary key,
name varchar(20)
);
CREATE TABLE car (
id serial primary key,
car_type_id integer references car_type
);
Now, try having two different connections insert with the same foreign key value (this field does not have a unique constraint):
Connection1:
BEGIN; INSERT INTO car (car_type_id) values(1);
Connection2:
BEGIN; INSERT INTO car (car_type_id) values(1);
You will see that the second transaction is waiting for the first transaction to commit. That is just rediculous and is one reason that Postgres is still small time.
My experience has been that saying MySQL can't handle load is like saying it gets hot in Texas in the summer. :)
One of the points of Slony-I is to provide an answer to this very problem. Slony-I supports versions 7.3, 7.4, and 8.0, and may be used to support a short-outage upgrade path.
Suppose you have a 7.3 database, and want an 8.0 one. You set up replication between the 7.3 database and the new 8.0 one. It may take a couple of days for the new one to get up to date, but you don't have to shut the 7.3 one down.
Once the databases are more or less in sync, you do a MOVE SET to change the "master" to be the 8.0 database. Since they are nearly in sync, this should only take a few seconds. Presto! The 8.0 database is the "master," and you can switch over to it with whatever brief outage is needed to get your application to point to a new server.
If you're not part of the solution, you're part of the precipitate.
Actually, it's not the format of the data store so much as changes to the system catalog, which are put in place during initdb.
There has been a project for upgrading in place, but it just hasn't had enough man power thrown at it to be a viable solution.
Maybe it's time for you to volunteer?
--- It is not the things we do which we regret the most, but the things which we don't do.
I believe that native windows support for PostgreSQL is essential, not necesarily to deploy apps in that enviroment but to test and develop them. When I started using MySQL on my windows box, I had also looked into PostgreSQL. The lack of windows binaries for PostgreSQL made MySQL the default choice for me. On features alone PostgreSQL wins hand down. Also, in my experience, the faster performance of MySQL over PostgreSQL dissapears when I use InnoDB tables for transactional data processing. The doors to PostgreSQL have been open to many developers stuck in the windows world. Perhaps I will try PostgreSQL for my ASP.NET apps in addition to my trusty MySQL.
Cheers
Adolfo
If you're going to use it expensively, then why not just go with Oracle?
if you think this is bad, you should have seen my last sig
There's ltree in contrib/. It's pretty much the same thing, but with different syntax.
Dude, with "kill -9" it will not be long until you corrupt PostgreSQL, MySQL, etc. too.
There's no 'technical' difference between a first digit change upgrade and a second digit upgrade.
As I understood from reading the lists, there is a technical distinction. a.(b+1).c is supposed to be more stable than a.b.d, even though it may have some additional features.
8.0 added so many powerful features that the developers did not want to imply that it would be more stable than 7.4. 7.4 is pretty much rock-solid, and 8.0 might not achieve that reputation until 8.1 or so.
Social scientists are inspired by theories; scientists are humbled by facts.
You could also check out my database tool DBInspect. No SQL syntax coloring (yet), but it's free, supports many databases out of the box, and has some nice features I've not seen in other tools.
Try this one:
CREATE FUNCTION name(int) RETURNS SETOF test AS '
SELECT * FROM test WHERE id=$1;
' LANGUAGE SQL;
Then try using:
select * from name(1);
Should work pretty well.
IMO, it has a "very steep" learning curve because so many people have to unlearn all the bad habbits from MySQL
Read your next statement Sadly, select count(*) is still slow and don't imagine that changing any time soon. Is using count(*) a bad MySQL habbit? PgSQL is difficult because it has unusual problems, not expected from experience with Oracle/MSSQL/MySQQL.
As for aggregates, I know PgSQL functions can't use indexes by design. But addressing the most common cases of COUNT/MIN/MAX/SUM and maybe AVG might be possible. For example, MIN/MAX queries can be rewritten with ORDER/LIMIT 1. It's really unexpected for someone new to PgSQL (but with a lot of experince with other databases) that COUNT(*) on large tables is unuseable AND there is no work around.
Assuming you had it configured properly, to correctly reuse all of your pages, you may still needed to run vacuum more than once per day.
One quite capable DB admin worked on it full time for about two weeks, another one consulted. They tried everything imaginable. Still, running vacuum slowed selects by 5-10 times. That was not acceptable during the day, thus we could run it only at night. And that was not often enough.
Two experienced people spent two weeks and failed to configure it for sufficient performance. Yes, you can say they were not experienced enough. I can also say that the configuration was overly difficult.
I assume you mean as a non-dedicated server. Also, assuming you're running on Linux
I mean a dedicated server, as in nothing but PgSQL 7.4 on a FreeBSD 4.9. Say, the DB is 1GB, server has 2GB RAM. PgSQL should just suck everything in RAM and run read queries directly from there. Why can't it do it? Or maybe it can, but how to configure it in such a way is really non-obvious.