Slashdot Mirror

Please. MySQL is a good SQL database for SQL newbies but it doesn't even support the most basic things that are required for more advanced projects.

In any well designed database you want to move the data-logic to the database where it belongs. You can probably live without statement-level stored procedures and views, but MySQL doesn't even support foreign keys, triggers and checks which are essential when you need to make sure your data is consistent.

In the future, when recommending a database to someone who is talking about Oracle, MsSQL, Sybase etc, please point them to PostgreSQL instead of MySQL. At least until MySQL v5.1 is released because there's where they plan to implement these features.

...right here.

It's powered by GForge, so it's backed by PHP and PostgreSQL.

There are a bunch of other sites running GForge listed here...

...just skip Java and go with Ruby.

I needed a small app to analyze SQL query usage, and Ruby worked great. As long as you're conscientious about writing unit tests, you won't miss Java's static typing at all.

BEcause every company running a DB wants to additionally take on the burden of coding, testing and porting features to it all the while hoping they don't mess it up and can still remain competative.

No, I very much doubt any entity, company or a Real Human Being, likes to take on additional maintenance burdens. Nor have I ever claimed they would.

What I did say, was that with an open-sourced database, you have the option of maintaining the old version, instead of having to port everything to a new version/system or living with the old version and it's bugs. With closed source you have no such option. And I'd imagine that companies would rather have an option than not having it, since they can always choose to not take that option when the time comes.

The kind of money you would have to pay, on an ongoing basis, to hire someone (or more) who can intelligently and correctly port to, tune, test and enhance something as complex as Postgress is shall we say "non trivial".

Yes, but is more nontrivial than the money you'd have to pay to port all your applications to a new system and test them ? The original poster said he had "awfull lot" of code that uses MS SQL server.

Your money woudl be much better spent porting to a supported DB system.

Supported in what sense ? If you mean commercial support, then there seems to be quite a few companies offering it.

I'm no database expert, but what you describe sounds an awful lot like PostgreSQL's Multiversion Concurrency Control.

This Sybase move now means that I can download and play with a serious database. It's a smart move because it means that I will be gaining skills in programming for that database engine, skills which are seriously marketable.

To paraphrase some of the comments by developers etc on the Postgres mailing list, if this database had any commercial value left to the company, they'd still be holding onto it.

By releasing it, they've shown how valueless it really is. To quote Tom Lane (whole post here)

"If they thought they could still make a dime off it, they'd have kept it. Since they don't think they can, what is a rational assumption about the value of the code to the rest of us?"

I suspect they are hoping some free development will make it relevant once again, and possibly boost consulting services, and the sale of other products that rely on it.

WTF is the version of the postgres docs are you looking at?

I'll tell you "what the fuck" version of the PostgreSQL docs I am looking at. It is 7.4. I'll even tell you "how the fuck" can you go there. Go to PostgreSQL official website at www.postgresql.org. Click Docs at the top of the page in the main menu. Select Static Documentation for the current version of PostgreSQL 7.4, right at the top of the page, the very first thing below bold and underlined "Official Documentation." In the Table of Contents skip Preface and go to the Part I of the documentation entitled Tutorial. Find the word "joins" in the Table of Contents for this part. There is only one instance, namely 2.6. Joins Between Tables. Voilà! Now search for the word "Exercise." There are two of them:

1. Exercise: Attempt to find out the semantics of this query when the WHERE clause is omitted.
2. Exercise: There are also right outer joins and full outer joins. Try to find out what those do.

Are you "fucking" satisfied? Call me oldfashioned but in the official "tutorial" for the leading RDBMS I would expect a little bit more on outer joins than leaving them as a pathetic exercise for the reader. Next time before you start using vulgar language please do a little research and please at least try to stay calm. PostgreSQL is unquestionably the best free software RDBMS. It is clearly superior to most of proprietary systems as well. It has already singlehandedly beaten MS SQL Server and beating Oracle is only a matter of time. No question about that. But let us not forget about the documentation. Of course for you and me outer joins is something utterly obvious and intuitive, but remember that there are people who are beginners to computing and programming in general and set theory and relational model in particular. We should forget about our infantile "leetism" and doubtful "intellectual superiority" if we ever want PostgreSQL to be ready for the desktop. And most certainly starting our sentences with "what the fuck?!" does not make us look any more competent in the business and scientific community. Let this be an appeal to everyone who often forgets that cursing like a drunken sailor does not help the free software movement, myself included. Because if we continue looking like a bunch of simpletons in the eyes of businessmen and politicians, our products and our agenda will never dominate the market of profanum vulgus, notwithstanding the meritorious superiority thereof.

