Beta
×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

PostgreSQL 9.0 Released

Soulskill posted more than 3 years ago | from the milestone-achieved dept.

Databases 344

poet writes "Today the PostgreSQL Global Development Group released PostgreSQL 9.0. This release marks a major milestone in the PostgreSQL ecosystem, with added features such as streaming replication (including DDL), Hot Standby and other nifty items like DO. The release notes list all the new features, and the guide explains them in greater detail. You can download a copy now."

cancel ×

344 comments

Sorry! There are no comments related to the filter you selected.

Thank you! (5, Insightful)

bjourne (1034822) | more than 3 years ago | (#33643220)

Congratulations to all the Postgres developers and a big thank you from me for an amazing job! Postgres is a wonderful RDBMS and one of the best free software projects there is. Rock on!

Re:Thank you! (1)

WoLpH (699064) | more than 3 years ago | (#33644028)

Second that. By far the most pleasant database I've worked with so far and if you don't have everything you need with the build-in features, it's easy to build them yourself.

Re:Thank you! (5, Informative)

Anonymous Coward | more than 3 years ago | (#33644378)

Thirded. There is absolutely no reason for anyone to be using MySQL any more other than the old silly excuse "my hosting provider doesn't have anything else". PostgreSQL is now faster than MySQL in all but the most trivial of contrived cases, doesn't require you to choose between table types for different load types, is just as easy to use and install, has all the features that MySQL has and runs on a Windows server (for those idiots who think that is a good thing). Also, the PG community is vastly more helpful and knowledgeable than the rabble that is the MySQL user base.

Finally, PostgreSQL is a proper independent open source project with a structure that all other open source projects should be judged by. MySQL has gone from hand to hand in the corporate world and has a future that is far from certain.

Down with the joke that is MySQL, and down with all the idiots that make me work with it.

First (-1, Offtopic)

theskipper (461997) | more than 3 years ago | (#33643228)

First Post!

(...gresql post)

Cool (4, Interesting)

iONiUM (530420) | more than 3 years ago | (#33643238)

I read the notes, noticed the Column and WHEN triggers. Is this in other SQL databases? If it is, I haven't seen it before. In any case, it's pretty cool that you can setup triggers on a conditional statement. That would really help me out in a lot of scenarios, as I work in the BI space, so alerting is a big deal.

Re:Cool (2, Informative)

jwpye (1905258) | more than 3 years ago | (#33643478)

Not sure if it's in other DBMSs, but it's in the SQL spec.

Re:Cool (2, Interesting)

rsborg (111459) | more than 3 years ago | (#33643502)

I read the notes, noticed the Column and WHEN triggers. Is this in other SQL databases? If it is, I haven't seen it before. In any case, it's pretty cool that you can setup triggers on a conditional statement. That would really help me out in a lot of scenarios, as I work in the BI space, so alerting is a big deal.

Isn't this just syntactic sugar? What's the difference between logic in the trigger determining when to issue the payload logic, and the logic outside the trigger... especially if the trigger (re)uses a parametrized stored proc for it's payload?

Not that this isn't nice, but it may also lead to code scattering, increasing maintainability issues.

Re:Cool (2)

MichaelDavKristopeit (1905312) | more than 3 years ago | (#33643688)

Not that this isn't nice, but it may also lead to code scattering, increasing maintainability issues.

i agree completely... when the OP said he would use it "in a lot of scenarios" my red flag of maintainability went up hard.

for example, if you're doing column when triggers, and initially you want to do something on a boolean conditional, you'll probably code the trigger as such... but now you add in a new column type and in debugging you find out the trigger isn't going off, and now you have to leave the code you're adding for the new condition to exist, and find and update the trigger code. i simply find that whatever process that allowed you to create the new condition should have also handled the payload of the trigger. code scattering is my biggest pet peeve in system engineering.

Re:Cool (1)

GooberToo (74388) | more than 3 years ago | (#33644038)

i agree completely... when the OP said he would use it "in a lot of scenarios" my red flag of maintainability went up hard.

That's FUD, plain and simple. This is called granularity, not "code scattering." Being able to maintain a trigger which only deals with column x without having to test if your changes break when column y is changed is a big win. Not to mention, you're no longer wasting cycles when column z is updated and x and y are not. Which also means you can throw away traditional CASE code which further improves readability, maintainability, runtime performance, possibly lowers test complexity matrix, and is generally all around good.

Re:Cool (2, Interesting)

MichaelDavKristopeit (1905312) | more than 3 years ago | (#33644230)

i'm not saying conditional triggers shouldn't exist... and i'm agreeing that more cycles would be used if a trigger was used where a conditional trigger could have been used, but my point is that in such situations the same job could be done without using any triggers whatsoever.

granularity is an odd label to denounce and replace scattering, as i can think of nothing that scatters more than grains.

generally, conditional triggers could be used by a platform layer that would automate the creation and updating of these triggers, but in the name of portability, that layer could handle such scheduling and processing on it's own.

if you're a farmer, do you want to "harvest the field" or "pick up every grain of corn"... as a programmer, do you want to "update the codebase" or "find every piece of the codebase you need to update"...

it's not FUD... it's a waste of time.

Re:Cool (2, Informative)

GooberToo (74388) | more than 3 years ago | (#33644294)

You clearly have no idea what you're talking about. Granularity is widely accepted for such linguistic applications. The reason being, its completely applicable.

Basically you're foolishly arguing that monster triggers are preferred over small, terse, highly readable, and much more maintainable triggers. Lots and lots of code is good. Less code which does the same thing is bad. That's simply ignorant, foolish, and FUD. "Scattering" is clearly being used to imply a negative connotation. Given there isn't a single negative with their use and that "granularity" is accepted and proper, making such statements squarely implies you're trolling.

Re:Cool (1, Troll)

MichaelDa.Kristopeit (1905336) | more than 3 years ago | (#33644482)

actually you clearly have no idea what i'm talking about.

i'm claiming that numerous, small, terse, highly readable triggers are LESS MAINTAINABLE, in regards of time required to change feature requirements, than other options.

claiming that statement is wrong is IGNORANT. relying on a platform layer to offset that amount of time INVALIDATES THE ADVANTAGES AND NECESSITY OF HAVING CONDITIONAL TRIGGERS.

Re:Cool (0)

Anonymous Coward | more than 3 years ago | (#33644588)

whoa whoa easy now no need to bring out the caps

Re:Cool (0, Troll)

MichaelDa.Kristopeit (1905336) | more than 3 years ago | (#33644608)

but there is a need to bring out the whoa whoa?

you're an ignorant hypocrite.

Re:Cool (1)

aztracker1 (702135) | more than 3 years ago | (#33644512)

I think this is probably offset if you have lazy update procs, or systems where all columns in a row are part of the update statement, which is often the case.

Re:Cool (4, Informative)

GooberToo (74388) | more than 3 years ago | (#33643984)

What's the difference between logic in the trigger determining when to issue the payload logic, and the logic outside the trigger...

No! Its far more than syntactic sugar. Performance, readability, and maintainability are what this brings to the table.

The difference between PostgreSQL's new column trigger feature and traditional triggers is they are only called when the column is modified rather than when any row is modified. This means, in many cases, the trigger will never be called and therefore, the DB isn't having to run at PL/SQL interpreter speeds during the execution of the trigger, to then determine there is nothing for it to do. Furthermore, a big headache which is extremely common to trigger code are IF/THEN/ELSE or long CASE statements to determine which columns are modified, or to determine if the trigger even cares that the row in question (example, columns which the trigger doesn't care about) has been modified.

The above combination of traditional triggers means lots of overhead, lots of needless PL/SQL code execution, and hard to read/maintain triggers for non-trivial actions. Whereas with the new feature, you can now have a single trigger relate to specific column, which is only ever executed when the trigger should actually execute. Its a win, win, win for all PostgreSQL users. And best of all, this means you can have smaller triggers when you need to perform different actions based on different column changes.

Re:Cool (4, Interesting)

lanner (107308) | more than 3 years ago | (#33643814)

I hate to say it, but good/useful features like that will be abused by stupid DB guys who can't program.

Once upon a time I worked in the entertainment industry and was working on a big MMO game project.

Company X could not scale up their game clusters past about 1000 players. Somewhere between 1000 and 2000 players, the game would just start bogging down and in-game events piled up and everything trainwrecked and was unplayable.

So, it turns out that most of the game logic was built off of complicated SQL stored procedures, triggers, logic, etc. Basically, they were using their hard drive as a processor.

The problem was with the MS-SQL server disk IO Wait. CPU was okay on all of the systems, but they could just not imagine that the disks in the database server (only one DB server per cluster) could be the source of the problems. Every time there was an item dropped, crafted, or certain other special things happened, there was an atomic commit and that basically required writing to disk on the spot. Get enough of that going and you're whole 20-something CPU cluster sits with idle CPU while the DB server works it's hard drives.

Company went chapter 11, all staff eventually let go, and later was sold off for nothing.

I had pointed out this problem to them, but it was late in development and when you tell the people who are responsible for designing the product that they are idiots, well, they behave like idiots and don't really listen. Not that they could have fixed it anyway due to time and intellect restraints.

Anyway, point of the story is that cool SQL features are cool. But don't use your hard drive as a processor.

Re:Cool (1)

GooberToo (74388) | more than 3 years ago | (#33644220)

I hate to say it, but good/useful features like that will be abused by stupid DB guys who can't program.

Hate to burst your bubble, but PostgreSQL's column trigger feature could have actually increased performance many times.

Anything can be abused. Negatively portraying a powerful feature which can dramatically improve performance, readability, and maintainability, in a polithera of use cases, is nothing but FUD and ignorance.

Re:Cool (0)

Anonymous Coward | more than 3 years ago | (#33644652)

..stupid DB guys who can't program.

no offense but this sounds like something a stupid programmer guy who can't DB would say :)

when you tell the people who are responsible for designing the product that they are idiots, well, they behave like idiots

name calling isn't very productive no matter what you're doing. well argued constructive criticism is usually well received. it's nice working with well mannered adults.

Re:Cool (2, Informative)

OlRickDawson (648236) | more than 3 years ago | (#33643930)

Yes. Oracle has those.

"Great leap forward" (-1, Flamebait)

XanC (644172) | more than 3 years ago | (#33643334)

The "great leap forward" for Postgres is live replication over the network? Get with the times! MySQL has had this for literally years!

Re:"Great leap forward" (4, Insightful)

catmistake (814204) | more than 3 years ago | (#33643406)

LMAO... unless my sarcasm detector is malfunctioning, comparing Postgres to MySQL is extraordinarily absurd... like comparing megaliths to legos.

Re:"Great leap forward" (5, Informative)

Anonymous Coward | more than 3 years ago | (#33643460)

Yes, but MySQL has a shoddy parser (needs a space after the -- comment tag), poor trigger failing (you have to do a kludge and dump a varchar into an int to get it to fail), apparent lack of direction (how many forks and engines?!), no CTE support and the list goes on. I am constantly banging my head against a wall with MySQL. I use MSSQL for work, Postgres at home and MySQL on hosting.
I am truly surprised that most web hosting companies do not offer Postgres. Postgres also allows writing of DB functions in C, Java, PHP etc. like Oracle, which is useful for bundling code into the DB (making the DB the application) without everyone having to see your SQL source for functions. It is also licensed on BSD which is good for using their libpq library in commercial apps; MySQL's C API is GPLd or licensed expensively from Oracle, although there are moves toward making it free for use in commercial apps (as far as I can tell from the mishmash of info coming from their sales rep via email).
Also, as far as I know, MySQL puts all of its indexes in memory for replication which is a problem if the node goes down. Can anyone enlighten me?

In any case, well done to the Postgres team. Not only is their software package neat, their documentation is some of the best I have ever seen.

Re:"Great leap forward" (1)

sourcerror (1718066) | more than 3 years ago | (#33643888)

Nested queries can cause a lot of headaches as well. (needs some redundant 'select*' inbetween)

Re:"Great leap forward" (1)

C_Kode (102755) | more than 3 years ago | (#33644396)

| Please tell me you're kidding. Anyone suggesting MySql for real work should just be laughed at.

I'm not sure how you got modded to +5 with this statement, but your statement is uninformed and completely false. While MySQL isn't in the class of Oracle for HA, MySQL with InnoDB is damn well is a competent database and I don't just mean for LAMP.

Re:"Great leap forward" (3, Informative)

Alex Zepeda (10955) | more than 3 years ago | (#33644624)

| Please tell me you're kidding. Anyone suggesting MySql for real work should just be laughed at.

I'm not sure how you got modded to +5 with this statement, but your statement is uninformed and completely false. While MySQL isn't in the class of Oracle for HA, MySQL with InnoDB is damn well is a competent database and I don't just mean for LAMP.

Eh. It's *okay* for lightweight work where you don't care about data integrity or don't add or modify a lot of data. Beyond that it falls apart quickly.

At a previous job we used ActiveRecord hooked up to MySQL to handle an influx of temporal data that was meant to be quickly processed and usable for reading back in real time. ActiveRecord uses sequences (so, auto increment fields in MySQL -- since proper sequences are lacking in MySQL) for the primary key. With Postgres this is not a problem at all. InnoDB, OTOH, locks *the entire table* to update an auto increment field. The sysadmin/dba was averse to using Postres, so the result was a series of complex and tedious to debug performance problems and queues. We spent countless hours dancing around the performance problems inherent to table level locking.

Of course we could have gone with MyISAM... but data integrity was important. There were other seemingly basic features that were lacking in MySQL (timezone support and a useful explain command come to mind). As far as I can tell there aren't a lot of good reasons to actively choose MySQL. The lightweight cases are well handled by SQLite, and the heavier stuff will almost certainly benefit from what Postgres has to bring to the table.

Re:"Great leap forward" (0)

Anonymous Coward | more than 3 years ago | (#33644706)

not really. the management tools alone are bad enough to disqualify it from my arsenal.

Has the Documentation Been Improved? (0, Troll)

careysub (976506) | more than 3 years ago | (#33643374)

The documentation (just links to web pages) has gotten out-dated and inconsistent, and hard to use over the years. Does the new release come with a clean up so that it is actually easy to use and understand?

Re:Has the Documentation Been Improved? (4, Informative)

caerwyn (38056) | more than 3 years ago | (#33643424)

Err, have you actually used the PostgreSQL manual? It's one of the best manuals I've ever seen for a software product.

Re:Has the Documentation Been Improved? (4, Informative)

shutdown -p now (807394) | more than 3 years ago | (#33643760)

It's actually one of the best manuals for SQL in general - at least, in my experience, it has the most clear explanations of many more advanced SQL constructs that are common between various RDBMSes.

Re:Has the Documentation Been Improved? (0, Troll)

GoChickenFat (743372) | more than 3 years ago | (#33643842)

Um...please point me to the copy of the PG manual you are using. being both a PostgreSQL and MS SQL user...MS BOL is significantly more useful...

Re:Has the Documentation Been Improved? (1)

hibiki_r (649814) | more than 3 years ago | (#33643962)

The copy? You go to their website, click on documentation, and select the version you want. I've been using it for years, and the info you want is always there, in a sensible format. I've never wanted more.

Re:Has the Documentation Been Improved? (1)

GooberToo (74388) | more than 3 years ago | (#33644096)

The fact he makes such a statement means two things. One, he's never bothered to look. Two, only interested in FUD'ing.

Re:Has the Documentation Been Improved? (0)

Anonymous Coward | more than 3 years ago | (#33644454)

Two comments of the ever so helpful type that are typical of OSS. Way to win hearts and minds guys.

Re:Has the Documentation Been Improved? (0, Troll)

miggyb (1537903) | more than 3 years ago | (#33644514)

RTFM!

Where is the manual?

RTFM!

Re:Has the Documentation Been Improved? (2, Informative)

turing_m (1030530) | more than 3 years ago | (#33644738)

Err, have you actually used the PostgreSQL manual? It's one of the best manuals I've ever seen for a software product.

AFAIC, it is the standard by which other software manuals should be judged. Good call.

Re:Has the Documentation Been Improved? (2, Insightful)

Anonymous Coward | more than 3 years ago | (#33643462)

If you have actually read the documentation, you would find that it is one of the finest pieces of software documentation out there. If one could have any complaint it could be that there is just so much of it.

Re:Has the Documentation Been Improved? (1)

catmistake (814204) | more than 3 years ago | (#33643488)

The documentation (just links to web pages) has gotten out-dated and inconsistent, and hard to use over the years. Does the new release come with a clean up so that it is actually easy to use and understand?

No. But there is the Postgres Primer at O'Reilly, and Amazon has Postgres for Dummiez.

I've looked from time to time for good documentation... most DBA's I've asked tell me not to bother; it's 30 years they'll never get back, and they want to save me the trouble, at least that's what they say.

Re:Has the Documentation Been Improved? (5, Informative)

jwpye (1905258) | more than 3 years ago | (#33643612)

hrm? The documentation is regularly updated... http://www.postgresql.org/docs/9.0/static/ [postgresql.org]

Re:Has the Documentation Been Improved? (1, Funny)

gmhowell (26755) | more than 3 years ago | (#33644464)

Dear lord, this many replies, and only one person has the decency to supply a link?

Kudos to you sir.

Re:Has the Documentation Been Improved? (1)

schmiddy (599730) | more than 3 years ago | (#33644042)

The documentation just links to web pages

Eh? Not sure exactly what you mean, but the postgres documentation is built from SGML into several formats, such as a giant PDF or the web documentation. It's pretty darn good, and if you have quibbles with any of it, post to pgsql-docs and you'll have someone on the case pretty quickly.

Re:Has the Documentation Been Improved? (1)

dskoll (99328) | more than 3 years ago | (#33644218)

You are kidding, right? The PostgreSQL documentation is the finest documentation I've seen for a free software project, and among the best I've seen for any software, free or proprietary.

Waiting for a capable PostgreSQL front-end (2, Informative)

bogaboga (793279) | more than 3 years ago | (#33643408)

Yes first, congratulations to those folks. I am still waiting for a front-end to PostgreSQL that is as functional and easy to program as Microsoft's Access.

I might be flamed here but there is nothing that bests Access in the open source world. Being able to program business logic into a form is something that Access and VB are pretty good at.

What open source program can replace these two Microsoft beasts?

Re:Waiting for a capable PostgreSQL front-end (3, Informative)

TheFuzzy (140473) | more than 3 years ago | (#33643496)

Why not just use .NET with PostgreSQL? You can put whatever you want on the back end.

Or you could use Once:Radix or Servoy, both of which integrate with PostgreSQL.

https://sourceforge.net/projects/onceradix/ [sourceforge.net]
http://www.servoy.com/ [servoy.com]

Re:Waiting for a capable PostgreSQL front-end (1)

dugjohnson (920519) | more than 3 years ago | (#33643532)

No flame, but there are a lot of front ends that can work with SQL backends, including PostgreSQL. Depends on what you are looking for and what you are trying to create. Many web frameworks can use PostgreSQL as the backend. Heck, you can even make VB talk to PostgreSQL if you want. Java does just fine. Delphi, since you are obviously willing to pay something. But it comes down to what language(s) you want to work in and then looking around a little bit.

Re:Waiting for a capable PostgreSQL front-end (0, Flamebait)

abigor (540274) | more than 3 years ago | (#33643542)

Probably OpenOffice Base is your best bet. I've never tried it, but I know it's often put forward as an Access alternative. But like most things OpenOffice, it probably falls short in several (or many) respects.

Re:Waiting for a capable PostgreSQL front-end (0)

Anonymous Coward | more than 3 years ago | (#33643654)

OpenOffice.org Base is alright for a very simple system. You can link it to an external data source using JDBC, ODBC or ADO (on Windows).
For queries, the SQL parser doesn't like simple things (like declare @myvar int for MS SQL) but you can use the button on the far right in the SQL editor to force it to accept the SQL you type in. Also, it doesn't like where you select from a temporary table and return the results - it thinks that there are no results returned.

For added frustration, once you have set up a query and want to create a chart from the results (for example), if you open Calc and press F4 to show the 'databases' view, if you drag the results of a query onto the spreadsheet, it *always* chops off the first 4 rows, or if you haven't navigated to the end of the resultset, it doesn't bother fetching them. Sometimes it works, using the Data to Text button the query view, but as to when this button is enabled is a mystery to me. Sometimes it doesn't let you drop the results you're dragging.

All in all I wasn't very impressed with it, but for form design and very very very simple queries etc., it might be alright, I suppose.

Re:Waiting for a capable PostgreSQL front-end (0)

Anonymous Coward | more than 3 years ago | (#33643598)

you know there is an odbc driver - i've been using access as a quick data manipulation, query and load tool for postgres backend for many many years.

EMS make a reasonable interface if you need to build tables, views etc... on the backend

There are many quick and dirty web front end builders for databases which are better suited than MS access for multi users - "code charge studio" comes to mind

none of the above i would call "programing" though so my guess is more homework is needed. ;)

Re:Waiting for a capable PostgreSQL front-end (2, Insightful)

h4rr4r (612664) | more than 3 years ago | (#33643672)

Access is too easy. It lets people who have no idea what they are doing make a half working solution that then has to be replaced with real code and a real DB.

Re:Waiting for a capable PostgreSQL front-end (3)

MichaelSmith (789609) | more than 3 years ago | (#33643724)

then has to be replaced with real code and a real DB.

Oddly enough I spoke to somebody last weekend who makes her living doing exactly that. I suspect that without access to kick these projects off she would have less work overall.

access -> sqlite -> mysql -> postgres -> oracle

Everybody looks down on the tools to the left.

Re:Waiting for a capable PostgreSQL front-end (1)

h4rr4r (612664) | more than 3 years ago | (#33643902)

I suspect they would call her earlier and the job might be done right from the start.

Re:Waiting for a capable PostgreSQL front-end (1)

MichaelSmith (789609) | more than 3 years ago | (#33643934)

Prototyping tools are important for marketing. Problems start when the go too far.

Re:Waiting for a capable PostgreSQL front-end (1)

LukeWebber (117950) | more than 3 years ago | (#33644104)

Oddly enough, when I go from MS SQL or Postgres to Oracle, I end up looking down on Oracle. Sure it's fast, but its standards compliance is lousy.
But yes, let's all point and laugh at the Access user, by all means. ;^)

Re:Waiting for a capable PostgreSQL front-end (2, Insightful)

domatic (1128127) | more than 3 years ago | (#33644696)

Better that it be Access rather than FileMaker Pro. There is an upgrade path of sorts from Access to SQL Server. So if you have one of those unfortunate cases where it was mandated that a dinky workgroup app be shoved out enterprise wide then at least there are options to move the data and app logic to platforms that can take the load. I'm not saying that it's easy but someone who knows what they are doing can get started on fixing it pretty quickly.

That situation with Filemaker Pro is much uglier and Filemaker Pro does even more to encourage marginal developers. FM Pro is an unholy glop of database, scripting language, and a widget set. Trouble with any of those domains tends to impact the other two. And one commercial FM Pro app I was saddled with would corrupt data when too many users hit the server at once. "Too many" being around 12 users. The other cute thing about that app was every person who used it had to have FM Pro client installed on the machine. The developer was a reseller for FileMaker too. Maybe THAT is why he didn't create a standalone runtime that could connect to the server. I'm happy to say that nasty thing is mostly phased out now.

Re:Waiting for a capable PostgreSQL front-end (1)

mysidia (191772) | more than 3 years ago | (#33643758)

There is an ODBC driver for PostgreSQL. You can probably access a PG database using MS Access just fine...

Re:Waiting for a capable PostgreSQL front-end (2, Informative)

schmiddy (599730) | more than 3 years ago | (#33644068)

Indeed. About 0.0005 seconds of Googling brought me this as the first link:

http://www.postgresonline.com/journal/archives/24-Using-MS-Access-with-PostgreSQL.html [postgresonline.com]

Though I think there are probably a grand total of 3 people on Earth who have used MS Access in any serious capacity and don't loathe it.

Re:Waiting for a capable PostgreSQL front-end (1)

mysidia (191772) | more than 3 years ago | (#33644192)

Not. I hate Access.
I loathe Access.
I'm sometimes forced to use it, usually as a result of stupid decisions made by n00b devs or sadistic PHBs, in the past.

Re:Waiting for a capable PostgreSQL front-end (1)

bogaboga (793279) | more than 3 years ago | (#33644322)

There is an ODBC driver for PostgreSQL. You can probably access a PG database using MS Access just fine...

That is not the bone of contention. What I wanted was an open source app just like PostgreSQL is. Do you know of any?

Re:Waiting for a capable PostgreSQL front-end (1)

mysidia (191772) | more than 3 years ago | (#33644450)

Depends on what you need to do... pgAdminIII is pretty useful; many of the 'fancy' tools are commercial.

Re:Waiting for a capable PostgreSQL front-end (5, Informative)

guusbosman (151671) | more than 3 years ago | (#33644088)

You know that you can point your MS Access client to any supported back-end right? Just create an ODBC connection on your Windows machine to your PostgreSQL server and you can use Access with pretty much all the features that work for the Microsoft JetEngine (PostgreSQL has ODBC drivers here; http://www.postgresql.org/ftp/odbc/versions/ [postgresql.org] )

Earlier this year we converted a huge Access application from MSSQL to PostgreSQL and the technical conversion, using ODBC to PostgreSQL instead of connecting to MSSQL, was a piece of cake.

Re:Waiting for a capable PostgreSQL front-end (1)

bogaboga (793279) | more than 3 years ago | (#33644298)

Agreed. Only that I was looking for open source applications. Know of any?

Re:Waiting for a capable PostgreSQL front-end (2, Interesting)

turing_m (1030530) | more than 3 years ago | (#33644716)

The last time I looked at ooo.org Base (at least a year ago, if not longer), I found it surprisingly capable, even workable. Give it a go, and have some patience. I only really had a look at the forms though, but I used to use subforms a lot and I could do what I wanted to do with it. Did not really look at reports though.

As far as business logic, put that in PostgreSQL.

Re:Waiting for a capable PostgreSQL front-end (1)

croftj (2359) | more than 3 years ago | (#33644472)

Personally, I find that Qt (C++ api from Nokia) has a nice sql api which will were with several database engines. Maybe not as mind numbingly simple as VB, but very versatile and easy to use.

Meh (-1)

Anonymous Coward | more than 3 years ago | (#33643426)

When postgres strictly checks data before inserting it into the database maybe I will give a try. Until then, I will stick with mysql which has been had this compliance since what, 1999?

Re:Meh (2, Informative)

caerwyn (38056) | more than 3 years ago | (#33643438)

I think you've got your databases backward when it comes to integrity and verification...

Re:Meh (0)

Anonymous Coward | more than 3 years ago | (#33643504)

Heh, what are you talking about? Postgres didn't even support foreign keys until version 5 and then even then it wasn't acid compliant. Mysql had all these things (pretty much on par with oracle) years before postgres.

Re:Meh (1)

mysidia (191772) | more than 3 years ago | (#33643776)

Perhaps, but PostgreSQL had the single most important extension AUTO_INCREMENT as a column type which easily trumped all of MySQL's advantages. While MySQL just had this clunky 'sequences' concept.

Re:Meh (4, Informative)

Daniel_Staal (609844) | more than 3 years ago | (#33644386)

Um, yeah. MySQL, out of the box, using the defaults, doesn't support foreign keys now. You have to specifically create the tables with a non-standard SQL code to get them to use the right database backend to get foreign key support.

Unless you mean by 'support' 'Will silently accept and throw away'...

Foreign keys have been enabled and working by default in Postgres since version 7. (There was no version 5...) That was released just over ten years ago at this point.

Re:Meh (1)

GooberToo (74388) | more than 3 years ago | (#33644152)

LOL! That's either one of the funniest or most ignorant things stated on /. in a while.

MySQL has a long, long reputation for poor ACID conformance. PostgreSQL, on the other hand, has a long and well respected reputation for both ACID conformance and a variety of lock/update methods which allow for varying degrees of integrity.

Firebird is better (-1, Troll)

Anonymous Coward | more than 3 years ago | (#33643482)

Firebird is a better Postgres than Postgres. It has similar or more advanced features and is hella faster and cleaner.

The fight is really Firebird versus MySQL, PostgreSQL isn't even in the game.

Re:Firebird is better (3, Insightful)

h4rr4r (612664) | more than 3 years ago | (#33643628)

Please tell me you're kidding. Anyone suggesting MySql for real work should just be laughed at.

Re:Firebird is better (1)

Mitchell314 (1576581) | more than 3 years ago | (#33644110)

Given the numbers, you better start laughing at much of the world. (note: not that I'm taking sides - I'm too stupid enough to figure out how to install postgreSQL the right way - though I'd prefer it over MySQL from what little I know)

Re:Firebird is better (0)

Anonymous Coward | more than 3 years ago | (#33644196)

If you're running Windows, there is a one-click installer available.
If you're on Linux, fetch a build from your repos (although it will be old). Set up the DB directory, and modify the conf file for access, and you're good to go. Check out pgAdmin if you like GUIs.
In any case, the only bit you need to read to get started is here: http://www.postgresql.org/docs/9.0/static/runtime.html

If you're used to MySQL and the "show databases" command etc., these don't exist on Postgres. Commands available via the command line tool psql are the best way to do it.
Also, if you're used to MS SQL or MySQL dumping all of their DBs in one or two files, this is different on Postgres. It spreads the data into lots of little files. Just thought I'd warn you! But it is easy to get going, thanks to the superb documentation.

Re:Firebird is better (1)

h4rr4r (612664) | more than 3 years ago | (#33644244)

From the packages ought to be easy enough for anyone. Rpms or debs available to anyone who knows how to use a web browser.

Re:Firebird is better (0)

Anonymous Coward | more than 3 years ago | (#33644642)

MySQL can be used for many things. Dismissing it as something that cannot be used for "Real Work" is ridiculous and a stupid statement. That is not too say there are things it should not be used for. But dismissing it out of hand is rather stupid. I think it does very well providing me with Wikipedia articles.

Re:Firebird is better (0)

Anonymous Coward | more than 3 years ago | (#33644040)

Firebird is substantially less capable than Postgres *or* MySQL, and is full of ridiculous arbitrary limits (i.e. per-table cap on *combined* column lengths in the 2KiB range? Really?)

If "anyone suggesting MySql for real work should just be laughed at", then anyone suggesting Firebird for real work should be deported (from Earth).

Captcha: sucked

As always... (5, Interesting)

jd (1658) | more than 3 years ago | (#33643520)

The new features are much admired by all (and deservedly so), but a heavier footprint typically means poorer performance overall even if there's accelerated performance in specific areas or improved programming. I'd like to see a performance plot, showing version versus performance versus different types of system load, in order to see how well new stuff is being added in. It might be merged in great and the underlying architecture may be superb, but I would like to see actual data on this.

Also, PostgreSQL and MySQL aren't the only Open Source SQL databases. Including variants and forks, you really need to also consider Ingres, Drizzle, MariaDB, SAP MaxDB, FireBird and SQLite. If you want to also compare against Closed Source DBs, then you'd obviously want to look at DB/2, Oracle, Cache and Sybase. I'd love to see a full comparison between all of these, feature-for-feature, with no bias for or against any specific development model or database model, but rather an honest appraisal of how each database performs at specific tasks.

I like PostgreSQL a lot. I rate it extremely highly. However, without an objective analysis, all I have is my subjective perception. And subjective perceptions are not something I could credibly use in a workplace to encourage a switch. For that matter, subjective perceptions are not something I would consider acceptable for even telling a friend what to use. Perceptions are simply not credible and have no value in the real world.

Re:As always... (0)

Anonymous Coward | more than 3 years ago | (#33643706)

So, what is keeping you?

Re:As always... (1)

jd (1658) | more than 3 years ago | (#33643994)

A broken laptop and two dead drives on my desktop. I'm a decent coder and can work my way round old-style rats-nest electronics with a soldering iron and a multimeter, but I'm not so good on rebuilding crashed drives or a cracked motherboard. Meh. I'll get them replaced sometime. If nobody has done the comparison by then, maybe I'll do one. But frankly, you're better off with an expert DBA designing such a test system, not a coder. A DBA =knows= what to look for and what to stress. That is their job and their training. A coder merely has an API. I've done enough DB design to do a decent job on developing databases, but I don't pretend to have the skills necessary to work the kind of magic that really exposes what a DB can and cannot do. That kind of wizardry is expressly in the domain of the creme-a-la-creme of DBAs.

Whilst I appreciate you thinking I should be in said category, the fact is that there are very very few such people. We are talking serious Database Black Magic, not just on one engine but a whole set of them. There's probably more people living in the same block as you than there are DBAs who have that level of expertise. Sure, there'll be plenty who are good on ONE engine (though not necessarily in enough aspects and enough case uses to really understand the complete range of loads), but to be sufficiently skilled across multiple styles of database over all manners of system load? That's a bit rarer. You no more want a software engineer attempting that than you want a database engineer working on a FIPS-180 crypto library in assembly. Whole 'nother animal.

Re:As always... (3, Interesting)

jwpye (1905258) | more than 3 years ago | (#33643754)

I think there has been some effort to bring a PostgreSQL "performance farm" online to show the differences in performance across versions of PostgreSQL, and to quickly identify regressions during development. I don't think it's up yet, but a search should reveal some details on the project.

I think Sun was giving them access to some... (0)

Anonymous Coward | more than 3 years ago | (#33644578)

sparc servers, but that went poof about 10 minutes after the Oracle merger.

Re:As always... (1)

GoChickenFat (743372) | more than 3 years ago | (#33643796)

Why do you like PostgreSQL so much and why did you leave MS SQL out of your closed source list? Subjective perception?

Re:As always... (0)

Anonymous Coward | more than 3 years ago | (#33643860)

MS SQL is actually pretty much the same as Sybase. Interestingly, they both rely on the TDS protocol for getting data from the server. No matter how much people dress up the ways of getting data from servers, you basically only need to be able to:
1. Send some SQL
2. Get a result code and row count
3. Iterate through the results if > 0
The efforts people go through to map variables to ODBC fields in C programs etc. is phenomenal, and long winded.

Just thought I'd say.

Re:As always... (0)

Anonymous Coward | more than 3 years ago | (#33644238)

MS SQL is actually pretty much the same as Sybase.

Circa SQL 2000 maybe.

That was the last version where MS and Sybase cooperated with that agreement.

Re:As always... (1)

jadavis (473492) | more than 3 years ago | (#33643868)

I'd love to see a full comparison between all of these, feature-for-feature, with no bias for or against any specific development model or database model, but rather an honest appraisal of how each database performs at specific tasks.

I intend this comment with sincerity: everyone would like that. But it's not very realistic, because there are so many variables in play. Even when you try to pick one aspect, like performance, it explodes into all different angles very quickly, and you can't really do an apples-to-apples comparison.

You can try to pick extremely simplistic measures, like how many simple INSERTs per second can be executed on a given machine, but that's really not representative of most real workloads.

The only thing you can really do is pick a few systems that appear to be of high quality, and understand them as best you can. Then, you will at least know what to expect in different situations.

However, version to version comparisons of the same system are a good idea -- still not easy, but it's more realistic to get apples-to-apples comparisons between versions. I think someone is working on it.

Wait a sec... (1)

fyngyrz (762201) | more than 3 years ago | (#33643872)

An engine like PostgreSQL is so complex, there are few standard tests that could really give you the data you're looking for, unless your application is so vanilla the KKK would endorse it. The only way to understand -- beforehand -- how a new version of a DB like this would work in an existing environment is to set up a test server, set up your database on it, and test it against the real-world operations the production server is experiencing, then compare the two in areas like execution time, memory utilization, thread count, etc. After you'd carefully evaluated what benefits any new features might bring you.

Seems to me that if you want to get beyond "perception" and into a worthy objective analysis, complaining is a waste of your time, because only you can do such a thing. If you want general info, the PostgreSQL team already gave you that -- they claim performance hasn't been hit. And a canned analysis doesn't, in my experience, reflect your own results. Benchmarks may give great results, but you write a simple little join a little funny, and oh brother, can your results differ. And so forth.

*** I've been using PostgreSQL for years; I'm not affiliated with those people, but I am pitifully grateful to them.

Re:Wait a sec... (1)

jd (1658) | more than 3 years ago | (#33644212)

To a degree, I agree. There will also be a number of things in database design that a DBA wizard could suggest that go beyond my knowledge. However, let's take a trivial example - basic SELECT, INSERT and UPDATE operations. What can you do with these? For any of the Open Source databases, you can compile with instrumentation and then measure the average length of each arc through the program that you can hit with just those three statements. With this, you can determine the maximum, minimum, mean and variance for the arc length for each of the three statements for some determined load level. You then do a series of load levels and obtain these four parameters for each statement at each load. This tells you something about the scalability on workload as well as the behaviour of the three functions.

(I pity the fool who actually tries to run a database with maximum instrumentation - you'll need hellish horsepower to get any kind of response even under light workloads. Under really heavy loads, the fountain of youth would be handy as well.)

Re:As always... (0)

Anonymous Coward | more than 3 years ago | (#33643882)

Please make that performance plot you are thinking about, and collect that actual data you are referring to, and write up that full comparison. I look forward to reading that very much.......... :)

Re:As always... (1)

TheFuzzy (140473) | more than 3 years ago | (#33643908)

JD,

You're absolutely correct that such a comparison would be a real asset to users. However, it would also be a Herculean task. Several people have tried to do similar things, but the number of indexes you need to compare (features, reliability, performance, etc.) is too large. And some things are so different it's hard to compare them meaning fully. Imagine trying to do a head-to-head comparison of all OSes in every way.

Here's a few comparison links, but they just scratch the surface:
http://troels.arvin.dk/db/rdbms/ [arvin.dk]
http://en.wikipedia.org/wiki/Comparison_of_SQL_database_management_systems [wikipedia.org]

Re:As always... (1)

jd (1658) | more than 3 years ago | (#33644124)

I fully agree it would be Herculean, which is why it would be good if we could find a Hercules to assign the task to. :)

In practice, you're right, there are some thing that are too different to compare readily. How do you compare an OO database with a Relational Database? For that matter, how do you compare a Star Database with a Relational Database? Even if they used an identical command language, the beasts are very very different. To an extent, that is a good thing - it means you can pick a database that's good for the problem, rather than forcing the problem to suit the database. In practice, precisely because they are so different, there's very limited metrics to make such a choice.

This has generally led to some assumptions. It is assumed relational databases is the most flexible but the slowest of the designs. There's next to no real proof of this, for the very reason you've noted, which means people are left with what amounts to folklore. It might be right, it might even be right for the right reasons, but it's simply not possible to know if this is the case right now.

And that leads back to the frustration that I feel. I am a software engineer with a strong science background. To be told that I have to accept folklore as a source of database knowledge - that is just so very very wrong. I can't accept that that's the best that can be done, even if I lack the skills to do better.

Re:As always... (1)

turbidostato (878842) | more than 3 years ago | (#33644494)

"How do you compare an OO database with a Relational Database? For that matter, how do you compare a Star Database with a Relational Database? [...] To an extent, that is a good thing - it means you can pick a database that's good for the problem"

Easy: you throw a typical problem from each class and then test all the engines against all of the problems. The fact that a relationally-oriented engine will do worse at an OO problem than an OO-oriented one doesn't preclude the test from being made anyways.

"I am a software engineer with a strong science background. To be told that I have to accept folklore as a source of database knowledge - that is just so very very wrong."

That's my point: this is the case not because is too dificult to produce proper benchmarks but because database vendors decided long time ago that they don't want you, the technician, to choose the database but the PHBs, which are much more open to marketing tactics than you (current license price tags for best renowned database engines are the success hallmark of their strategy).

Re:As always... (1)

turbidostato (878842) | more than 3 years ago | (#33644416)

"You're absolutely correct that such a comparison would be a real asset to users. However, it would also be a Herculean task."

I don't think so. I think that it even would be quite easy and cheap because, for the most part, it's already done!

I think that it's not done exactly because what you stated: it would be a real asset to users. RDBM vendors don't want that because RDBM choice is greatly based on gut feelings, which are much better handled by marketing than hard data.

Think of it: don't you think that, specially the big vendors like Microsoft, IBM or Oracle, don't already have their own internal benchmarking teams? So, from a naive point of view all that would rest is for them to public their results.

Given that the hard part is already done (knowing what to measure and having the tools for in fact measure it) releasing it to the public should be quite easy from the technical standpoint: you just open a competition and allow for each team to bring up their own technicians (so they can't say, "yeah, but it was not properly tunned") to test under loads defined from a trusted authority upon comitee from the vendors themselves.

That's how, say, all motorsports competitions work and it does work.

I guess we really are the leader now (4, Funny)

TheFuzzy (140473) | more than 3 years ago | (#33643876)

PostgreSQL *must* be the leading open source SQL database, now. People are bashing us on Slashdot. That's always a sign of success.

Thanks, guys!

--Josh Berkus
    PostgreSQL contributor

Re:I guess we really are the leader now (1)

Anonymous Coward | more than 3 years ago | (#33644120)

Nothin' but love here, buddy. And all the bashing is coming from those who don't know what a real database is.

Re:I guess we really are the leader now (0)

Anonymous Coward | more than 3 years ago | (#33644656)

I concur. PostgreSQL is one of the few examples of exceptional software engineering I provide as an example when somebody asks.

fp troolkore (-1, Troll)

Anonymous Coward | more than 3 years ago | (#33644314)

medical practice (0)

Anonymous Coward | more than 3 years ago | (#33644438)

would it be possible to store my information in this database? i am a medical doctor, see patients daily and need to record what drugs they are on. thanks. please email me ready solution to download for my medical PC desktop. thanks.

Re:medical practice (1)

croftj (2359) | more than 3 years ago | (#33644536)

While we're making silly requests, please email me a big bucket of money along with your requirements.

Re:medical practice (0)

Anonymous Coward | more than 3 years ago | (#33644560)

i also need to record patient data of birth and phone numeber.

thanks.

Re:medical practice (1)

h4rr4r (612664) | more than 3 years ago | (#33644620)

Hey doc, how about you share the wealth and maybe someone will.

Load More Comments
Slashdot Login

Need an Account?

Forgot your password?

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>