Slashdot Mirror

There's always PostgreSQL.

/continuing being too serious ;)

plJava

Did you already take a look at slony?
http://gborg.postgresql.org/project/slony1/projdis play.php

"a view which is automatically populated by the database like a table".

I suppose you are referring to materialized views, which are also present on Oracle. It ~is the base of ROLAP a "type" of OLAP (for you acronym lovers). I'm afraid Postgres doesn't have real support for this, yet. There is a project which aims to add it, but it is in a very early state, and it hasn't been updated for a year.

Change Column Types

A column's data type can now be changed with ALTER TABLE.

Before, to do this, you had to create a temporary table with the changed column type, copy all the data over to it, and then rename the temp table as the old table.

Thank you Postgres team! Now, if we can rename a column, that would be a nice bonus.

Looks like there are are whole bunch of companies that specialize in PostgreSQL. Not sure where you get only from. http://techdocs.postgresql.org/companies.php

Hot installation was an explicit design goal of Slony-1.

You have to compile Slony-1 for which the sourcecode from PostgreSQL needs to be present on the system you are compiling on. After that, you log in to your database and run a SQL script to tell the database where the compiled code is. That is it, no restarts, no switching of the PostgreSQL binary required.
This dynamic adding of extensions is one of the standard features of PostgreSQL and is used for many extensions. For instance, all the stored procedure languages (pl/pgsql, pl/R, pl/PHP, pl/Perl etc.) can be added this way, as do large features such as PostGIS.

Here ya go

You may want to wait a week or so until they get caught up to Postres...

For lite uses, and many heavy ones, SQLite seems excellent. I haven't used it yet, but whoever writes for the project is an excellent communicator.

In my experience, most open source projects, and almost all commercial products, have a (maybe mostly unconscious) plan: "We will carefully measure how much hassle people will accept, and make sure we don't document anything more than enough to just barely keep people from rejecting us."

It's common to visit an open source project and find that, yes, they have a new version, but the manual is two years old. There are plenty of commericial projects that are the same way, like Netgear's FVS318. Their reference manual is for version 1.4, but the latest version is 2.4.

An advantage of open source projects is that they are usually far more honest than commercial projects. I love this from the PostgreSQL What's New page: "Although tested throughout our release cycle, the Windows port does not have the benefit of years of use in production environments that PostgreSQL has on Unix platforms and therefore should be treated with the same level of caution as you would a new product." Marketing people are generally so dishonest that they would not allow an honest statement like this.

MySQL is a non-standard implementation of SQL. That's a problem that's probably partly caused by not doing good documentation. If they had documented everything as they wrote MySQL, they might have seen what a mess they were making. Bad documentation obscures programming messes.

PostgreSQL has an elaborate documentation system, and the new features are very impressive.

For lite uses, and many heavy ones, SQLite seems excellent. I haven't used it yet, but whoever writes for the project is an excellent communicator.

In my experience, most open source projects, and almost all commercial products, have a (maybe mostly unconscious) plan: "We will carefully measure how much hassle people will accept, and make sure we don't document anything more than enough to just barely keep people from rejecting us."

It's common to visit an open source project and find that, yes, they have a new version, but the manual is two years old. There are plenty of commericial projects that are the same way, like Netgear's FVS318. Their reference manual is for version 1.4, but the latest version is 2.4.

An advantage of open source projects is that they are usually far more honest than commercial projects. I love this from the PostgreSQL What's New page: "Although tested throughout our release cycle, the Windows port does not have the benefit of years of use in production environments that PostgreSQL has on Unix platforms and therefore should be treated with the same level of caution as you would a new product." Marketing people are generally so dishonest that they would not allow an honest statement like this.

MySQL is a non-standard implementation of SQL. That's a problem that's probably partly caused by not doing good documentation. If they had documented everything as they wrote MySQL, they might have seen what a mess they were making. Bad documentation obscures programming messes.

PostgreSQL has an elaborate documentation system, and the new features are very impressive.

