PostgreSQL 7.3 Released

rtaylor writes "Nearly a year's worth of work is out. The new tricks include schema support, prepared queries, dependency tracking, improved privileges, table (record) based functions, improved internationalization support, and a whole slew of other new features, fixes, and performance improvements. Release Email - Download Here - Mirror FTP sites (at bottom)."

Comments

Just maybe

[unterderbrucke]

Taco can use it to fix the search function :-P

Question

[altaic]

I've heard this is the best SQL server around. Is it really better than MySQL?

Re:Question

[h2so4]

For "simple" stuff, MySQL is (supposedly) quicker than PostgreSQL but you might want to check the following link for just why MySQL sucks for non-toy apps

Re:Question

[Anonymous Coward]

So, you're a new troll then. First off, fuck you. Second, best depends on your desired application of said server. Postgres might be better for some things than MySQL, and vice versa. One person might be more comfortable with one package than another. Bottom line is that "better" is objective in many respects.

Re:Question

[Tribbin]

Your answer is much more stupid than his question

Re:Question

[ruckc]

That article is 2 years old. MySQL has advanced greatly since. The author of that paper seems to suggest that the only reason to not use MySQL is for its lack of transactions. Well boys it is in and works great.

Re:Question

[KristsInferno]

Come on. Not only are you getting off topic, but that article is two years old AND just from the ACS standpoint. I understand your point on the line between light and robust data needs, but what does "simple" mean? I have made web apps using MySQL in 50,000 record environs and never had problem one. I do not comsider that a "toy app"

Re:Question

[FattMattP]

For "simple" stuff, MySQL is (supposedly) quicker than PostgreSQL but you might want to check the following link for just why MySQL sucks for non-toy apps

I think that you're mischaracterizing that web page. That page describes why that one project chose to not use MySQL. It's not meant as a "MySQL sucks" page. There might be non-toy apps that a user has that don't require the extra features and data integrity support that PostgreSQL offers. That doesn't mean that MySQL isn't useful. In the end, the developer should choose the right tool for the job. For one person MySQL might be the better tool for what they are doing instead of PostgreSQL.

Re:Question

[dhogaza]

That article's very much out of date, as is stated in an addendum at the top and as is made clear in the later posts in the discussion of the piece.

I'm the OpenACS project manager so want to make sure that people understand that the piece (and much of the commentary) was accurate when written, but that it was written many moons ago. However, nowadays MySQL has the InnoBase backend which provides full transaction support, and has seen other major improvements.

Our project only supports Oracle and PostgreSQL, and I still feel MySQL is lacking in many areas, but it has improved greatly in the last couple of years.

So has PostgreSQL, of course! We love it ...

Re:Question

[Sivar]

but you might want to check the following link for just why MySQL sucks for non-toy apps

You're absolutely right.

Only complete idiots would ever use MySQL for anything serious.

Idiots like The U.S. Census Bureau, Slashdot, Yahoo, and Novell.

Re:Question

[alacqua]

...so I was typing my term paper on my beowulf cluster which was running MySQL, when all of a sudden - bleep, bleep, bleep! And like my term paper was gone. Then dad got PostgreSQL and we haven't had any trouble since. ACID man, ACID.

...just wanted to see how many I could get into one post.

Re:Question

[Anonymous Coward]

Consider stepping into the 20th century, 'tard. That openacs.org article is horribly outdated.

Re:Question

[WebCowboy]

Hmmm... I guess it is all a matter of perspective. For an RDBMS to handle 50,000 records, particularly on todays hardware, is actually quite trivial. I've regularly run into apps that size (web based and otherwise) using (shudder) MS Access 2000. While Access is a real dog, it can handle it and "never have problem one" size wise.

In my experience with database applications, anything under 100,000 records is considered a small database. I have to admit in the last year or two my firsthand experience with mySQL has been limited, but from what I see in the current feature set there are situations where PostgreSQL is the definite superior choice.

Sure, MySQL can handle a lot of records and is quite stable and lightweight, however the lack of such basic features as foreign key constraints and restrictions such as table-level-only locking limit its usefulness as an "Oracle killer".

Yes, MySQL can do SELECTs from million-plus record tables, do lots of inserts per second etc etc in all the benchmarks, but what happens when you have 100,000s to millions of records and dozens to hunderds (maybe thousands?) of concurrent users who all frequently write to and delete from the database in addition to doing SELECTs. mySQL falls apart because all the users have to wait in line to write to a given table because the whole damn thing gets locked out until the first user to open a write transaction hasn't finished yet.

A large database also adds challenges to maintaining referential integrity. If foreign keys were so expendable that they could be eliminated (functionality put in the application) to boost performance, why does such functionality remain in Oracle MSSQL and others? Maybe because if it is done in the database (and done right--solid and stable) you don't have to trust your data integrity to application code. You don't want to deal with orphaned records when you have 45 tables and 2.5 million records to manage, just because some flunky Java programmer didn't cover all the bases in a class buried somewhere in a big enterprise web app. Foreign keys allow for an extra layer of protection for important data--if a database constraint is violated by an application bug it'll sure get noticed faster.

These are but two examples where PostgreSQL has the edge (even if it can't top out some arcane benchmark). Given that, mySQL is still very useful--and it doesn't have to be limited to "toy apps". Slashdot can use mySQL quite well, thank you very much. It is certainly NOT a "toy app", and mySQL's performance can handle all the slashdotting thrown at it, as far as retrieving data goes. Keep in mind though, that Slashdot data is fairly static. Its database is read far more often than it is updated/inserted/deleted (even given how many comments and articles are posted and with moderation). The relationships between tables are simple enough that referential integrity is manageable through application code, and above all, the data is not THAT mission critical (if a comment or 12 disappear from Slashdot, the lights will stay on and planes will not fall out of the sky). Speed and compactness are primary for Slashdot, the rest is secondary, and mySQL can fit the bill. If I need something to handle payroll, or health records or whatever--I thing PostgreSQL has reached the point where I find it the more trustworthy of the two with data of that nature.

While PostgreSQL vs mySQL can ignite a virtual Jihad, it should come down to this--pick the right tool for the right job (not just features and performance, but also personal preferences--what tools are you most comfortable with, etc etc).

Re:Question

[smittyoneeach]

Agree with your last statement.
I'll add that, for all open-source databases do a great job, there will always be a market for the 800 lb. gorillas like Oracle and DB2.
There are some high performance features of these proprietary applications that, I daresay, will never trickle into the Open Source world.

Re:Question

[Anonymous Coward]

all your term paper are belong to us!

Re:Question

[some guy I know]

Consider stepping into the 20th century, 'tard. That openacs.org article is horribly outdated.

Which century is that now?
I thought that we were currently occupying the 21st century.

If you are going to call someone a "'tard", try to avoid looking like one yourself.

Re:Question

[zootread]

Well the fact of the matter is MySQL still has no stored procedures and no triggers. Two very important things IMHO.

Re:Question

[5alligator]

1. beowulf
2. mysql vs. postgres
3. linux vs. microsoft
4. all your base
5. ellen feiss
6. ?
7. profit!!

Re:Question

[gh0ul]

I prefer mysql, now that its GPL'd software

Re:Question

[Anonymous Coward]

Is there a single post here that actually talks about the cool new stuff in Postgresql 7.3? ... or do we just want to whinge about how unfair it is that PG is not as popular as MySQL.

Re:Question

[MacOSR]

I thought I would chime in from someone that is currently using MySQL as a back-end database to a web application.

First some statistics: We have ~140 tables, 7,500,000 records and see ~100,000,000 queries monthly. We update a minimum of 300,000 records daily and are running RHLinux on a Dell 2650 Dual 2.4 with HT.

Performance: Our system performs EXTREMELY well. The database is easily handling the load we are putting on it so I wouldn't say that it falls apart under load.

Foreign Keys: Would I like to have foreign key support in MySQL? No, I would LOVE it! However, we have written apps to check database integrity throughout the day. I agree 100% that if you have unqualified programmers posting final code to a server you could quickly have a corrupt database system. This really should not be the purpose of Foreign Keys, although foreign keys do achieve this.

Database Speed: Generally, when dealing with large applications and row-level locking if speed is an issue with a MySQL database it is generally due to poor database structure. I will be the first to admit, however, that we are looking at ProstgreSQL because of row-level locking and a few other bells and whistles...who knows, MySQL 4.x may fit the bill depending on how well it performs.

My suggestion to anyone looking--try both and use what fits your needs!

Re:Question

[Anonymous Coward]

what if a few years down the road someone builds a different app to the same database for the organization and youre no longer there anymore. or an adhoc third party tool is bought to insert data against the database? looks like all that validation code is worth jack shit.

I work in a in organization that has dbase and foxpro databases that have a ton of validation code int their apps. a lot of frickin code. But i bet the data model like the one at ours sucks and to get any real meaning data for some important analysis is useless. I can almost guarantee that to anyone who thinks foreing keys are an optional thing.

Re:Question

[MacOSR]

I am one of the owners of the company so I do not see this happening. Besides, our system is HIGHLY documented. Besides, we are also saving over $80,000.00/year off what Microsoft proposed for our licensing of 2000 Server. So, would I rather spend maybe 40 hours/year in extra coding time just to do things right or shell out an extra $80k for software that would protect me from my poor mistakes? It was a simple decision for me. Anyone can screw anything up. I have seen MS SQL databases screwed up even with keys. Bottom line - if you don't know what you are doing don't touch it!

could it be?

[orallo]

THat I get first post???

I feel ashamed I just posted this...

Cool! PostgreSQL always runs on my iBook

[MarkWatson]

PostgreSQL is a great database. I always run it as a daemon on my iBook since the Smallttalk development environment that I run needs a relational database for source code control.
-Mark

Quick question

[Noose For A Neck]

Did they do anything to improve/add replication support? That seems to be the only real thing that was holding it back from replacing Oracle, as far as I can tell. I know several projects for such a thing were in the works, but they appeared to be very beta.

Re:Quick question

[Anonymous Coward]

Did they do anything to improve/add replication support?

They did. More about this here. Why ZDnet is covering this, rather than hawking Microsoft's Sequelserver is a mystery to me as well...

Re:Quick question

[Anonymous Coward]

Why ZDnet is covering this, rather than hawking Microsoft's Sequelserver is a mystery to me as well...

It's amazing how people claim that Slashdot is a Linux zealot's site, and yet such cheap shots such as the above get modded down as troll, rather than funny. Hey Linuxxites, you can do better than that!

Re:Quick question

[Khalid]

I don't know about this one, but one the things that were holding it back from replacing Oracle were stored procedures Table function now brings one of the features of stored procedures : the ability to return sets.

Table Functions : Functions returning multiple rows and/or multiple columns are now much easier to use than before. You can call such a "table function" in the SELECT FROM clause, treating its output like a table. Also, PL/pgSQL functions can now return sets.

Re:Quick question

[rtaylor]

I'm afraid that there hasn't been a big effort to make replication user friendly -- whats there works very nicely as asynchronous master -> multiple slaves.

There are a few things on the developers minds prior to replication. 2 Phase commits, improved inter-database connections (see dblink), Point in Time Recovery (via WAL logs).

Once any 2 of the above three are completed, then replication should be a piece of cake :)

That said, I'm holding out on asynchronous multi-master replication.

Re:Quick question

[Anonymous Coward]

you wouldn't say that if you had clicked on the link :-) (or rather (0) )

Re:Quick question

[Sokie]

Postgres has had stored procedures for a while, look up CREATE FUNCTION. But adding better support for result sets does make them quite a bit more useful, now if only there was a decent JDBC driver that implemented result sets more completely.

-Sokie

Re:Quick question

[slamb]

Did they do anything to improve/add replication support? That seems to be the only real thing that was holding it back from replacing Oracle, as far as I can tell.

I think that's the sort of thing that as soon as that feature is filled in, people will say it's "just" something else that's missing. There are a bunch of features I can think of that would be nice and PostgreSQL doesn't have. And probably there's someone who considers each one to be vital:

• Database links to Oracle data warehouses. Obviously Oracle has a bit of an advantage here, but you might want to use PostgreSQL and link to an existing system outside your control.
• Materialized views. These are kind of a cross between tables and views. They are used for expensive views; ones with complex calculations and/or ones over data links. They can be refreshed manually, every N hours, or in some cases when the underlying tables change. They can even be updateable. You can use them to rewrite queries that don't even know about them.
• Index-organized tables. This is just a performance optimization - instead of the primary key index referencing the table row, the entire row is stored in the index. Good for tables with few columns where you often look for the primary key.
• Point-in-time recovery. (Planned for 7.4, and not too big a step from what they already have with the WAL, I think.)
• Savepoints/nested transactions. There's a discussion about this for 7.4. It would also allow a failed update/insert/whatever to not invalidate the entire transaction.
• Better cursor support in JDBC bindings (and presumably other language interfaces). Right now, executing a query fetches the entire results to memory. That doesn't scale of course. But I hope to see this change soon. Nic Ferrier is working on a patch, though it won't work with resultsets you use across transactions. (PostgreSQL doesn't (yet?) support cursors outside of transactions.)
• executeBatch and such that I think would be helpful for inserting a lot of rows quickly. There's COPY, but I think it's completely non-standard.
• Surrounding tools. Oracle Forms & Reports, for instance. I consider GNUe Forms & Reports to be a long way from a replacement. Don't know of any other projects even as close as they are.
• Tablespaces. Mostly for performance, I think - we just keep all the indexes in a different tablespace on a different array for less disk seeking.
• Multi-column function-based indexes. "create index person_upper_name_idx on per.person (upper(lname), upper(fname)) tablespace bob".
• Good Win32 support.
• Database migration fairies. We use Oracle at work, even though it is a relatively small database. Even if all the other features were completed, I don't think we'd switch unless database migration fairies helped us with the transition.

Re:Quick question

[Malcontent]

Some of that stuff would be nice to have, some of it can be done by other tools (espcially forms and reports).

But really it's free and it's great. If you want oracle like features you are going to pay oracle prices.

Re:Quick question

[the eric conspiracy]

I think that's the sort of thing that as soon as that feature is filled in, people will say it's "just" something else that's missing

Maybe, but I have had to disqualify postgresql from consideration becasue of this issue on two projects this year. I really would have preferred to use it, too. I can't say that about any other feature.

Re:Quick question

[nconway]

Good Win32 support.

This is planned for 7.4 -- the plan is to have native Win32 support in CVS by the end of December.

executeBatch and such that I think would be helpful for inserting a lot of rows quickly. There's COPY, but I think it's completely non-standard.

Can you elaborate on what executeBatch is?

Other than that, I agree that all the points you raised are worth implementing.

Re:Quick question

[bwt]

I'll add a few more...
- better monitoring capabilities, specifically
+ IO, memory, CPU, stats for all cached SQL statements
+ system and per-session stats on wait events
+ some way to check io balance per table, tablespace, disk level (need tablespaces first, of course)
+ list all SQL currently executing, with runtime
- use archived logs and data files to do backup and recovery
- log mining capabilities, including redo and undo SQL reconstruction
- fast raw data loading, (compare oracle sql*loader)
- multi-CPU parallel query capability
- something akin to oracle's CONNECT BY queries on self-referential tables
- bitmap indexes
- job scheduling
- queueing/messaging
- sql optimizer hints

Re:Quick question

[slamb]

Can you elaborate on what executeBatch is?

Sure. That's the JDBC method for it - there may be a more generic term I don't know. You just prepare an insert/update statement. Then you loop over it and call addBatch() instead of execute() and an executeBatch() at the end and it's faster than calling execute() each time, even within the same transaction. I think it does this by sending them all to the database at once (which I guess the JDBC driver could/maybe does do already; just semicolon-separating them) but also eliminating parsing the statement, which is necessary even with prepared statements now ("execute mystatement (param, ...)"). (That's planned for 7.4, right?) Maybe something else, too. So I don't know that it's anything that can't be accomplish with COPY; it's just more standard.

Other than that, I agree that all the points you raised are worth implementing.

Cool. It's nice to know a PostgreSQL developer values everything on my wishlist.

Please let me know when the database migration fairies are finished. ;) Seriously, thanks for the great work. I use Oracle at work, but I love PostgreSQL at home.

Re:Quick question

[GooberToo]

Tablespaces. Mostly for performance, I think - we just keep all the indexes in a different tablespace on a different array for less disk seeking.

Planned for 7.4 IIRC.

Good Win32 support.

Planned for 7.4. Seems code is already available, it's just being cleaned up prior toward merge.

Re:Quick question

[Anonymous Coward]

> Better cursor support in JDBC bindings (and presumably other language interfaces). Right now, executing a query fetches the entire results to memory. That doesn't scale of course.

To put it mildly. This is what killed pgsql for me. I thought I was just doing it wrong for so long...

Re:Quick question

[stevecoh1]

Better cursor support in JDBC bindings (and presumably other language interfaces). Right now, executing a query fetches the entire results to memory. That doesn't scale of course. But I hope to see this change soon. This is the one that bit me in the butt last week. A very large result set caused OutOfMemoryExceptions. setFetchSize() wasn't implemented. I got around it by using the handy but non-standard LIMIT-OFFSET syntax, but this is subpar, IMHO. I'd be very interested in this enhancement.

Re:Quick question

[Anonymous Coward]

Win32 support: Why bother? A house built on sand etc...

Warning

[Anonymous Coward]

PostgreSQL has a bad security record recently. Make sure you don't run this in a production enviroment.

Re:Warning

[JohanV]

You are linking to something that counts security advisories for Linux and compares them with security advisories for other OS'es. You seem to have misunderstood that security advisories for a certain OS are something entirely different from security advisories for a RDBMS. The 2 are completely unrelated.
In fact, if you want to you can run PostgreSQL on Windows if receiving less security advisories is more important than the time-to-fix. Or if you feel really paranoid, just run PostgreSQL on OpenBSD. That gives you an excellent safety record, with only 1 remotely exploitable security problem in nearly 6 year.