WTF is the version of the postgres docs are you looking at?

I'll tell you "what the fuck" version of the PostgreSQL docs I am looking at. It is 7.4. I'll even tell you "how the fuck" can you go there. Go to PostgreSQL official website at www.postgresql.org. Click Docs at the top of the page in the main menu. Select Static Documentation for the current version of PostgreSQL 7.4, right at the top of the page, the very first thing below bold and underlined "Official Documentation." In the Table of Contents skip Preface and go to the Part I of the documentation entitled Tutorial. Find the word "joins" in the Table of Contents for this part. There is only one instance, namely 2.6. Joins Between Tables. Voilà! Now search for the word "Exercise." There are two of them:

1. Exercise: Attempt to find out the semantics of this query when the WHERE clause is omitted.
2. Exercise: There are also right outer joins and full outer joins. Try to find out what those do.

Are you "fucking" satisfied? Call me oldfashioned but in the official "tutorial" for the leading RDBMS I would expect a little bit more on outer joins than leaving them as a pathetic exercise for the reader. Next time before you start using vulgar language please do a little research and please at least try to stay calm. PostgreSQL is unquestionably the best free software RDBMS. It is clearly superior to most of proprietary systems as well. It has already singlehandedly beaten MS SQL Server and beating Oracle is only a matter of time. No question about that. But let us not forget about the documentation. Of course for you and me outer joins is something utterly obvious and intuitive, but remember that there are people who are beginners to computing and programming in general and set theory and relational model in particular. We should forget about our infantile "leetism" and doubtful "intellectual superiority" if we ever want PostgreSQL to be ready for the desktop. And most certainly starting our sentences with "what the fuck?!" does not make us look any more competent in the business and scientific community. Let this be an appeal to everyone who often forgets that cursing like a drunken sailor does not help the free software movement, myself included. Because if we continue looking like a bunch of simpletons in the eyes of businessmen and politicians, our products and our agenda will never dominate the market of profanum vulgus, notwithstanding the meritorious superiority thereof.

WTF is the version of the postgres docs are you looking at?

I'll tell you "what the fuck" version of the PostgreSQL docs I am looking at. It is 7.4. I'll even tell you "how the fuck" can you go there. Go to PostgreSQL official website at www.postgresql.org. Click Docs at the top of the page in the main menu. Select Static Documentation for the current version of PostgreSQL 7.4, right at the top of the page, the very first thing below bold and underlined "Official Documentation." In the Table of Contents skip Preface and go to the Part I of the documentation entitled Tutorial. Find the word "joins" in the Table of Contents for this part. There is only one instance, namely 2.6. Joins Between Tables. Voilà! Now search for the word "Exercise." There are two of them:

1. Exercise: Attempt to find out the semantics of this query when the WHERE clause is omitted.
2. Exercise: There are also right outer joins and full outer joins. Try to find out what those do.

Are you "fucking" satisfied? Call me oldfashioned but in the official "tutorial" for the leading RDBMS I would expect a little bit more on outer joins than leaving them as a pathetic exercise for the reader. Next time before you start using vulgar language please do a little research and please at least try to stay calm. PostgreSQL is unquestionably the best free software RDBMS. It is clearly superior to most of proprietary systems as well. It has already singlehandedly beaten MS SQL Server and beating Oracle is only a matter of time. No question about that. But let us not forget about the documentation. Of course for you and me outer joins is something utterly obvious and intuitive, but remember that there are people who are beginners to computing and programming in general and set theory and relational model in particular. We should forget about our infantile "leetism" and doubtful "intellectual superiority" if we ever want PostgreSQL to be ready for the desktop. And most certainly starting our sentences with "what the fuck?!" does not make us look any more competent in the business and scientific community. Let this be an appeal to everyone who often forgets that cursing like a drunken sailor does not help the free software movement, myself included. Because if we continue looking like a bunch of simpletons in the eyes of businessmen and politicians, our products and our agenda will never dominate the market of profanum vulgus, notwithstanding the meritorious superiority thereof.

