Slashdot Mirror

http://www.postgresql.org/

Just saying.

What do you find so hard? You have to install an extra module, but it'll only take a few minutes of your day. This is Levenshtein matching, but it's just an example. There are plenty others.

SELECT firstname, lastname, unaccent(c.lastname) ~* unaccent('Smith') AS match FROM people WHERE (unaccent(c.lastname) ILIKE 'Smith' OR c.lastname % 'Smith') ORDER BY match DESC

http://www.postgresql.org/docs/8.3/static/fuzzystrmatch.html

*No* modern, desktop-usable file systems today guarantee new files to be there if the power goes out except if the application specifically requests it with O_SYNC, fsync() and similar techniques (and then only "within reason" - actually the most guarantee that the file system will recover itself, not the data). It is universally true - for UFS (the Unix file system), ext2/3, JFS, XFS, ZFS, raiserfs, NTFS, everything. This can only be a topic for inexperienced developers that don't know the assumptions behind the systems they use.

The same is true for data ordering - only by separating the writes with fync() can one piece of data be forced to be written before another.

This is an issue of great sensitivity for databases. See for example:

• http://postgresql.mirrors-r-us.net/docs/8.2/static/runtime-config-wal.html
• http://archives.postgresql.org/pgsql-performance/2004-09/msg00080.php
• http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm

That there exist reasonably reliable databases is a testament that it *can* be done, with enough understanding and effort, but is not something that's automagically available.

I think this one further in the thread contains a better explanation:

a NOT IN b is equivalent in the spec to NOT(a IN b). a IN b is equivalent
to a =ANY b. a =ANY b returns true if a = x is true for any x in b. a =ANY
b returns false if a = x is false for all x in b. Otherwise it returns
unknown.

0 = NULL returns unknown
0 = 1 returns false
So, 0 IN (NULL,1) returns unknown.

NOT(unknown) is unknown.

WHERE clauses only return rows for which the search condition is true, so
a row is not returned.

That's a misunderstanding of the spec. NULL has no type, so evaluating NULL = 1 results in an unknown. That does not imply that NULL is an unknown value. I believe this reply on the PostgreSQL mailing list explained it best:

0 <> NULL (Indeed nothing equals NULL, other then sometimes NULL itself)

0 <> 1

Therefore, the statement: 0 NOT IN (NULL, 1)
Should always equate to false.

Therefore No rows returned. Ever.

It's a bit weird, but it makes sense when you actually follow the logic.

But none of those are failures of the databases themselves. I won't blame MySQL if a meteor takes out the data center.

Actually, what you said was "A database should work 100% of the time, for everyone, and discussion should revolve around features or price or support. It should go without saying that it never loses data. That's just not something you should ever even have in the back of your mind.", and I was bringing up those other failure modes to reinforce the fact that you should always have data loss in the back of your mind.

No one is offering you a 100% guarantee that when you put data into your RDBMS, you will necessarily be able to get it out. This is why god invented backups, binary logs, point in time recovery, etc. Even your beloved PostgreSQL has an entire facility for Backup and Recovery. ;)

I'll blame the heck out of it when I get "Table 'FOO' is marked as crashed and should be repaired" for no apparent reason.

If you are going to chastise me for bringing up older pg examples, you are going to need to start talking about InnoDB. MyISAM made some design choices to favor speed and simplicity over data consistency, and this fact is not in dispute.

InnoDB will not randomly tell you your table is corrupt and lock you out until you issue some weird command.

I think you might want better examples next time.

OK, here are over 70,000 of them: http://www.google.com/search?q=postgresql+data+loss.

What did you say your point was, again? "That it's happening to some people often enough that people are talking about it."? ;)

Any database might, for one reason or another, eat your data. I'm not sure why you insist upon acting so surprised at this fact of life.

Any database can experience data loss. That includes Oracle, SQL Server, and even your beloved PostgreSQL. This can happen for any number of reasons, including (but not limited to) hardware failure, power failure, user error, etc.

Postgres isn't going to help you if you forget a WHERE clause. Oracle isn't going to help you if your RAID is corrupted.

FWIW, I have never had a MySQL database lose data, and I have committed more "user errors" than I'd like to admit. Hell, I once pulled up the wrong window and moved the frickin' database file right out from under a running MySQL server to another volume. No data loss.