For a complete list of supported platforms see http://developer.postgresql.org/docs/postgres/supp orted-platforms.html

Re:Warning

[Skjellifetti]

I don't like to feed trolls, but it should be pointed out that PostgreSQL is not even mentioned at all in the URL you provided. Also, the Aberdeen report upon which the URL is based has been pretty thoroughly debunked because the data source upon which it was based (CERT Advisories) are a very poor measure of how secure one OS is relative to another.

Re:Warning

[Anonymous Coward]

(lie, troll, FUD): PostgreSQL has a bad security record recently [wininformant.com]. Make sure you don't run this in a production enviroment. OK, normally I'm not that paranoid but I've seen several anonymous "MS FUD Trolls" in here recently. Just a thought: would "they" pay someone to troll at slashdot? Seems like it...

Any clue when it will support....

[Spock the Vulcan]

Replication out of the box?

Re:Any clue when it will support....

[Anonymous Coward]

that will be good. but Its more importan that they WAL and checkpoints... a good way to differentiate from FOXPRO.

Re:Any clue when it will support....

[Anonymous Coward]

Yes, Next release.
7.4 will answer most of the common feature requests including replication and native Win32

real kewl

[slipped]

kewl

Dropping Columns finally supported

[limekiller4]

WOOHOO!

DROP COLUMN [column] FROM TABLE [table];

This up-until-now lacking feature has been the bane of my existence. I HATE cruft being left lying around.

