PostgreSQL, Inc. is today contributing its commercially developed replication solution, eRServer(c) v1.0+ for the PostgreSQL database, to the PostgreSQL project and the global open source community. There seems to be a discrepancy between the announcement version, v1.0+ and the actual download which seems to be v1.2.

In keeping with its pronouncement made in December 2000 to open source all and any of its products after two years, PgSQL, Inc. is releasing its proprietary version of the eRServer to the community under the BSD Open Source Software License.

eRServer is a descendent from the rserv code. It is a trigger-based single-master/multi-slave asynchronous replication system. eRServer will enable live replication of PostgreSQL databases. This replication can be used to distribute copies of read only databases and provide a failover database. Replication is considered to be a crucial requirement for enterprise systems.

The single-master/multi-slave replication describes the data path. The master database is the only one which is available for INSERTS, UPDATES and DELETES and for data definition statements, such as CREATE TABLE. The slave databases are available for querying only except for the eRServer process. If any other updates occur on the slave they will corrupt the premise of perfect replication.

"However," cautions Fournier, "replication can be an extremely complex area for programming in enterprise systems - so even the more advanced database users should expect to invest a good deal of development time and effort in properly deploying this software."

I expect that this release, as it is downloaded and used, will be improved steadily. Ideally the difficulty in deployment will lessen as the product matures.

See SlashDot if you are interested in trolls and ill-informed arguments sprinkled with a few insights and praises for PgSQL, Inc. It is interesting that the mine-is-better-than-yours arguments span across a good number of other databases, Oracle, Firebird, MSSQL, mysql.

More information about and download of eRServer is available on GBorg. The eRServer-general mailing list will also be a good resource.

Contributors: Marc G. Fournier scrappy@hub.org, elein at varlena.com

eRServer Initial Problems [GENERAL] erserver 1.2 problem / [Erserver-general] erserver installation 28-Aug-2003

I downloaded eRServer and tried to build it quickly... and ran smack into the first problem:

   BUILD FAILED

   javax.xml.parsers.FactoryConfigurationError: 
	   Cannot load class SAXParserFactory 
	   class "org.apache.xerces.jaxp.SAXParserFactoryImpl

	zip -FF java/lib/xerces.jar

An initial postings of problems with eRServer seem to point to a last minute interface change from Pg.pm to DBI. Apparently, those people who built successfully and had configured correctly had everything running, but no replication actually occurred. The problem was in the code which added tables and ids to the _rserv_slave_tables_.

The solution is to use SQL on the master database directly:

	INSERT INTO _rserv_slave_tables_ (tname, cname, reloid, key)
        SELECT r.relname, a.attname, r.oid, a.attnum
        FROM pg_class r, pg_attribute a
        WHERE r.oid = a.attrelid
        AND r.relname = 'your_relation_name'
        AND a.attname = '_ers_uniq'

It is not unusual for the first public glimpse of a highly anticipated project to have a few kinks to work out. I am very positive that the initial problems will be worked out quickly and I look forward to using eRServer regularly.

Contributors: Andrew Rawnsley ronz@ravensfield.com, Bernd Helmle mailings@oopsware.de, Andrew Sullivan andrew@libertyrms.info, Peter Teichman peter@ximian.com, elein at varlena.com

Inserting into an Array Value Hint by way of Joe Conway 25-Aug-2003

Conversion from a select list into an array value is possible. The technique is drop dead simple for 7.4, and a little more difficult in 7.3.

The effect we want is this. This does not work.

	INSERT INTO foo (arrayval) select a,b,c from bar; 

In 7.4, simply use the array construct like this:

	INSERT INTO foo SELECT array[a,b,c] FROM bar;

In 7.3, you must define a function to do the conversion for you. This function cannot handle a variable number of arguments, so you may find yourself overloading the function by redefining it with different numbers of arguments.

	create or replace function ints2array(int,int,int)
	returns integer[] as '
	DECLARE
  	   retarr integer[];
	BEGIN
 	   return ''{'' || $1 || '','' || $2 || '','' || $3 || ''}'';
	END
	' language 'plpgsql';

Contributors: Thomas tcbinder at web.de, Joe Conway mail at joeconway.com, elein at varlena.com

What's in 7.4? 7.4 Pre-Release Notes 30-Aug-2003

The final feature list is still a little fuzzy to me. The Release Notes for 7.4 are coming together. The full yet terse notes are in the HISTORY file (here truncated to 7.4) at the top of the postgresql tree. This file was last updated 1-Aug-2003. The other place to look is the Developers TODO List, last updated 25-Aug-2003. Bruce Momjian is the primary maintainer for both these lists.

Before moving on to the list of updates from the TODO List, we have a little note about conversion:
WARNING! A dump/initdb/reload is REQUIRED when converting from 7.3 to 7.4.

From the TODO List we have the following features marked as included in 7.4. At this point in time (no pun intended) of the 7.4 Beta we can assume that these items are complete.

Reporting

• -Allow elog() to return error codes, module name, file name, line number, not just messages (Tom)
• -Add error codes (Tom)
• -Make error messages more consistent
• -Add GUC log_statement_and_duration to print statement and >= min duration