Slony1, open source postgresql replication!

Read the fucking webpage: PostgreSQL is a highly-scalable, SQL compliant, open source object-relational database management system.

Do you know of any database (free or commercial) that supports such a feature (auto)magically?

yes : this one

see http://slony.info/ as the other poster mentioned

... and it would really really rock!

• Hot tablespace-based backups, combined with write-ahead log backups, as seen in Oracle since version 8 (or even 7?) This is an extremely nice feature when you have large databases and no chance for regular scheduled downtime and still want backups, both complete and incremental ones. Compared to the export-based feature of PostgreSQL, it would put way less load onto the server, because tablespace-based and WAL-based backup bypasses the SQL engine, so it is copying a (for example) 200 GB files vs. 200 GB query-based export
• Better and more integrated replication. There are a number of independent projects that want to create replication add-ons, like pgreplication and the older, more academic Postgres-R, but that's not really production quality so far. According to some consultant that is working tightly with PostgreSQL and the developers, they are working on it, but he was really hedging when asked about advanced features and was theorizing how practically impossible and/or expensive multi-master realtime replication would be... An optional feature for many users, granted, but still something you might want for scaling beyond certain limits.

Said that, PostgreSQL is a really great thing, and being FOSS, I could of course always go ahead and add the named features... .)

Slony is what you're looking for.

Yes, you can vacuum your tables without locking them.

Master-Slave replication is available through Slony1. (This is currently used by Afilias on the .info domain) Slony2 is in progress and will provide multi-master replication.

Vacuuming is still necessary, but it no longer locks tables. The distribution includes a utility called pg_autovacuum which can take care of all the vacuuming tasks on an automated basis if you desire. (The gentoo release automatically installs this with a nice init script :) )

PostgreSQL offers substantial additional power by incorporating the following additional concepts in such a way that users can easily extend the system:

• inheritance
• data types
• functions

Other features provide additional power and flexibility:

• constraints
• triggers
• rules
• transactional integrity

These features put PostgreSQL into the category of databases referred to as object-relational. Note that this is distinct from those referred to as object-oriented, which in general are not as well suited to supporting traditional relational database languages. So, although PostgreSQL has some object-oriented features, it is firmly in the relational database world.

From what I understand, internally PostgreSQL datatypes are represented as objects/structs. The way it supports User Types suggests this. Is it really an Object Database as in the imfamous ODBMS? No, but the name seems apt.

CREATE TABLE cities (
name text,
population float,
altitude int -- (in ft)
);

CREATE TABLE capitals (
state char(2)
) INHERITS (cities);

An adequate replacement for MySQL on Windows. Can anyone say WAPP instead of LAMP?

Not only that. Here's the most important link: What's New in 8.0. (To editors: why there are links to torrents, but no link to features?)

One of the most exciting features of 8.0 is plperl, their Perl-based server side language, allowing for triggers and persistent storage. On another note, I wish MySQL would catch up to PgSQL. Even if you don't like MySQL, the competition keeps them innovating. If PgSQL is light years ahead, what's pushing them?

In reality, the basic GA framework is "just" another efficient search algorithm, no cooler or more complex than, say, a hash table or a binary search tree; at its simplest, a GA is a way to find an optimal configuration of components without looking at all possible (potentially explosively exponential) combinations; instead, you look at just some permutations, and as you iterate through generations, applying breeding and mutation, you arrive at a generation which is statistically close to optimal.

GAs are also in no way new or unproven technology; a nice example of mainstream use is PostgreSQL's query planner, which uses GAs to optimize query plans.

i think you'll find PostgreSQL [postgresql.org] is also pretty good value for money! cw

i think you'll find PostgreSQL [postgresql.org] is also pretty good value for money! jug

My insider information tells me FreeBSD and PostgreSQL.

Compare:
http://www.postgresql.org/about/

with...

http://www.redhat.com/about/mission/enterprise.htm l