(btw, I don't know if that is the correct syntax, just a guess)

Re:Dropping Columns finally supported

[baywulf]

I don't think even Oracle had the drop column feature until Oracle 8i or something.

Re:Dropping Columns finally supported

[limekiller4]

baywulf writes:
"I don't think even Oracle had the drop column feature until Oracle 8i or something."

I must be missing something here. I have to wonder why they would not put such a useful feature in the first release. *shrug

Re:Dropping Columns finally supported

[BlueUnderwear]

(btw, I don't know if that is the correct syntax, just a guess)

Not sure whether it's the same in Postgresql, but in oracle, it would be:

ALTER TABLE [table] DROP COLUMN [column];

Re:Dropping Columns finally supported

[zulux]

And you can rename tables and colums on the fly too!

And the default identifier length is 63! ForReallyLongAndDescriptingColumnNames!

For all you people out there with Access who think this is old hat - the tables and column renaming and droping can happpen while people are connected to the PostgreSQL database - you don't have to kick anybody off the database.

If you're considering migrating your Aceess database to MS SQL Servier - do consider PostgreSQL. From experience, the amount of suffering is about the same for both transitions, but when your done, PostgrSQL is more robust, less expensive and less buggy.

Re:Dropping Columns finally supported

[Anonymous Coward]

ALTER TABLE products DROP COLUMN description;

Re:Dropping Columns finally supported

[zulux]

Because PostgreSQL and Oracle are designed to be a 24/7 live database. With most of the othere databse packages, you have to disconnect everybody then rename/drop columns. This feature is not terrably usefull if you can't do it while the database is running.

As an aside, you alway could do this with a down PostgreSQL database: dump the database, then rename, then reload.

You can *now* do this with a *live* PostgreSQL database.

Re:Dropping Columns finally supported

[symbolic]

AND it doesn't have the nasty side-effect of being owned, controlled, and licensed by Microsoft.

Re:Dropping Columns finally supported

[archeopterix]

"I don't think even Oracle had the drop column feature until Oracle 8i or something."

I must be missing something here. I have to wonder why they would not put such a useful feature in the first release. *shrug

It's a bit hard to implement cleanly. When you have the records placed in blocks and you drop a column, you make each record a few bytes smaller, therefore getting a nasty fragmentation - lots of 'holes' too small to fit a record in. So my guess is they preferred to leave it out instead of having it implemented poorly.

Re:Dropping Columns finally supported

[rtaylor]

Not to mention in PostgreSQL almost all DDL statements (like Drop Column) are transactional -- which adds a touch of added complexity.

Re:Dropping Columns finally supported

[kcbrown]

And you can rename tables and colums on the fly too!

Oh, it's even better than that. You can do these things within transactions. If you rename a table within a transaction and abort the transaction, it's as if the rename never took place.

This is very cool stuff. I suspect that dropping columns works the same way. It means that you can do things like exchange table names atomically.

Re:Dropping Columns finally supported

[limekiller4]

BlueUnderwear writes:
"ALTER TABLE [table] DROP COLUMN [column];"

That actually looks like what it would be, don't know why I didn't think of it. PostgreSQL uses the "ALTER TABLE" syntax too.

Re:Dropping Columns finally supported

[JourneymanMereel]

If you're considering migrating your Aceess database to MS SQL Servier - do consider PostgreSQL. From experience, the amount of suffering is about the same for both transitions, but when your done, PostgrSQL is more robust, less expensive and less buggy.

OK, you've got my attention. This is something I hope to do more of in the future and being that you have some expirience, I'd like to ask a couple questions...

1. I recently did an Access to MS SQL conversion and ended up with an access database in .adp format which contained the connection string to the SQL server and required no ODBC setup on the clinet PC's. Is this similar in Postgre?
2. In that conversion, the .adp file contained the forms, reports, and macros. The queries from the old access db were stored on the server either as views or Stored Procedures. Is this also similar (or at least compatible)?
3. MS SQL allows you to use your current windows credentials as the authentication to the SQL server. This is nice because then the users don't have to enter/remember another password. Can I do this in Postgre?
4. I've found that Access generates absolutely horrid SQL. Fortunately, because all these queries are stored on the SQL server, they can be edited/optimized after the conversion. This question ends up being a two-parter. How compatible is Postre with Access' ugly SQL? And if there's a compatability problem with the generated SQL, can I at least edit it server side and make access not care that that's happened?

I'm sure there's more issues I'd run accross, but this is all I can think of setting on my couch during my extended weekend away from work :)

Re:Dropping Columns finally supported

[WasterDave]

The answer to all the above is: You are going to have to put in some effort, sorry. The Microsoft curve always runs easier at the start and harder as time goes on. The Unix curve generally loses some wizards and other gumpf in exchange for a need to get a bit low down and grunty. It'll take you longer to get going, but when it's up you'll understand *why* things are they way they are and how to maintain it in the long term. Harder at the start, easier as time goes on.

I guess it depends where your head sits.

Dave

Re:Dropping Columns finally supported

[zulux]

1. I recently did an Access to MS SQL conversion and ended up with an access database in .adp format which contained the connection string to the SQL server and required no ODBC setup on the clinet PC's. Is this similar in Postgre?

Nope, You do need a PostgreSQL ODBC driver, but the link settings can be managed by your Access databsse if you relink on client startup with a VBA script.

2. In that conversion, the .adp file contained the forms, reports, and macros. The queries from the old access db were stored on the server either as views or Stored Procedures. Is this also similar (or at least compatible)?

You can store the queires as views on the PostgreSQL server - no problem there. In 7.3 procedures can return a set of data now - though, I'm waiting for reports from the field to come back and report that it's working well before I jump on it myself.

3. MS SQL allows you to use your current windows credentials as the authentication to the SQL server. This is nice because then the users don't have to enter/remember another password. Can I do this in Postgre?

I don't think there is any way you can do that in the PosgreSQL ODBC driver - you could rewire the ODBC link on the fly though. Another login is a pain in the ass, but nobody seems to care. It may be possible to get this to work with a Linux server through PAM - if you can get PostgreSQL to work though PAM. I don't know, though.

4. I've found that Access generates absolutely horrid SQL. Fortunately, because all these queries are stored on the SQL server, they can be edited/optimized after the conversion. This question ends up being a two-parter. How compatible is Postre with Access' ugly SQL? And if there's a compatability problem with the generated SQL, can I at least edit it server side and make access not care that that's happened?

Both the PostgreSQL server and ODBC driver can massage the horrid Access built queries into normalacy. Typically you don't have to migrate the queries off of Access and into server views because of this - they just work. It's the KSQO that does the magic, from the docs: Key Set Query Optimizer causes the query planner to convert queries whose WHERE clause contains many OR'ed AND clauses (such as "WHERE (a=1 AND b=2) OR (a=2 AND b=3) ...") into a UNION query. KSQO is commonly used when working with products like MicroSoft Access, which tend to generate queries of this form.

This hasen't always been the case, Access queries used to crash PostgreSQL a few years ago becuase they were so odd.

General thoughts on both:
It takes a *bit* longer to get Access to play with PostgreSQL but once it's there, there are no odd bugs to work out. Upgrade Jet to the latest version on the clinet boxes, and set the ODBC time out in the regestry from 600 to 0 - there's a bug in the way Access relinks to a timed-out ODBC session, so by setting the timeout to 0, it never times out.
The Access/ODBC driver sometimes has problems with creating a record using contininous forms - any new record should be created using VBA rather than by filling out the blank entry in an Access continuis form or list of records.

Good luck - I've been very happy with the migration myself.

Oh, setup an hourly cron job to dump the database to a file then gzip it and stash it on a NFS server. Easy hourly backups! Never had to use them, but it's nice to know that we'll never loose more than an hours worth of work!

Re:Dropping Columns finally supported

[Malcontent]

"# In that conversion, the .adp file contained the forms, reports, and macros. The queries from the old access db were stored on the server either as views or Stored Procedures. Is this also similar (or at least compatible)?"

You had to translate all those queries from access SQL to MS-SQL language right? They are incompatible in many ways. Also many access queries tend to have functions in them like dlookup() all of which won't work in SQL server. SO depending on the complexity of the SQL you will have to translate some of them.

"# MS SQL allows you to use your current windows credentials as the authentication to the SQL server. This is nice because then the users don't have to enter/remember another password. Can I do this in Postgre?"

You can embed a username and a password in the ODBC driver and have everybody log in as the same user or you can write some VB code to get the user name from the windows login and make right registry hacks. Not too big of an ordeal.

" 4. I've found that Access generates absolutely horrid SQL. Fortunately, because all these queries are stored on the SQL server, they can be edited/optimized after the conversion. This question ends up being a two-parter. How compatible is Postre with Access' ugly SQL? And if there's a compatability problem with the generated SQL, can I at least edit it server side and make access not care that that's happened?"

Pretty much the same as SQL server. You can create stored procedures or views and link them up like tables. Study up on the postgres RULE system. It basically allows you to create views with code and also makes all views writable if you want to code the writes.

Look into SQL Porter http://www.realsoftstudio.com/ordersqlporter.php it might save you a ton of work and it does not cost a lot of money.

You can also use "pass through" queries to take advantage of postgres features like regular expressions and such.

Re:Dropping Columns finally supported

[zulux]

Oh, it's even better than that. You can do these things within transactions.

Good greif, that's cool! Do a whole database transformation in a transaction, and if it borks out, it rolls back? Cool!

Re:Dropping Columns finally supported

[Anonymous Coward]

Who marked this comment Insightful? It's informative, not insightful.

Sheesh.

(I know this comment is offtopic, I just hope the meta-moderator catches the problem)

Re:Dropping Columns finally supported

[WasterDave]

I stand corrected, it's easier than I thought :)

Dave

Re:Dropping Columns finally supported

[Bradley]

Good greif, that's cool! Do a whole database transformation in a transaction, and if it borks out, it rolls back? Cool!

Yeah, it is cool. The side effect, of course, is that the space from DROP COLUMN can't be automaticaly reclaimed - for that you need to run a VACCUM FULL, which does need an exclusive lock. You can batch updates, however, and then do the vaccum at some time in the future when the db isn't being heavily used (ie 3am on a public holiday)

Newly added rows don't have much overhead for the dropped columns though, apparently. The postgresql websties are still pointing to 7.2 docs, but 7.3 says:

"The DROP COLUMN command does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent inserts and updates of the table will store a NULL for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated. To reclaim the space at once, do a dummy UPDATE of all rows and then vacuum, as in:

UPDATE table SET col = col;
VACUUM FULL table;"

Re:Dropping Columns finally supported

[leonbrooks]

MS SQL allows you to use your current windows credentials as the authentication to the SQL server. This is nice because then the users don't have to enter/remember another password. Can I do this in Postgre?

I don't think there is any way you can do that in the PosgreSQL ODBC driver - you could rewire the ODBC link on the fly though. Another login is a pain in the ass, but nobody seems to care. It may be possible to get this to work with a Linux server through PAM - if you can get PostgreSQL to work though PAM. I don't know, though.

Even if not, this is Unix. A simple glue script to fetch the necessaries from Samba and push them into the Postgres authentication table(s) should do the trick.

When it works, make a hero of yourself, re-render it in C or something at least a little more robust than BASH or whatever you prototyped it in, and throw it at Postgres' contribs.

Re:Dropping Columns finally supported

[gid]

Well the cruft isn't totally gone until you update each row and then do a vacuum analyze. So you can do "update table set column=column; vacuum analyze" after the alter table.

Some more minor alter table stuff is still missing: In mysql you can "ALTER TABLE ADD column2 INT DEFAULT 0 NOT NULL AFTER column". Postgresql doesn't support adding a column at a specific location, nor can you set the default for a column, nor can you make it not null. With Postgresql you can still do it I guess, it's just more difficult using ALTER TABLE set default and ALTER TABLE set not null, or whatever the exact syntax is, but you cannot set where the column is add at.

Actually, one of the weirdess things with Postgresql, they removed the LIMIT N,M sytax for LIMIT M OFFSET N. Which will definitely break some of my code, albeit not hard to change it to be compatible again, but WHY?

Re:Dropping Columns finally supported

[einhverfr]

PostgreSQL uses the following syntax
ALTER TABLE [table] DROP column [RESTRICT | CASCADE]

In this way, you can drop all columns that depend on the column (i.e. when deleting a primary key, for example). In this way, referential integrity is maintained.

Re:Dropping Columns finally supported

[TarpaKungs]

PAM works - even using PG 7.2 (compiled with configure --with-pam)

I don't believe it was an extra patch - I don't see any PAM specific patches in my source tree.

Here's the pg_hba.conf to make it work: (for me)

local all pam postgresql
host all 127.0.0.1 255.255.255.255 pam postgresql
host all 10.0.0.0 255.255.0.0 pam postgresql
host all 81.2.78.40 255.255.255.248 pam postgresql

and the /etc/pam.d/postgresql (which isn't perfect - and you will definately have to change this
to suit your system. Taking a copy of /etc/pam.d/login is a good way to start).

#%PAM-1.0
auth sufficient /lib/security/pam_unix.so likeauth nullok
auth required /lib/security/pam_deny.so

session required /lib/security/pam_unix.so

Warning - if your network is insecure, then you should be using an SSL connection
to the postgres server - otherwise your passwords will be wandering around in plain text.

Re:Dropping Columns finally supported

[TheLink]

Yep none of that DDL DML bullshit. You can assume transactions are transactions in postgresql, except DROP DATABASE MYDB; ROLLBACK; isn't supported as of current versions, but I haven't seen anyone complaining about that ;)...

A nice Postgresql party trick to show to Oracle DB people:

(pseudosql)
begin;
lock oldtable;
create table newtable (wantedcolumns modifiedcolumns);
select wantedcolumns from oldtable into newtable
drop oldtable;
rename newtable oldtable;
create indexes on newtable;
OOOPS I screwed up.
No problemo.
rollback;

Tada!!! Everything works like before.

So this new feature just makes dropping columns more convenient - esp if you have FKs or cannot afford rebuilding indexes.

Link.

Re:Dropping Columns finally supported

[bovinewasteproduct]

Check the mailing lists, but it was do to standards.

BWP

Re:Dropping Columns finally supported

[Anonymous Coward]

2. In that conversion, the .adp file contained the forms, reports, and macros. (...)

You can store the queires as views on the PostgreSQL server - no problem there. In 7.3 procedures can return a set of data now - though, I'm waiting for reports from the field to come back and report that it's working well before I jump on it myself.

You guys are confused. Access is not a "Database Management" program in the sense that Postgres is.*

Access is a GUI Forms and Reporting tool. It uses a database system included with Windows (MS Jet), or MS-SQL.

I suspect there's some other OSS tool that attempts to replace Access, but it's not Postgres or MySQL by themselves.

(* In PC tradition Access is sold as a "desktop database" program, but in fact the database management stuff is seperate from Access.)

Re:Dropping Columns finally supported

[Anonymous Coward]

Frankly, I'd rather have both easy at start and easy longterm. I can't see the reason for trading off one for the other.

Shocking arrogance

[Burgundy Advocate]

From the oracle-killer dept.

Um. Right. Of course, since it's an OpenSource database, it's obviously better. I'm sorry, but that's not quite logical.

I've gotta say, that kind of logic runs rampant in the OpenSource community. Linux is a "Windows Killer". Gimp is a "Photoshop Killer". Abiword is a "Word Killer". Apache is a "IIS Killer". Yet none of these systems are really any better than what they are supposed to kill -- they're all playing catch-up!

Did you ever stop to think where you'd be when you finally kill your idols and have noone left to copy?

If regular products are replaced by shoddy knock-offs, the incentive to innovate will be gone. Do you want to live in a world where things like the GUI, 3D graphics, wordprocessing, webserving, and other commercial products were never developed?

I didn't think so. Your arrogance will come back to hurt us all if you don't lay off it.

Re:Shocking arrogance

[Anonymous Coward]

So what are you trying to say? You don't like Open Source?

Re:Shocking arrogance

[D4Vr4nt]

Um. Right. Of course, since it's an OpenSource database, it's obviously better. I'm sorry, but that's not quite logical.
Isn't cheaper better? *cough*
And besides if it wasn't for bashing "idols" where would all the Gates jokes go? :P

Re:Shocking arrogance

[haystor]

You need to remove Apache from that list. Its IIS that has been playing catchup. Sure they've taken a few benchmarks at times for displaying static pages but that's it. Apache is the standard by which other web servers are judged.

Re:Shocking arrogance

[reaper20]

It's an Oracle killer based on the fact that 80% of Oracle installations out there are overkill.

Seriously, look at the amount of Oracle installations out there. Now how many of them _need_ any of those features? Is it worth the extra cost of Oracle? More than likely it's all some marketing crap that eveyrone is led to believe than "only Oracle can do this".

My PHB is like this. He insists we use Oracle because we need an "industrial strength database", for a db with 40,000 records. Argh! Oracle is freaking expensive, we got Larry Ellison crusing around in some damn yacht race on our bill. In our case, Postgre would be an Oracle-killer, it's just getting people past that fact that Oracle is unnecessary for alot of applications.

Re:Shocking arrogance

[Anonymous Coward]

You are obviously a troll. Apache is not playing "catch-up" to IIS. It is the other way around.

Re:Shocking arrogance

[Corvaith]

While for most of the others, I'll agree... Apache is a 'shoddy knock-off'? Whah?

I'm not about to switch to Linux for my laptop, I'll admit, though I might load it onto my other machine once I've got it fixed, just for fun. I happen to be very enamored of Photoshop. But, at the same time, while I write my school papers in Office XP--I do my pleasure writing in OpenOffice.org because it has features that MS Office doesn't. (Two words: Page styles.) And Apache has served a lot of websites well for a very long time.

Open Source doesn't automatically make something good--but it doesn't automatically make it bad, either. Maybe you should try actually evaluating these products on their own merits, sometime?

Re:Shocking arrogance

[Brown]

Apache is a "IIS Killer"

...conveniently ignoring that IIS is of course playing catchup with Apache, not the other way round!

http://www.netcraft.com/survey/

- Chris

Re:Shocking arrogance

[Anonymous Coward]

Umm Apache is *NOT* playing catch up in ANY FIELD (features OR marketshare). Apache beats IIS in both feature set and popularity.

Linux has far better features and is used more widely in the server market.

In terms of home user market share, Linux is playing catchup. But only in terms of MARKET SHARE.

learn some crap.

Re:Shocking arrogance

[the eric conspiracy]

Do you want to live in a world where things like the GUI, 3D graphics, wordprocessing, webserving, and other commercial products were never developed?

With the exception of wordprocessing all of the innovations you cite were developed by academic R&D teams, NOT by commercial software vendors.

This one exception, wordprocessing was NOT an innovation arising from the computer revolution - it's roots go back to IBM Selectric Magcard typewriters.

All the commercial vendors have done is copy, copy and copy.

Citations:

GUI - Stanford Research Institute Augmentation Center
Wordprocessing - IBM Typewriter Division
3D graphics - Evans and Sutherland, UofUtah and Harvard
Webserving - T. Berners Lee, CERN

The fact is that if the closed source behemouths were to dry up and blow away, it would probably enhance innovation by reducing the barriers to entry in the marketplace imposed by the likes of Microsoft.

After all, what VC is going to fund a new wordprocessor these days? VC's know all to well what Microsoft would do to any new market entrant that starts to gain traction. They have the horrible example of Netscape to look back on.

Re:Shocking arrogance

[Jondor]

so it simply makes sure it IS death and it stays that way..

Re:Shocking arrogance

[WetCat]

Just curious: how many front-end connections to back-end can PostgreSQL hold as its maximum. How many can Oracle do?

Re:Shocking arrogance

[prell]

hes right! remember when all other OSes copied microsoft by adding GUIs and threading and secure filesystems. oh wait..

Re:Shocking arrogance

[spanky555]

My PHB is like this. He insists we use Oracle because we need an "industrial strength database", for a db with 40,000 records. Argh! Oracle is freaking expensive...

I couldn't agree more, but I'd also like to expand this to MS SQL Server - it also is expensive (for the enterprise edition) and its PR is even more adept at brainwashing PHBs. For a relatively small DB, a PHB CTO at the last job suggested we continue to use MS SQL Server and give them even more money for a shitty DB, because he wanted to add another instance of the DB, and pay 15K or whatever for that license, too....we could easily do with Postgres (or MySQL for that matter) but he thought he was some sort of DBA....his other suggestions were to do all coding in the DB, too, using stored procedures (when you learn how to use a hammer, everything starts to look like a nail), but that's another whole story altogether. A freaking idiot, but now he's busy running that company into the ground, and I have no job.

Oracle or maybe DB2 should be used for the truly big databases, and Postgres for the midsize and maybe MySQL for smaller projects, IMHO. MS SQL Server is *so* frustrating because anyone who can point and click in that GUI admin tool thinks they are DBAs, and hence you have to deal with fuckups from the likes of these people - I have seen it *literally* dozens of times. These same dipshits will go and directly modify tables in this GUI instead of in the proper place: a modeling tool that has archives of changes. It's also frustrating because of the various misfeatures I've run into over the years.

But what I've noticed is that the least qualified people are the ones who are the ones makes decisions on key things like these. I've also noticed they are apt to look to the dumbest programmers or "admins" in the staff to "help" them. So I'm sure that people will continue to use shitty products like MS SQL Server because it can enable low-paid bungling fuckups to be "productive".

Re:Shocking arrogance

[mrobinso]

> My PHB is like this.

Instead of "telling" your PHB, why not try "showing" your PHB. Reimplement your db in postgres, take your PHB by the hand, and demonstrate the effectiveness of your alternative.

I've always found "doing" achieves far better results than "talking".

Mike

Re:Shocking arrogance

[Anonymous Coward]

That's hilarious.

FWIW, I once fixed up a company hopelessly wedded
to Oracle by essentially replacing all their
reporting systems by just:

• Dumping all tables as ascii
• Using Unix cut/join/sort to put them together
• Using Unix awk to make the reports they wanted

The end result ran under 10 minutes, while the old
crap took overnight.

My motto has always been, get the sh*t out of
the fabled "DB" as soon as you can. Then you
can use awk/python/perl whatever to actually
do something useful with it.

Re:Shocking arrogance

[telecaster]

We've been running Postresql in production for over 3 years, we have hudreds of thousands of rows for each customer that uses our product. Fast? Please, it screams. Our initial choice was Oracle, but we couldn't eat the cost, it would have cost about $12,000 just for one server -- nuts if you ask me.
We chose PG because it had 90% of what Oracle had and less the fat. Postgresql is far easier to get running and far better on the memory footprint, it runs in around 8mb's... big whoop. If you know anything about Oracle, its hardly good about memory, and one bitch of a product to get running (right).
Lastly, I'll say this about Postgres, its in our opinion, and this is from 20 years of experience with Oracle (I go back to the Oracle 6.24 days when I worked for Prime Computer and ported Oracle to the 50 Series machines for Prime), that Postgres is much more stable out of the box on Linux than Oracle could ever be, Oracle is as buggy as it gets, and don't let Larry fool you. Its got bugs.

As far as this dude throwing shit at open source and saying that the commercial counterpart is better? Possibly, on the desktop you might be on to something, but I firmly believe that you CAN'T beat PHP, Apache and Postgresql as an application platform for 99% of the Web world out there. The three products in question that I mentioned are far better products than ANYTHING on the commercial market right now... And yes, they are Open Source.

Re:Shocking arrogance

[slashuzer]

Instead of "telling" your PHB, why not try "showing" your PHB.

This is not always possible. Some org's are very rigid in such matters.

Re:Shocking arrogance

[Its, not It's Guy]

In the true spirit of /. pedantry, please note that "it's" is a contraction of "it is", whereas "its" denotes that "it" is in possession of something (in this case roots that go back to IBM Selectric Magcard typewriters).

Best Wishes,
Its, not It's Guy

Re:Shocking arrogance

[Anonymous Coward]

SPELLING FLAMES CONSIDERED HARMFUL

Every few months a plague descends on the network called the spelling flame. It starts out when someone posts an article correcting the spelling or grammar in some article. The immediate result seems to be for everyone on the net to turn into a sixth grade English teacher and pick apart each other's posting. This is not productive and tends to cause people to get angry with each other.

It is important to remember that we all make mistakes, and that there are many users on the net who use English as a second language. There are also a number of people who suffer from dyslexia and who have difficulty noticing their spelling mistakes. If you feel that you must make a comment on the quality of a posting, please do so by private email, not on the network.

In our loop we often talk about losing weight. Many members will write that they need to "loose weight." Other members will read the incorrect spelling of "lose" and cringe ... but it will be very poor Netiquette to write that person and say: It's "lose weight", Dummy, not "loose weight. Loose weight is that stuff that hangs off you after you LOSE weight."

Re:Shocking arrogance

[Its, not It's Guy]

I think you're looking for the loose, not lose guy - I'm someone different entirely. And while I hardly think these posts are flames, if they are then they're grammar flames, not spelling flames. :-)

Best Wishes,
Its, not It's Guy

Re:Shocking arrogance

[Anonymous Coward]

You're right on a lot of those, but Mozilla being the "IE Killer" is entirely true.

Re:Shocking arrogance

[mrobinso]

> This is not always possible. Some org's are > very rigid in such matters.

Yup, and it's a real shame, because both postgresql and mysql have grown in leaps and bounds for the last couple of years. Mysql has been trying to add some sorely missed features, and postgresql's performance has been steadily improving. These two items seem to be the major gripe in the mysql/postgres wars.

I suppose there will always be that [unfounded] mentality that free-as-in-beer stuff couldn't possibly be as good or better than the expensive stuff. Oh well. They can keep spending millions unnecessarily, and we'll keep smiling because we know better. :P

Mike

I'll be good, I promise. Now gimme back my karma.

Re:Shocking arrogance

[kristjansson]

Apache is a (sic) "IIS Killer"

I think you have it backwards. IIRC, httpd was originally written by NCSA at the University of Illinois at Urbana, on some flavor or another of BSD or SYSV Unix (BSD, if I remember right...), and is older than NT3.1, at least for purposes of availability. Apache is the end result of a bunch of server admins patching the NCSA sources after NCSA quit supporting httpd (A Patchy Web Server).

The concept of the GUI (and the mouse, for that matter) date back to Xerox PARC in 1975. Incidentally, that was the same time MSwas founded. IIRC, they were selling a BASIC interpreter for some architecture or another of Intel 8008 or 8080 (anybody know the answer to that one?), probably for PL/1, out of Albuquerque, NM. Apple released the first real GUI desktop machine with the LISA in 1983. Windows 1.0 was released around '85 or '86 (the "look-and-feel" suit of Apple vs. MS was basically tossed out because Apple's UI was too close to the PARC Star UI).

As far as invention and innovation go, look to Universities and pure R&D shops. Don't look to Microsoft, unless you want accounting and marketing innovation. DOS has a rather interesting history, as does IE, at least as far as the licensing deals go.

"Do you want to live in a world where things like the GUI, 3D graphics, wordprocessing, webserving, and other commercial products were never developed?"

Most, if not all, of these technologies, originated either on pure proprietary platforms, long since extinct, or on some flavor or another of unix. Word processing in its most raw form has existed since computing cycles became cheap enough for it to be practical. 3D really took off with SGI IRIX and SGI GL, and moreso with OpenGL. In all fairness, MS did join the OpenGL steering council, but they needed to find a way to get around the limitations of the Windows GDI under NT 3.1 so that they could go after the CAD/engineering market.

I will credit you with your point about the arrogance, and to a lesser degree, the "chasing taillights" syndrome. Many people will not try something presented to them with the "[insert commercial product here] Killer" as the main advertising point.

Re:Shocking arrogance

[/dev/zero]

Actually, having done app development with both PHP and the Jakarta tools, I'd say you can't beat Jakarta and PostgreSQL as an application platform.

Re:Shocking arrogance

[Anonymous Coward]

People often make this argument just before they implement the solution in Access.

It sounds great on paper, don't it. Unfortunately you're never around when the solution fails.

Re:Shocking arrogance

[telecaster]

Hmmm... I've done some Jakarta and Java, for that matter and I have to say that as much as I love the language, I find it sloooowww... I think PHP's Postgresql api is fairly easy and fast. I can't think of anything that beats it in performance, and if you've tried JDBC you'll not its not a speed demon.

I think i'd rather do a native C++ PHP module than actually instantiate nine gazillion class libraries in Jakarta/Java/JDBC.

There was a recent article, someplace, that has Yahoo! switching over to PHP. If you know anything about Yahoo, they want speed. They must know something we don't know for them to ditch their propietary C++ stuff in favor of PHP.

Re:Shocking arrogance

[/dev/zero]

Depends on what you're doing. For what Yahoo! does, I think PHP is probably the ticket. There are quite a few things that I use PHP for.

For business apps, which tend to be fairly complex with lots of business logic, I think Java brings more to the table. Yes, there's a performance cost in the VM layer. The tradeoff is better tools (look at all that Jakarta offers, as a starting point), a more robust language, and a more featurefull environment (servlet container).

Re:Shocking arrogance

[Anonymous Coward]

Don't know... I've never seen a published comparison of the two.

Re:Shocking arrogance

[Anonymous Coward]

In Holland "Its" is a gay club.

Re:Shocking arrogance

[telecaster]

I agree the toolset for Java is better, and for business logic, you MIGHT sway me into believing Java has a better way of encapsuling the business logic -- but I'm not completely sold on this. If we're talking a Web application that accesses a database, does some XML and XSL, I have to be honest, I think PHP, Sablotron, Xalan, Apache and Postgresql are a far better application platform choice because of the performance, and its ability to scale vs. Java. IMHO.

Java is a heavy monster that requires a very large footprint on the machine, and I have NEVER seen a jsp or server container site scale all that well. You ever notice why the high traffic sites NEVER use Java (or if they do, they have an array of Sun boxes to make it scale well)? Amazon, Yahoo, ebay, Slashdot, Google, etc. etc. They choose to use the concept of "get in, get out" -- where if a page accesses a database, its very fast and very efficient and a very tiny mode of operation. I think Java is great, but the execution path of a servlet is almost three or four times as much overhead as something like PHP. When your talking PHP and the Postgresql layer, your basically at the bare metal, its going to be faster.
I know, The argument is the development environment and is it worth the cost of performance for better development -- For me, And I have over 16 years of doing software development, have never been a fan of trading performance for "easier development". I think the customer has to use the application a lot longer than it takes to develop or maintain it, why make them pay the price for the developers needing easier development tools or a better "language". Sure, I love a great tool or language just like the next guy, but if the customer has to pay the price and watch and hourglass or wait for a page refresh -- that great tool is NOT going to make it onto my development platform.

But here's my thoughts on this:

1. Its not the language, its the design: PHP and Java both support the concept of "classes", arguably, PHP is not as powerful or as robust -- but if your an Object person, and you need them, they are there. To me, I've written so many languages in my life, that its almost a laughable argument as to "what language is better".

2. Java has better tools: Yes, they have MORE tools, I would agree with that. However, and this is where I've actually had to bite my tounge on Java because I think the tools out there pale in comparison to (I can't believe I'm saying this) to Visual Studio. I think the benchmark for good developer tools is actually Visual Studio, which everyone seems to try and mimic, but can't.

I've used JBuilder, Visual Cafe and Eclipse, and you know what? I end up going back to Emacs and the javac because I don't trust the damn tool, or the development environment was unstable or slow. JBuilder, the better of the bunch out there is OK for dialog based stuff, but when we are talking about server-side? Nah, Emacs will do.

Oh, and don't bring up some $10,000 tool, as I'm sure there are great ones out there, but lets face it, we're comparing it to Emacs and "free".

Lastly, I think its a testimoney to Postgresql that we're talking about the tools around it, and not making a fuss about the database itself. Going back to the original thread, I think Postgresql is probably one of the BEST open source products out there and deserves to be hailed as viable Oracle competitor, just as Linux is hailed as a threat to Windows on the server market.

Re:Shocking arrogance

[Anonymous Coward]

This is configurable at postmaster startup; it is limited by available shared memory and buffer space. Each backend needs a minimum of 16Kb.

Re:Shocking arrogance

[Anonymous Coward]

The problem with Jakarta is, the developers of Jakarta have not taken any concrete action with regard to the fight against terrorism. As a result, almost 200 Australian died when a couple of Jarkata users detonated a carbomb outside a popular nightclub.
Jakarta is a tool of terror.

Im not trolling but.....

[Anonymous Coward]

My/Postgesql are NOT SUTIBLE for enterprise level databases. I am an adminstratior for a corporationwho has a 50 million row database which needs to be dynamicly Queryed at a rate of 2000 queries a second, need dynamic replication, and complex 3d boolean queries and only the high end MicrosoftSQL server data center edition can currently proivide this. Sometimes, its WORTH paying for Quality software.

Re:Im not trolling but.....

[Anonymous Coward]

I agree, MYSQL borks at anything above 8000 rows, and Postgres is miles away from professional databases such as micrsoft SQL and Oracle. They ARE a joke for REAL WORK.

Re:Im not trolling but.....

[spanky555]

If you aren't really trolling, and you really think it's worth paying for quality software, why would you pick a crappy RDBMS like MS SQL Server over something with a far better track record, like Oracle or DB2?

Re:Im not trolling but.....

[abirdman]

Enterprise noun def: an organization so large that the people who buy systems don't know how they work and so have to hire someone who does.

PostgreSQL can handle the 50 million rows provided the data structures are well-designed, and according to their press release they can handle the replication (it's always dynamic). The query rate of 2000/sec is more a question of the server hardware, server configuration, and network infrastructure than the database software per se. I don't know about "complex 3D boolean queries" but I know for a fact that PortgreSQL can process big, ugly, inefficient queries pretty well.

What makes M$SQLServer and Oracle more "suitable" for enterprises is their add-on tools (both have loads, though of varying quality) and the fact they maintain service centers for when the DBA throws up his hands and gives up. From my perspective though, the reduced licensing costs (even for large installations, we're talking about hundreds of $'s times thousands of users!) could pay for a lot of up-front hardware and ongoing code review. If I were starting a largish e-commerce project today, I'd start on Open Source just because or the reduced up front cost. You don't become among the richest people in the world by giving anyone a good deal.

Re:Im not trolling but.....

[symbolic]

If mysql is 'borking' at anything over 8000 rows, I'd take a real hard look at my design if I were you. I did a conceptual demo for a client once, where we sucked up a raw data file from their mainframe - 65000 rows, five tables, and the largest table had about 18 columns. The import took all of 45 seconds, and there was absolutely no performance problems of any kind. This was being assessed to determine how much faster this would have been compared to a RAD-based solution they were currently using. But 65,000 rows is small potatoes.

Re:Im not trolling but.....

[AlexCV]

65000 rows in 5 tables is not small potato, it's inconsequential. It will fit all in RAM on anything recent. Performance will obviously be miles better.

A more realistic test would have been 1000000 rows in 15-20 tables. That's more on the line of the kind of system I've seen. Maybe on the small side.

For the record, give Pg 2 processors, 1 gig of ram, fast raid scsi discs and configure it properly. I swear you'll think your dealing with 65000 rows no matter what you do (but don't screw up the indices, use EXPLAIN a lot.)

Alex

Re:Im not trolling but.....

[GT_Alias]

Wow, that's way off base. If you're dealing with a lot of SELECTs and not a whole lot of (concurrent) INSERT and UPDATEs, MySQL performs just fine. To say MySQL borks at 8,000 rows implies you haven't really used it, or you've used it in a very poor fashion -- i.e. wrong tool for the job or bad database design.

And just b/c MySQL might be the right tool for a given job doesn't mean that job isn't "REAL WORK". Kinda like the whole Linux/Windows debate...it's all about suiting the app/OS/whatever to the given task. And MySQL performs very well for tasks that have lightweight database needs.

Re:Im not trolling but.....

[Anonymous Coward]

Please stop humoring this fucking retard. If he were right, then mission-critical projects undertaken by Yahoo, Nasa, Motorola, and Ericsson would all be in trouble. Somebody cut this fucking moron poster's hands off, as he has no business touching a keyboard.

Re:Im not trolling but.....

[WasterDave]

What makes M$SQLServer and Oracle more "suitable" for enterprises is ...[snip] ... the fact they maintain service centers for when the DBA throws up his hands and gives up.

Redhat will quite happily sell you a version of Postgres that they then support. For a price, of course, but a shitload cheaper than either Oracle or DB2.

Dave

Re:Im not trolling but.....

[symbolic]

A more realistic test would have been 1000000 rows in 15-20 tables. That's more on the line of the kind of system I've seen. Maybe on the small side.

We tried to arrange for some time on a massive beowulf cluster equipped with a 3 TB RAID and several GB of memory installed so that we could test against 1000000000 records and 52.7 tables, but they were booked solid.

Needless to say, I can assure you that based on the client's needs, the test I outlined (as inconsequential as it may seem), was quite appropriate.

IN SOVIET LITHUANIA

[hasse]

Mysql postgresql YOU!

Re:IN SOVIET LITHUANIA

[Anonymous Coward]

Go back to FYAD.

Re:IN SOVIET LITHUANIA

[Anonymous Coward]

Nesupratau juoko. Gal kas paaiskintu?

Aciu.

DAMNIT!!!

[Dave2 Wickham]

DAMNIT!
I literally downloaded the old version of PostgreSQL yesterday... Damn you :-P.

Oh well, a bit of fun bandwidth loss to look forward to (I'm on 56k, BTW...)

Re:DAMNIT!!!

[Anonymous Coward]

Whew, I only figuratively downloaded it.

Re:DAMNIT!!!

[bovinewasteproduct]

Time to learn how to use cvsup...:) Hit google up for it.

BWP

Oh yes you are..

[Marcus Green]

OK so you have found software that addresses your problems and that accounts for the conditions that are vital for about 0.0001% of people who are looking for a database. For everyone else who is looking for a database solution PostGres, MySQL etc might just be worth a look in.

Go on then.

[caluml]

Cue all the people telling us why it's better than MySQL.

So - why it it then?

Re:Go on then.

[kcbrown]

Cue all the people telling us why it's better than MySQL.

Well, if you insist... :-)

It's better for certain things (most things, actually). PostgreSQL is a bit more feature-complete as a SQL database than MySQL is. MySQL is improving, certainly: it now has transactions and such. But PostgreSQL has quite a bit more: triggers, rules, stored procedures, and views, for instance.

In terms of speed, MySQL is faster for certain specific operations but that speed comes at the price of database integrity: the lack of rules and triggers means that it is not possible for the database to enforce consistency between tables. One must thus trust applications to do the right thing, which is generally not wise.

It's like the difference between an OS with memory protection and one without. The one without may be faster for certain things, since the OS doesn't have to worry about messing with page tables and dealing with page faults of various kinds, but the price is that you now have to trust the applications running under the OS to do the right thing and not touch memory that doesn't belong to them.

As I said, MySQL is faster for certain things. But PostgreSQL is reportedly better at handling lots of concurrent transactions than MySQL. It's not clear, then, that MySQL is much better than PostgreSQL, if at all, under high load situations. And if it isn't, then there's really little reason to go with it over PostgreSQL.

Finally, even if MySQL is faster, it's not likely to be so much faster that it is the difference between success and failure. And I can tell you this: experience shows that the initial requirements of a project are often vastly different, and usually much less demanding, than the final requirements for the same project. So it makes more sense to go with the most capable database backend you can lay your hands on, as long as it remains within your budget (your real budget: remember that you're likely to spend a lot more money than you expected, if only because the requirements will change over time). That means going with PostgreSQL over MySQL, if given the choice. You have to make the decision early because changing your database engine mid-project is extremely difficult, especially if your code was written to work around the limitations of the database engine, as it almost certainly will if you're using MySQL.

These days I don't think the question should be whether you should go with PostgreSQL instead of MySQL. It should be whether you should go with MySQL instead of PostgreSQL. PostgreSQL should be the default choice these days, because it is so much more capable at the same price.

Re:Go on then.

[Anonymous Coward]

mysql is reportedly much easier to install on windows. I wouldn't know, but the few times I've had to install PostGRE on windows boxes for other teammembers it HAS been a nightmare.

It wouldn't surprise me in the least if people still insist on using mysql simply because they know how to set it up on their desktop.

Re:Go on then.

[abirdman]

Here's just one example:

create view [viewname] as [query]

Over time data structures in a database are invariably found to be "less than conducive" to all the data and report requests that pile up on the typical DB specialist's desk. Some VP of marketing decides he needs a report that shows distributions of product lines by sales period or something. No problem, we create a tortured multi-table join, often with unions (oops, can't do that in MySQL, use intermediate tables instead), inner joins, and sub-selects (oops, can't do that in MySQL either, have to create some more intermediate tables), but eventually, voila, here's the new marketing report.

Now, everyone joins the VP-MKTG's bandwagon and wants their new reports compiled and summarized that way. In MySQL, without support for views, every query ends up having to be constructed again, including the tortured logic involved in having no sub-selects or unions. With view support, all we need to do is toss that awful query into a view, and select out of the view. It's not gorgeous, but it works. And you can even hand off the view to the other developers, so they don't get stuck in the quicksand of recreating the logic from scratch.

And now, because PostgreSQL supports functions that return datasets, we can toss all that logic into a function, and call that instead.

So, in answer to the question of what can PostgreSQL do that MySQL can't do: unions, subselects, views, functions. All are time savers. Lacking them, we can devise work arounds, but having them is very, very nice.

Re:Go on then.

[Zeut]

Postgresql still does not have a native port to Windows, you still have to use Cygwin. One of the top priorities for the 7.4 release is native win32 support. I will be very happy when that is done.

Re:Go on then.

[chriskl]

MySQL 4.0 supports UNIONS. I'm a PostgreSQL fan - but it's still important to get the facts straight.

Chris

Re:Go on then.

[Al Al Cool J]

For simple applications I far prefer working with MySQL. The following things really annoy me with Postgres.

1. No simple equivalent of SHOW COLUMNS. Consequently, it's hard to find any high level language API that allows you to read the structure of a database. Python... nope. Tcl... nope. Perl... nope. (Or at least not the last time I checked).
2. Proper keyboard support for the psql client is broken by default on many of the linux installs I work on (debian seems to be particularly bad). It's *very* frustrating trying when the arrow keys and tab completion doesn't work.
3. The documentation is tough reading. Very formal. Obviously done by comp-sci academics.
4. No matter how hard I try, I cannot grok the date-time functions, which I find to be extremely cryptic. For example, the simplest way I've found to calculate the number of days elapsed between today and a given date is:
   

   int4(date_part('epoch',timestamp('today')-mydate)/ 60/60/24)

   
   Ugh, painful!

Al/

Re:Go on then.

[Christopher Cashell]

MySQL 4.0 is still officially considered "beta" software, though. You can't fairly count features unless they're found in the released stable version.

Re:Go on then.

[Christopher Cashell]

1. No simple equivalent of SHOW COLUMNS. Consequently, it's hard to find any high level language API that allows you to read the structure of a database. Python... nope. Tcl... nope. Perl... nope. (Or at least not the last time I checked).

Erm. . . doesn't "\d TABLENAME" give you the information that you want? Or perhaps I'm not understanding what you're looking for. It's been a little while since I've used MySQL, but I remember this being the equivalent syntax.

2. Proper keyboard support for the psql client is broken by default on many of the linux installs I work on (debian seems to be particularly bad). It's *very* frustrating trying when the arrow keys and tab completion doesn't work.

I've actually never had a problem with this, at least not on any of the Debian, Red Hat, or NetBSD machines that I regularly use PostgreSQL on. Arrow keys and tab completion seem to work perfectly for me on the Debian box I'm currently working from.

3. The documentation is tough reading. Very formal. Obviously done by comp-sci academics.

All of it? Have you tried the two PostgreSQL books that are online (full text, free)? Both are fairly good general purpose books, and I'd say they're quite accessible to average techies.

Practical PostgreSQL
PostgreSQL: Introduction and Concepts

No matter how hard I try, I cannot grok the date-time functions, which I find to be extremely cryptic. For example, the simplest way I've found to calculate the number of days elapsed between today and a given date is:

I won't really comment here, as I've never had any real problems dealing with dates. I can't offer you a simpler form for that calculation off hand, but I'm pretty sure one must exist.

Re:Go on then.

[Bronster]

No simple equivalent of SHOW COLUMNS. Consequently, it's hard to find any high level language API that allows you to read the structure of a database. Python... nope. Tcl... nope. Perl... nope. (Or at least not the last time I checked).

$dbh->func($table, 'table_attributes');

works fine for me in perl.

Proper keyboard support for the psql client is broken by default on many of the linux installs I work on (debian seems to be particularly bad). It's *very* frustrating trying when the arrow keys and tab completion doesn't work.

So file a bug report with your distribution. You can't blame poor packaging on the postgres people. I really haven't had these same problems, and I would suggest that the client checks if a library it needs is installed, and doesn't provide readline without it. Perhaps the package only suggests libreadline as a recommends rather than a requires and you haven't installed it? (I don't have any potato machines still running postgres to check against)

The documentation is tough reading. Very formal. Obviously done by comp-sci academics.

True, the documentation sucks. This is the main thing that annoys me.

No matter how hard I try, I cannot grok the date-time functions, which I find to be extremely cryptic.

Not to mention changing the functions without changing the documentation with 7.2.1, fuckers. This cost me many hours of debugging only to find that ('oh, and we removed a couple of bits of functionality without EVEN LOOKING AT THE HOWTO ON THE WEBSITE') it was a compatibility change.

Moving right along. I don't do particularly many calculations with dates inside postgres, um - I think they should really import a decent date handling library and dispose of those icky functions - I remember writing expressions at least 3 lines long to work out the time period of a month (1st through [28-31]st of a calendar month) at least 11 days previously. Messy.

Re:Go on then.

[rycamor]

I agree that the documentation could be presented much better. Better searching, better user interface, better examples, etc...

The whole structure of your database (and many other details) are available inside the pg_* system tables. Once your learn how these work, you can just make queries against these, which can return any aspect of the table structure. (in a far more sophisticated way than MySQL, by the way)

Also, I have to comment on the date/time issue. I found out, through a very painful piece of experience (a project that was all about dates), that it is impossible to rely on MySQL to manage dates properly. Mainly this is because MySQL doesn't support proper constraints for date column types. Any year can have a 0 month, as well as a 1-12 month. Any month can have a 0 day, as well as 1-31 (notice, it doesn't deal with differences in the number of days in each month). The result, A year can actually have 13 months, and every month can have up to 32 days. This just begs for data corruption!!

PostgreSQL's approach to date manipulation is more complex. That's because it is CORRECT. Every aspect of date/time management is available, including the all-important INTERVAL datatype, which is a dream for serious time-related projects. (and it is probably a dream to imagine it will ever appear in MySQL's TODO list)

By the way, casting to INTERVAL is the correct way to deal with your "elapsed days" problem:

(now() - mydate)::interval

This is usually all you need when dealing with this, if you are using it for other date calculations. But, if you really want to get just the days as an integer, without the chance of seeing '1 year 2 months' in the output, then your above expression should be updated to:

int4(extract(epoch from (now()-mydate))/ 60/60/24)

This is still somewhat ugly, but here is the beauty of a true DBMS: just use it to create a function (stored procedure) that you can call anytime, which could look something like:

days_elapsed(now(),mydate);

Now, is everybody happy?

Re:Go on then.

[Anonymous Coward]

you sir are an idiot!

No simple equivalent of SHOW COLUMNS. Consequently, it's hard to find any high level language API that allows you to read the structure of a database. Python... nope. Tcl... nope. Perl... nope. (Or at least not the last time I checked).

\? or \h may be too difficult for you to grasp
In fact, from perl its even simpler... why don't you perldoc DBI instead of looking for a handout.

Proper keyboard support for the psql client is broken by default on many of the linux installs I work on (debian seems to be particularly bad). It's *very* frustrating trying when the arrow keys and tab completion doesn't work.

Uh, recompile with readline support you imbecile

Re:Go on then.

[Al Al Cool J]

Erm. . . doesn't "\d TABLENAME" give you the information that you want? Or perhaps I'm not understanding what you're looking for.

"\d TABLENAME" only works in the interactive client. It's not a statement that you can pass from a script through an API. SHOW COLUMNS however works just like an SQL query. Consequently, the MySQL database APIs for languages like Tcl and Python can easily get at that information. To do the same in Postgres you have to figure out the structure of the pg_* system tables (which I spent the better part of a day trying to do with little success -- are these things even documented anywhere?)

Apparently though I was wrong in that the Perl API does work (thanks Bronster!). I don't really have first-hand experience with this in Perl (I do in Tcl and Python). I know that the Perl wrapper library that a guy in my company developed can extract table structures from MySQL but not Postgres. My mistake for jumping to conclusions. The fact remains though that it is much easier to create such an API for MySQL over Postgres.

Arrow keys and tab completion seem to work perfectly for me on the Debian box I'm currently working from.

I'm envious. It's broken on the Debian box of our only Postgres-using client, and it's also broken by default on Mandrake 7.2, which is what's running on our older linux machines. That's 75% of the systems I work with Postgres on. I agree with Bronster that this is a problem with the distro, not Postgres, but regardless of who's to blame, it hasn't helped warm me to working with Postgres :-)

I agree that feature for feature, Postgres is far superior. I agree that for serious database use, Postgres is the way to go. But my company develops web-based applications that have to work with both Postgres and MySQL. So all of the amazing stuff that Postgres can do and MySQL can't is pretty much useless to us, as we generally have to code to the lowest common denominator.

Oh, and thanks for the links to the online books. I'll check 'em out.

Al/

Re:Go on then.

[Anonymous Coward]

well thank fuck for that.

Re:Go on then.

[imroy]

# No matter how hard I try, I cannot grok the date-time functions, which I find to be extremely cryptic. For example, the simplest way I've found to calculate the number of days elapsed between today and a given date is...

Er, so you found the date_part() function, but you couldn't just use it to get the 'days' from your calculation? Try this simpler version:

date_part('days',now()-mydate)

BTW, I just checked that snippet on a (slowly growing) table of temperature readings. Not only does it work, but it doesn't round up like the more complicated version.

Re:Go on then.

[Al Al Cool J]

Thanks. That is simpler (though I still have to run int4() on it so that I can do modulo arithmetic (%).)

I actually found the original function from a web/usenet search, after failing to figure one out on my own. Hopefully future generations of people in the same boat will google their way to this thread. :-)

Al/

MOD PARENT UP (+5, Funny)

[unterderbrucke]

n/t

Why Slashdot

[Anonymous Coward]

Posting anything on PostgreSQL and MySQL will invitably lead to meaningless post over which is best, PostgreSQL or MySQL; and I also mean posts like this. ;)

Drop Column

[farnsworth]

Drop Column
PostgreSQL now supports the ALTER TABLE ... DROP COLUMN functionality.

HURRAY! this has been my biggest annoyance with postgresql since I've started using it. there are workarounds for older versions, but they become arduous when you have a lot of existing data.

this is a *very welcomed* implementation.

YOU FAIL IT!

[YOU FAIL IT!]

You should be ashamed that you FAILED! Infact, by the time you FINALLY came round to reveal your spectacular FAILURE to the world, you had already been defeated by two ON TOPIC posts!

Like John Romero driving a Sinclair C5, you are truly a FAILURE of galactic proportions!

YOU FAIL IT!

Is there support for replication?

[RelliK]

see subject

Enunciate!

[SEWilco]

"Nearly a years worth of work is out."

Speak more clearly.
"A years" does not make sense.
You obviously meant to say "eight years", but you need to speak more clearly.

Re:Enunciate!

[Tribbin]

There is only one (1) worth he is talking about and that is a worth of multiple years.

Nearly a years-worth of work is out.

It is work that is worth (working-)years.

At least in Dutch this sentence makes completely sense.

<dutch>
Bijna een jarenwaarde aan werk is uitgebracht.
</dutch>

Re:Enunciate!

[abirdman]

Perhaps "a year's worth" would be more clear. Regardless how obvious it seemed, they did not mean eight years (though it might be eight--or more--programmer years). I think the use of an apostrophe to indicate the possessive is optional in this case.

imho

Re:Enunciate!

[SEWilco]

I think the use of an apostrophe to indicate the possessive is optional in this case.

No'pe, aint O'ptional.

FINALLY!!

[Anonymous Coward]

Dancing Girls
The PostgreSQL now includes a number of beautiful dancing girls

I can't tell you how long I've been waiting for this feature! Now I can get rid of Oracle for GOOD!!

Kudos to the PostgreSQL team!

Re:FINALLY!!

[IrvineHosting]

I don't know about dancing girls...

but with the money you saved using PostgreSQL instead of Oracle, you could afford to get a prostitute every week for the next two years.

and that counts for something!

Hm

[Anonymous Coward]

Is there any good way to get a PostGreSQL DB to connect to a MS SQL macihne?

Re:Hm

[Anonymous Coward]

try an ethernet cable

online backup?

[Anonymous Coward]

So does PostgreSQL have hot backups yet? or is it still the toy its always been.

Re:online backup?

[telecaster]

Yes its got a very good back up system.
pg_dump, which is by far the ONLY way i'd want to back up. Not only does it dump your data, but it dumps your tables, views, stored proc's, etc. And to re-recreate your database, you simply import the script...
Now if your talking replication? Thats a different story.

High Availability

[slumpie]

Any experience with high availability postgresql cluster?

I don't get it.

[Anonymous Coward]

n/t.

WARNING! Goatse.cx link in parent!

[Anonymous Coward]

There you go

You could at least have used SQL injection...

[Anonymous Coward]

... to create a g*tse redirect on postgresql's own site! A postgresql.com link would have been so much more credible, and SQL injection is not that difficult to pull off!

Bogus warning!

[Anonymous Coward]

Maybe we should have another category: Score:-1, Spoilsport

Re:Cool! PostgreSQL always runs on my iBook

[squiggleslash]

My boss is particularly fond of it. He uses it across our extra-intranet to leverage our Extreme Programming synergies across the multiple platforms we support. With PostgreSQL, we can proactively manage our solutions database, enabling drilling down through datasets to empower our associates in building our paradigm.

Of course, he uses it on a Thinkpad, but you have to spend money to make money.

table (record) based functions

[hector13]

Does this mean stored procedures returning record sets is finaly supported?

This was the main thing stopping me from using postrgres. Every other (commercial) database I have used allows this, and I couldn't believe postgres didn't when I installed it.

Re:table (record) based functions

[rtaylor]

Thats exactly what it means..

SELECT * FROM function()...

Re:table (record) based functions

[Malcontent]

Well Postgres always had a rule subsystem which allowed you to code custom recordsets. Unlike MS SQL stored procedures you could also allow writes!. In other words posrgres rules allow you to code table like structures with both read and write access.

I always thought this was the coolest thing ever in a database. Now postgres has given you another tool in functions which return recordsets.

Re:table (record) based functions

[Doctor Faustus]

Every other (commercial) database I have used allows this

MS SQL Server didn't support this until 2000. I've been working without it, because we're just now getting around to upgrading from SQL Server 7.

Re:table (record) based functions

[hector13]

Huh? I am pretty sure I was able to return a record set from a sproc back in version 6.5 (earlier too perhaps).

I know for sure that sybase sql server 11.9 supports it, so I would assume MS SQL server 7 would.

I dont meen running:

SELECT * FROM EXEC sp_some_sproc

I am simply talking about a stored procedure that returns a recordset to the client.

Re:table (record) based functions

[Doctor Faustus]

Oh, sorry. Yes, I meant the "SELECT * FROM EXEC sp_some_sproc" version.

If stored procedures can't even return recordsets to the client, are they strictly for server processes?

had to be said

[carpe_noctem]

Ok, I'm probably going to get modded down for saying this, but what's up with this increasing trend of /. being a forum for software release announcements? I might be mistaken here, but that's what freshmeat does on OSDN, iirc. I think as far as the /. community is concerned, seeing releases for new kernels and new mozillas are the only pieces of software that people really care about. Beyond that, how many people are going to rush out and download the new postgresql (not even a major version increase, anyways).

Re:had to be said

[NineNine]

As far as I'm concerned, Mozilla is a dead lump of shit floating in space. It's useless. Linux. Ho hum. Nothing exciting. It runs. But an alternative to Oracle (if PostGre ever becomes that) really is news. But yeah, Slashdot is becoming... "Version 1.1.2.3.4 of gnuwhatever released" alternated with "M$ is 3vil!". It's pretty damn stale.

Re:had to be said

[archeopterix]

Ok, I'm probably going to get modded down for saying this

Next time I see this line I'm gonna mod down without reading the rest of the post. C'mon people, karma is not the dick size. Well, ok, it is the dick size (mine is excellent), but still whining about supposed modding down is annoying.

Re:had to be said

[zmooc]

Well one of the most important subjects on slashdot is the broader acceptance of open source software. Things have been going pretty fast lately; we have a state-of-the-art office-suite, database-server, browser, movieplayer etc blabla you name it. Many of those things weren't ready for prime time until very recently. Now they're getting there _fast_ so it is to be expected that some of the (now much more frequent) major milestones in the history of broad acceptance of open source get posted on slashdot. Though I agree with you that not every Linux kernel release should be on Slashdot. But this release of Postgresql has some VERY important features which will make it much more interesting to use as a replacement for Oracle or Sybase.

Re:had to be said

[Anonymous Coward]

You're probably going to be modded down for saying that.

Doh

[Anonymous Coward]

PostgreSQL was developed in Lithuania. MySQL was developed in Sweden. This is a European joke.

Crapola

[Anonymous Coward]

This topic is crapola! Bury it with -1 posts!

• My penis feaatures:
• 25% in your ear
• 45% in your mouth
• The remaining percent covered with girl glaze
• Michael Jordan has no dick

JDBC

[Anonymous Coward]

Anyone have any experience using PostgreSQL in a production environment with JDBC?

Re:JDBC

[aftermath09]

have a look at: http://jdbc.postgresql.org/doc.html

Re:Cool! PostgreSQL always runs on my iBook

[Richard_at_work]

Whoa, buzzword attack!

/me gets tinfoil hat

unicode support and proper collation

[zby]

I've found it really disinformative when thay claimed unicode support in 7.2 but for most scripts there is no proper collation. That was not so easy to find out since the unicode support was generally really hart to set up and I was assuming I must had made some mistake.
Is it better in 7.3? I can't find anywhere any list of scripts supported.

Re:unicode support and proper collation

[Giant Robot]

When I installed postgresql 7.2.x, unicode was supported automatically! I use it to store chinese and japanese text in a dictionary type lookup database.

The beauty was that the java code would automatically convert the original Big5/JIS/other encodings to UTF8 first, and then JDBC would store the unicode into postgresql.

Re:unicode support and proper collation

[zby]

As far as I remember it well I had to run the initdb with unicode locale set in the environement. And that was not that obvious task.

And my question - how about sorting?

Re:unicode support and proper collation

[Giant Robot]

(btw, I'm running 7.2.1-2. I think the previous versions don't do it by default when I tried..)

I would just imaging that the characters are sorted by their unicode sequences, just like ascii characters are sorted by their ascii sequence.

If you are refering to manually sorting the data for speed, my project was pretty simple, so I didn't and don't really know how.

What type of of application are you building that needs unicode?

Cheers

Re:unicode support and proper collation

[zby]

The problem with sorting is a bit more complex - my language (polish) uses mostly ascii with some additionall characteres. The additionall characters shoud be sorted IN the sequence of the ascii characters - so sorting just by unicode does not work for this. And I believe this is quite common case.

The application was just meant to store names in some european languages - ideally with originall spelling.

Er, not really

[oGMo]

That article is 2 years old. MySQL has advanced greatly since.

No, it hasn't. A summary of the list of missing features:

• No subselects
• No SELECT INTO TABLE
• No stored procedures and triggers
• No Foreign Keys (!!)
• No views
• No -- as a comment parameter

Of course, they give crap rationalizations for each, and/or that "it's planned for [distant version of MySQL]". Of these features, only the last might be considered trivial, and even that is quite a pain if you're trying to write some portable SQL.

The others, particularly the lack of triggers and foreign keys, make this a data integrity nightmare for anything nontrivial.

Sure, sure, "but you can do it all in code": typical response. You know, that was their response to lack of transactions, too. "Too slow", "you don't need those". Right. You could just write a whole database in your code, too. The point of using a RDBMS (and, lacking relations of any sort, makes MySQL just a DBMS) is reliability so you don't have to constantly worry about these things.

PostgreSQL has all of the above features, and quite a few more. It's an OORDBMS. (Yes, this is very cool, and lets you do some very nifty things.) It's got better-than-row-level-locking (MVCC; MySQL does table locking only.) And all the other things people have mentioned here.

MySQL is a toy database, and should be treated as such. Not just for transactions: for all the things that make a robust RDBMS.

Re:Er, not really

[ruckc]

Wrong my friend, it isn't a toy. I have successfully used it many times over the past year and a half for a multitude of things, yes it lacks features, but guess what, who really gives a hoot. I can do everything i need to with it, and i use it in critical places, because guess what fellas, its fast, and I prefer to work around its lackings than put up with PostgreSQL that has never been anything but a headache.

Re:Er, not really

[Anonymous Coward]

* No subselects
* No SELECT INTO TABLE
* No stored procedures and triggers
* No Foreign Keys (!!)

InnoDb provides foreign keys.

* No views
* No -- as a comment parameter

Is this important? But -- works fine for comments.

Re:Er, not really

[Anonymous Coward]

Given it lacking ACID compliancy til very recently... well... you're an idiot for using it in "critical places".

Re:Er, not really

[Anonymous Coward]

you can make the same case for foxpro and dbase....

Re:Er, not really

[Kashif Shaikh]

Buddy, all your points are good -- you seem to prefer/require a ACID dbms' for your needs. But you argue the wrong way as you belittle MySQL efforts without showing how it stacks up to the ACID databases.

MySQL on the other hand(you can read the FAQ and other docs for proof on MySQL's website) has taken the approach of removing features that aren't that worth while to the "mainstream person". Rather than tell you to RTFM, here's a quote directly from the horses mouth:

1.7 How Standards-compatible Is MySQL?

This section describes how MySQL relates to the ANSI SQL standards. MySQL Server has many extensions to the ANSI SQL standards, and here you will find out what they are and how to use them. You will also find information about functionality missing from MySQL Server, and how to work around some differences.

Our goal is to not, without a very good reason, restrict MySQL Server usability for any usage. Even if we don't have the resources to do development for every possible use, we are always willing to help and offer suggestions to people who are trying to use MySQL Server in new territories.

One of our main goals with the product is to continue to work toward ANSI 99 compliancy, but without sacrificing speed or reliability. We are not afraid to add extensions to SQL or support for non-SQL features if this greatly increases the usability of MySQL Server for a big part of our users. (The new HANDLER interface in MySQL Server 4.0 is an example of this strategy. See section 6.4.2 HANDLER Syntax.)

We will continue to support transactional and non-transactional databases to satisfy both heavy web/logging usage and mission-critical 24/7 usage.

MySQL Server was designed from the start to work with medium size databases (10-100 million rows, or about 100 MB per table) on small computer systems. We will continue to extend MySQL Server to work even better with terabyte-size databases, as well as to make it possible to compile a reduced MySQL version that is more suitable for hand-held devices and embedded usage. The compact design of the MySQL server makes both of these directions possible without any conflicts in the source tree.


I don't consider MySQL a toy database, thats akin to calling Linux a toy operating system, where the real companies use AIX,Solaris, HP-UX, etc.

Re:Er, not really

[the eric conspiracy]

yes it lacks features, but guess what, who really gives a hoot.

I do. I really dislike having to repeatedly write code to get around the lack of features in MySQL. MySQL developers have often pointed out that there are workarounds for the missing features, however they don't talk about the impact this has on application performance, quality and development time.

Re:Er, not really

[ShieldW0lf]

A better word would be "amateur" database.

Re:Er, not really

[einhverfr]

InnoDb provides foreign keys.

Well,MySQL supports foreign keys (for use in joins, etc.) but fails to support any mechanism of enforcing referential integrity.

Look-- for a basic dynamic web page, MySQL is fine-- it is simple, it is fast, and it lets you write sloppy SQL code. But for a large application, you want something that conforms to every major part of the ANSI standards, for portability and maintainability. The point of RI enforcement is to ensure the quality of your daya-- SO WHAT if you can't back up or restore a single table? At least your data is internally consistant. And for many applications, that is a serious issue.

Don't get me wrong-- there are many places where MySQL is OK. But for any serious application, the lack of RI enforcement is a real biggie.

Re:Er, not really

[Anonymous Coward]

Since when is PostgreSQL or any other modern DBMS implementation Relational? If you allow Nullable columns then you don't have a relational db. In fact SQL 92 isn't even relational.
See www.dbdebunk.com and the various articles therein.

Re:Er, not really

[Betcour]

There's still an amazing array of critical applications that work in Cobol on systems that predates ACID relational databases. If you think doing payroll processing on non-ACID database is suicide, try to imagine what is sending rockets into space or controlling nuclear missile silos is in the 60s with systems that only support flat files...

Re:Er, not really

[Betcour]

Well,MySQL supports foreign keys (for use in joins, etc.) but fails to support any mechanism of enforcing referential integrity.

You are wrong. With the InnoDB table handler the foreign key integrity is also enforced automatically inside MySQL.

Re:Er, not really

[Progoth]

I have successfully used it many times over the past year and a half for a multitude of things, yes it lacks features, but guess what, who really gives a hoot.

um....me? read the other pro-postgres posts for some details. granted, when you're writing crappy little one-table apps, mysql is fine. I rewrote my website from perl/mysql to php/postgres, and I could have just as easily used mysql. but I started development on a book lending library for my linux user's group, which I thought was going to be relatively simple. not done yet, but I've already made extensive use of transactions, row-level locking, subselects, triggers, and foreign keys. when you use good database design, and want real data integrity, these things basically become required.

anyway, plenty of people give a hoot. mysql has its place, but it is, in fact, basically a toy.

Re:Er, not really

[Anonymous Coward]

Yeah, but considering that the MySQL manual contained anti-Foreign Key FUD for years, it will take them a long long long time to convice the world they now support them, and it will take even longer before the P3r1 d00dz figure out how to use them.

Re: answer

[Anonymous Coward]

No, pad're HiswikiP&Qs....whatever is NOT as functional and intuitive as M$ ACCESS for 90% of home/office tasks. Let the dweezles drools, ACCESS ruleZ.

Re:Question. I cry foul!

[jdkane]

That posted link to "why MySQL sucks for non-toy apps" (also known by the proper title: "Why Not MySQL?") in the previous post is in fact 2.5 years old, and therefore 2.5 years late to the party. Obviously a lot has happened since then in the development of PostgreSQL and MySQL software packages. It should be illegal to post such old references because old information can be very misleading. However, fortuneately, MySql has a website with current information on it (go figure).

Maybe it was a troll, but here's the proper information:

- Transactions are available to MySql.
MySQL 3.23 release has several major features not present in the 3.22 or 3.21 releases. These include: full-text search, replication between a master and many slaves and several new table handlers that support large files and transactions

- Is that information about foreign key constraints in the MySQL manual? .. Why, yes!

Are record locks really a non-existent issue? Maybe the MySql user manual can shed some light on that point: "Performing a read in share mode means that we read the latest available data, and set a shared mode lock on the rows we read."

- The stored procedures and triggers are not here yet. Thank goodness something in that old link doesn't need to be refuted!

So yes, you might want to check out that really old critique of MySql, but then again you might want to look at the MySql.com website if you want current information. Then you can compare the newest PostgreSQL to the newest MySQL.

I want replication. please.

[Anonymous Coward]

Main feature I've been waiting for replication.

As of a couple of months ago none of the replication options for postgres were any good. Most were unreliable, offered very small features or very hard to set up.

Some looked like they had promise, but were not there.

Please, please, please, add replication to the next release :)

I also wish performance for simple case dbs was faster. eg key value dbs compared to the performance of sleepy cats berkley db.

I'm sure there would be a *lot* of money to be had if someone were to make a good replication system. Possibly releasing it blender styles? Or offering to implement replication for businesses for a fee?

Perhaps one of the postgres groups could ask for donations from some of us users so some developers could work on it full time. I know I could easily convince my boss to cough up for it. Almost any business that relies on postgres could be convinced to chip in I think.

Wowsers!

[Anonymous Coward]

It has an Answer Table, just like Paradox for DOS had 10 years ago. I'm impressed!

Seriously, this is good news from a usability standpoint. Dr. Mark Pauker must be proud of these guys.

Re:Cool! PostgreSQL always runs on my iBook

[SoSueMe]

Who moderated this as "Troll"?
There are some seriously Humor-impaired people with Mod points!

Re:Cool! PostgreSQL always runs on my iBook

[johnfoobar]

Yeah, but there still aren't any ShutdownItems on Mac OS X to complement the StartUpItems, so it looks like I have to write an AppleScript app so my client can shut down PostgresSQL safely. And they have to remember to run it every time they want to switch off their iMac.

BAD Apple! No biscuit!

(Yes this is a plea for help in disguise. If you have solved the problem of running rc.d-ish scripts at shutdown time on Mac OS X 10.2 please leave details.)

PS On the plus side, PostgreSQL 7.3 builds with no errors on Mac OS X, unlike 7.2. This was important enough to me that I used the beta in my (development) code.

Congrats to all involved in this release anyhow.

Yours,
A grateful PostgreSQL User && a pissed off Apple Customer

OORDBMS?

[delmoi]

I remember playing around with Postgres a while ago and looking at the supposed OO features, and quite frankly they didn't seem very impressive at all. Basicaly just 'inheritance' of table structures. I mean... Color me underwhelmed. I can't think of a single reason why you would need this.

It didn't have nearly the same kind of features of true OODBs, What exactly is the use for Postgres's OOness?

Re:Cool! PostgreSQL always runs on my iBook

[astrashe]

I agree with you -- it was one of the few attempts at humor here that actually came off.

Are you saying slashdot is well run?

[delmoi]

Well, I would definitely call the management of Slashdot 'idiotic'. And I certainly don't have much faith in them technically.

MySQL was used for the US census's website, not for their central purpose. I would be very suppressed if they were actually storing the real data in a MySQL database.

And again, MySQL was used for a small part of Yahoo (the finance stuff) not for storing their link catalog.

Re:Are you saying slashdot is well run?

[Sivar]

I was being sarcastic. My point was that several very large websites such as Yahoo and Slashdot use MySQL with great success. Apparently the moderaters didn't get it either.

Related question re. Database development

[cute-boy]

This is slightly off-topics but anyway...

There have been some references to msAccess here, what I like about Access is the ease I can build an ad hoc database application (but where the data could be reused easily should there be a later requirement).

While Postgres sounds great, I want to know if there are and tools that approach this ease of development, within a linux environment. Ability to choose the back-end database would be a huge plus - I'd certainly give Postgres a go.

RG

Re:Related question re. Database development

[Malcontent]

The Kompany makes an access like tool.
pgaccess does some of what access does.
Openoffice has nice database developmet tools.
You can build database apps very easily with kylix and there are even open source repirting engines available.

If all of that is not good enough then you can always use postgres as a back end to access via ODBC.

Re:Related question re. Database development

[zulux]

Unfotrunatly there is no all-in-one rapid developemnt and flat-file database rolled into one, like Access, in the Unix world. But don't let that stop you from using Access though - it's a great tool for rapid development.

I use it all the time - you rapidly develope the small database, and when it outgrown the Access flat-file .mdb sotrage on about the fith concurent user, migrate the backend database to PostgreSQL and keep the Access front end. Once, the database get's really popular, migrate the Access front end over to Delphiand keep the PostgeSQL back end. The Access to Delphi transition should be done in stages - migrate the data enrty first, then migrate the reporting later.

Once that is donem and your database is really popular - migrate the front end again to Delphi to Delphi/Kylix and you'll be able to support Linux/FreeBSD and Windows desktops. People can VNC into a FreeBSD server that shares the Kylix app over VNC for other systems - Solaris, Mac, Psion.

Cool stuff.

Re:Related question re. Database development

[flacco]

I want to know if there are and tools that approach this ease of development, within a linux environment. Ability to choose the back-end database would be a huge plus - I'd certainly give Postgres a go.

I've just started looking at Rekall by theKompany.com (KDE etc). It's quite Accesss-like, and it runs on Windows, Linux, and Mac. It only costs $70 too!

Not as versatile/powerful as Access yet, but I think it may get there.

Re:Related question re. Database development

[HiThere]

I used to be willing to say that Access had good report design features, and that it's screen design (dialog) was better than Glade. I suppose that's still true, but...

The other day (last month, actually) I discovered that sometimes instead of spontaneously failing (causing me to need to recompile the programs, without changing anything..cute!) it would spontaneously corrupt. This time no error was generated, but the code started generating and saving incorrect data. Again the only fix was to recompile, but after that I started a much more vigorous campaign to move away from access. Currently I'm moving applications that were in Access into Java, even at the price of generating the reports via html.

To my mind, Access has proven itself unuseable for any serious purpose. It doesn't matter that it's easy to use (in some ways). What matters is that you can't rely on the answers.

N.B.: This problem cannot be replicated at will, and gives no obvious sign that it is present. Perhaps it doesn't occur in single-user applications. But as the only fix is recompilation, I don't believe that my code is at fault. And I do believe that Access is (errors that are fixed by recompilation have occured several times over the years).

Re:Related question re. Database development

[jbolden]

What part of open offfice has something like access?

don't use Access

[Anonymous Coward]

Even Paradox for Windows 8/9 makes a better web server than Access.

http://www.thedbcommunity.com/inet/qa.htm

Not exactly...

[delmoi]

The first time I tried using JDBC was with postgres, It was pretty simple, but I ended up using Accesss (and eventualy MSSQL) for production stuff, simply out of lazyness. I do recall that JDBC seemed to have a lot more capabilites on postgres then on the Microsoft stuff. (like, you could call moveFirst(), moveLast(), etc on resultssets).

But its entirely possible I just don't know what I'm doing. In any event, it seemed to work fine.

Unfortunately, not much has changed

[slashuzer]

see subject.

Maybe if...

[WebCowboy]

...mySQL had proper, full support for constraints Slashdot wouldn't post so damn many duplicate articles...

HAR HAR HAR...just jokin' around...pleeeeze don't kill me.....

PHP+PostgreSQL || PHP+MySQL

[FyRE666]

I've used MySQL with PHP/Perl for a while on various projects, and always half considered Postgres, but since many web hosts don't offer it (why?) never really considered it seriously (don't wish to rewrite a lot of db code - and yes I do know about PEAR with PHP, but it's a performance hit).

So, does PG+PHP match the speed of My+PHP? Is it as thoroughly tested? As I'm moving away from using Perl, I'd be really interested in seeing some benchmarks with this new version of PG...

Re:PHP+PostgreSQL || PHP+MySQL

[kcbrown]

So, does PG+PHP match the speed of My+PHP? Is it as thoroughly tested? As I'm moving away from using Perl, I'd be really interested in seeing some benchmarks with this new version of PG...

The question shouldn't be whether PG+PHP matches teh speed of MySQL+PHP, but rather whether the speed of PG+PHP is good enough for your application and, more importantly, whether it will scale reasonably well under load.

This seems to indicate that PG+PHP will scale better than MySQL+PHP, but that will certainly depend on the configuration of MySQL (in particular, which table type you use).

You should be a lot more concerned about the features of the database that you will require, instead of the speed, because once you select a database engine you'll have a lot of trouble migrating to a different one. Speed can always be gained by throwing more hardware at the problem if necessary. Database features can't.

Re:PHP+PostgreSQL || PHP+MySQL

[X_Caffeine]

In my experience, PostgreSQL is faster if your code is well written and carefully optimized (particularly through careful use of transactions).

Re:Question. I cry foul!

[scheme]

These include: full-text search, replication between a master and many slaves and several new table handlers that support large files and transactions

What they don't tell you is if you use innodb tables for transactions, you won't be able to back up your database without shutting mysql down unless you buy the innodb hot backup tool. So yeah, they have transactions it just doesn't work well with doing backups on your data.

Are record locks [mysql.com] really a non-existent issue? Maybe the MySql user manual can shed some light on that point: "Performing a read in share mode means that we read the latest available data, and set a shared mode lock on the rows we read."

Postgresql has mvcc (multiversion concurrency control) meaning that readers or writers don't block other readers or writers from accessing the same data.

The stored procedures and triggers are not here yet. Thank goodness something in that old link doesn't need to be refuted!

If you are going to start talking about vapor features then what about postgresql's plans to support point in time recoveries (pitr), redo logs, savepoints, and full clustering with multiple masters.

Incidentally, the link you point to says that stored procedures are planned for version 5 of mysql. Mysql is currently on version 3 with development work being done on version 4. Version 5 is a long time off. Triggers are something that the mysql developers will consider, and are not guaranteed to be implemented.

Getting back to what is present in the software now, mysql doesn't support stored procedures or triggers. Postgresql meanwhile supports triggers, stored procedures (written in python, perl, or sql), and rules (which allow you to intercept and rewrite sql queries).

Re: answer

[Anonymous Coward]

It is a fantastic video game.

Re:Cool! PostgreSQL always runs on my iBook

[Anonymous Coward]

Mod parent up +1 funny, and a 'lighten up, Francis' to the UNSAT moderator.

HHmmmmm...

[Jack Hughes]

... Now I'm just waiting for a pedantic post from the "It's not Its Guy".

postgresql use required account even for anonymous

[Jameson Burt]

I have worked at using PostgreSQL for 3 years,
but I have not used it yet because I have not
seen that anonymous users can use it.
I sense that its strong security prevents expected use. If I say anyone can read my tables but no one
can write to them, I would hope "anyone" would include even those without accounts.
The only way I found to do this in Debian
changed root permission to update postgres and the many Debian packages using postgres.

For three years, I continue to be optimistic about PostgreSQL, and every 8 months I look at it again, thinking I missed how both root can automatically update Postgres, yet users can anonymously use PostgreSQL when "allowed".

Re:Cool! PostgreSQL always runs on my iBook

[Anonymous Coward]

bahhahahahaha. nice one.

Trnasaction Log?

[Anonymous Coward]

Kudos to the postgre team. Does postgres have the concept of the transaction log? undo and redo? just curious.

Foul indeed...

[ttfkam]

Let's go down the list:

What the MySQL developers conveniently fail to mention is that if you use transaction-aware table types, performance drops dramatically. Under load with multiple concurrent connections, PostgreSQL is pretty close to the speed of MySQL or faster by default and blows MySQL's doors off when MySQL is transaction safe.

Regarding foreign key constraints, see note regarding transactions. And if you are really concerned about fk speed, you don't have to use them in PostgreSQL either.

Record locks hunh? It may surprise you to know that as a user, you don't need to explicitly tell PostgreSQL to lock tables with your queries. Ever. This a relational design issue. This should be handled by your database architect when they layout the table structure, rules, foreign keys, views, and triggers. You do have someone that designs the table structure ahead of time right? Sure you do. But why don't you have to explicitly lock the tables is PostgreSQL?
Maybe it's because PostgreSQL is smart enough to know when you need them without your help.

Transactions? Aren't those only for banks and e-commerce? Nope. Let's say you want to update all the users in Slashdot to give all of those loyal geeks one extra karma point. So you select on all users, grab their current karma, add one, and update the record. This has two problems: concurrency and completion. What happens if the user is moderated up or down in between the moment that the record is selected and the moment it's updated? Looks like the user has accidentally been given either (a) an extra point or (b) had a point taken away. Also look at what happens if the database goes down while doing the work (someone kicked a cord), who got the extra point and who didn't? Darn. Wish I had transactions...

So you use the transaction-aware MySQL tables. Wow! Performance has sure dropped out and we have to think about implementation details like locks. I sure wish there was a way to avoid stupid programming mistakes like forgetting a lock. Well...you could use just about any other database out there (including PostgreSQL).

As for stored procedures and triggers, you need not talk about features that aren't here *yet*. Version 4 isn't out of beta yet and you're hanging on a possible v5 feature? While we're at it, let's talk about how multi-master replication will appear in PostgreSQL by then. And didn't you hear? Microsoft's IIS will have its security holes patched up in two years too. Vaporware is vaporware. Believe it when you can download it.

------

Now then, on to personal gripes about MySQL above and beyond the ones I have listed above.

Benchmarks: On MySQL's benchmark page comparing PostgreSQL, they complain that no utilities are available for benchmarking but their own. This is not strictly true. No benchmark can be made because the syntax to the different RDBMSs are so dissimilar that none can be made currently without a strong bias. Stored procedure support, for example, would definitely skew results away from MySQL. But that wouldn't be fair for a benchmarking tool since MySQL doesn't support stored procedures. The same is true of triggers, rules, views, and other such "unimportant" features.

Of course MySQL's benchmark shows MySQL in a good light. They use only the feature set of MySQL to perform the benchmark.

They also mention on the page that they've contacted the PostgreSQL developers for tuning information and methods of improving the benchmark tool. I cannot express loudly enough that THIS IS A LIE! The PostgreSQL mailing list has many instances of developers reporting that they (a) never heard about this "contact" until someone pointed out the MySQL page, and (b) they have been ignored when they've tried to submit tuning techniques and other optimizations. Sounds like some people don't want their benchmarks to give the "wrong" results. Heaven forbid!

As it stands now, the benchmark is a year and a half old. MySQL is on its 53rd patch revision and PostgreSQL is two minor version releases later since this benchmark was released. Weren't you saying something about posting stale information? They still have a page complaining about vacuum bugs and the desire for a newer version of PostgreSQL that fixes the problem. ...and a year after the bug was fixed, do we see a benchmark update? Nope.

Feature comparisons: another source of info, it talks about the query speed on mostly read only data. Did someone forget to mention that flat files are even faster for mostly read only data?

It states that since MySQL has more users, it must be better than PostgreSQL. Funny how that logic doesn't seem to work for Windows. They use the same logic with the number of books. It wouldn't surprise me if there were more books on DOS than Linux. Does that make DOS better? Does that say anything about the relative quality of those books? No.

MySQL supports more APIs and languages. This is correct unless you want to count stored procedure languages. Oh wait, MySQL doesn't support stored procedures. (Yet! They'll be there in two or three years or so. ...whatever.)