• -Allow easy display of usernames in a group
• -Add start time to pg_stat_activity
• -Allow CIDR format to be used in pg_hba.conf

• -Add IPv6 capability to INET/CIDR types
• -Change NUMERIC data type to use base 10,000 internally
• -Add GUC variables to control floating number output digits (Pedro Ferreira)
• -Allow current datestyle to restrict dates; prevent month/day swapping from making invalid dates valid
• -Prevent month/day swapping of ISO dates to make invalid dates valid
• ARRAYS
  • -Allow arrays to be ORDER'ed
  • -Support construction of array result values in expressions (Joe)

• -Remove Cyrillic recode support

• -Add btree index support for reltime, tinterval, regproc (Tom)
• -Certain indexes will not shrink, e.g. indexes on ever-increasing columns and indexes with many duplicate keys
• -Allow LIKE indexing optimization for non-ASCII locales using special index

• -Allow LIMIT/OFFSET to use expressions (Tom)
• -Return proper effected tuple count from complex commands [return]
• -Make a transaction-safe TRUNCATE (Rod)
• -Allow UPDATE to use SET col = DEFAULT
• -Add config variable to prevent auto-adding missing FROM-clause tables
• -Have SELECT '13 minutes'::interval display zero seconds in ISO datestyle
• ALTER
  • --Add ALTER TABLE tab SET WITHOUT OIDS (Rod)
  • -Add ALTER SEQUENCE to modify min/max/increment/cache/cycle values
• CLUSTER
  • -Allow CLUSTER to cluster all tables (Alvaro Herrera)
• CURSOR
  • -MOVE 0 should not move to end of cursor (Bruce)
  • -Allow cursors outside transactions
• SHOW/SET
  • -Allow EXPLAIN EXECUTE to see prepared plans
  • -Allow SHOW of some non-modifiable variables, like pg_controldata
• SERVER-SIDE LANGUAGES
  • -Allow array declarations and other data types in PL/PgSQL DECLARE
  • -Make PL/PgSQL %TYPE schema-aware
  • -Allow PL/PgSQL to support array element assignment (Joe)

• -Allow psql to show transaction status if backend protocol changes made
• -Add schema, cast, and conversion backslash commands to psql (Christopher)
• -Allow pg_dump to dump a specific schema (Neil Conway)
• -Allow SSL-enabled clients to turn off SSL transfers
• -Modify pg_get_triggerdef() to take a boolean to pretty-print, and use that as part of pg_dump along with psql
• ECPG
  • -Add SQLSTATE
  • -Make casts work in variable initializations
  • -Allow multi-threaded use of SQLCA
  • -Understand structure definitions outside a declare section
  • -Allow :var[:index] or :var[] as cvariable for an array var

• -Support statement-level triggers (Neil)

Write-Ahead Log
• -Add checkpoint_min_warning postgresql.conf option to warn about checkpoints that are too frequent (Bruce)
Optimizer / Executor
• -Improve Subplan list handling
• -Allow Subplans to use efficient joins(hash, merge) with upper variable
• -Add hash for evaluating GROUP BY aggregates (Tom)
• -Allow merge and hash joins on expressions not just simple variables (Tom)
• -Make IN/NOT IN have similar performance to EXISTS/NOT EXISTS (Tom)
• -Inline simple SQL functions to avoid overhead (Tom)
Miscellaneous
• -Get faster regex() code from Henry Spencer
Source Code
• -Modify regression tests to prevent failures do to minor numeric rounding
• -Add OpenBSD's getpeereid() call for local socket authentication
• Wire Protocol Changes
  • -Show transaction status in psql
  • -Allow binding of query parameters, support for prepared queries
  • -Remove hard-coded limits on user/db/password names
  • -Remove unused elements of startup packet (unused, tty, passlength)
  • -Fix COPY/fastpath protocol
  • -Error codes
  • -Report server version number, database encoding, client encoding

Contributors: Bruce Momjian pgman at candle.pha.pa.us, pgsql-hackers at postgresql.org

Thank you! Thank you for the survey responses 30-Aug-2003

Thank you to each of you who took the time to fill out the survey in Issue # 40. If you have not yet responded to the survey, there is still time :-)

Here are a few Varlena Web Site Hints:

• Use the Archives!
• Item Titles by Issue are available under each Month on the Archive Page
• Google at the bottom of each page provides searching within GeneralBits. Google is always an issue or two behind, but it does not do too badly.
• The Tidbits Page may have what you are looking for.
• To receive an email reminder of each publication, send a note to EMail Notification.
• All of the issues translated into Portuguese are listed under Artigos Português

Current Issue:	http://www.varlena.com/GeneralBits/
Archives:	http://www.varlena.com/GeneralBits/archive.php
TidBits:	http://www.varlena.com/GeneralBits/Tidbits/
Portuguese:	http://www.varlena.com/GeneralBits/po.php

As always, comments, corrections and suggestions are welcome.

Editor: elein at varlena.com