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?
You have a couple of full text indexing options with postgres, just go into the contrib directory and install one. Wow, that was tough.
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."
While it is true that the PostgreSQL project doesn't include full text searching there is a full text searching engine for PostgreSQL. The "problem" is that it is licensed under the GPL, not a BSD-style license, and so it is not included in the official distribution. Here's the link.
OpenFTS has been around for quite a while, and is used pretty heavily, so there really is very little reason to put up with MySQL's many shortcomings.
I guessed you missed OpenFTS, which has been out for a couple years now.
In short, yes and perhaps.
.org servers (Affilias).
Replication (master & multiple slaves) works great with Slony.
Fail over should not be done by the database, but by the operating system or an external monitoring system (Big Brother). RedHat Enterprise with the standard failover configuration works fine.
You just need to tell Slony that the new node has been elected as master. It's not as clean as it could be (no gui tools, etc.) but it is functional enough for the
Rod Taylor
"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 is a touch slower than Oracle in some special cases (single user on multi-cpu machine doing large or very complex queries) since Oracle can split the work up amongst CPUs.
.0 release) so long as the hardware is adequate (doesn't lie about writes hitting disk when they're really in cache).
With 50 to 100 users, both have similar performance for many workloads (you need to test your specific workload), but Pg may require a touch more tuning.
Pg is, however, quite a bit more reliable than Oracle as far as corruptions go (keeping in mind this is a
There are ways of purposfully crashing PostgreSQL as an authenticated user (particularly if you have root access and can write functions in C), but in standard operation it's quite reliable.
I cannot speak for DB2, but based on their Docs I think Pg is nearly as feature complete in most areas, and much more complete in many others.
Rod Taylor
Here's the ISOC's response to Oracle FUD.
In my 8 years as an Oracle DBA I don't think i've ever seen a corrupt index. Saying that, I don't even know how you could force a index to get corrupted so I don't think were seeing all the info here.
There is alot of hype in the database market as is there with any other area such as OS preference. A good DBA, like a good software developer will pick the database that fits the needs. Saying that, usually DBA's (like anyone else) are under certain constraints from management but heres my take:
For:
Mission Critical Apps and large DB's: Oracle or DB2
Mid-Range apps/Mid-sized DB's: The above + SqlServer, PostgreSQL.
Small: SqlServer, PostgreSQL. (+ Oracle and DB2 if you like to toss around the $$).
I'd feel comfortable using PostgreSQL for upto and including mid-sized DB's. Currently I wouldn't use mySQL for anything until they fix their Gotcha's.
If you really and I mean really have faith in your developers and they have reviewed these gotcha's i'd consider MySQL but unfortunately, as every DBA knows this isn't always possible. It only takes one bad apple to mess everything up.
Of course there are many other factors to choosing a database but the core comes down to your employees and licensing. If all your DBA's have extensive training on Oracle then it doesn't make much sense to retrain them to use another DB just because it maybe a better fit. Also, if you have a server license for Oracle it doesn't make much sense to buy another so you can use DB2.
At our shop we have a mix of Oracle and SqlServer. But we do installs for DB2, Redbrick, informix, everything and anything under the sun.
Anyways, thats my 2 bits.
"Thanks to the remote control I have the attention span of a gerbil."
Using postgresql 7.4.2:
That will print all the company names in my database.
And yes, I could add arguments to that:
Obviously this is a simplified example, but you get the idea. And I assume that this capability extends to other embedded language like perl or ruby.
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.
Looking with a narrow view at situations that need 100% guaranteed zero transaction loss on failover, you are right. Many businesses however can live with a little (few seconds) lag and the risk of losing the last couple of transactions, given that there is a mechanism to later analyze the failed server (after recovery) and find out what had been lost, to solve these cases manually or inform users/customers.
The true failover functionality you are talking about will be the goal of my follow-up project Slony-II, which will implement synchronous multi master replication for PostgreSQL. The design phase will start in about 3-5 months.
Sincely, Jan
It takes a real man to ride a scooter
There have been attempts at adding 2 phase commit which would work with Java applications -- these were deemed incomplete, unsafe or needing work.
It is likely work will continue on this feature, though don't expect a replication system to be based on this as there are a number of problems within the spec itself (failure modes that leave the system essentially out of order).
See the pgsql-hackers archives (Google Groups) for details.
Rod Taylor
You may want to look at backplane.
evil is as evil does
From my POV the MySQL -> PostgreSQL migration isn't so important as the Oracle -> PostgreSQL migration. Having been a part of the migration team porting the Red Hat CMS from Oracle to PostgreSQL I can say that even when we started with PG 7.2 it was more than capable as an oracle replacement. With improved optimizer performance in 7.3 and now even better Pl/SQL compatability, native Win32, savepoints, PITR and replication available, there is even less reason to use Oracle. I think PG will be able to take a large chunk out of Oracle in the Low & Mid-range markets.
MySQL supports full PITR in version 4.1, which I believe will be Production end of Sept. Last I heard they were integrating PITR management into their GUI tool
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
you might be thinking of ingres... recently open-sourced by ca.
As far as I'm concerned, the only truly missing feature is distributed transactions. Are there any plans to add them any time soon?
Yes. IIRC, in the last release, the protocol has been expanded to allow for this concept. Nested transactions were also required. These are now in place. I'd guess that two phase commits and distributed transactions (sometime after 2pc) will follow sometime after the 8.0 series stablizes.
In other words, it's on their radar but I don't know what priority they are placing on it.
Gentoo Sucks
Btw, does PostgreSQL have row-level locking yet?
Static docs provide your answers.
Remember, one of the points of using MVCC is to avoid row locking whenever possible. But, I think you'll be hard pressed to come up with a situation where your desired locking facilities are not provided for with PostgreSQL.
To quote the online documentation:
"In addition to table-level locks, there are row-level locks. A row-level lock on a specific row is automatically acquired when the row is updated (or deleted or marked for update). The lock is held until the transaction commits or rolls back. Row-level locks do not affect data querying; they block writers to the same row only."
The documentation describes the differences between tsearch2 and openfts like this:
It is true in most cases that a point release upgrade can be done in place without re-initing the db. However the upgrade from 7.4.1 to 7.4.2 does require some extra steps. Please Read The Fine Manual if you're using 7.4.1 and upgrading to 7.4.2+
Upgrades which require an initdb can be performed within a limited maintenance window. The proceedure is relatively straight-forward:
The above requires more work, but when you have a database which takes about 10 hours to load and index and a maintenance window of no more than 2 hours / month...
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
don't use a serial datatype for the id, and everything will be fine.
And kids, serial datatypes are nice for some things; very few things, though. more often than not, they wind up being a huge pain in the ass.
Stop grumbling and go here:
c /F AQ/FAQ_DEV.html+Developers-FAQ
http://developer.postgresql.org/readtext.php?sr
and implement it. This ain't no commercial app, it's open source.
--- It is not the things we do which we regret the most, but the things which we don't do.
If you don't mind Java GUIs, Db Visualizer is pretty slick.
$x = ($x * 10) % 10 >= 5 ? 1 + int $x : int $x
I regret to inform you that MySQL is actually not faster than Oracle.
MySQL cannot partition data and then either: 1. eliminate partitions and only perform i/o on the relevant one(s), 2. process against all partitions in parallel.
MySQL cannot maintain aggregated images of your data an re-write ad-hoc queries to use the aggregates.
MySQL cannot store metadata that will tell it that postal code implies county, and hence a query that wants data aggregated to the county can use the image of data at postal code granularity. (It is not true that postal code implies county, but you get my point).
MySQL cannot perform an efficient hash-join to save its life (even though a properly designed hash join is mathematically superior in a great many cases) -- and so it falls back on sort-merge regularly.
MySQL cannot re-write NOT IN or NOT EXISTS clauses as merge anti-joins. Neither can it re-write IN or EXISTS clauses as merge outer-joins.
I have to get back to work now, but surely you get the idea.
Cheers,
Scott
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.
Well, sequences exist outside the transaction, so I can't say that it should effect it in the least.
;
;
I'm running 7.4.2 here and I get this:
Session 1:
test=# insert into car_type values ( 1 )
INSERT 33665 1
test=# BEGIN; INSERT INTO car (car_type_id) values(1);
BEGIN
INSERT 33666 1
Session 2:
test=# begin; insert into car( car_type_id) values ( 1 )
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
Sounds like he's fudding, using a different isolation level, or using an old version of PostgreSQL.
Prehaps he can restate exactly what his problem is and exactly what his test case is. As he presented it, it would simply fail because of a forign key constraint. As you can see, I inserted a row to allow it...but, it sounds like he's confused or only telling part of the story.
it has binary dumps for quite a while now...
pg_dump --format=c
peace
``If a program can't rewrite its own code, what good is it?'' - Mel
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.
I beleive you have made a mistake. The error you are recieving is due to a failed statement in your transaction. I have tested his example in 7.4.3 and found it to be correct. I even removed the sequences from the example and the block still happened. I don't know why the block is occuring. Maybe because the reference checks in both transactions are trying to read the same row? That doesn't right so I don't know.
As a previous post said, changing the isolation level will probably fix the problem. Here is the documentation.
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.
While I do agree the MySQL.com site could have better navigaion, it is still cake to find anything you want if you know the name...
example: just type into your browser "mysql.com/interval" and you are instantly transported to the proper documentation discussing SELECTING and using INTERVAL. Another great feature that I believe was implemented from the guys at PHP.net (learned this at the MySQL conference last year)
This may be rude.. but I think you need to do more reading.
I have now implemented two huge applications (>100,000 LOBS)in postgresql using both LOB's and BYTEA, using Windows ODBC *and* the C API. The C API requires more work and is ofcourse rather custom/non-portable - so is MySQL's interface. The ODBC app code is EXACTLY like Oracle, MySQL, SQL Server, even MS Access. The first of these apps was written 3 years ago using LOB's and ODBC. I'm afraid to say that you may have to update you're view of PG - your opinions may have been valid at one stage, but certainly not for the last *3* years and I can provide proof of that..
I'm sorry if this seems harsh its just that I'm getting very tired of MySQL pundits who have no clue comparing MySQL to enterprise class DB's like Oracle/SQL Server/PostgreSQL (not that I'm saying u have no clue!!).
When your DB supports triggers,rules,stored procedures,views,extensible types,extensible operators,check constraints,functional indexes,better than row-level locking, full sub-selects etc.. then feel free to compare MySQL and PG again - until then keep playing with your toys.
(Sorry if this sounds harsh)