It then touts MySQL's fine replication facilities. Hello people! How often has slashdot gone down due to database issues? Hardly a poster child for stability or reliability.

According to mysql.com, PostgreSQL doesn't have a unit test/regression test. It makes one wonder if they've even used PostgreSQL.

PostgreSQL is said to be deficient with ODBC. Too bad they couldn't provide any specifics.

I'll relax about the statement that MySQL had more functionality with ALTER TABLE. FYI for readers, that has just changed with PostgreSQL 7.3.

They are correct that PostgreSQL doesn't have MERGE. Instead, they use the SQL92 standard UNION. Does the same thing. And let's not forget about views. What was that!? MySQL has extensive use of non-standard syntax? Any queries you write in MySQL will only have a prayer of working on MySQL? Say it ain't so!

PostgreSQL has had full text search for a while as part of contrib.

I don't even want to start with "MySQL Server is coded from the start to be multi-threaded, while PostgreSQL uses processes." Aside from databases on Windows, this helps whom significantly? A clue folks: Apache HTTPd also uses processes extensively. Pure thread support was only really added for the sake of Windows. This is one of those times where stability and consistency are more important than raw speed. This is your data!

And on and on...

As a final note, I would like everyone to take a trip down memory lane with me and recall that the MySQL dev team didn't see a need for MySQL to have transactions or any other of those "fancy" things at all until a couple of years ago -- when everyone started to realize that MySQL wasn't really twice as fast as PostgreSQL even though MySQL was crippled feature-wise.

