With the Beta release of version 8, PostgreSQL has just taken three giant steps into Enterprise DBMS territory, with the addition of point-in-time recovery (PITR), transaction savepoints (the equivalent of nested transactions), and tablespaces, as well as various other improvements. Also, PostgreSQL will now run as a native Windows application, which should make it a more attractive choice for many medium and small businesses. LWN has more.
PostgreSQL 8.0.0 Officially Goes Beta
About The Author
Eugenia Loli
Ex-programmer, ex-editor in chief at OSNews.com, now a visual artist/filmmaker.
Follow me on Twitter @EugeniaLoli
Is PostGIS included as a part of 8.0 or is it still plugin?
I just downloaded the native win32 version and so far it seems very solid and very impressive.
The win32 version should provide excellent performance when used with a web server and a connection pool.
The win32 version does not use threads but a single process for each connection. While this will have steeper memory requirements it offers better stablility because one misbehaving thread will not take down the whole server.
Overall I am very impressed with this release.
I would like to see better temp table support where the query plan is reset if you drop a table in a function.
currently you have to use the execute statement or it will think the table still exists because it is cached in the query plan.
Go Postgresql!!!!
PS I was a long time firebird user, but NO MORE PG is so far superior it’s not even funny
Anonymous, you wrote
“I was a long time firebird user, but NO MORE PG is so far superior it’s not even funny”
I used InterBase a while back, and am looking at Firebird for a new project. Can you tell me why Postgresql is “so far superior”?
Genuinely curious.
That would get me to use it.
It’s about time they did a native Windows version. We’ve had problems runing the cygwin version so instead we have a single Linux box dedicated to running postgre. Once it’s out of beta we can move back to windows. Excellent work, thanks postgre.
I heard it was firebird or postgresql.
Ironically almost the opposite for me, though I agree that it is great they are going to support Windows. I like to develop and deploy in Linux. However for testing and for some clients it would be nice to be able to support Windows. I am not religious on the subject of OS of choice. The ability to deploy on Windows as well as Linux, AIX, Mac (I think) etc. is a definite bonus.
This is great news since it addresses my last annoyances with postgres.
Namely I’m happy to see that you can change data types, something that can be a pain while designing a database. Boolean aggregate functions are very handy for me as well. actually, any of the new big features will be helpful.
I came from Mysql myself and i can say that I would never go back. Postgresql offers a very solid platform.
I will give you a few reasons why I think PG is better than FB.
1. No weird dialects
2. Ability to write functions using other languages i.e. perl, java,tcl,R
3. plpgsql is much more robust and flexible than firebirds PLsql.
4. You can do just about anything in a Postgres function.
(try and create a new table in a firebird stored proc or a new user YOU CANT)
5. Postgres has temp tables and they can be used in functions with a refcursor eliminates the need to do complex
joins in a number of situations.
6. you don’t have to reference database by there full pathname on the server. FB has aliases now, but it’s not the same.
I could go on and on and these are not even new features in 8.0 like exception handling in functions.
In 8.0 you can include a exception block in your function:
BEGIN
EXCEPTION
END;
I just tried this on the new native win32 and it works as advertised.
“I heard it was firebird or postgresql.”
->
Dude wake up… Oracle was one of the first…
FirebirdSQL and PostgreSQL both have their own liniage, which has nothing do to with Oracle
As I mentioned, I think these are great steps forward for PostgreSQL. IMHO there are only two areas that I would really like to see future progress on:
Multi-master eager replication – with PostgreSQL, the two approaches to replication right now are synchronous master/slave, and asynchronous single-master/multi-slave. But there is no current support for multi-master/multi-slave with automatic conflict resolution. This is the holy grail of database replication. Imagine having a group of servers distributed anywhere you want geographically, and being able to write a transaction to any machine, without worrying whether a transaction will be lost in limbo. This is also called “eager” replication, and there was an older PostgreSQL project that implemented this for awhile on PostgreSQL 6.5, and there were some attempts to port it to PG 7 back in 2002, but it seems to have been abandoned. Google for “Postgres-R eager” and you will see some interesting links. Look for “Don’t be Lazy: be Consistent”, by Kemme, which is the seminal research paper on this for PostgreSQL. AFAIK, Oracle is the only system which has these capabilities in full (see this).
The other big thing I would like to see in PostgreSQL right now is a much better/consistent approach to user-defined types and domains. For example, CREATE TYPE does two completely different things, depending on the parameters you use. It is quite confusing, with (I think) a wrong concept of what a “composite” type should be (they equate it to a set-returning table expression), when the actual implementation of true composite types is left to low-level type creation with C structs, and procedural declaration of functions to handle representation, etc … True custom datatypes in PostgreSQL are still subject to “procedural” thinking, and in fact are only easy if you are a C programmer. According to C.J. Date, custom types should be handled by creation of domains, with declarative handling of composite representation, and there should be no need of other mechanisms. Right now, PostgreSQL has essentially 3 ways, and each is conceptually different.
But, on the bright side, find the other open source DBMS that even comes close to the new PG capabilities, let alone most commercial DBMSs. PostgreSQL has definitely upped the ante. It will be interesting to see the spin that will issue from Oracle and MySQL (for two different reasons, obviously) in dealing with this ;-).
Oops… forget to post my link for “see this” above:
http://gborg.postgresql.org/genpage?replication_72sum
I love PostgreSQL, but let’s not bash Interbase/Firebird. Both has its pluses and minuses. At the end, it boils down to what your needs are.
Though Postgres is progressing along at amazing speed (practically leaving other OS databases in the dust), here are a few things at which Postgres still suck or seriously lack behind other OS DB’s.
1. Internationalization. IB/FB is light years ahead of Postgres in this regard. In IB/FB you can set charset/encoding per table column (which is as intended by the SQL standard). Postgres (and MySQL, I think?) still relies on OS locale facility, so it can only do per-process charset/encoding.
That is why, I guess, IB/FB is a favorite among Russians, Brazilians, etc.
2. Embedded server. Postgres is not embeddable, and won’t be in any near future (the Postgres developers don’t regard embedded DB as robust, so they won’t go this route for now). FB can be embedded, as can MySQL.
3. Two-phase commits (distributed transactions). IB/FB has had this for years. Postgres will probably acquire this feature in 8.1?
4. Mature win32 port. Ok, certainly Postgres will catch up. But IB/FB is like one or two decades ahead.
Hm, what else am I missing? Can anybody add?
But nevertheless, Postgres is still my favorite DB 🙂
Oh yes, vacuuming is still not an automatic process (including in 8.0; but autovacuum will probably be integrated in 8.1).
This will certainly disappoint or at least surprise new users. “What? I have to ‘vacuum’ my database regularly myself?”
IB/FB has had automatic garbage collection (they call it sweeping) for as long as anybody remembers.
There, Postgres does suck too, ain’t it? 🙂
Anonymous wrote: FB has aliases now, but it’s not the same.
Dude, if it accomplishes exactly the same thing, how is it not the same, for any practical reason?
Ironically almost the opposite for me, though I agree that it is great they are going to support Windows. I like to develop and deploy in Linux. However for testing and for some clients it would be nice to be able to support Windows. I am not religious on the subject of OS of choice. The ability to deploy on Windows as well as Linux, AIX, Mac (I think) etc. is a definite bonus.
Many people or organizations are stuck with certain OS’es (either for technical reasons or otherwise). So it’s certainly important that Postgres supports Windows natively if it wants to conquer the world. (It does, doesn’t it? 🙂
If a Windows shop needs to have Postgres before 8.0, sure, they could just buy a box and install Linux on it. But then who would administer it? They might need to hire a Linux admin, etc.
it’s not the same because in FB you have to manually setup the alias while it’s all automatic in Postgres.
Postgres (and MySQL, I think?) still relies on OS locale facility, so it can only do per-process charset/encoding. </i]>
Not. Quite. Really.
http://www.postgresql.org/docs/7.4/interactive/multibyte.html
http://www.postgresql.org/docs/7.4/interactive/sql-createconversion…
Although I will agree that it doesn’t work the standard way that I suppose IB uses. But, at least you can get the encoding you want where you want.
[i]That is why, I guess, IB/FB is a favorite among Russians, Brazilians, etc.
Apparently PostgreSQL is quite popular in Japan, seeing as Fujitsu has undewritten quite a few of the new developments. Also, there are quite a few Russians and Brazilians involved in PostgreSQL, if you read the lists and browse related sites. Pretty international, I think.
Not that I am bashing Firebird at all. In fact, in the PostgreSQL mailing lists they tend to be quite sympathetic to FireBird development, as unlike another well-known open source DBMS ;-). Each has its strengths, and each is improving, but I believe PostgreSQL is making faster progress these days.
My thanks to Anonymous, Steven Haryanto, and rycamor for your posts. I see I need to take a serious look. One more question, if I may: is there a good windows-based or web-based admin tool? TIA.
Not. Quite. Really.
http://www.postgresql.org/docs/7.4/interactive/multibyte.html
http://www.postgresql.org/docs/7.4/interactive/sql-createconversion…
Yes, Postgres can already store strings in different charsets/encodings, but that’s basically it. The most important things like collation/ordering can only be set during initdb. Thus you can’t sort both a Chinese column and a Japanese column properly within the same database. You can use Unicode, but…
Improvements in internationalization will enable stuffs like more transparent case insensitive searching and sorting (unlike today’s way with UPPER()/LOWER()). Postgres *will* go towards this goal, but probably in 3-5 years? They will need to implement their own locale…
it’s not the same because in FB you have to manually setup the alias while it’s all automatic in Postgres.
Hm, I haven’t followed FB’s development for a while. I thought they allow wildcards in aliases? If not, well, they probably should. 🙂
PgCluster promises multi-master synchronnous replication:
http://www.csra.co.jp/~mitani/jpug/pgcluster/en/index.html
It’s basically a patchset for PostgreSQL (if you run FreeBSD it can be automagically installed via a single port). I tried it some time ago, and it basically just worked (though there were problems that seem to be addressed in newer versions).
I just backed up and restored one of our production databases(from linux) that uses a Delphi front end using the Zeoslib connectivity components and I must say it runs perfectly on the win32 8.0 beta. The speed is actually faster than on our linux 2.4gzh Xeon server with 2gb ram.
The machine I am running it on now is a Athlon XP 3200+ which is actually 2.2 or 2.1 gzh.
I must say for a beta the win32 port is surprising stable and solid.
Congrats to all the postgres developers, they have done a OUTSTANDING JOB!!!!
Some good admin tools:
http://www.pgadmin.org/
http://phppgadmin.sourceforge.net/
http://www.ems-hitech.com/
And more at http://gborg.postgresql.org/browse.php?83
The most important things like collation/ordering can only be set during initdb.
You’ve got a good point there. I suppose there might be some way to hack it by adding a custom operator with the USING clause for ORDER BY, but that’s not a good long-tern solution.
I think it’ll happen sooner than 3-5 years, though. I predict that many more companies will start using PostgreSQL after verison 8.0, and all it takes is a few more willing to put developer time and money into these things.
>>”I heard it was firebird or postgresql.”
>Dude wake up… Oracle was one of the first…
>FirebirdSQL and PostgreSQL both have their own liniage,
>which has nothing do to with Oracle
To expand out a bit:
Firebird derives from Interbase, so it got its start later than the others mentioned.
Postgres comes from Ingres. Ingres was an academic project at UC Berkeley that later went commercial and closed source; Postgres is a continuation of the “academic line”.
Oracle is roughly contemporaneous with Ingres, although I think Ingres may have started a little bit earlier. Oracle’s major influence is a paper in IBM’s Research Journal which described the SEQUEL programming language that was being developed for SYSTEM R. Granted that this is something in common with practically everybody, but Oracle was the first DB to use SQL — they got it out the door even before IBM itself did.
I suppose it is possible that Oracle was later influenced by the RDB database, which they bought from DEC. This was *much* later on though.
The biography “The Difference Between God and Larry Ellison (God Doesn’t Think He’s Larry Ellison)” goes into Oracle’s history in great detail. Fascinating stuff.
One thing I’m missing and still missing in 8, I guess, are named parameters for functions.
I don’t like to be dependant of the order of the parameters to send them to the function. I like it the way SQL server does it. In VB, you can simple call the SP, ask its parameters, fill them (I think the callbyname function can be used to do something like sp.parameter = callbyname (myobject, myproperty)) … Anyway, I used to have an activex object that did my filling …
I know you can use aliases in PG. But it would be nice to call my functions by calling the parameters by name, and not by position.
If someone has a workaround for this (in either what language) they can always let me know ….
Y.V
version 8 has named params:
create or replace function test_func(myparm1 int4, myparm2 int4) returns int4 AS$$
BEGIN
return myparam1+myparm2;
END;$$
Then from the client side you can do things like this:
sql.add(‘select test_func(:param1,:param2)’);
parambyname(‘param1’).asstring:= 10;
parambyname(‘param2’).asstring:= 20;
open;
How easy is that?
.net code is similar.
This is delphi code using Zeoslib at http://www.zeoslib.net
Howdy, I’m on the PostgreSQL Project and thought I’d answer a bunch of points which have been raised. Keep in mind that many members of our community may not share my opinion; we’re a varied and democratic lot.
1) Oracle, Ingres and Postgres are the 3 oldest database systems in terms of code origin and development. Of these three, Postgres is the youngest; we did not, in fact, use any of Ingres’ code in 1984 but started over.
2) Regarding Postgres-R, it still exists but could not be kept current with PostgreSQL development because of incompatibilites with our Durability and Isolation implementations which were completed in version 7.0. You are more than welcome to try, we’d love to see it work.
3) Rick, we’d dearly love to have you straighten out the Type situation. Join the project!
4) We would love to have per-column internationalization. Again, this is a project in search of a developer.
5) We have half a 2-phase commit implementation. The issue which has kept this out of the core code for 2 versions now (!) is that the team working on it did not implement the code for recovery in the event of server failure and restore, which is actually the more difficult part of 2PC. Once again, we’d be thrilled to have someone finish this, especially if they can make it compliant with the XA protocol.
6) If there ever is a “PostgreSQL Embedded”, it will be a fork. I don’t think there’s likely to be; nobody really wants it. There are a lot of good embedded OSS databases already out there; why should our team make a second-class embedded database just so we can hang the PostgreSQL name on it?
7) PGCluster is a query-replicator, which does work for some applications of MM replication, but has limited scalability.
8) We do not have MSSQL-style named parameters. Unfortunately, none of the developers currrently hacking away at our Function/Procedure code are interested in this, mostly I think because Oracle doesn’t do it either. Again, Your Code Contributions Can Make The Crucial Difference.
9) Download. Install. Test. Submit Bug Reports!
If a Windows shop needs to have Postgres before 8.0, sure, they could just buy a box and install Linux on it. But then who would administer it? They might need to hire a Linux admin, etc.
—
a pure database server in linux hardly requires any linux admin knowledge. this is not the primary reason for a linux port but the high cost of MS SQL and access just doesnt match the typical requirements. a pure windows shop usually never goes for anything other than MS SQL much less for postgres.
Hey Josh,
Thanks so much for replying. Touché of course ;-). Yes, I have been really studying up on C.J. Date’s approach to types as detailed in The Third Manifesto, and would love to help. But how can someone who is not much of a C hacker help in this area? I’m all ears.
As to the discussion in general, I have nothing but admiration for the PostgreSQL dev team. I think you guys are pursuing things in just the right order.
Well we use Postgres here for almost all our projects and they all include japanese. So all our databases are in Unicode and then through the connect we decide in which encoding we need the data (most the time Shift_JIS).
I had absolutly no problem with it (yet). I also see no reason, not to store data in unicode in the database. Especially if you server or connect from various different clients who all cook their own encoding soup.
But well, I come from mysql. Postgres is like heaven for me. Foreign keys! Subselects! Yeah man!
Folks,
One correction:
Turns out a Finnish programmer *is* still working on Two Phase Commit, though of course he’d love to have help.
I have had early version of the 8.0(aka 7.5 dev) working. The 8.0 beta won’t work for me on multiple machines(windows xp and windows 2000).
Has anyone got it to work?
That’s odd because I have it working on multiple Windows XP and 2000 machines.
I just followed the prompts and let it create the postgres user account with my password.
It’s so far worked perfect on all.
You might want to ensure you have the required rights on what ever machine you are trying to install on.
Postgres needs ntfs for permission handling, could be the problem.