Yes, those are very similar. However, I don't think there's anything sinister in that... remember back in the day when those Amazon-style folder tabs were all the rage? Well, Amazon surely wasn't the first site to ever come up with the concept, and it wasn't the last. Like fashion, site designers adopt what's considered the latest and greatest design style... rounded borders on the top menu, list of links on the left.

A list of new features as compared to 7.x? I can't find one on the PostgreSQL website.

Here you go. To find a link to it, I had to go to the original announcement of the beta...

If you have any questions on MySQL licensing, feel free to contact us: USA and Canada: + 1-425-743-5635

Commercial license is NOT required for in-house (written and distributed) app running on one server. If we replicate to another server for web access, then we would need a commercial license.

Many small office I.T. managers may now breathe a small sigh of relief, or begin investigating http://www.postgresql.org/

From the article:

> PostgreSQL has a much richer feature set but
> has scalability problems and doesn't have
> a company behind it providing
> enterprise-level support;

Bah. What about this? Lots of companies there, and many of the folks involved are core PostgreSQL developers...

• Is Postgre or MySQL an acceptable database backend?

Postgre? It's called PostgreSQL.

And to answer your question, it is an acceptable RDBMS for a small business.

Aside from taking advice from an AC who says something "sux" (real technical term there. Care to back it up?) There are no "auto incrementing" datatypes in postgresql, only default nextval('somesequence'). Note the "default" there.

Before calling this "broken" ask yourself how you'd insert a row into a database with two columns having the same number, without having an external object to get that number from. I use this all the time in billing systems that deal with families. One field for the ID, one field for the ID of who gets the bill, usually the same. In postgresql I can use nextval('sequence') for the first field and lastval('sequence') for the second, in a single command. In MySQL InnoDB only appears to support a single AUTO_INCREMENT column, so I'd have to insert the record, locate the record and read its id, then update the record with that id in the second field.

Not only that, MySQL can only count by one, unlike postgres which can use whatever number is next in the sequence: positive, negative, or even zero.

...SQL Performance Tuning is an excellent book. It has a lot of good discussion on when to use certain SQL contructs and how to check your database to ensure you're actually getting improvements.

PLUG: Which SQL queries are taking the most time? PQA home page, download.

What's this PostGRE thing you're talking about? It's called (and spelled) PostgreSQL.

> PostgreSQL has so much more speed,
> stamina, strength and beauty.

The PostgreSQL database on RubyForge has been slowly growing over the past year or so... it's up over 825K records and ticking along smoothly. I can't see ever going back to MySQL unless forced to...

When I joined the company I'm currently with, I found it hard to believe they were using MySQL instead of it, but it looks like MySQL got the foothold first.

Stick PostgreSQL on a Reiser 4 filesystem with full journalling enabled, and barring hardware problems your database server should be as solid as a rock.

Nothing against Ingres here, since it worked for me at Uni in 1994, but it's hard to go past the best of the open source when you're already familiar with it.

...advocacy list about Oxford switching from Ingres-based apps to PostgreSQL - right here.

UTILITY PLUG: Here's an open source PostgreSQL query analyzer

Nothing is worse than an AI-type. They make big claims and never deliver.

I don't know about that, the AC-type that makes big criticisms and never backs them up is pretty bad.

RDF and OWL, the two most popular scripting languages for the semantic web, are just "semantic nets", an AI concept from the 1970s, rehashed.

Neither RDF nor OWL are scripting languages. As for the age of the idea, Linux is simply OS concepts from the 1960s, rehashed, SQL is simply set theory concepts from the 19th century, rehashed... all technology is based on what precedes it, what's wrong with that?

Yup, when you can't sell any snake oil, just rename it to something else and profit... and profit they do, but does society ever see the AI-types' promises come to fruition?

Of course. To take an example from software I am upgrading right this moment, PostgreSQL uses genetic algorithms to improve efficiency. Work done by the "AI types" you despise frequently finds its way into real-world applications; e.g. spam filters. But don't let that stop your character assassination, will you?