The MySQL has so much misleading information (apart from the items that are outright false) on the web site, I wouldn't recommend it to anyone who wants correct information, "current" or otherwise.

Re:Foul indeed...

[sheldon]

Welcome to the world of Linux advocacy where the motto is "Don't let facts get in the way of a good argument."

Re:Foul indeed...

[Anonymous Coward]

mod parent up... dead on!

Re:Foul indeed...

[Anonymous Coward]

They are correct that PostgreSQL doesn't have MERGE. Instead, they use the SQL92 standard UNION. Does the same thing.

No. A MERGE is more a like a view on a UNION. It's MySQL's poor-mans version of partioned tables like they have it in Oracle.

I don't even want to start with "MySQL Server is coded from the start to be multi-threaded, while PostgreSQL uses processes." Aside from databases on Windows, this helps whom significantly? A clue folks: Apache HTTPd also uses processes extensively. Pure thread support was only really added for the sake of Windows.

Well, for version 2.0 the Apache folks have re-created their architecture. Apache has now a number of memory/process models (called MPMs). For Unix there exists now a threaded version besides the traditional forking server.

Re:Foul indeed...

[TheLink]

Don't _ever_ have to lock tables in Postgresql?

I'm a happy postgresql user, but seems you know something which I would really like to know.

How do you avoid or deal with this situation?(pseudosql follows):