WTF is the version of the postgres docs are you looking at?

I'll tell you "what the fuck" version of the PostgreSQL docs I am looking at. It is 7.4. I'll even tell you "how the fuck" can you go there. Go to PostgreSQL official website at www.postgresql.org. Click Docs at the top of the page in the main menu. Select Static Documentation for the current version of PostgreSQL 7.4, right at the top of the page, the very first thing below bold and underlined "Official Documentation." In the Table of Contents skip Preface and go to the Part I of the documentation entitled Tutorial. Find the word "joins" in the Table of Contents for this part. There is only one instance, namely 2.6. Joins Between Tables. Voilà! Now search for the word "Exercise." There are two of them:

1. Exercise: Attempt to find out the semantics of this query when the WHERE clause is omitted.
2. Exercise: There are also right outer joins and full outer joins. Try to find out what those do.

Are you "fucking" satisfied? Call me oldfashioned but in the official "tutorial" for the leading RDBMS I would expect a little bit more on outer joins than leaving them as a pathetic exercise for the reader. Next time before you start using vulgar language please do a little research and please at least try to stay calm. PostgreSQL is unquestionably the best free software RDBMS. It is clearly superior to most of proprietary systems as well. It has already singlehandedly beaten MS SQL Server and beating Oracle is only a matter of time. No question about that. But let us not forget about the documentation. Of course for you and me outer joins is something utterly obvious and intuitive, but remember that there are people who are beginners to computing and programming in general and set theory and relational model in particular. We should forget about our infantile "leetism" and doubtful "intellectual superiority" if we ever want PostgreSQL to be ready for the desktop. And most certainly starting our sentences with "what the fuck?!" does not make us look any more competent in the business and scientific community. Let this be an appeal to everyone who often forgets that cursing like a drunken sailor does not help the free software movement, myself included. Because if we continue looking like a bunch of simpletons in the eyes of businessmen and politicians, our products and our agenda will never dominate the market of profanum vulgus, notwithstanding the meritorious superiority thereof.

WTF is the version of the postgres docs are you looking at?

I'll tell you "what the fuck" version of the PostgreSQL docs I am looking at. It is 7.4. I'll even tell you "how the fuck" can you go there. Go to PostgreSQL official website at www.postgresql.org. Click Docs at the top of the page in the main menu. Select Static Documentation for the current version of PostgreSQL 7.4, right at the top of the page, the very first thing below bold and underlined "Official Documentation." In the Table of Contents skip Preface and go to the Part I of the documentation entitled Tutorial. Find the word "joins" in the Table of Contents for this part. There is only one instance, namely 2.6. Joins Between Tables. Voilà! Now search for the word "Exercise." There are two of them:

1. Exercise: Attempt to find out the semantics of this query when the WHERE clause is omitted.
2. Exercise: There are also right outer joins and full outer joins. Try to find out what those do.

Are you "fucking" satisfied? Call me oldfashioned but in the official "tutorial" for the leading RDBMS I would expect a little bit more on outer joins than leaving them as a pathetic exercise for the reader. Next time before you start using vulgar language please do a little research and please at least try to stay calm. PostgreSQL is unquestionably the best free software RDBMS. It is clearly superior to most of proprietary systems as well. It has already singlehandedly beaten MS SQL Server and beating Oracle is only a matter of time. No question about that. But let us not forget about the documentation. Of course for you and me outer joins is something utterly obvious and intuitive, but remember that there are people who are beginners to computing and programming in general and set theory and relational model in particular. We should forget about our infantile "leetism" and doubtful "intellectual superiority" if we ever want PostgreSQL to be ready for the desktop. And most certainly starting our sentences with "what the fuck?!" does not make us look any more competent in the business and scientific community. Let this be an appeal to everyone who often forgets that cursing like a drunken sailor does not help the free software movement, myself included. Because if we continue looking like a bunch of simpletons in the eyes of businessmen and politicians, our products and our agenda will never dominate the market of profanum vulgus, notwithstanding the meritorious superiority thereof.

WTF is the version of the postgres docs are you looking at? table expressions

PGAccess it Tk-based. phpPgAdmin, though, is php-based.

Other GUI clients in the pgSQL FAQ.

