General Bits (ok, me, really), has been on a busman's holiday for a little while and is now ready to start anew. I am another year older (again!) and hopefully a bit wiser.

PostgreSQL 8.0 has been released! Congratulations to all who have participated. And this means the hundreds of people who by coded changes and features, ran builds, reported bugs, wrote documentations, and kept the community up and running as we went along. It also includes those people who asked questions and answered questions that led to changes in code and documentation and people who lent their advice on feature development.

The Postgresql.org web site has gone through a painful rewriting but the result is splendid. I'm still looking for some links, but I'm sure the reorganization will work out its kinks and become a valuable complement to the tried and true software, PostgreSQL, that it represents.

While I was on sabbatical, the second of three Slony articles was published at the O'Reilly OnLAMP site. Look forward to the third article to be published soon.

And welcome back!

Editor: elein at varlena.com

Quick Overview of 8.0 Release Notes Overview of 8.0 Features 23-Jan-2005

The Release Notes for all releases are in Appendix E in the PostgreSQL documentation.

Microsoft Windows Native Server

This is the first PostgreSQL release to run natively on Microsoft Windows® as a server. It can run as a Windows service. This release supports NT-based Windows releases like Windows 2000, Windows XP, and Windows 2003. Older releases like Windows 95, Windows 98,and Windows ME are not supported because these operating systems do not have the infrastructure to support PostgreSQL.

A separate installer project has been created to ease installation on Windows. See-- http://pgfoundry.org/projects/pginstaller

I was able to install and run PostgreSQL 8.0 even though I have Cygwin installed on my PC. But check your PATH variable just in case. Using a GUI seemed to make more sense on the PC than on a Linux system and Pervasive's GUI for configuration of the postgresql.conf file made the configuration easier and more accessible.

The windows release is an important step for "world domination." Be aware that many more database newbies will be inquiring about PostgreSQL. This is our chance as a community to "subvert the dominant paradigm" and convert the masses to a genuinely friendly, open source project.

Savepoints

Savepoints allow specific parts of a transaction to be aborted without affecting the remainder of the transaction. Prior releases had no such capability; there was no way to recover from a statement failure within a transaction except by aborting the whole transaction. This feature is valuable for application writers who require error recovery within a complex transaction.

Savepoints are very useful in handling error conditions, even with psql. When you have a complex script you may bungle, savepoints can help. For example, here we're copying data into a table in a block transaction. A savepoint is created after each successful copy. We are trying to figure out which source file is bad, for example. The second copy failed (in this case because the file name was wrong) and no statements were valid after that. But we were able to rollback to the copyone savepoint to save our successful work and then commit it.

	db=# begin;
	BEGIN
	db=# copy whois from '/tmp/whoall.dat';
	COPY
	db=# savepoint copyone;
	SAVEPOINT
	db=# select count(*) from whois;
 	count
 	-------
    	524
    	(1 row)
    	db=# copy whois from '/tmp/whoall2.dat';
    	ERROR:  could not open file "/tmp/whooll.dat" for reading: No such file or directory
    	db=# select count(*) from whois;
    	ERROR:  current transaction is aborted, commands ignored until end of transaction block
    	db=# rollback to copyone;
    	ROLLBACK
    	db=# select count(*) from whois;
     	count
     	-------
        524
	(1 row)
	db=# commit;
	COMMIT

Point-In-Time Recovery

In previous releases there was no way to recover from disk drive failure except to restore from a previous backup or use a standby replication server. Point-in-time recovery allows continuous backup of the server. You can recover either to the point of failure or to some transaction in the past.

Point-In-Time Recovery is really an alternative backup solution that happens to facilitate recoveries up to a specified time or transaction. (Although, by transaction is not currently fully supported.) A fair amount of setup work is required in order to set up PITR backups and much of it should be done by a good system administrator.

A special file system back up of the database must be made and then the Write Ahead Log files (WAL) must be archived. The backup should be done fairly often and the archival of the WAL files must be done on an ongoing basis. Careful thought must be put into how much space you have to save WAL files and where they should be saved and how.