begin transaction;
do lots of stuff;
insert unique stuff into a table unless it exists in that table already.
commit;

Because you can't lock a nonexisting row, you can't use select for update.

You can put a unique index, but it means the "do lots of stuff" will be rolled back in Postgresql in event of a clash, and will have to be redone again. This is due to Postgresql's strict transaction semantics. Other RDBMs don't force the entire transaction to be rolled back for an insert error, so unique indexes are ok (you can say those RDBMs are nonstandards compliant, but seems many find that convenient).

So I resort to locking the table, then selecting, then only inserting if necessary (seems cleaner than "try insert and hope it doesn't clash" too).

How do you deal with this or avoid this?

Or a worse hypothetical situation: Insert 4 if 2 or 3 don't already exist. And in a concurrent transaction do insert 2 unless 4 exists. Unique indexes won't work for this.

Savepoints may not help either.

I haven't needed to do this sort of thing tho, but I'd use locks if ever have to. But if you really know a way to do it without locks, I'm VERY interested.

Re:Foul indeed...

[ttfkam]

No. A MERGE is more a like a view on a UNION. It's MySQL's poor-mans version of partioned tables like they have it in Oracle.

I've just learned something new. Thanks.

Well, for version 2.0 the Apache folks have re-created their architecture. Apache has now a number of memory/process models (called MPMs). For Unix there exists now a threaded version besides the traditional forking server.

The hybrid threading/forking MPM is used by UNIX (mostly Solaris and Irix), but the pure threading MPM is basically just for Windows. It makes Apache much faster on Windows at the expense of robustness -- if the one process goes down, down goes the whole shebang.

Re:Foul indeed...

[Anonymous Coward]

works the same way for Windows advocacy

Re:Foul indeed...

[imroy]

Welcome to the world of Slashdot, where trolls drag almost anything into a Linux/MS issue.

Re:Foul indeed...

[Anonymous Coward]

begin transaction;
do lots of stuff;
insert unique stuff into a table unless it exists in that table already.
commit;

Really?
How about..

select count(*) into from a table;
if i > 0 then
insert
endif

I think it would do the job.
I agree nested transactions would be great but any reason why this won't work?

Re:Foul indeed...

[TheLink]

Don't quite understand your example.