The Computer Associates Trusted Open Source License (CATOSL) apparently qualifies as an "open source" license, but it would probably not qualify as a "free software" license. The focus on user's software freedom found in the free software movement is important in interpreting what powers the license grants and what the license claims to regulate.

Section 10.1 tries to control use of the program--if one's rights under the license terminates, the license claims that that user's rights to use the program terminate as well. But the FSF tells us that US copyright law doesn't permit setting conditions on merely running a computer program (outside of a license or encryption manager) and that if this were to become accepted, would extend copyright law in a dangerous way. This was part of the rationale for saying the first and second revisions of the Apple Public Source License were not free software licenses.

Section 11.4 of the CATOSL claims that no licensee will bring a legal action under the license more than once a year. When one does bring a legal action, one is supposed to waive a jury trial and hold the trial in the state of New York. Licensees in other districts may enjoy rights which the state of New York does not recognize or grant, including the right to bring suit more than once a year; rights licensees would want to retain should they need to go to court.

I'm sure a more thorough examination of the CATOSL would reveal more problems for users. I don't recommend getting involved with programs licensed under the CATOSL. This shouldn't pose a practical problem for anyone because there are excellent database programs under more amenable licenses, including PostgreSQL (licensed under the new BSD license) and MySQL (licensed under the GNU GPL). I also don't recommend licensing one's own programs under the CATOSL.

...and figure out which ones are being run most frequently with PQA. Works with PostgreSQL, too!

...and figure out which ones are being run most frequently with PQA. Works with PostgreSQL, too!

I remember a long time ago they actually told you how to pronounce it on their website.

Axtually, that was Postgres.

Actually, I did have the complete processing for each report in a begin/commit pair.

• Bulk load the data into the table first, then create indexes and foreign key constraints. Creating an index once the data has already been loaded is significantly faster than incrementally updating the index as each new row is added. (This advice applies to most RDBMSs, AFAIK.)
  
• Consider temporarily disabling fsync.
  
• Increase maintainence_work_mem and checkpoint_segments -- the former increases the amount of memory PostgreSQL will use for in-memory sorting (e.g. when creating B+-tree indexes), and the latter increases the number of WAL segments.
  

This is the current state of replication on PostgreSQL (according to its FAQ).

...before blaming MySQL! Ha ha!

Er, anyhow, as an apology, here's an open source SQL analyzer.

I use PostgreSQL for all my DB stuff. Comparing it to MySQL is like comparing a mini-pickup to an 18-wheeler. PostgreSQL ANSI support is good, and it's very stable.

When I write custom apps (pretty much all I do) I write to the full capabilities of PostgreSQL. It's "man enough" to handle the biggest projects I'll likely ever see, and I'm not worried that the vendor will up and leave, since there isn't one.

In fact, PostgreSQL has had an interesting history in that the primary supporters of it have changed several times. PostgreSQL has weathered them all with grace, and remains today an excellent database package with a good, active community and quality developers.

So, I don't worry about JDBC, I don't write to cross platform, I could give a whit about Oracle or DB2 or whatever, but I don't. PostgreSQL is free, plenty good enough, and it won't go away.

Why bother trying to make my application portable?

PS: The article's example about null is stupid. Null = "I don't know". In that context, how could you include values of " 47" if you don't know what the value should be for a particular record?

I'm interested in this new DB language, if it actually offers a real benefit - but the article does a lousy job of getting me excited about it...

postgresql, for one, offers array datatypes

No reason to use MySQL, now that the PostgreSQL crowd finally got their act together and provided windows binaries.

Saying PostgreSQL is like saying supercalafradjalisticexpialadosous. A real pain.

Saying PostgreSQL is like saying supercalafradjalisticexpialadosous. A real pain.

I've found PostgreSQL to be an easier database to work with and admin. When properly tuned I can't tell the difference between the two for most queries. Just my $0.02.