Also FWIW, it is possible to experience data loss with Postgres, where it is Postgres's fault (as opposed to the RDBMS not being able to recover from some external fault). Example 1. Example 2.

• Postgres only just got ENUMs with 8.3 (2008-02-04) [1]
• Oracle still doesn't have them yet.
• MySQL has had them since 2002 if not earlier [2]

ENUMs are an absolutely essential feature when doing data modeling.

[1] http://www.postgresql.org/docs/8.3/static/release-8-3.html

[2] According to MySQL documentation on ENUMs; there are comments there from 2002.

i'm glad that i'm not the only who actually pronounces it as "S-Q-L" instead of "sequel."

i don't know why, but i've always thought it was weird to call MySQL "my sequel" or to say "sequel code," though i guess that's how most people in the industry pronounce it. and i've always just pronounced PostgreSQL post-greh S-Q-L, but apparently the developers called it post-gres Q-L according to Wikipedia and this mp3 clip on the official PostgreSQL site.

now if i can just figure out how to pronounce "Bjarne Stroustrup..."

I think PostgreSQL has suffered because people don't know how the hell to pronounce the damn name. You can't recommend or adopt a piece of software if you are too embarrassed to say its name.

Of course, the other side of that coin is Drizzle. Easy to pronounce, but WTF?

Why fork it? Just let it die.

There is a serious, open source, Object Relational DBMS available.

Yes, and Vim is better than Emacs. Why don't we discuss that too?

Why fork it? Just let it die.

There is a serious, open source, Object Relational DBMS available.

If your DB server is swapping, either you've misconfigured things, or some process has run away with your memory.
Keep your swap file small so that the OOM killer kicks in sooner rather than later.

What you say makes sense, but unfortunately Oracle doesn't. The Oracle installer simply won't start if you don't have at least 1GB of swap space, no matter how much RAM you have.

Want something that works well? Get Postgres. Want to make your boss happy and keep your job? Get Oracle... Sad but true :(

Mysql has manuals online, as does postgresql and Oracle.

The GPL keeps you from taking my code and locking it up in some proprietary application where I won't get to use it.

So the existence of these proprietary postgres-derived databases means that you don't get to use postgres because its code is now "locked up"?

Wanna code? Great, go for it. But consider this: what Open Source really needs is more people who can write decent documentation, create user interfaces that actually work, and draw pretty icons.

How many Open Source projects are there with a homepage that doesn't even bother to tell you what the project does? If the first words on your homepage are not the name of your project, followed by about three lines of very clear descriptive text, you have lost most of your prospective users already - they will never figure out what your great project is in the first place. And if you have a user interface of any kind, stick a screenshot or two in there already.

Next, documentation. There are some really, really great Open Source projects out there that I'm sure I could get much more out of, if only I could figure out how. This is true for API's and for complete applications.

And some existing applications would be more pleasant to work with, if only someone did some prettification on the user interface. I cannot understand why noone has gone and drawn good-looking OpenOffice.org icons yet - or why the OOo team hasn't paid someone to do it.

If you want to see a project that gets it absolutely right, go over to http://www.postgresql.org./ The name is at the top. Directly next to it is a 7-word description of what it is. The documentation is available for current and older versions, and is top-notch. Really, this is what we need far more of.

Incidentally, does anyone know a place where I can order custom-drawn icons and bitmaps at cost? I'm looking for "good-looking" and "repeatability": if I need ten extra icons next year I want to be able to get them in the same style.

Actually, the worst they can do is not apply it and decide you're incompetent/don't play by the rules/etc. Then you risk your future submissions being less likely to be considered even if you improve later. The person who wastes the time of a patch reviewer is not soon forgotten by that reviewer.

It really is better to not submit a patch at all if you don't know what's going on yet, which is exactly why guides like this one are helpful. I've worked on a similar one for PostgreSQL because it's hard for new people to pick up the unique requirement quirks of a group of developers, and lowering that barrier improves the health of the project.

I don't see what this has over EXPLAIN and an appropriate graphical display tool like PgAdmin-III. There are large numbers of tools that display graphical query plans - and unlike this simple SQL parser, they know how the database will actually execute the query once the query optimiser is done with it.

Furthermore, a simple SQL parser has no idea about what indexes are present, available working memory for sorts and joins, etc. It can't know how the DB will really execute the query, without which it's hard to tell what performance issues may or may not arise.

See comment 24461217 for a more detailed explanation of why this whole idea makes very little sense.

Can you expand on what you mean? Some databases have a more expanded view of what partitioning means but PostgreSQL does support partitioning.

I think you're confusing prepared statements with stored procedures. See this wikipedia article for an overview of what a prepared statement is.

As to stored procedures, they allow you to centralize business logic, validations, and a ton of other functionality in your database so that everything that interacts with your data follows the same rules and gets the same stuff back.

They are quite flexible and mature in Postgres and you can write them in many different languages. They are very new in MySQL, you can only use SQL and I'm sure they are laden with quirks.

I think I disagree on 'rollback prepared'; PREPARE signals intent to COMMIT, and a successful PREPARE is a promise that nothing will impede a later COMMIT.

If you have a table "employee" with a FK "department_name" referencing department, and you have two transactions:
1) INSERT INTO department VALUES ('accounting', ...);
2) INSERT INTO employee VALUES('joe', ..., 'accounting');

