Slashdot Mirror

• Squeak Toys
• More Squeaking
• Very fast implementation
• For grownups
• The Smalltalk Portal

• Postgresql RDBMS

• Free Smalltalk Library
• PostgreSQL Book

You have to love Websense and their dedication in making sure that sites actually match the categories their products list them in.

Access to this web page is restricted at this time.
Reason: The Websense category "Peer-to-Peer File Sharing" is filtered.
URL: http://www.postgresql.org/download/

The PostgreSQL extended query protocol doesn't require escaping of any value because it won't parsed as part of an sql statement, making injection a non issue. The only sql that is parsed is supplied by the source code.

People often only get as far as writing the simple query part of the protocol which takes a stream of potentially multiple commands mixed in with data supplied by potentially hostile third parties.

I've witnessed it these past two weeks while sniffing postgresql clients while writing my own as an exercise in Limbo

Postgresql also allows the use of named queries, who stay prepared for as long as the connection is open (unless deleted deliberately).

I don't know how long the parse & prepare phase takes but I know it is not zero so there could be some speed improvement too.

I'm not convinced libpq uses it to full advantage but I didn't do in depth research, just a nod to say that if you do serious postgresql programming you could well do with looking at writing your own protcol client.

Would the Postgres implementation of JDBC use 'addslashes()' to bind variables in a prepared statement?

No. Addslashes is a PHP function that many people use. It's not recommended for use with any database.

JDBC would use the correct string escaping routines in the postgresql client library, PQescapeStringConn. That is perfectly safe.

In fact, it appears the only real changes they made were to break bad code that produced invalid multibyte sequences. They also broke the use of the " \' " (backslash + single quote) sequence in unsafe situations that involve multibyte, client-only encodings.

Read more at the release notes:
http://www.postgresql.org/docs/8.1/interactive/rel ease.html#RELEASE-8-1-4

Everything the postgresql people did seemed quite reasonable and only breaks code that was broken to begin with. The announcement was a little cryptic and short on the technical details, but the release notes fill in the blanks. The reason they probably worded the announcement that way was to call attention to poor use of addslashes in php, or other naive escaping tricks. Just use the built-in client library escaping functions, and everything is fine.

PostgreSQL ignored invalid UTF-8 sequences, meaning a ' character at the end of a incomplete sequence could cause only one ' to be seen by the parser when escaped.
See http://www.postgresql.org/docs/techdocs.50 for the details.

You're safe. From the faq:
# If application always sends untrusted strings as out-of-line parameters, instead of embedding them into SQL commands, it is not vulnerable. This is only available in PostgreSQL 7.4 or later.

> My understanding of PreparedStatements is that they are bound at a very low level in the db to allow for maximum speed through caching etc...

I don't think the JDBC driver automatically uses server-side prepared statements. From this:

"There are a number of ways to enable server side prepared statements depending on your application's needs. The general method is to set a threshold for a PreparedStatement. An internal counter keeps track of how many times the statement has been executed and when it reaches the threshold it will start to use server side prepared statements."

Hope that helps.

captcha: injects

You're safe. From the faq:
# If application always sends untrusted strings as out-of-line parameters, instead of embedding them into SQL commands, it is not vulnerable. This is only available in PostgreSQL 7.4 or later.

> My understanding of PreparedStatements is that they are bound at a very low level in the db to allow for maximum speed through caching etc...

I don't think the JDBC driver automatically uses server-side prepared statements. From this:

"There are a number of ways to enable server side prepared statements depending on your application's needs. The general method is to set a threshold for a PreparedStatement. An internal counter keeps track of how many times the statement has been executed and when it reaches the threshold it will start to use server side prepared statements."

Hope that helps.

captcha: injects

Only up to 8.1.3 were listed here as we composed this:

    http://www.postgresql.org/download/btlist

Oh, and it would be gerat to have just ONE torrent to d'load, eg, per platform.

