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."

Comments

You can save a few clicks...

[tcopeland]

...and jump right to the beta announcement message.

where to download

[jbellis]

http://developer.postgresql.org/beta.php

the windows installer is at
http://pgfoundry.org/projects/pginstaller

BitTorrent Downloads

[Anonymous Coward]

http://bt.postgresql.org

Bittorrent [was Re:where to download]

[dfetter]

http://bt.postgresql.org

Join the torrent! :)

Re:I recommend Mysql users to take a look at PG

[Jason Earl]

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.

full text search

[jbellis]

I guessed you missed OpenFTS, which has been out for a couple years now.

Re:I recommend Mysql users to take a look at PG

[rtaylor]

In short, yes and perhaps.

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 .org servers (Affilias).

Re:What the versions mean

[SSpade]

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.

Re:Can it Compete with Oracle or DB2?

[rtaylor]

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.

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 .0 release) so long as the hardware is adequate (doesn't lie about writes hitting disk when they're really in cache).

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.

Re:Can it Compete with Oracle or DB2?

[Stone316]

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.

Re:I recommend Mysql users to take a look at PG

[JamesKPolk]

Using postgresql 7.4.2:

create function testfunction()
returns setof record
as 'select name from companies;'
language sql;

select name from testfunction() as names(name char(50));

That will print all the company names in my database.

Re:Newbie Question - UI Tool

[rycamor]

Try PGAdmin III.

Re:replication is not a failover solution

[wieck]

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

Re:DROP COLUMN, ALTER COLUMN TYPE and SQL-99 Specs

[rtaylor]

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;

Really cool stuff in PostgreSQL

[cmowire]

• user defined types, including the ability to create customized indicies for them. So there's a type specifically to handle encrypted authentication passwords, for example, so you don't need to mess with crypt() all of the time.
• Inheritance for tables, so you can agregate different types of data without needing to explicitly set all kinds of primary keys.
• Full ACID support, like a real database
• MVCC instead of row or table locking.
• BSD license

Re:P.I.T.R

[GooberToo]

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."

Re:I recommend Mysql users to take a look at PG

[Ragica]

"tsearch2" which openfts is based on is BSD licenced, and included in the PostgreSQL contrib package. It's pretty good, and all you need for creating full text searchable indexes.

The documentation describes the differences between tsearch2 and openfts like this:

OpenFTS is a middleware between application and database, so it uses tsearch2 as a storage, while database engine is used as a query executor (searching). Everything else (parsing of documents, query processing, linguistics) carry outs on client side. That's why OpenFTS has its own configuration table (fts_conf) and works with its own set of dictionaries. OpenFTS is more flexible, because it could be used in multi-server architecture with separated machines for repository of documents (documents could be stored in file system), database and query engine.

MySQL only fast for single user?

[wieck]

You really need to update your "known facts".

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 ... except for MySQL's outstanding marketing.

Sincerely, Jan