Your proposal is to allow #2 to commit so long as #1 has prepared. But what do people see in the window in between when #2 commits and when #1 commits? They would see an employee without a department.

Are you free to elaborate on your overlap constraint implementation?

Here's my original proposal:

http://archives.postgresql.org/pgsql-hackers/2008-06/msg00404.php

Even finding overlaps isn't a terribly efficient operation, unless you're also using a form of spatial indexing on your custom datatype

I am.

My reply is somewhat brief because I am on my way out the door.

nifty features like on-commit triggers that let you enforce constraints no other database will help you enforce

Can you be more specific? PostgreSQL offers something called "constraint triggers", which can be deferred until commit time (using the same semantics as deferred FK checks).

http://www.postgresql.org/docs/8.3/static/sql-createconstraint.html

someone already did

I'm unfamiliar with MySQL's partitioning -- is it radically different from postgresql's partitioning?

I'm using inheritance to implement table partitioning with a rather large (50+ gig) PostgreSQL/PostGIS database. Constraint exclusion allows the query planner to use CHECK constraints to avoid even looking at tables where conditions contradict the constraints.

I'm unfamiliar with MySQL's partitioning -- is it radically different from postgresql's partitioning?

I'm using inheritance to implement table partitioning with a rather large (50+ gig) PostgreSQL/PostGIS database. Constraint exclusion allows the query planner to use CHECK constraints to avoid even looking at tables where conditions contradict the constraints.

Also, at least one application is BSD licensed yet has the option to link against Readline and GNU doesn't seem to care.

Which, afaik, could bite them in the ass (from a legal standpoint) at some point, because they're choosing to selectively enforce the GPL.

Thank you. I was trying to post the same, but I apparently lost my cookies when I upgraded to FF3. Many projects use the {Major}.{Minor}.{Bug/Service Release} model, pretty much as described, but many only appear to use the model. For some, the second digit is the major version.

First of all, it would be

DELETE * FROM comments WHERE poster_name="Anonymous Coward";

Actually, no, it wouldn't. The DELETE command doesn't take field names. You'd either do an ALTER TABLE or an UPDATE to do what you want.

(yes, I checked against multiple SQL references, all for different products, before opening my big mouth.)

> 70% of most code is just common-everyday stuff that doesn't
> NEED to be documented in the sense that comments are completely wasteful.

So true! Rather than this code:

# Finds the most recent orders for the passed in person
def get_rec(p)
    # blah blah
end

I'd much rather see an intention-revealing method name (hat tip Marcel Molina):

def find_recent_orders_for(person)
    # blah blah
end


I'm still not really sure what documentation is really useful - maybe a few diagrams plus some use case descriptions that go through the code, maybe? I'm not sure. I guess it depends on the project - it is a widely used library? Is it an internal department app to track the coffee fund? etc.

My experience with open source code has been that the large projects have decent docs... I was just reading through some of the PostgreSQL docs on backups this weekend and they're quite good.

Also, if you are going to try it out, please also have a look at the wonderful PgAdminIII. Brix will be shat, as they say... ;-)

Aye. I've often wondered why there are no equivalents to Windows Script in the open source world.