Alternatively, create an All-In-One ISO (preferably CD-ROM set -and- a DVD ISO)

(Help us to save you bandwidth...)

"Remember: It isn't released until its torrents are released" :-/

If your code is doing escaping "by hand", for instance by doubling quotes and backslashes, you really need to fix it to use the library routines instead. If you're avoiding the need for escaping at all, by sending variable strings as out-of-line parameters, then you've saved yourself a whole lot of trouble and can stop worrying.

Mismatches between different character encodings seem to have been responsible for vast swathes of security vulnerabilities over the past few years. The sooner everybody moves to programming languages and software that use Unicode natively, the more secure we will all be.

Unfortunately, the languages receiving the most attention for web development have abysmal Unicode support. PHP and Ruby haven't a clue, although the next version of PHP is supposed to be much better in this respect. Python developers can at least handle things fairly well, although it's still a bit of a pain in the neck.

This vulnerability is probably going to cause quite a few problems for people, as it's a client issue that will probably need whatever adapter you use to be updated. Here is the user guide to the vulnerability for PostgreSQL. psycopg should be fixed shortly.

Remember, Windows is the prevalant desktop operating system.

Examine the options in the tools available to you, pick what works for you.

I've tried MySQL and PHP and mod-perl and CGI and python, but my current favorites are PostgreSQL and Tomcat hosting Java Servlets. No books required, their included documentation is quite good.

;-)

http://www.postgresql.org/docs/7.4/interactive/dat atype-net-types.html

"PostgreSQL offers data types to store IPv4, IPv6, and MAC addresses, shown in Table 8-17. It is preferable to use these types over plain text types, because these types offer input error checking and several specialized operators and functions."

Can you please provide a link to the open, published specification? I'd like to see it so that I know that anyone can implement that spec under whatever license they feel most comfortable with.

An example of a truly open standard is:
http://www.oasis-open.org/committees/download.php/ 12572/OpenDocument-v1.0-os.pdf
Or also, the PostgreSQL wire protocol:
http://www.postgresql.org/docs/8.1/static/protocol .html

The MySQL wire protocol is proprietary. You would have to reverse engineer the product to create code under a different license. They did that on purpose.

Note, there are documents in existence, such as:
http://www.redferni.uklinux.net/mysql/MySQL-Protoc ol.html

That is someone who read the source files and produced a spec for a hypothetical re-implementation. The problem with that is that MySQL has made no promises to adhere to that spec, so it's quite possible that MySQL could manipulate the protocol to break 3rd party implementations without breaking their own. And nobody would have a standard document that they could use to file a bug report against. That's the definition of a proprietary protocol.