A few suggestion that aren't exactly free as in beer - as the development environments are commercial. But they'll let you put together a nice, freely redistributable interface to a good, solid backend RDBMS such as PostgreSQL and the effort you'll likely save will be worth more than the sub-$1000 price tags

WebObjects will let you generate most of the view, edit, insert, delete functionality you need with minimal effort. It can be used to generate a standalone java application or a web-based interface. It'll talk to anything with JDBC - including PostgreSQL.

There are a few open-source workalikes of WebObjects too, that you may want to look at.

Another option is Qt. It has a fairly easy RAD environment for developing cross-platform apps in C++, and pretty solid SQL support, including database connected widgets for fairly easy quick-and-dirty view, insert, edit, delete functionality. Free for GPL code on Mac, Linux etc. Commercial, but reasonably priced, for Windows.

Report generation sucks. If you don't want to write your own, and can't live with the generally poor level of open source reporting engines, look at Crystal Reports - which sucks too, but sorta works. I've looked at most of the open source / free reporting engines. They may be usable if your standards are very low, but they're pretty nasty.

A few suggestion that aren't exactly free as in beer - as the development environments are commercial. But they'll let you put together a nice, freely redistributable interface to a good, solid backend RDBMS such as PostgreSQL and the effort you'll likely save will be worth more than the sub-$1000 price tags

WebObjects will let you generate most of the view, edit, insert, delete functionality you need with minimal effort. It can be used to generate a standalone java application or a web-based interface. It'll talk to anything with JDBC - including PostgreSQL.

There are a few open-source workalikes of WebObjects too, that you may want to look at.

Another option is Qt. It has a fairly easy RAD environment for developing cross-platform apps in C++, and pretty solid SQL support, including database connected widgets for fairly easy quick-and-dirty view, insert, edit, delete functionality. Free for GPL code on Mac, Linux etc. Commercial, but reasonably priced, for Windows.

Report generation sucks. If you don't want to write your own, and can't live with the generally poor level of open source reporting engines, look at Crystal Reports - which sucks too, but sorta works. I've looked at most of the open source / free reporting engines. They may be usable if your standards are very low, but they're pretty nasty.

"This query is called a left outer join because the table mentioned on the left of the join operator will have each of its rows in the output at least once, whe reas the table on the right will only have those rows output that match some row of the left table. When outputting a left-table row for which there is no right -table match, empty (null) values are substituted for the right-table columns.
Exercise: There are also right outer joins and full outer joins. Try to find out what those do."

when there really should be:

"TODO: There are also right outer joins and full outer joins. FIXME: We MUST write more."

Not to mention the "RTFS" answers in "TFM" for questions very frequently asked by beginners:

"4.3) How do I get a list of tables or other things I can see in psql?"
"You can read the source code for psql in file pgsql/src/bin/psql/describe.c."

