One of the more interesting features in 7.4 is the information schema which describes the SQL standard definitions of a PostgreSQL database. It shows the elements of the database usually found using the pg_catalog schema or the slash commands in psql.

The information schema is the implementation of the SQL Information Schema as defined in ISO 9075-2:1999 chapter 20. It was added to 7.4 by Peter Eisentraut but it did not show up on the TODO list. The information schema implements an SQL standard and so it does not reflect all parts of the PostgreSQL specific features, for example, it does not display the different languages in which functions can be written.

The schema is installed by default in 7.4 databases and is named 'information_schema'. By default the schema is not on the search path and so it must be added to the search path in psql or accessed explicitly. For example:

	SET search_path TO public, information_schema;
	or
	SELECT * FROM information_schema.tables;

This table shows the names of the views created within the information_schema. Some are self-explanatory and some are obscure in keeping with standard specification standards.

Every column in all of these views is one of the following four types:

cardinal_number	A nonnegative integer.
character_data	A character string (without specific maximum length).
sql_identifier	A character string. This type is used for SQL identifiers, the type character_data is used for any other kind of text data.
time_stamp	A domain over the type timestamp

Boolean (true/false) data is represented in the information schema by a column of type character_data that contains either YES or NO. (The information schema was invented before the type boolean was added to the SQL standard, so this convention is necessary to keep the information schema backward compatible.)

The information contained in the information schema can be fast and helpful or it could be both inadequate and overwhelming. All of the information available is also available in the pg_catalog schema, however, some people may find the information_schema easier to read, particularly with the expansion of the arrays stored in the pg_catalog.

The information_schema is very useful for those building tools to manipulate the database in standard SQL ways, but it does fall short of providing PostgreSQL specific information. The slash commands in psql provide PostgreSQL specific information fairly well, however, they are not necessarily available in views. Grabbing and translating the queries used to construct the information in slash commands in not always as easy as it should be and is the subject of many questions.

As you can tell by the pluses and minuses and the ambiguity, using the standard SQL information schema will be a matter of preference as is direct access to the pg_catalogs. Perusal of the information schema is highly recommended.

Contributors: peter_e at gmx.net, elein at varlena.com

A Quick New Operator or Revisiting boolean algebra [GENERAL] XOR logical operator 17-Oct-2003

The question at hand was how to create an exclusive or (XOR) expression. Writing out the CASE statement is rather tedious if more than two operands are involved.

	select case when $1 then
		(select case when $2
			then 'f'
			else (select case when $3 
				then 'f'
			...and so on...
	end

The fun part of this thread was Peter Child's quick creation of a new boolean operator. It illustrates how easily a new operator can be created. The function xor(bool,bool) is created and bound to the operator sequence of "~|", a relatively arbitrary choice of an operator string. Binding a function and a left and a right argument type to a string is the basic procedure to create an operator.

	CREATE FUNCTION xor(bool,bool) RETURNS bool AS '
		SELECT ($1 AND NOT $2) OR (NOT $1 AND $2);
	' LANGUAGE 'sql';

	CREATE OPERATOR ~| (PROCEDURE='xor',LEFTARG=bool,RIGHTARG=bool);

Manfred Koizar then reeled us back to reality and beginning boolean algebra by reminding us that <> (not equals) on boolean arguments gives us exactly the same functionality as the desired XOR. Oh, yeah, I knew that.

Contributors: Nagib Abi Fadel nagib_postgres at yahoo.com, Pavel Stehule stehule at kix.fsv.cvut.cz, Peter Childs blue.dragon at blueyonder.co.uk, Manfred Koizar mkoi-pg at aon.at

ShmemAlloc error signals postgresql.conf change [GENERAL] ShmemAlloc errors 16-Oct-2003

A dump of a database with many tables caused the following error:

 $ pg_dumpall >full.db
 pg_dump: WARNING:  ShmemAlloc: out of memory
 pg_dump: Attempt to lock table "vs_dfa554862ac" failed.  ERROR: 
 LockAcquire: lock table 1 is out of memory
 pg_dumpall: pg_dump failed on bandwidth, exiting

The database had 3000 tables and each table has about 1500 rows containing date, timestamp and quantities. The size of the database ran from 25Gb to 9Gb.

The recommendation was to increase max_locks_per_transaction in the postgresql.conf file. A restart of the server is required for this change. It is generally known that this particular ShmemAlloc error is caused by insufficient max_locks_per_transaction. With very little prompting, Tom Lane volunteered to duck under the 7.4 Strings freeze and add this information to the error message.

The thread continued discussing the rationale behind creating 3000 tables instead of one large indexed one. With the new information and the fix for index bloat, the reasoning will be reexamined.

Contributors: Nick Burrett nick at dsvr.net, Joseph Shraibman jks at selectacast.net, Tom Lane tgl at sss.pgh.pa.us, Sean Chittenden sean at chittenden.org, Manfred Koizar mkoi-pg at aon.at, Greg Stark gsstark at mit.edu

7.4 Postgresql.conf changes 7.3 to 7.4 differences 16-Oct-2003

There were many changes to the postgresql.conf file between 7.3 and 7.4. Although many of the changes are documented in Josh and Shridhar's Annotated Postgresql.conf it is worth examining the specific differences now, just before the 7.4 release. This information should be used to update your postgresql.conf file for 7.4.

Default Changes The change to datestyle is related to the change in the date input and the others here are the result of many long discussions on pgsql-hackers and pgsql-performance.

Developer level flags were removed. Most of these required build changes in order to be useful.

Name Changes Several variables had their names changed. None of the defaults were changed.

Feature and Miscellaneous Changes The following options were added (or removed) as feature changes or redevelopment.

Contributors: elein at varlena.com