Anyway the 1st difficulty is that concurrent transactions don't see uncommitted inserts.

Even if they can (you reconfigure the pgsql transaction behaviour to see uncommitted data- not recommended!), the second difficulty is between the select and the insert, concurrent transactions can also do a select, in which case since the insert hasn't happened, the selects don't see it.

Nested transactions may not help for the bad case hypothetical scenario I mentioned where the stuff inserted isn't constrained by uniqueness, rather it has arbitrary constraints - e.g. insert 2 unless 3 and 4 already exist vs insert 3 unless 2 exists, and so on.

Arbitrary user locks could help without the performance hit of table lock, however if the db and apps grow large it's hard to ensure that everyone uses the same standard user lock, whereas when you lock the table you are sure nobody else can touch it. I've proposed arbitrary user locks, but I don't know pgsql and C well enough to implement it, and the pgsql dev team have lot on their plate already.

Thus the table locks.

(read pgsql as postgresql).

well then..

[Anonymous Coward]

since you mysql doenst have the concept of undo and redo... you really cant call foxpro, which happens to have more sql features than mysql and stores each table as a file like mysql, a toy.

Not good when you can get more integrity out of a foxpro database. they have "transactions" to ( an utter kludge of course but they do).

answer to my own question

[Anonymous Coward]

looks like they have checkpoints and WAL...

very nice.

Damn straight!

[ttfkam]

MySQL supporters are like people who believe that the moon landings were a hoax. No matter what contrary information they're confronted with, they still consider MySQL to be the best. It's all just an anti-MySQL conspiracy apparently...