What excites me about it in general is its usefulness for desktop applications. For example, the Klient IRC client uses it to support 6 separate languages with very little effort. If you want to do something similar for non-Windows platforms, you're pretty much stuck implementing a separate engine/interface for every single language yourself.

Once you have a basic architecture like that in place, you can add security controls to it (like PHP's deprecated "safe mode", or a pervasive version of Perl's taint system), and end up with something useful in the browser. Even non-web apps have uses for this sort of thing; PostgreSQL has similar needs and has ended up with "trusted" and "untrusted" language plugins.

Now Microsoft is working on the same thing for .NET, which is what the DLR is for. Their approach of using verifiable code in a VM sandbox is sound, and provides some much needed security. However, between Microsoft hate and NIH syndrome, I suspect the open source world will ignore the idea of DLR (even with Moonlight around), and we'll be yet again left without a decent common framework for scripting languages.

Which means getting such a thing in even the top 3 browsers will be impossible. Sigh.

This might be true, however only if: 1 every attribute changes for every record 2 if they are not related to each other 3 when each column can appear in a WHERE clause. And IMHO it is still bad design to have 1000+ separate unordered attributes for a single entity. I would like to see a real world example where this is really needed. When you can make a logical subdivision: PostgreSQL implements array's (also 2D), where you can store vector type data(or "multiple columns") up to 1 Gb, but more importantly it implements user defined composite datatypes: http://www.postgresql.org/docs/8.3/static/sql-createtype.html Assuming there is some logical subdivision to make in your 1000+ attributes, this can be implemented in a "few" user defined types. And another method in postgresql would be to use inherited tables, where the attributes that are the same for every record are inherited and the attributes that are specific per project are added to the child table for that project. And yes, sub-queries can be difficult, but wading through a 1000+ attributes in a single table and maintaining integrity constraints are as well.

How about PostgreSQL? http://www.postgresql.org/

two of the big downsides of open source software to me are the lack of documentation

Proof, please. Documentation is highly dependent on a number of things, not the least of which is the projects you use. This is true in any paradigm, OpenSource, proprietary, something-you-bought-at-Walmart, or any other project. For instance, from my point of view (as all things are, eh?) the Postgres has absolutely excellent documentation. Not only does it describe options, tools, and how to setup and use Postgres, but it gives you context, like when one should consider a certain setup or action, what the known bugs/caveats exist, and even the core concepts of lots of problems. It is so good, in fact, that even when I use other database products, I quite often will find myself using the Postgres documentation to help me understand how to better to solve my problem with the other database.

Now juxtapose Postgres' documentation with, say, that of OpenOffice. The OpenOffice documentation has the advantage of context sensitive help. Whenever I click a help button, it doesn't just point me to "the docs", but it opens up the exact page and scroll position of where I should start reading. However, it's documentation is not quite as thorough as Postgres. I will often have to do some experimentation before I understand exactly what I've messed up or need to do.

And finally, for a third example, take a look at Mozilla Thunderbird. It doesn't even include help (at least my copy of it through Gutsy), but points me back to the website (via the Help menu). Perusing the website, the best documentation I see is a series of Howto's for different specific tasks. Not very thorough.

Saying that OpenSource documention "sucks" [paraphrased] is inaccurate and way too general. It also attempts an untrue quality distinction from proprietary software. Have you ever had to deal with Microsoft errors? For example, the Windows Update Tool (via Internet Explorer) will sometimes fail, and yields merely a diagnostic code. So, you put the code in the search, and the documentation is a sparse help page saying that the update may have failed for one of a few reasons. The usual suggestion is to reboot and try again. You do so, and get the same error message. I'm not saying anything about the quality of the product, but of the unhelpful documentation from a proprietary company. (I have plenty of other proprietary-documentation-sucks examples if you'd really like.)