postgres has no clustering support. It has limited replication support from a project called slony (http://gborg.postgresql.org/project/slony1/projdi splay.php).

The mysql clustering is pretty much bunk anyways. All of the DBs need to live in RAM. What happens if you have a catastrophic UPS failure?

PostgreSQL supports these capabilities natively, without needing to resort to third-party packages. The best place to learn about setting it all up is from the PostgreSQL docs. Between the FAQ and the manuals, you should have more than enough to get you started.

Adding new stuff all the time? Have a look at the website, it's very informative: What's New in 8.1.

There are quite a few properties that can be altered without having to backup, drop and recreate the database. The only sometimes-handy-to-have thing that I can't really see being present is changing the character encoding of an existing database.

If there is a problem with mySql, one could always migrate to Postgresql. Postgresql is well known for its advanced features and its excellent ability to work with large-size dbs.

You can always solve a problem multiple ways. Depending on the project, the costs involved solving it the hard way may be greater than the costs of paying for licensing if the added features save you enough time.

I've migrated postgres from 6.something to 7.1, then to 7.2, then to 7.4, and now we are looking to move to 8.1 to solve some performance problems. With 8.1 we are getting closer and closer to having everything we need, but I have to admit that I am sick of upgrading my database over and over again to get new features.

We migrated to 7.1 to get referential integrity.
We migrated to 7.2 to get the new light-weight VACUUM which doesn't lock the entire table.
We even did an emergency upgrade from 7.2.3 to 7.2.4 to fix a problem where postgres would crash several times a day when the load was at its peak.
We migrated to 7.4 which improved performance dramatically, primarily because postgres' optimizer finally would re-order outer joins.
Now, we are migrating to 8.1, since it finally fixes the problem with referential integrity blocking crud statements if another crud statement is referencing the same row. This can be a big performance problem if you have a foreign key to a table with three rows, so you can have at most three statements updating a table referencing the table with three rows.

For more information on the referential integrity row locking problem that is fixed in 8.1:
http://archives.postgresql.org/pgsql-docs/2005-07/ msg00022.php

Postgres 8.1 will also allow us to maintain an up-to-date backup with point-in-time recovery:
http://www.postgresql.org/docs/8.0/interactive/bac kup-online.html

How often do you have to upgrade oracle, and how often do you have to dump all of your data and restore it?

I am really sick of upgrading, and I think there are still a few features that we could benefit from down the road. I'm still uneasy about replication in postgres, and postgres still doesn't have binary backups, so I think a full dump and restore takes much longer than necessary.

People think it is a waste of time to learn how to administer Oracle. What about all the time spent updating and testing our application for each new version of postgres? If our application didn't need to run 24/7, maybe all these upgrades wouldn't be so stressful and wouldn't require such extensive testing.

You can always solve a problem multiple ways. Depending on the project, the costs involved solving it the hard way may be greater than the costs of paying for licensing if the added features save you enough time.

I've migrated postgres from 6.something to 7.1, then to 7.2, then to 7.4, and now we are looking to move to 8.1 to solve some performance problems. With 8.1 we are getting closer and closer to having everything we need, but I have to admit that I am sick of upgrading my database over and over again to get new features.

We migrated to 7.1 to get referential integrity.
We migrated to 7.2 to get the new light-weight VACUUM which doesn't lock the entire table.
We even did an emergency upgrade from 7.2.3 to 7.2.4 to fix a problem where postgres would crash several times a day when the load was at its peak.
We migrated to 7.4 which improved performance dramatically, primarily because postgres' optimizer finally would re-order outer joins.
Now, we are migrating to 8.1, since it finally fixes the problem with referential integrity blocking crud statements if another crud statement is referencing the same row. This can be a big performance problem if you have a foreign key to a table with three rows, so you can have at most three statements updating a table referencing the table with three rows.

For more information on the referential integrity row locking problem that is fixed in 8.1:
http://archives.postgresql.org/pgsql-docs/2005-07/ msg00022.php

Postgres 8.1 will also allow us to maintain an up-to-date backup with point-in-time recovery:
http://www.postgresql.org/docs/8.0/interactive/bac kup-online.html

How often do you have to upgrade oracle, and how often do you have to dump all of your data and restore it?

I am really sick of upgrading, and I think there are still a few features that we could benefit from down the road. I'm still uneasy about replication in postgres, and postgres still doesn't have binary backups, so I think a full dump and restore takes much longer than necessary.

People think it is a waste of time to learn how to administer Oracle. What about all the time spent updating and testing our application for each new version of postgres? If our application didn't need to run 24/7, maybe all these upgrades wouldn't be so stressful and wouldn't require such extensive testing.

> What the fuck does pg_hba mean? I don't know what hba stands for.

Umm... this one is tough... If you'd RTFM, you'd see from the *2nd* sentence of the documentation section on the pg_hba.conf file, under the Client Authentication chapter:

"(HBA stands for host-based authentication.)"

I think even you can guess what the pg means.

I don't think it's so much a case of elitism -- it's just that not every application needs to be overburdened by a complete GUI frontend. The documentation for PostgreSQL is quite rich actually: see http://www.postgresql.org/docs/8.1/interactive/ind ex.html for the latest sets. The amount of work that goes into detailing nearly every aspect of configuring, running, and working with this database system would seem to indicate that the developers are far from lazy. Not bothering to read said documentation on the other hand ... There is also an extremely helpful set of mailing lists, wikis, and related sites (such as the General Bits site: http://www.varlena.com/varlena/GeneralBits/ ) such that keys to the "mysteries" of various parameters and how to tweak one's server for best performance are quite readily available.

docs at http://www.postgresql.org/docs/8.1/interactive/bac kup.html

I do just like they say, the pg_dumpall, then upgrade the database to the new major release, then do the restore. when I restore with psql -f infile postgres. and it always errors with things like the users not existing, the database not existing, etc. so i have to go in and create the users by hand, and then create an empty database, etc. I always just have to play around until I can make it work. upgrading between postgresql major releases is a pain, always has been, probably always will be.

like i said, thats why I love mysql, install the new rpms and just keep on going.

Also, PG is perceived as less stable than Oracle, and even less than MySQL.

Like all Linux desktops, Ubuntu has limitations. It lacks applications such as Photoshop, Framemaker, Pagemaker, Visio, Access, Quickbooks, a PDF converter, legal DVD players and most importantly income tax preparation software. Without those applications ported directly to Linux, Ubuntu remains a mid-level desktop.

• The GIMP satisfy virtually all "photoshop" needs (maybe not some small part in some graphics shops, but otherwise you're bitching without real knowledge).
• I don't do much in the area of Framemaker or Pagemaker, but most desktops will do fine with the functionality present in OpenOffice.org Draw
• Visio has some nice features, but I've lived for years with Dia managing a network of almost 200 equipments in a variety of multi-level networks
• Access is b0rked by design. PostgreSQL and MySQL are on Enterprise level, and they're at your feet on most GNU/Linux distributions
• PDF Converters? Have you tried printing? Go there. Notice the Create a PDF Document option...
• Legal DVD players? Write your congressmang, senator, whatever favorite politic of choice and influence and tell them how wrong DMCA is.

Now, the reason I wouldn't use PostgreSQL in some instances, PostgreSQL still does not have any clustering features that I am aware of.

It sure does:
* Slony-I: master-slave, table-level backup. Works across postgresql versions, which means it's very convenient to use to upgrade. Very good software, well tested in live environments (that's what serves the .org in slashdot.org, and every other .org or .info).
* PgPool: Query based replication software and network management software. This can work as multi-master by distributing queries, or work with Slony.
* Also, 8.1 has the very nifty feature called "Two Phase Commit" (a.k.a. 2PC). This allows network management software to synchronously commit on multiple machines to create a multi-master solution.

gives real-world usable examples of each documented function,

If something is lacking in the documentation, please write to the mailing lists. I'd be happy to write a few documentation pages in my spare time. However, what might be obvious to a PostgreSQL person might not be to someone coming from another database background. So, I don't know what about the documentation needs work.

Anyone that lauds date handling in PostgreSQL should take a closer look at the wire protocol. Here's loadCalendar() in the JDBC driver:

http://gborg.postgresql.org/cgi-bin/cvsweb.cgi/pgj dbc/org/postgresql/jdbc2/TimestampUtils.java?rev=1 .18;cvsroot=pgjdbc

HORRID. Aside from being complicated and verbose, it changes format pretty dramatically depending, presumably, on how the server was built and what OS it runs on. Some years ago I found myself patching the damn thing because somehow the standard RPM on RedHat6.2 used yet another slightly different format.

32-bit UNIX-style seconds-since-1970-GMT might be inadequate, but a 64-bit milliseconds-since-epoch is just fine and much, much more reliable. And if you really want to be guaranteed infinite future-proofing, send the number as a string.

I moved to Firebird for a while, but now I use MySQL predominantly for one reason only - integrated, robust, proven database replication. When and if PGSQL catches up, I'll consider migrating back.

Jeff Schnitzer

From http://www.postgresql.org/docs/ you have the choice of the manual for each version of PostgreSQL since 7.4, with or without user comments.

I have not sat down and read the manual all the way through; however I have used it extensively for reference and I have found it an excellent resource for learning about PostgreSQL. I would even say that it is the definitive PostgreSQL reference, even the best one that I have seen.

I use it because it's incredibly well supported. When I first started using it (WAY back in the day) I found something that didn't work quite right, (actually it did, I just didn't know right from wrong at the time,) posted a message to the mailing lists, and had workaround within a few hours.

Here's a post to the advocacy page from someone in wisconson working as a contractor. His praise mirrors mine, and that of hundreds of other users.

http://archives.postgresql.org/pgsql-advocacy/2006 -03/msg00126.php

On most OSes, shared memory is a precious resource and requires either static, compile-time tunables, or run-time tunables that are difficult to juggle. Shared memory is hard to port between OSes because nobody supports it quite the same way.

http://www.postgresql.org/docs/7.4/static/kernel-r esources.html

So now we have to recompile our kernels just to support Postgres? That is not cool, and means remote configuration of server machines to support Postgres is *very* dangerous.

Additionally, when Postgres starts eating up all my system's shared memory, the other, play-nice facilities that don't consume masses of the stuff start choking because it's difficult to tell Postgres to throttle itself. So now I have to tell *both* the system *and* Postgres to match their limits. Postgres expects me not only to reconfigure my kernel, but to tell it what I've done.

After all this, I find out that Postgres, like every other database engine out there, has a bunch of quirks in its command-line, the same way that MySQL and every other DB engine out there does.

I'm sorry, but it's just not practical to support Postgres without extensive tuning and configuration, that I just can't afford to do on my remote machines. Therefore, MySQL or the newly-freed commercial engines are my only options.

You didn't read the manual? :)

Because it's so terribly difficult to go to postgresql.org and click documentation.

Perhaps there is still time for you all to attend the PostgreSQL High-Availability Webinar on March 23rd?

DISLAIMER: I have never used a database in my life, shut up already.

PostgreSQL has it all...

Windows port: http://www.postgresql.org/ftp/binary/v8.1.3/win32/
GUI Tool: http://pgadmin.org/ (comes with the windows install)
Lots of documentation: http://www.postgresql.org/docs/8.1/interactive/ind ex.html
Books: http://www.postgresql.org/docs/books/

And don't repeat my mistakes, you should always mind scalability.

PostgreSQL has it all...

Windows port: http://www.postgresql.org/ftp/binary/v8.1.3/win32/
GUI Tool: http://pgadmin.org/ (comes with the windows install)
Lots of documentation: http://www.postgresql.org/docs/8.1/interactive/ind ex.html
Books: http://www.postgresql.org/docs/books/

And don't repeat my mistakes, you should always mind scalability.

PostgreSQL has it all...

Windows port: http://www.postgresql.org/ftp/binary/v8.1.3/win32/
GUI Tool: http://pgadmin.org/ (comes with the windows install)
Lots of documentation: http://www.postgresql.org/docs/8.1/interactive/ind ex.html
Books: http://www.postgresql.org/docs/books/

And don't repeat my mistakes, you should always mind scalability.

"The name is pretty awkward, and that can be a fast turn-off for many people.2

I doubt many people would not use good software because the name is hard to pronounce! Anyway, there has been an mp3 of the prunounciation on the website for at least 8 years!

Personally I love PostgreSQL, MySQL always had less features but was faster than PostgreSQL because of it, as MySQL adds these features it's becoming slower effectively making it the same product... when MySQL finally has all the features of PostgreSQL I'm sure it will be the slower of the 2 as PostgreSQL will be way ahead in terms of optimising the features they've had for years.

I don't like driving Mercedes because the name is just plain messed up. Heavens forbid someone might ask me to spell it!

One of the great features of Postresql is Multi-Value Concurrency Control (MVCC). In a nutshell, readers never block: "querying a database each transaction sees a snapshot of data (a database version) as it was some time ago."

If you have a single, long-running write transaction (e.g. a batch process), and many short-running read transactions (e.g. serving web requests), this works very well. When the batch process completes, readers "atomically" switch to the newly-committed version. This (drastically) simplifies the batch process, since you don't have to worry about readers blocking or seeing inconsistent state. (Things get more complex in the many-writers scenario, however.)

I don't think MySQL has this feature. (Please correct me if I am wrong.)

I believe 8.0+ supports nested transactions ('sub-transactions').
http://www.postgresql.org/docs/8.1/interactive/rel ease-8-0.html

I'd personally love to see an application that detected your memory and other settings and came out with sane settings, at least with such an option you'd have a place to start.

Postgresql's replication has seen much improvement recently, and shortly you might have to eat those words.

the Postgres people had postcards on their table of whatever their mascot is

an elephant

......there are more dumb people than smart people in the world.

'operation too complicated to perform'

(Clippy popping up): It seems you want to perform a join query. Try the query on your 2Gig database without a join statement

I think I stick with Postgresql; even though Access is easyer to pronounce.

And you can even use R within PostgreSQL with plr.

The article says Oracle compares its US$15G/yr revenue to MySQL's US$30M/yr. But as Paul Graham says, it is OK to shrink a US$30G/yr industry to US$30M/yr, if your absolute share of the new US$30M is bigger than the one on US$30G was. Or in other words, MySQL will laugh to the bank on growing from US$30M, while Oracle will strive to keep their US$15G.

Also, IBM, Oracle and Intel did not make Linux. Richard Stallman created GNU, Linus used GNU and complemented it with Linux, and now IBM, Oracle and Intel help Linus with Linux and RMS with GNU.

I wonder how long will IBM and Oracle continue think they can sell proprietary servers on free platforms, without facing significant competition from free servers too. And how long Intel think they can sell proprietary machines to run free software without facing competition from free (think 'open') hardware? Now they are winning, IBM and Oracle using GNU/Linux to face competition from Microsoft, and Intel to crush proprietary RISC (think they ignoring OpenFirmware); but how long before we are running PostgreSQL (or better yet, Rel) on some OpenCores system booting with OpenFirmware or something the like? Not on the short term, for sure, but eventually maybe it is inevitable, unless DRM forces us into a police state.

> I can't even find what the the license cost for MySQL I have vague recollections of something about $250.00 for a commercial
> license free for non profit. On the web site there is a $595.00 survice contract, but no mention of a commercial use license.

MySQL Network Basic is $600/server/year: https://shop.mysql.com/
You *may* not have to pay anything however. But that depends on GPL/LGPL licensing complexities, and you will probably need a lawyer to know for sure (MySQL AB recommends just buying a license if you're confused).

In comparison, Oracle/DB2/SQL Server are free for small databases, regardless of how you use them. Then if you need to license it can still be cheaper than mysql - since they typically have an initial license (say, $750) and then just 18%/year afterwards. All three of these databases can be *far* more expensive, but db2 in particular can also be cheap in many configurations. I'm running a terabyte-sized data warehouse on about $28k in total licensing. Compared to the cost of the hardware this is nothing.

And of course, postgresql & sql
    - http://www.postgresql.org/
    - http://www.sqlite.org/
are completely free.

> So I can only assume that those of us that need a database and not even a feature rich database will simply move on to something
> else or pony up the $5000 to $40000 to have a DB to keep track of the CD collection.

No, you're fine for several reasons:
    - you can probably get by with the free mysql version for now
    - postgresql is completely free
    - sqlite is completely free - and is probably the best choice for a tiny project like this
    - oracle is completely free for something of this size
    - db2 is completely free for something of this size
    - sql server is completely free for something of this size

Unless of course you're talking about building a competitor to cdnow, in which case you will face some costs ;-)