Other than that I would say that PostgreSQL is definitely the way to go today. Once you get used to reading the source code as documentation (it is actually very clean and properly commented, so that's not such a big deal), you will really love it. And you will have the most important thing: ACID features. I hope it helps, I wish you the best luck.

See also:

1. http://www.postgresql.org/
2. http://www.mysql.com/
3. http://en.wikipedia.org/wiki/MySQL
4. http://en.wikipedia.org/wiki/PostgreSQL
5. http://en.wikipedia.org/wiki/Firebird_(database_se rver)
6. http://en.wikipedia.org/wiki/Relational_database
7. http://en.wikipedia.org/wiki/Database_management_s ystem
8. http://en.wikipedia.org/wiki/ACID
9. http://en.wikipedia.org/wiki/Relational_model
10. http://en.wikipedia.org/wiki/SQL
11. http://en.wikipedia.org/wiki/Set_theory
12. http://en.wikipedia.org/wiki/Axiomatic_set_theory
13. http://en.wikipedia.org/wiki/Predicate_logic
14. http://www.glom.org/
15. http://www.servoy.com/
16. http://www.dotcomsolutionsinc.net/products/fmpro_m igrator/index.html
17. http://www.firebirdsql.org/

(Please forgive me if I repeat anything which has already been said. I started to write it as a first post but it took some time and I am sure that other

making business justifications for buying Oracle instead of MySQL for the corporate F&A database

If you have trouble coming up with justifications for spending company money, then you might want to review your decision - it just might be wrong.

Or, in this specific case, you could just sidestep the whole problem and get PostgreSQL instead.

...BitTorrent is a boon for open source projects with large files. PostgreSQL, for example, publishes torrents of their releases and the the "PG Live" ISOs. On a much smaller scale, we've put up a torrent for the Ruby windows installer on RubyForge - it's only 11 MB, but even a small file like that is worth torrenting.

PLUG: Here's the beginnings of a Ruby BT library. Just parses the metainfo file for now, but it's a start...

If all software was free, why would anyone bother developing it?

Gee, I can't think of anyone who would develop software without getting paid for it...

But seriously, there are several reasons people would write software whose price is 0:

• People want better software to do $WHATEVER (for values of $WHATEVER that make money, which is most of them), so they write it
• People want to get a job as a programmer so they write a software package to prove they aren't total code monkeys
• People like fame; they like being admired and appreciated
• An industry consortium decides they need an open, standard, free way to do $WHATEVER
• Some people have a political motivation to undermine proprietary software (we may not have that same motivation; but it is a real driving force for some people)
• Some people like to help others (ditto)
• Your company might want to make your product universally (or nearly so) used in order to be able to charge money for training, certification, etc.
• I mentioned 15 high-profile products that are competitive with best-of-breed and are available for $0 (and not all of it is Free as in speech). All of them were written because one of the above bullet points (or one I forgot) applied.

There are lots of motivations for people's actions besides money.

Unfortunately, it doesn't seem as though php supports cursors yet.

It's just SQL, create a cursor and do a "FETCH" in place of a "SELECT".
http://developer.postgresql.org/docs/postgres/sql- declare.html
and
http://developer.postgresql.org/docs/postgres/sql- fetch.html

pgsql_exec($db,"DECLARE ...");
$res = pgsql_exec($db,"FETCH ...");
$foo = pg_fetch_array($res,0);

Unfortunately, it doesn't seem as though php supports cursors yet.

It's just SQL, create a cursor and do a "FETCH" in place of a "SELECT".
http://developer.postgresql.org/docs/postgres/sql- declare.html
and
http://developer.postgresql.org/docs/postgres/sql- fetch.html

pgsql_exec($db,"DECLARE ...");
$res = pgsql_exec($db,"FETCH ...");
$foo = pg_fetch_array($res,0);

I'm not sure why I'm bothering to reply, when you're so obviously a troll who can't do basic arithmetic, but the various possible approaches for backup/restore in Postgres are described in http://developer.postgresql.org/docs/postgres/back up.html. The PITR-related options are new as of 8.0, but we have always had some form of hot backup.

For those of us that use Windows there's now a Windows version of PostgreSQL available. Check it out at here and here.

That's not correct. Check the documentation
http://www.postgresql.org/docs/7.4/static/user-man ag.html

"Every database cluster contains a set of database users. Those users are separate from the users managed by the operating system on which the server runs."

Atomic commits seems to be called transactions in PostgreSQL. More info can be found in the tutorial.
PostgreSQL also has support for Ruby (and Python) which is sufficient to write a lot of useful scripts.

Look at the features of their upcoming 8.0.0 release.

I don't have a recommendation but I do have a strong anti-recommendation.

I normally recommend the associated Oreilly book for most computer topics but in the case of PostgreSQL I have to say Practical PostgreSQL is the worst Oreilly book that I have read. Most of the book is a regurgitation of the freely available postgres manual with an additional chapter that is a blatant plug for the author's proprietary product.

I thought Oreilly could do no wrong until I bought this piece of crap.

PG 8.0 now has point in time recovery and ofcourse fancy stuff with the write ahead log. Theres a script out there somewhere where someone has really hammered the PITR stuff but i cbf looking for the link ;P

What's wrong with Rekall, or perhaps OpenOffice's DB interface (it works very well too) or maybe even using Microsoft Access. There's a commercial one from the UK too but I can't for the life of me remember the right incantation to bring it up in Google.

IIRC they are all programmable. Rekall's programmable in Python, OO in Java, Python and whatever else you can interface to it and Access in VB.

Am I missing something here? Tried to use postgres as the backing store for a java message queue. This involves very rapid INSERT, UPDATE and DELETE operations. Found that it starts out at 100+ transactions per second but drops to less than 20 per second after just one minute. Have to VACUUM continuously just to keep the average up. This is not a solution. How about a table option to eliminate redundant tuples in realtime instead of when VACUUM is run? As it stands, pgsql is not suitable for persistance in message queues.

PS: the PITR stuff in 8.0 is looking really good. Will have to work up some backup/restore shell scripts and give the beta a good workout.

> The PGAdmin3 tool

PLUG: Another good tool - PQA, a SQL query analysis tool. Here's a sample report.

> The PGAdmin3 tool

PLUG: Another good tool - PQA, a SQL query analysis tool. Here's a sample report.

Well, just start a few chapters into the excellent postgres manual, skipping the bits that say "introduction to sql" and paying close attention to bits like "advanced features" and "extending sql"...

Is using count(*) a bad MySQL habbit?

Absoluetely not. There is nothing wrong with it. It's just the developers don't want to have to hard code an optimal path for that aggregate. The issue exists because PostgreSQL supports user extensible aggregate functions, whereas most DB's don't.

PgSQL is difficult because it has unusual problems, not expected from experience with Oracle/MSSQL/MySQQL

Well, that I'm not willing to swallow. Simple fact is, each and every RDBMS has their own distinct sets of issues, problems, caveats. If something as simple as "select count(*)" is a serious problem, then chances are, you shouldn't be using a database to begin with. While clearly more cumbersome, triggers are easily implemented which allow you to easily and rapidly store derived values, such as counts. This is a very, very minor work around compared what things that MySQL forces people to do. The primary difference is that most people don't realize they they are doing things they hardway with MySQL until they get experience on other systems.

As for aggregates, I know PgSQL functions can't use indexes by design.

I'm not sure we're talking about the same thing here, but PostgreSQL has allowed functional indexes for some time now. But I don't think it would make sense to think that you can create a functional index on an aggregate. I think they were introduced in 7.1 or 7.2. Here's a reference to the 7.2 docs which talk about it.

One quite capable DB admin worked on it full time for about two weeks, another one consulted. They tried everything imaginable. Still, running vacuum slowed selects by 5-10 times. That was not acceptable during the day, thus we could run it only at night. And that was not often enough.

I certainly don't mean to knock your DBAs. My point is, did they attempt to get support from the mailing lists? Did they use the available documentation to it's fullest? Thinking that a Sybase DBA is going make an Oracle database sing, without Oracle DBAs helping, isn't very realistic. That's my point. Far too often, I see people state that they had problems, usually very, very basic problems, but never once bothered to get help from the people that are most qualified to help. Whos failing is that? PostgreSQL's or the DBA's? Obviously, I can't comment on the specifics here, but I think you can understand where I'm coming from.

Yes, you can say they were not experienced enough. I can also say that the configuration was overly difficult.

Well, I certainly do believe that there is a learning curve. You'll find that any non-trivial RDBMS has such a curve. IMO, PostgreSQL is no greater than any other. In fact, I would argue that it's certainly far less steep than Oracle or MSSQL. Having said that, just because there is a curve, is not an excuse (making an assumption here) to not obtain assistance from the wonderful support mechanisms which are readily available to any PostgreSQL user.

I mean a dedicated server, as in nothing but PgSQL 7.4 on a FreeBSD 4.9. Say, the DB is 1GB, server has 2GB RAM. PgSQL should just suck everything in RAM and run read queries directly from there. Why can't it do it? Or maybe it can, but how to configure it in such a way is really non-obvious.

Well, unlike most DB's, PostgreSQL assumes that the OS will do a fair share of caching. This means, you want to leave a fair amount free for system cache. Having said that, for the internal buffers that PostgreSQL does keep, version 8.0 does add several optimizations which greatly enhances many types of workloads (seq scan no longer flushes buffers of commonly used rows, etc).

Many more specifics can easily be gained from reading the documentation, reading the easy to follow tuning guide, which I previously pointed people at, or heck, either doing simple searches of the archives or simply posting a question to a ma

Honestly, have you ever worked with PostgreSQL? I have for the past year, and the docs are excellent. There's a few odds and ends, but I have never seen any docs without them.

I beleive you have made a mistake. The error you are recieving is due to a failed statement in your transaction. I have tested his example in 7.4.3 and found it to be correct. I even removed the sequences from the example and the block still happened. I don't know why the block is occuring. Maybe because the reference checks in both transactions are trying to read the same row? That doesn't right so I don't know.

As a previous post said, changing the isolation level will probably fix the problem. Here is the documentation.

• More recently, .org and .info domain registry company Afilias has sponsored developer Jan Wieck to work full time on developing a new, enterprise-class replication system for PostgreSQL called Slony-I, to be presented next month at OSCON in Portland, Ore.

What's slightly noteworthy about Slony is that it has "switchover" support. If the master fails, one of the slaves become the master.

Of course, since Slony's replication is asynchronous, it means that at the point of switchover, slaves might not have received all pending changes, thus becoming out of sync with the master. Unfortunately, Slony doesn't really address this problem.

The design overview is useful reading, if rather confusing; the writer's first language is probably not English.

• More recently, .org and .info domain registry company Afilias has sponsored developer Jan Wieck to work full time on developing a new, enterprise-class replication system for PostgreSQL called Slony-I, to be presented next month at OSCON in Portland, Ore.

What's slightly noteworthy about Slony is that it has "switchover" support. If the master fails, one of the slaves become the master.

Of course, since Slony's replication is asynchronous, it means that at the point of switchover, slaves might not have received all pending changes, thus becoming out of sync with the master. Unfortunately, Slony doesn't really address this problem.

The design overview is useful reading, if rather confusing; the writer's first language is probably not English.

Myself, I use Eclipse to do all my coding, and I find the Quantum DB plugin to Eclipse to be awesome. Because we use Oracle, mysql, and Postgres, it's handy to have one piece of software handle all the DBs (so long as you have the right JDBC driver.

"8 bit clean"? Oh, now you've convinced me... So the fact that I have the inconvenience of running my data through escape_bytea() before storing it as a binary string is THE major reason why PostgreSQL shouldn't be used? That discounts anything else about what a DBMS should do?

Yeah, PostgreSQL doesn't support 'standard' data types, and MySQL does? I really hope someone isn't reading this thinking you are serious. (You are not serious, are you?). MySQL's support for real datatype constraints is so bad that it should be considered typeless.

The whole point of a serious DBMS like PostgreSQL is not to allow your data to do things it shouldn't. If you don't want constraints or real data integrity, then fine; Use MySQL. Just be aware that your application still has to reimplement half the things that the DBMS normally handles.

I've lately read that one thing where PG shines is, that it actually would compress TEXT and *CHAR fields on-the-fly. And since base64 compresses quite well this would also accomodate any redundancy in binary files, so files may occasionally even become slower when stored this way (instead of in BYTEA fields).

Btw, does PostgreSQL have row-level locking yet?

Static docs provide your answers.

Remember, one of the points of using MVCC is to avoid row locking whenever possible. But, I think you'll be hard pressed to come up with a situation where your desired locking facilities are not provided for with PostgreSQL.

To quote the online documentation:
"In addition to table-level locks, there are row-level locks. A row-level lock on a specific row is automatically acquired when the row is updated (or deleted or marked for update). The lock is held until the transaction commits or rolls back. Row-level locks do not affect data querying; they block writers to the same row only."

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

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

There's two ways to do binary types.

One way, of course, is to use a binary type, which can be tricky, depending on your client API. It mostly sucks if you are using an actual text-based insert query and response, whereas most of the time, there's *also* a way to use a more binary-oriented interface -- using the binary COPY interface and the relevant C API.

The other way is to use the large object support.

There's two ways to do binary types.

One way, of course, is to use a binary type, which can be tricky, depending on your client API. It mostly sucks if you are using an actual text-based insert query and response, whereas most of the time, there's *also* a way to use a more binary-oriented interface -- using the binary COPY interface and the relevant C API.

The other way is to use the large object support.

There's two ways to do binary types.

One way, of course, is to use a binary type, which can be tricky, depending on your client API. It mostly sucks if you are using an actual text-based insert query and response, whereas most of the time, there's *also* a way to use a more binary-oriented interface -- using the binary COPY interface and the relevant C API.

The other way is to use the large object support.

The SQL standard defines a different binary string type, called BLOB or BINARY LARGE OBJECT. The input format is different compared to bytea, but the provided functions and operators are mostly the same.

create function GetEmployees?() returns setof employee as select * from employee; language 'sql';