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

Native Win support - awesome

[Stone316]

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!

Windows support

[Anonymous Coward]

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.

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.

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:I recommend Mysql users to take a look at PG

[Svennig]

Okay, I'll bite

Stored Procedures. They arent functions. Functions are different. Functions should be called inline, from within SQL statements. There should be a difference.

When looking towards migrating to an OSS database from MS SQL Server I looked into how easy it was to use the postgres stored procedure/functions/things. I couldn't find any equivalent of returing a resultset. In MS SQL you use:

CREATE PROCEDURE name AS select * from test

The only way that I could find to do that in postgres was:

1. Return a cursor reference, in which case throuch JDBC you need to obtain this horrid postgres specific class from the driver.
2. Return a set of results (or was it records, my mind fails me), but I couldn't get this to work
3. Return a set of created types, but this doesnt work well for dynamic queries and makes maintainance a headache - you'd end up defining hundreds of types!

4. So it seems that we're stuck with MS SQL server.

   If someone DOES know how to do this is a non-evil manner, please tell me! Were planning on doing an upgrade, and I'd rather not have to fork out the money for SQL Server licences etc...

replication is not a failover solution

[RelliK]

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.

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: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: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;

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

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

Windows Native Support

[adolfojp]

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