And, just like with OpenSource, there is good documentation with different proprietary products as well: Oracle provides some good documentation with their database. The best documentation from them is not free, but the Oracle administration handbook (read: frickin' monstrous club) is very helpful and well-written.

and the lack of quality control.

This is the real issue this time around, and also is hard to nail down exactly what it means. What is quality? Security? Lack of crashes? Useful-to-users? Once again, this is also highly project and problem dependent. Since I've already fanboied the Postgres project, I'll use it again: extremely high quality product for the problem the project attempts to address. You want an ACID database? Look no further. Postgres makes you work really hard if you want to corrupt your data. It even has transactional DDL statements. (Oracle doesn't even have that.) You want security? The entire Postgres project, from the product, to the documentation, and even to the community, practice and preach doing things "the right way."

On the other hand, then there are examples like the OP, that let this trojan creep in. Where's the quality control, you ask? Well, for my purposes, Firefox is still an incredibly high-quality product. From a historical perspective, the community is obviously creative as Firefox had tabbed browsing way before, at least IE. From an Engl

I'm a big fan of PostgreSQL, the most advanced open source database server. For the small to medium sized production facility (about 50 employees) where I do both administration and development, postgres running on Slackware is a perfect fit. It's almost a joy to administer.

Post / Grez / Queue /El

Because it is such a common question (and such an odd name), it is aptly an entry in the PostgreSQL FAQ.

If you don't like my phonetic spelling and prefer IPA, Wikipedia has you covered.

Many people, myself included, simply call it Postgrez.

MySQL was outstanding. At least in my opinion. Where Oracle took 2 GB of space to download then had to be burned to a CD, MySQL was a lightweight, straight-forward relational database managment system.

Closing off MySQL will have serious consequences for PHP and C developers since SUN will rewrite the entire MySQL API strictly in Java.

Perhaps now would be a good time to migrate to PostgreSQL.

Goodbye MySQL. :-(

For PostgreSQL :) http://www.postgresql.org/

Would you like another round of ammo with that foot gun Sun?

MySQL has nearly caught up to PostgreSQL in terms of features.

PostgreSQL's Generalized Search Tree (GiST) indexing is still better than anything MySQL has to offer, in terms of performance and capability.

The PostgreSQL OpenFTS full text search engine is another marvel of engineering. It routinely outperforms similar extensions for MySQL in terms of performance, memory usage, and concurrency.

I hope that an upcoming release of MySQL deals with the maximum field size problem. With PostreSQL, there is a max field size of 1 GB. For MySQL, it's a mere 50 MB. For textual representations of certain geographic system data, it's not unusual these days to have individual fields that need to store 500 to 600 MB of data. PostgreSQL handles these fields fine. MySQL fails.

My company requires that there be multiple backups at all times

Master/spare is trivial with WAL shipping. Start with http://www.postgresql.org/docs/current/interactive/continuous-archiving.html , then read http://www.postgresql.org/docs/8.3/static/warm-standby.html
You start with a backup, then the spare site keeps processing the master's WAL segments to keep it's database up-to-date, then when the master falls over, you can fire up the spare which would be up-to-date as of the last WAL segment it received (you can set it to produce a WAL segment file after N seconds in case the 16MB segment isn't full). So, if nobody cares if database2 is actually available for load balancing, etc, then you can set this up, do a full backup of database1 every sunday night, ship WAL segments until sunday morning, and during the day sunday you test database2 to make sure it's working (did it process all of the WAL segments correctly, drives are functioning, etc) before wiping it and starting over with the next full backup.

The great thing with this is that you can have multiple warm spares without any extra stress on the master db or interfering with actual usage (actual live replication would require more and more delay in order to get more and more servers to agree to commit each transaction or obey each lock) simply by sending the log segments to multiple databases, though you need more administrative work to ensure that only one of the spares becomes "live" (and the other spares need to start receiving logs from that new live server, starting with a new full backup)

My company requires that there be multiple backups at all times

Master/spare is trivial with WAL shipping. Start with http://www.postgresql.org/docs/current/interactive/continuous-archiving.html , then read http://www.postgresql.org/docs/8.3/static/warm-standby.html
You start with a backup, then the spare site keeps processing the master's WAL segments to keep it's database up-to-date, then when the master falls over, you can fire up the spare which would be up-to-date as of the last WAL segment it received (you can set it to produce a WAL segment file after N seconds in case the 16MB segment isn't full). So, if nobody cares if database2 is actually available for load balancing, etc, then you can set this up, do a full backup of database1 every sunday night, ship WAL segments until sunday morning, and during the day sunday you test database2 to make sure it's working (did it process all of the WAL segments correctly, drives are functioning, etc) before wiping it and starting over with the next full backup.

The great thing with this is that you can have multiple warm spares without any extra stress on the master db or interfering with actual usage (actual live replication would require more and more delay in order to get more and more servers to agree to commit each transaction or obey each lock) simply by sending the log segments to multiple databases, though you need more administrative work to ensure that only one of the spares becomes "live" (and the other spares need to start receiving logs from that new live server, starting with a new full backup)

Why PostgreSQL Instead of MySQL: Comparing Reliability and Speed in 2007

I'll have a 2008 update out soon now that PostgreSQL 8.3 has been released.

Managing that with slony just plain sucks

Dunno what kind of requirements you have, but if you just want to be able to update master database A and have changes immediately appear in read-only databases 1-12, using pgpool (to run inserts/updates across all databases) for whatever system is in charge of updating the databases while the read-only clients connect to their respective databases directly might be far easier to manage than a real "replication" setup (and if you guarantee the clients only have read-only access and pay attention when pgpool tells you something failed, everything will be consistent. If you use sequences/serial columns for primary keys or other default values, read its docs on table locking to ensure that the sequences are consistent). Bonus points because you can get pgpool to repeat CREATE DB and ALTER TABLE commands across all servers, other replication systems just shit themselves at that point ;)

If you're looking for something that ships updates only nightly or something like that, you'll probably have to either write yourself a custom system or pay someone else to do it. Depending on downtime requirements you could use postgres's filesystem/WAL backup from the master server (no downtime needed) nightly, then ftp'd over to the other servers (requires shutdown, replace filesystem entries, restart... see the PITR documentation http://www.postgresql.org/docs/current/interactive/continuous-archiving.html since that's based off of the same principle except for stopping the recovery process at a given point in time rather than doing a complete recovery) This is what we're looking into for an offsite hot spare... start with a backup and update with WAL segments until the main site is nuked.

The release notes for 8.3 mentioned that both Bucardo and Slony-I replication packages are included. Both of those are also usable on releases older than 8.3.

http://www.postgresql.org/about/press/features83.html

There is a number of access like applications for postgresql.

Checkout the postgresql website.
http://www.postgresql.org/docs/interfaces

Here are two postgresql form creators I have found useful in the past
http://www.glom.org/
http://www.treshna.com/bond/

The subject of this article, EnterpriseDB, is trying to target this market with GridSQL. As it's new in it's current form, impossible to say how reliable systems built with it will be quite yet. Those looking for reasons behind the IBM investment might consider whether GridSQL might one day talk to DB2 databases as well.

The closest fully open-source PostgreSQL solution to your requirements that's been around a bit is pgpool-II. It think it's still too immature to be considered five-nines quality though, and there are some restrictions you have to observe. A PostgreSQL replication solution that is very robust and proven is slony but it's not a load-balancing solution in the way I suspect you want.

There's also the Greenplum Database, which isn't free or open-source but is rooted in PostgreSQL technology.

Good enterprise-grade clustering with load-balancing is still on the PostgreSQL work in progress list rather than being here right now. I expect the core infrastructure piece needed to really make it work well (support for read-only warm-standby slaves) will make it into PostgreSQL 8.4 and be released around a year from now. I started a comparison page of the replication solutions currently available that's on the PostgreSQL wiki now that is trying to track progress in this area. Much like core PostgreSQL support for enabling replication, it still needs some work .

The subject of this article, EnterpriseDB, is trying to target this market with GridSQL. As it's new in it's current form, impossible to say how reliable systems built with it will be quite yet. Those looking for reasons behind the IBM investment might consider whether GridSQL might one day talk to DB2 databases as well.

The closest fully open-source PostgreSQL solution to your requirements that's been around a bit is pgpool-II. It think it's still too immature to be considered five-nines quality though, and there are some restrictions you have to observe. A PostgreSQL replication solution that is very robust and proven is slony but it's not a load-balancing solution in the way I suspect you want.

There's also the Greenplum Database, which isn't free or open-source but is rooted in PostgreSQL technology.

Good enterprise-grade clustering with load-balancing is still on the PostgreSQL work in progress list rather than being here right now. I expect the core infrastructure piece needed to really make it work well (support for read-only warm-standby slaves) will make it into PostgreSQL 8.4 and be released around a year from now. I started a comparison page of the replication solutions currently available that's on the PostgreSQL wiki now that is trying to track progress in this area. Much like core PostgreSQL support for enabling replication, it still needs some work .