Rational and complete arguments like yours be damned. :(

This is your data. If it's important enough to store, it's important enough to protect. ACID is not optional.

Re:Damn straight!

[Betcour]

If it's important enough to store, it's important enough to protect. ACID is not optional.

Since MySQL is ACID what's your point here ?

Re:Damn straight!

[ttfkam]

No, MySQL *can* be ACID-compliant. It is not by default. A new user would not necessarily know that they have to use an "atomic" or "transaction" table. MySQL is supposedly newbie-friendly. Yet they must determine advantages of MyISAM over InnoDB over plain tables. For a newbie user of PostgreSQL, they can just create a table and be done with it.

I disagree that ACID can ever be optional. Once again, if it's important enough to store, it's important enough to protect.

Re:Damn straight!

[Betcour]

Yet they must determine advantages of MyISAM over InnoDB over plain tables. For a newbie user of PostgreSQL, they can just create a table and be done with it.

Well if you are not experienced enough to make the difference between two table handlers, then you obviously won't be any more capable of using BEGIN/COMMIT/ROLLBACK correctly. Typing "TYPE=InnoDB" at the end of every CREATE TABLE is whithin any beginner reach, so I don't see why that would be a problem to someone who understand the concepts of ACID and the subtilities of transactions.

Once again, if it's important enough to store, it's important enough to protect.

That's absolutely wrong. Plenty of applications don't care about protecting what they store : caching systems, Web stats, etc. For all those, MySQL gives people the choice to use a non-ACID table handler (MyISAM) that is fast and simple. For everything else, there's InnoDB (ACID compliant, row level locking, etc.).

Re:Damn straight!

[ttfkam]

Well if you are not experienced enough to make the difference between two table handlers, then you obviously won't be any more capable of using BEGIN/COMMIT/ROLLBACK correctly.

You're absolutely right about using BEGIN/COMMIT/ROLLBACK. However, simple UPDATE statements also use transactions without that syntax. All statements are protected and atomic in PostgreSQL. You only need BEGIN/COMMIT/ROLLBACK for a group of statements. A newbie who doesn't fully understand ACID can still take advantage of the protections afforded by ACID.

As for MySQL, how does he know when to use InnoDB versus MyISAM, BDB, HEAP, MERGE, etc.? My point was that in other databases, the default behavior is to protect the data. You should have to choose speed, not safety. If InnoDB was the default, while I would still have reservations about MySQL, it would allay many of my concerns regarding its general use by newbies.

Once again and with feeling, "Joe Newbie doesn't automatically need to use every feature of PostgreSQL." Just as beginning programmers don't need to know how to do memory mapping, you don't need to write triggers. But if they ever need to use memory mapping, isn't it nice to know that it's there?

*** As a side note, transactions took me less time to figure out than SELECT, INSERT, UPDATE, and DELETE. Views took me two minutes. Triggers and rules took a little bit longer. I worry about folks who say that PostgreSQL is too complex to figure out. Intelligent database design takes much longer to figure out than query semantics. This "simpler" mantra from the MySQL camp sounds like a red herring these days.

If all you need is CREATE TABLE, INSERT, UPDATE, SELECT, and DELETE, then just use those constructs. Nobody's holding a gun to your head forcing you to use stored procedures. But if a problem comes up down the line that is perfect for a view, why not use it? The only reason I can see not to is if you are saddled with MySQL.

That's absolutely wrong. Plenty of applications don't care about protecting what they store : caching systems, Web stats, etc.

The data may not be relied upon for extended periods of time, but its integrity is still important.

You don't care about errors in your web statistics? Why gather them in the first place then? I'm not talking about storing last week's stats; I'm talking about using this week's.

The best case scenario for an invalid cache entry is that the app ignores it. The worst case is that bad data is used. This is not acceptable. If the data is unreliable, why are you using it? Why not pull from /dev/random? Is it because the data has some coherent meaning? If so, it's worth protecting.

If *you* decide to discard the data after a few seconds, that's your prerogative. But it should always (always!) be your decision as a user of programmer -- not the result of a data integrity issue.

Non-determinism is the harbinger of a little mind.

Re:Damn straight!

[Betcour]

However, simple UPDATE statements also use transactions without that syntax

True, but MySQL UPDATE/DELETE are also atomic in MySQL (even without using Innodb) so that's a non-issue here.

As for MySQL, how does he know when to use InnoDB versus MyISAM, BDB, HEAP, MERGE, etc.?

I've never known anybody who used BDB, HEAP or MERGE. MyISAM and Innodb are really the two table handlers that 99,99% of MySQL users use. As for newbies, they'll hopefully RTFM and the MySQL doc is pretty complete and clear on the subject. I'm sure even with PostgreSQL you also have to read the doc sometimes ?

If InnoDB was the default, while I would still have reservations about MySQL, it would allay many of my concerns regarding its general use by newbies.

I think there's a switch somewhere to make it the default table handler. But again if someone isn't capable of typing "type=innodb" when creating a table I'm not sure I'd trust that person with a SQL command line and table creation rights on any database...

You don't care about errors in your web statistics ?

No. For example I've a MySQL table that has a counter for everytime a page is viewed. I don't really care if the counter value is 2048674654 or 2048674652. A 1% margin of error is more than enough here. But even then, MySQL won't make any error here since the UPDATE is atomical so I'm sure every query is handled correctly and page view counted.

Nice!

[Anonymous Coward]

Im going to call in a few years and see if I can get paid lots of money to build a data warehouse , and clean alot of crappy data.

The sad thing this can happen in any database product with a poor data model... but this will be a given.

Re:I want replication. please.

[(void*)cheerio]

Me too.
I want replication too.

Thanks in advance.

So....

[NerveGas]

What's wrong with creating a PG user called "anonymous"?

steve

Re:Cool! PostgreSQL always runs on my iBook

[Daleks]

Yeah, but there still aren't any ShutdownItems on Mac OS X to complement the StartUpItems, so it looks like I have to write an AppleScript app so my client can shut down PostgresSQL safely. And they have to remember to run it every time they want to switch off their iMac.

Most services that have StartupItem's in /System/Library/StartupItems define three functions: StartService(), StopService(), and RestartService(). The StopService() function is executed at restart/shutdown, afaik. Just define the function to shutdown however you want it to, such as: pg_ctl stop -D DATA_DIR -m fast. You'll have to do some other things, but the easiest way to do this is to use a setup that is provided by someone else and just tweak it. Also, you should put the StartupItem in /Library/StartupItems, not /System/Library/StartupItems.

What the MySQL'ers dont understand

[esconsult1]

The thing that makes Postgresql completely different from MySQL is that it is an *active* RDBMS. By active, I mean that you can set it up so if it gets certain kinds of data, it can operate on that data to create new records, delete records, update other tables etc.

Postgresql has the *intellegence* built in. You can write all sorts of georgous functions to do stuff, especially if, like us, your shop uses several languages... PHP, Perl, Java, Python, C++, etc. Why replicate your business logic everywhere?

Transaction support and file/record locking are the least of your problems. If you do serious database stuff, at some point, you are *going* to want VIEWS, TRIGGERS, RULES, and STORED PROCEDURES (functions). Having this functionality in the database engine, instead of in your code makes a heck of a lot of difference when the time comes to scale.

Coming from a MySQL backgroud in a multi-language shop, we clearly saw the limitations, and decided to switch the entire database platform over to Postgresql a year ago.

We haven't looked back since.

Re:What the MySQL'ers dont understand

[Anonymous Coward]

With all due respect, your business logic should stay in the middle tier, not embedded in the data layer. (I'm assuming you're talking about n-tier enterprise development) If your BL is all in stored procedures, then you've got your system IO bound, (standard db queries) as well as CPU bound ( business logic calculations ) which is just a painful situation to be in if you want to ever scale up.

Re:What the MySQL'ers dont understand

[bwt]

Generally, you are right that "business logic" should be in the middle tier. A middle tier should have business logic, but there are a LOT of tasks that use SQL besides business logic.

For the data intensive operations used within the business logic, it is often helpful to encapsilate the data access using an API that resides in the database. This stops large amount of data going across the network. For example, an order fulfilment system might have a middle tier that decided whether an order could be shipped. It might call a "get_backordered_part_count" function and make a series of decisions based on the result.

Implementing that function in the middle tier accomplishes nothing because the same SQL hits the DB either way. If the logic in the function is complicated and can only be coded with several SQL statements, the extra network traffic and server round trips can be unacceptable.

A lot of other situations call for Stored procedures and triggers. For example, they are appropriate for writing a data integrity layer: if you denormalize your data model for performance, you need to write triggers to enforce the data integrity. Similarly, if you have raw data processing operations as is common for external system interfaces, data load and transformation operations, periodic jobs etc then it is good to write these in the data layer unless you'd rather push lots of extra data across the network for no identifiable reason.

actually what was holding back was no WAL

[Anonymous Coward]

getting WAl with undo and redo is what separates it from other databases that have "sql transactions"( mysql, foxpro and dbase). They have redo pretty much done.. but they need to finish undo. This is what makes an industrial multi user database. replications is good to.. but UNDO is much more important.

kudos to postgresql team.. they seem to understand why its important.

Re:trans are NOT the least.

[Anonymous Coward]

triggers and stored procs are good. but until WAL... it doesnt really matter. FOR REAL database work if transactions are the least of youre problems..you might as well as use FOXPRO! Any good DBA shouldnt let anybody write stored procedures who thinks otherwise.
Data warehouse guys get paid a ton of money cleaning bad data from Oracle databases by developers that have this attitude.

Question for you all...

[shepd]

What are the major differences in functionality between this and Oracle 8i?

Re:Question for you all...

[crm114]

postgresql $0 - oracle EE $40,000 PER PROCESSOR.

Re:Question for you all...

[abirdman]

Oracle has a much richer set of tools. It ships with DBA Studio, db*Loader, and SQLplus just for starters. PostgreSQL has nothing approaching the power and breadth of the Oracle software that comes with the database, though the psql command line interpreter is a good tool (I hope they've addressed the bug where Ctrl-C to stop scrolling occasionally sends the interpreter into la-la-land).

As far as strictly data engine features go, Oracle has:

materialized views (which can increase performance)

point-in-time recovery

data partitioning

more flexible backup and restore options

the ability to use Win NT passwords and security (I know, that's not a big issue for most)
PostgreSQL does have some support from third-party software. phpPgAdmin for example, is a great tool and gets better with each release. pgAccess is a tool that comes with PostgreSQL, but I haven't used it much in the last year or so, and I remember it feeling like it was not quite ready for prime time. But it's a Windoze front-end for the PostgreSQL server and that's a big deal! There are also a couple of books about it (with more on the way, I hope), one of which, Practical PostgreSQL, published by O'Reilly is very good and available online.

The biggest thing you get for all the money you spend on Oracle is a "known" product. There are hundreds of books on Oracle (many are awful--caveat emptor) as well as classes and trainers and consultants and DBA's everywhere in the world (repeat caution above).

I love PostgreSQL, enjoy working with it, and am delighted with the new functionality the developers chose to include in version 7.3 (almost like they read my mind). Face it, for most medium-sized projects, we create a connection to the database (I often use ODBC) and start firing off queries. It doesn't matter to our program which database is behind the connection. We want speed, efficiency, and safety for our data. Anything more is window dressing (or comfort for the suits). Long live PostgreSQL!

Oops, I'm going to get down-modded for editorializing... *sigh*

Re:Question for you all...

[Anonymous Coward]

about $35,000 per/ CPU

Re:Question for you all...

[Anonymous Coward]

> pgAccess [pgaccess.org] is a tool that comes with PostgreSQL, but I haven't used it much in the last year or so, and I remember it feeling like it was not quite ready for prime time. But it's a Windoze front-end for the PostgreSQL server and that's a big deal!

Note that pgaccess doesn't come bundled with (at least) Red Hat Linux 7.3's PostgreSQL package, but is available as a separate download from pgaccess.org.

Yours is the only shocking arrogance I see.

[Anonymous Coward]

You really should look at the Netcraft survey before making stupid comments about Apache playing catch-up to IIS. It makes you look foolish. The simple truth is, IIS is playing catch-up to Apache, and failing badly in its attempt. Apache has always been better than IIS, and always will be, no matter how much code the MicroSofties rip off from it. (You did know MicroSoft ripped off the BSD tcp/ip stack to get online way back when, didn't you? And that they've been playing catch-up to Unix since their MS-DOS days.)

True, the Gimp's UI sucks turds through a straw, and the OS office apps all need some serious polish, but Postgres is closing in on Oracle. It may not be ready run General Motors, but it is ready to run any small or medium business. And it'll do it a hell of a lot more economically. The only real benefit of licensing Oracle is knowing you're helping to fund Larry Ellison's America's Cup yacht. I got better things to spend money on.

Re:Yours is the only shocking arrogance I see.

[Anonymous Coward]

Netcraft?! You're an idiot.

Making The Switch?

[suwain_2]

I'm surprised this hasn't been asked yet...

Just today I found a need (not a chance to use, but a *need*) for a subquery. While contemplating copying and pasting (it's only like 30 rows) data between database tables, I happened to see this article.

How easy is it to switch over from MySQL to PostgreSQL? Is there a simple tool to convert between the two? (And as a sidenote... The machine I want to do this on is a third-hand computer, a 300 MHz, 128 MB RAM webserver... Am I going to notice a performance hit if I put PostgreSQL on it?)

Re:Making The Switch?

[Anonymous Coward]

I never made the switch - I started with Postgres because the MySQL license was far from "free" at the time. (Odd, the same people who beat up on KDE and still beat up on Qmail embraced the "free for personal use" license back then.) Anyway, I had a Linux/Apache box with Postgres being accessed on about 80% of the page views. The box was a Pentium MMX-200 with 128mb RAM. The site traffic was averaging over a million page views per month, and had climbed to a million and a half when the site was shut down (too many assholes on the message boards, and revenues were covering about 2% of the operating expenses).

It should be easy to write something in Perl that would dump from MySQL to Postgres. I tried screwing with MySQL and found it harder to set up than Postgres. For installation, "less INSTALL" and "./configure | less" will be your friends. When you have it running, there's a hell of an impressive html tutorial and user/programmer guide in the docs directory. Try it, you'll like it.

Re:Making The Switch?

[imroy]

Zachary Beane of GIMP fame, has a MySQL to PostgreSQL migration page with a Perl script and some advice.

soon, but not yet.

[crm114]

Actually, until postgresql provides point-in-time recovery, it is not suitable for 24/365 operation. (supposedly this is coming in release 7.4).

other than hot backups, postgresql is the open-source dbms, with almost all of the scalability and features of the leading commercial dbms's, and will probably surpass them just as linux has long ago surpassed the x86 competition in operating systems.

ORDBMS, not OORDBMS

[axxackall]

PostgreSQL has all of the above features, and quite a few more. It's an OORDBMS.

PostgreSQL is not OORDBMS, it ORDBMS - and that is a benefit of it. There are plenty of document on Internet explaining it from different angles.

Here is what make PostgreSQL ORDBMS different than other RDBMS.

The Third Manifesto us the best book covering the subject.

My own experience shows, that OOP paradigm should be neither ignored or overused. Languages, like Python, help you to use OOP only when you really need it. Similar way, DBMS, like PostgreSQL, helps you to use objects in your databases only when you really need them, and according with SQL'92 standard.

OORDBMS, offenly stays for ODBMS databases, with some SQL interface extensions. Primary such systems are designed for persistence of serialized objects. Therefore they inherit all problems related to ODBMS, and first of all - lack of theoretical support (OOP is based on heuristics), lack of ad-hoc queries, lack of reflection mechanisms, very tight-coupling, lack of on-the-fly db schema changes, lack of consistent replication, and so on, and so on. In few words, it's easy to use OORDBMS as ODBMS, but you cannot use OORDBMS as RDBMS (without additional SQL-compatible transaction manager) - it's ODBMS by it's nature.

In ORDBMS PostgreSQL relations are first class objects with all theoretical support of relational algebra, while inheritance and ADT are just addons. You can use ORDBMS as RDBMS (that's the way most of use use in real life), but you cannot use it as ODBMS (without additional OR-mapping manager) - it's RDBMS by its nature.

Re:ORDBMS, not OORDBMS

[Anonymous Coward]

> Here [slashdot.org] is what make PostgreSQL ORDBMS different than other RDBMS.

"404 File Not Found

The requested URL (PostgreSQLhasalloftheabovefeatures,andquiteafewmo re.It'sanOORDBMS.) was not found.

If you feel like it, mail the url, and where ya came from to pater@slashdot.org."

Grammar and spelling

[ttfkam]

Non-native speakers and dyslexics aren't excluded from the responsibility of communicating clearly and effectively. It may be more difficult for them, and that fact should be respected. But the skill's overall importance is not dependent upon the writer.

Mistakes happen, and flaming someone for making an honest mistake is inappropriate. Pointing out the mistake hurts no one. It presents the opportunity to avoid the mistake in the future and allow someone to become a more effective writer. If you choose to ignore the correction, that's your prerogative.

As a point of fact, bad spelling and grammar hurt non-native and dyslexic readers much more than native readers. Also, I find that non-native speakers tend to demonstrate much better skills in grammar than most Americans.

(Making sure I stay at least slightly on topic...)

MySQL doesn't care about spelling and grammar as much (No foreign keys, etc.). The lazy like that about it. PostgreSQL can be made a bit more anal retentive about your data.

At the end of the day, if the same person who wrote it into MySQL has to read it back, it works fine. PostgreSQL enforces the rules so that anyone -- not just the original author -- can read it back with perfect comprehension. :)

Comment removed

[account_deleted]

Comment removed based on user account deletion

One master, many slaves? Yes.

[leonbrooks]

PiT rollbacks, multi-master and the like are coming down the 'chute too but are too much mucking around to be worth the bother just yet.

I stand corrected

[ttfkam]

on the concurrancy issue. But once again, what if the update fails halfway through? How do you know which records have been updated and which still need updating?

Going to add and drop a temporary column?

With PostgreSQL (and any other ACID database), that same SQL you wrote is atomic. It either works completely or not at all. No special keywords. No extra steps. It just works.

When it's that easy in PostgreSQL, why would you use MySQL? Note that this is a write operation; Don't assume that MySQL is faster.

Re:I stand corrected

[ttfkam]

Simpler to install? It comes packaged by default in RedHat. For Debian, it's apt-get install postgresql. For a source install, it's ./configure;make;make install. Please explain to me how MySQL is easier.

Simpler? When you need to get a job done, you either have a simple database or a simple app. The amount of logic is the same.

If Joe Newbie doesn't need views, Joe Newbie doesn't have to use views. ...or rules, triggers, explicit transactions, or any of the other things that PostgreSQL can do that MySQL can't. It's not like PostgreSQL mandates the use of every feature.

On the other hand, Joe Newbie shouldn't need to learn about explicit locks, database integrity issues, workarounds because a feature isn't there, etc.

pg_dump is indeed required whenever a minor or major version change happens. That happens once a year or so. It's not like it happens once a week. And of course, that assumes that you upgrade according to PostgreSQL's release schedule. Why you would do that, I have no idea. More likely, you are upgrading/replacing the database box so you would have to dump the data no matter what software you were using.

If the version and hardware configuration are working well, why would you change anything (other than apply patches) by default?

As far as cheap hosting, that's fine. If the hosting company I have to work with has IIS, I'll be using asp won't I? An abundant option isn't necessarily better. It's just more abundant. Going from computer to computer, you're more likely to find Windows 98 than any other operating system. Does that mean it's better? Indeed, sometimes you have to use suboptimal tools to get the job done. This doesn't mean that you have to seek those suboptimal tools out.

If your data doesn't matter as much to you, it doesn't much matter what solution you choose or from whom you get your hosting. If you value your data, why not show it?

If you're hosting your own database machine -- not exactly a rare occurance -- and PostgreSQL and MySQL (and SAP DB and Firebird) cost the same, why on Earth would you choose MySQL?

MySQL is NOT easier to install in my experience. So why choose MySQL?

MySQL is less feature-rich. So why choose MySQL?

MySQL is not significantly faster. Is this why you choose MySQL?

MySQL cannot enforce data integrity.

Need I go on? If crappy hosting is MySQL's only ace in the hole, it's in more dire straights than I had originally thought. As far as I see, the biggest reason to stick with MySQL is that if you've spent too much development time with it (written app logic that the database sould've already handled), it would be too much trouble to switch.

Re:I stand corrected

[GlowStars]

But Joe Newbie is a newbie, and MySQL has all the features that he needs. So why would he have to learn about workarounds for features that he wouldn't need to use in the first place?

By that logic, Joe Newbie shouldn't have used MySQL in the first place, when simpler options like http://www.sqlite.org do exist. And guess what: SQLite supports transactions, triggers
etc. And since it is a library rather than a separate process running on your machine, installation and usage is a lot easier than MySQL.

Re:I stand corrected

[j7953]

Simpler to install? It comes packaged by default in RedHat. For Debian, it's apt-get install postgresql. For a source install, it's ./configure;make;make install. Please explain to me how MySQL is easier.

It's available for Windows with a standard Windows-style setup program. Lots of people who develop a small website will do so on Windows, even if their server runs a UNIX-like system. I think that if there were a similar easy-to-install Windows distribution of PostgreSQL, it'd have much more users.

Comment removed

[account_deleted]

Comment removed based on user account deletion

major feature missed in pgsql

[axxackall]

What PostgreSQL does really need is a better marketiing. Today 90% of enterprise programmers on a question "Why Oracle [Sybase, MSSQL, DB2]? Why not open source database in you project?" usually answer: "MySQL? We've tried. Doesn't really work for our projects." And if you try further "Did you try PostgreSQL?" then they counter-ask "Postgres-who?"

Too bad. When Internet burned tons of startup money, they hired lots of "so-called programmers" to do web-development stuff. No wonder that MySQL and PHP (and Linux!) was typically a choice. Who cares about transactions? Who cares about aspect separation? Just show the first home page to the boss!

The positive outcome: big bosses heard about Linux. Could Linux be where it is now without those so-called programmers? I doubt so. Professional Services from IBM and Microsoft would decide for you what technology to use after your boss has decided what partnership contract to sign.

But that wasn't the only way to "educate" big bosses about Linux: startup boom sparked Linux marketing boom creating OSDN, and others, including Slashdot. As a result, Linux is not self-selling itself: everyone loves Linux therefore Linux is protecting your investments. Crowd effect.

Could it be possible would Linux be really bad? No. Why it didn't happened to PostgreSQL? I think b/c PostgreSQL-based few companies didn't care about marketing. Or cared wrong. Or didn't have money to care. Compared to what? To Linux. Try to find some subject about Linux using google - besides mail-lists you've got many official documents, FAQs, HOWTOs, learning courses, support companies. Try to do it for PostgreSQL - mostly mail-lists and few official docs.

With improved better marketing PostgreSQL may become in one or two years as Linux today. Without good marketing only PostgreSQL developers, few enthusiasts and some Slashdot readers will know that not all open-source databases are so bad.

Re:major feature missed in pgsql

[Anonymous Coward]

"MySQL? We've tried. Doesn't really work for our projects." And if you try further "Did you try PostgreSQL?" then they counter-ask "Postgres-who?"

Funny story..

A year or so ago, a friend of mine was just getting started with dynamic web pages, and he's asking for help with his DB queries..

He asks me a question, which the obvious answer is to use a subselect.. so I give him some sample code, and the next day I get an email telling me that MySQL didn't like it..

So I tell him that he should try Postgres instead.. and his response was "Postgres? Is that a front-end for MySQL?"

*sigh*

Re:major feature missed in pgsql

[Khalid]

Open source software rely mainly on the network effect for it's development and for it's Marketing too (that's what is called viral Marketing) yes lately companies like IBM, Redhat and many others have done a lot to make Linux maintstream, but the main Marketing medium for Linux remains word of mouth.

On the other hand, Linux is the admiral ship for all open source software, it come bundled with it, Linux has chown that OS is viable and it's success will make OS prevail too. It only needs time.

Re:I want replication. please.

[sporty]

Me too.

I want replication too.

Thanks in advance.

Lotsa little things

[leonbrooks]

The OWNER option on CREATE TABLE, for example, will save a lot of fidgeting around. Some of the deeper changes show that they really do have a handle on their code. I expect them to surpass Oracle in every respect bar bloatedness and management-as-a-career by Christmas 2003. (-:

I do note a *lot* of `breakme' changes like integers no longer accepting an empty string as equivalent to zero. I guess this is where we find how portably we really wrote our code.

Re:Cool! PostgreSQL always runs on my iBook

[einhverfr]

I am the project administrator for a CRM app on sourceforge. We prefer working with PostgreSQL and highly recommend it in our documentation. Of course Sourceforge only has MySQL for the demo so we have to support it too ;-)

IIRC, 7.3 has ALTER TBLE DROP COLUMN (one annoying thing missing when you are trying to initially design a table), and it compiles under Windows, so my two complaints are now gone.

Actually-- try

[einhverfr]

If you compile the TCL support, you can also get an application (bundled with it) called pgaccess. This is sort of like an MSAccess clone written in TCL with PostgreSQL on the backend.

So yes, there are RAD tools similar to Access.

Re:Actually-- try

[cute-boy]

Hey thanks for the tip, just what i needed.

RG

How good is the JDBC driver these days

[matsh]

Does it support the latest JDBC standard, and does it work fine under heavy load?

Re:How good is the JDBC driver these days

[Anonymous Coward]

yes. it's been great since 6.3.

Re:How good is the JDBC driver these days

[stevecoh1]

Uh, no. On Postgres's own web site they link to a site showing a 42.7% level of compliance for Postgres 7.2 with the JDBC 2.0 API. While I'm not sure how important all these issues are, this level is not what I would call "good", let alone "great" and you don't have to get too intensively into it to find things that are missing.

I would be interested in knowing what the level of compliance with 7.3 is.

I would call the JDBC support in 7.2 "adequate".

Re:How good is the JDBC driver these days

[Nadir]

I wouldn't say it's awesome, but it does its job. the JDBC driver for 7.3 also supports JDBC3 (not all features though).

Not hard

[einhverfr]

Prior to 7.3, I used to do most of my prototyping in MySQL. Then I would convert the database over, and test it, then I would dump, add triggers, etc. and restore.

There are two scripts that come with PostgreSQL to take a database dump from MySQL and turn it into something you can use with PostgreSQL. So the switch is painless.

3 cautions, though ;)
1) PostgreSQL timestams are time-zone independent, and the database manager will correct for timezone if set. So if your timestamps are off by a certain factor, that is probably why.
2) Timestamp format is different, so you may have to rewrite any time-stamp parsers.
3) Limit clauses in MySQL are non-standard.

Coming from someone who supports both ;)

Re:Not hard

[jadavis]

In addition to the above info:

MySQL uses "autoincrement" for the serial fields. The main issue you'll run into here is when you're trying to use the value that was inserted by a previous query, you use (usually) currval() in postgres whereas in MySQL they use a different function. So, you might need to update a few queries in the client app to use currval().

Re:Not hard

[Anonymous Coward]

Timezones are no longer stored by default, but still can be used. (7.3)

Enjoy!

Comment removed

[account_deleted]

Comment removed based on user account deletion

Re:Cool! PostgreSQL always runs on my iBook

[Anonymous Coward]

someone who uses all these buzz words on a dialy basis... with a straight face... in short, a mac user.

Re: Firebird / Interbase

[nederhrj]

What about Firebird? (http://firebird.sourceforge.net) This is the open source decendant from Interbase, the database that has been running at Motorola, Nokia, Boeing, and the Boston Stock Exchange for many years. It offers excellent concurrency, high performance, and powerful language support for stored procedures and triggers. Also an excellent web frontend is available (http://http://ibwebadmin.sourceforge.net/)

Bill and Larry?

[theolein]

"Did you ever stop to think where you'd be when you finally kill your idols and have noone left to copy? "

Does this apply to Bill Gates and larry Ellison as well?

Date intervals ( was Re:Go on then. )

[Anonymous Coward]

In recent PostgreSQL, this date addition is simple:

Number of days since I was born:

joel@joel=# select current_date - '1972-10-12';
?column?
----------
11007
(1 row)

Number of days between Christmas 2002 and Valentine's Day:

joel@joel=# select cast ('2002-12-25' as date) - '2002-02-14';
?column?
----------
314
(1 row)

[we need to cast at least one as a date explicitly, otherwise PG assumes there are both strings data.]

Nothing complicated here, kids, move on.

BTW: for those using now() in PostgreSQL, moving to CURRENT_DATE (or CURRENT_TIMESTAMP to get the time and date) is recommended -- it's less quirky in procedures, and is ANSI standard syntax.

Data migration can be critical.

[TheLink]

I don't have this problem, but "insitu" data migration "fairies" can be very important.

Example: DB takes 20 terabytes. 10 terabytes free.

Say it's time to upgrade to latest DB. Who's wants to tell the boss: "We need to buy 10 more terabytes which we won't use for months after that". Or "there'll be long downtime reloading 20TB from backup tapes".

Worse if the backup tapes don't store the DB in a version independent format! (which could be the case for ASAP disaster recovery - snapshot of everything). Backups still work, but upgrading could be a bit harder eh? ;)

Re:Question. I cry foul!

[GooberToo]

If you are going to start talking about vapor features then what about postgresql's plans to support point in time recoveries (pitr), redo logs, savepoints, and full clustering with multiple masters.

PITR, was planned for 7.3 but was delayed for 7.4 because of some other internal changes but they didn't wish to delay 7.3 for it. It's now scheduled for 7.4.

Clustering is such an overloaded word. Nonetheless, multi-master (IIRC) replication is already underworks.

Distributed quiries is something that comes up from time to time. Not sure what the actual work effort is, however, I do know the protocol is being significantly enhanced to allow for replication and distributed queries.

foxpro

[Anonymous Coward]

foxpro has sql transations too. but they dont have WAL, which kind of makes it useless.

Could be done before.

[TheLink]

IF (big if) you can temporarily remove those FK stuff etc and your users can wait for the time it takes to reload the affected table, you could do this in Postgresql (and not Oracle).

(pseudoSQL)
begin;
lock oldtable;
create newtable (withcolumns that you want);
select columnsyouwant from oldtable into newtable;
drop oldtable;
rename newtable;
build indexes etc;
commit;

If anything fails everything will rollback and things will be fine.

I did this on a live site and it went well - old columns, new columns, modified columns whatever. Didn't have to resort to backups ;).

That's where Postgresql beats Oracle.

Another potential gotcha: MS Access and nulls.

[TheLink]

Apparently MS Access treats fieldname=NULL differently. Nonstandards compliant.

AFAIK the standard says to do NULL tests using IS NULL and IS NOT NULL. fieldname=NULL should always return null according to standards.

You have to turn on a setting in Postgresql to support MS Access stuff which use the nonstandard behaviour.

Probably many others. But don't let crap stop you from switching from crap to good stuff.

Standardization

[Anonymous Coward]

Many companies try to standardize on as few platforms/dbs/etc as possible trying to minimize support costs. And they have to standardize on something that covers their highest needs as well as their lowest. Cost may not be a problem since they often have global licensing frameworks.

So that's why they do install their 5000 record database in Oracle. They might even be using a different instance in a shared installation...

Of course, for small companies this could be different.

My 0.02

Ease of use?

[HendriX]

ditto

fair enough...

[ttfkam]

But MySQL isn't libre software on Windows is it? It wasn't the last time I checked.

So on Windows you have the choice of an installer (MySQL) or open source (PostgreSQL).

Did I miss something?

Re:fair enough...

[Anonymous Coward]

yeah that cygwin+postgresql is a nightmare to install and prone to just failing everytime the stupid ass developer decides to install iMesh.

do i sound like I wish I'd gone with MySQL, just so it would be easier for people in my team who use windows to run it? well, its a close run thing.

Reporting

[cduffy]

Considered using FOP for your reporting? That way you get more layout control than with HTML, your final output is in PDF format, you can use XSLT to build the stylesheets to do the formatting (and just have your program output XML to the reporting layer)... generally cool stuff.

Re:Reporting

[HiThere]

Yeah, I considered it. But even converting to Java puts me under the gun time-wise. That's for some later version (and it will probably end up being Crystal Reports, because that's what the people who make the decisions have heard about [which is the reason for Java instead of, say, Python or Ruby]).

Nope

[ttfkam]

You are indeed correct. I mistyped. With updates like in my example, you don't have to explicitly lock a table. Strictly speaking, you don't have to in MySQL either; you just do it with some risk to your data.

In the next versions of PostgreSQL, support for nested transactions would solve those particular problems. But as that's a feature not yet available, I cede the point.

In your example, a lock is indeed the way to go.

Re:I want replication. please.

[Anonymous Coward]

Me too.

I want replication too.

Thanks in advance.

You need to report distribution bugs

[Anonymous Coward]

So report a bug. How can I fix things if the problems aren't reported? (Not that I've heard of this from anyone else either -- I suspect your client built his own postgresql package without having readline development files installed.)

Debian maintainer for PostgreSQL

Re:I want replication. please.

[Anonymous Coward]

PostgreSQL Inc offers a replication solution, though it's not free. I've not used it myself, but the folks running the .info domain use it and seem fairly happy with it.

Sure... forum.geizhals.at

[Anonymous Coward]

forum.geizhals.at, discussion forum (german language only!) for the geizhals.at price comparisons ... Does approx. 3 mil. Page Impressions per month and is 100% Resin 2.1.x(JSP)/PostgreSQL 7.2.3/Apache-based (originally built with Resin 1.2.4, PostgreSQL 6.3 in Q2/2000). Runs off a single dual-CPU Pentium III/800MHz server (including the database, which is also used by other parts of geizhals.at, which has approx. 18 mil. Page Impressions per month).

The size of a field is too long

[GlobalEcho]

This talk of Access and Postgres reminds me...how do you get past the problem where you try to link Access to Postgres, and Access 2K complains something like "The size of a field was too long"? I gave up on the linking because of this.

It's not the ODBC driver, as Excel does a data import of the same view with no problem!