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

19-Oct-2003 Issue: 48

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

Information Schema
7.4 Features 18-Oct-2003

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;
	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.

applicable_roles check_constraints column_domain_usage column_privileges
column_udt_usage columns constraint_column_usage constraint_table_usage
data_type_privileges domain_constraints domain_udt_usage domains
element_types enabled_roles information_schema_catalog_name key_column_usage
parameters referential_constraints role_column_grants role_routine_grants
role_table_grants role_usage_grants routine_privileges routines
schemata sql_features sql_implementation_info sql_languages
sql_packages sql_sizing sql_sizing_profiles table_constraints
table_privileges tables triggered_update_columns triggers
usage_privileges view_column_usage view_table_usage views

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, elein at
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...
There is a binary XOR operator. If the boolean expressions could be reduced to 1 or 0 then it could be used. This unfortunately just adds an extra layer to a rather simple problem. If you find yourself converting data types to other data types in order to use their operators, that is a hint that you may need to create an operator for the original type or understand why there isn't one already.

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';


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, Pavel Stehule stehule at, Peter Childs blue.dragon at, Manfred Koizar mkoi-pg 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, Joseph Shraibman jks at, Tom Lane tgl at, Sean Chittenden sean at, Manfred Koizar mkoi-pg at, Greg Stark gsstark at
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.

datestyle'iso, us''iso, mdy'

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

Name Default
wal_debug 0
debug_assertions true
show_btree_build_stats false
trace_notify false
trace_locks false
trace_userlocks false
trace_lwlocks false
debug_deadlocks false
trace_lock_oidmin 16384
trace_lock_table 0

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

7.3 Name7.4 NameDefault
server_min_messages log_min_messagesnotice
hostname_lookup log_hostnamefalse
show_source_port log_source_portfalse
show_parser_stats log_parser_statsfalse
show_planner_stats log_planner_statsfalse
show_executor_stats log_executor_statsfalse
show_statement_stats log_statement_statsfalse

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

-autocommittrue functionality moved from
server to clients & libraries
+preload_libraries'' libraries to preload at startup time
+checkpoint_warning30 0 is off, in seconds
+log_error_verbositydefault terse, default, or verbose messages
+enable_hashaggtrue Enable or Disable Hash Aggregates
+from_collapse_limit8 1 disables collapsing of from clauses
+join_collapse_limit8 1 disables collapsing of explicit JOINs
+default_transaction_read_onlyfalse For read only databases
+extra_float_digits0 min -15, max 2
+add_missing_fromtrue 7.3 Backward Compatibility
+rendezvous_name'' Rendezvous broadcast service name

Contributors: 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