Once the special backup is done (scheduled) and the WAL logs are archiving, then you are prepared to use the restoration commands as necessary. The restoration consists of restoring the backup and applying the archived WAL files and any partial WAL files available in the installation that has failed. Careful attention must be paid to what files are where and copies of everything are recommended. However, it seems that restore can be done piece by piece or several times over in order to achieve the restoration desired.

There is a new configuration file for PITR, restore.conf which needs to be set up and at least one new value must be set in the postgresql.conf file.

If your installation is well configured and controlled, setting up PITR should not be that difficult. However, if your installation tends to be, ahem, dynamic, then perhaps PITR is not for you.

Tablespaces

Tablespaces allow administrators to select different file systems for storage of individual tables, indexes, and databases. This improves performance and control over disk space usage. Prior releases used initlocation and manual symlink management for such tasks.

Now it is possible to define the storage for your database using SQL statements rather than fussing with symbolic links.

First you must create a directory on the disk targetted for the tablespace and chown it to the postgres superuser. Then create the tablespace using the CREATE TABLESPACE command. Once created you can use tablespaces as part of the CREATE statement for other database, table, index or constraint. The most common usage is to create separate storage for particularly large tables.

	CREATE TABLESPACE logspace LOCATION '/tmp/Logs';
	CREATE TABLE audit_log
		( al_when timestamp, al_what text, al_who text)
		TABLESPACE logspace;

Improved Buffer Management, CHECKPOINT, VACUUM

This release has a more intelligent buffer replacement strategy, which will make better use of available shared buffers and improve performance. The performance impact of vacuum and checkpoints is also lessened.

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

The USING clause is what can make this quite interesting. In this example, I changed the timestamp field to an interval relative to now(). It might have made better sense to make it relative to a constant time, but for demonstration of USING this will do.

	db=# SELECT * FROM audit_log;
             al_when           | al_what | al_who
	----------------------------+---------+--------
	 2005-01-23 17:48:20.094704 | now-1hr | elein
	 2005-01-22 16:50:18.656588 | now-1dy | elein
	(2 rows)

	db=# ALTER TABLE audit_log ALTER COLUMN al_when TYPE interval USING (now() - al_when);
	ALTER TABLE
	db=# select * from audit_log;
	        al_when        | al_what | al_who
	-----------------------+---------+--------
	 -00:57:13.322723      | now-1hr | elein
	 1 day 00:00:48.115393 | now-1dy | elein
	(2 rows)

New Perl Server-Side Language

A new version of the plperl server-side language now supports a persistent shared storage area, triggers, returning records and arrays of records, and SPI calls to access the database.

The ability to write plperl trigger functions and run queries and use arrays in plperl is just what the perl mongers ordered. This array of functionality comes with the ability to store data across calls by storing them in dictionaries as is already possible with plpythonu and R. The power available in plperl functions has increased and, unless you already write stored procedures in plpython or plR, you must give it a try, particularly for the strong parsing.

Along with the plperl improvements comes the ability to enclose function body statements in alternative quotes, Dollar Quoting. This is an enormous relief for function writers in all languages. The Dollar Quoting allows you to embed quotes normally in your function body and helps eliminate the herds of backslashes and single quotes that usually stampede through your pl functions.

Comma-separated-value (CSV) support in COPY

COPY can now read and write comma-separated-value files. It has the flexibility to interpret non-standard quoting and separation characters too.

Although we have always been able to copy in and out comma separated value files using copy with the delimiter, this enhancement has a greater flexibililty with non-standard quoting. You can specify the quoting character, the escape character and can force specific columns to be non-null. These options along with the DELIMITER option and the NULL AS option to COPY create a useful and flexible tool.

Beware that this type of CSV file does not do special handling for the header rows usually supplied by Excel or other applications. This is planned for 8.1. In the meanwhile, be sure to strip out the headers when going from file to PostgreSQL and add them when going the other way if it is required.

Contributors: elein at varlena.com