PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

1-Sep-2003 Issue: 41

Archives | General Tidbits | Google General Bits | Docs | Castellano | PortuguÍs | Subscriptions | Notifications | | Prev

General Bits is a column loosely based on the PostgreSQL mailing list pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see

eRServer v1.0+ Released!
[ANNOUNCE] PostgreSQL, Inc. Releases Open Source Replication Version 26-Aug-2003

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, elein at
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:


	   Cannot load class SAXParserFactory 
	   class "org.apache.xerces.jaxp.SAXParserFactoryImpl
By perusing the erserver-general mailing list I found that this problem could be solved. The problem seems to be that the file java/lib/xerces.jar is corrupted. It can be repaired by doing:
	zip -FF java/lib/xerces.jar

An initial postings of problems with eRServer seem to point to a last minute interface change from 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'
You can also add extra slave tables this way, one at a time, if you need to.

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, Bernd Helmle, Andrew Sullivan, Peter Teichman, elein at
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;
The array constructor in 7.4 is a very useful feature if you use arrays.

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 '
  	   retarr integer[];
 	   return ''{'' || $1 || '','' || $2 || '','' || $3 || ''}'';
	' language 'plpgsql';

Contributors: Thomas tcbinder at, Joe Conway mail at, elein at
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.


  • -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
Data Types
  • -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
    • -Allow arrays to be ORDER'ed
    • -Support construction of array result values in expressions (Joe)
Multi-Language Support
  • -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
    • -Add ALTER SEQUENCE to modify min/max/increment/cache/cycle values
    • -Allow CLUSTER to cluster all tables (Alvaro Herrera)
    • -MOVE 0 should not move to end of cursor (Bruce)
    • -Allow cursors outside transactions
    • -Allow EXPLAIN EXECUTE to see prepared plans
    • -Allow SHOW of some non-modifiable variables, like pg_controldata
    • -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
Referential Integrity
  • -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)
    • -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, pgsql-hackers at
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:

As always, comments, corrections and suggestions are welcome.

Editor: elein at

Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009

Search General Bits & Search WWW