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

18-Aug-2003 Issue: 39

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

ORDBMS and Arrays
Arrays and "goodness" in RDBMSs (was Re: [GENERAL] join of array) 15-Aug-2003

In discussing the semantics of array operations with regards to the SQL99 specification, the question was raised whether arrays are "an anathema to all we know and love" in relational databases. Of course the array semantics are defined in the SQL99 specifications...

PostgreSQL is an ORDBMS, not just an RDBMS.

A column holds a type of value. Any kind. The structure and operands define the type. The data defines the value. This holds true for simple types like an integer or complex types like an array.

The database data is relatively "type blind" in an ORDBMS. It uses the standard overloaded operands to determine the type of function to perform for all of the usual RDBMS utilities. Constraints, triggers, sorting, etc. all apply.

That's what the ORDBMS stuff can give you. Arrays are a natural extension.

Arrays don't necessarily imply denormalization. Of course you can denormalize via arrays, but it tends to make things harder for you. And I believe the same thing is true for denormalized integer columns.

This begs the question of when and why would one use arrays in a relational model. This may be difficult to see because arrays are an abstract data type. A specific example would be clearer.

For example, a definition of a polygon is an array of Points. Points, themselves are an array. The particular polygon may have a unique key for normalized inclusion as a foreign key, but the data is still in array form. The data is only useful as a complete array.

The actual postgreSQL implementation of polygons and points doesn't use the newer cleaner array abstraction, I think. But if I were reimplementing them, I would build on top of the new array capabilities. The point is to show an array structured object which makes sense in context.

Contributors: Ron Johnson ron.l.johnson at, Andrew L. Gould algould at, elein at
Where is the perldoc?
[GENERAL] Simple DBI question. 12-Aug-2003

DBI is a database independent client side perl interface for PostgreSQL. Because it is perl, it uses perldoc rather than man.

To see the documentation use

	$ perldoc DBI 

The documentation is quite long but it gives you key information on the basics. One question involved with this thread was how to connection across a network. To do this, create a PostgreSQL connection string and use it as a parameter to DBI->connect() A connection string looks like this: "dbi:Pg:dbname=db;host=machine;port=5432"

	use DBI;
	my $dbh = DBI->connect( 
	   {RaiseError => 1} )
	or die "Unable to connect: $DBI::errstr\n";

Contributors: David Siebert dsiebert at, Thomas A. Lowery tl-lists at, Sean Chittenden sean at, Dennis Gearon gearond at
Clarification of Deferment with Constraints
[GENERAL] types of constraint deferment 11-Aug-2003

The specification of the timing of constraint checks is defined when you create a table and add a constraint. You may not specify the timing for all constraints--only for foreign key constraints.

The constraint is checked according to the deferment options: either immediately or possibly at the end of the transaction. When you are loading data that is not in dependency order, for example, you may want to do so in a transaction and have the constraints deferred.

The deferment options on foreign key constraints are:

Reformatted from 73/doc/html/sql-createtable.html
DEFERRABLE or NOT DEFERRABLE controls whether the constraint can be deferred.
  • NOT DEFERRABLE is the default.
  • A constraint that is not deferrable will be checked immediately after every command.
  • Checking of constraints that are deferrable may be postponed until the end of the transaction.
  • Only foreign key constraints currently accept this clause.
  • All other constraint types are not deferrable.
INITIALLY IMMEDIATE or INITIALLY DEFERRED If a constraint is deferrable, this clause specifies when to check the constraint.
  • INITIALLY IMMEDIATE is the default.
  • If the constraint is INITIALLY IMMEDIATE, it is checked after each statement.
  • If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction.
  • The constraint check time can be altered with the SET CONSTRAINTS command.
How these options work together is not clearly documented, so Stephan Szabo has supplied the following guidelines:

Constraint Specification 
Deferability Check time Effective Result

Contributors: Ron Johnson ron.l.johnson at, Stephan Szabo sszabo at, Dennis Gearon gearond at
Vadim Mikheev leaves the PostgreSQL Core Development Team
[HACKERS] Farewell 10-Aug-2003

Vadim Mikheev is leaving the PostgreSQL Core Development Team. For some time now Vadim Mikheev has not had time to be a major contributor to PostgreSQL and seems to feel this is the time to acknowledge this formally.

Vadim was one of the initial Core Team which took over ownership of Postgres95 when it was turned over by UCBerkeley and Andrew Yu and Jolly Chen.

Many people have given great thanks to Vadim for his excellent work. The majority of authors of the public responses to Vadim's message are all notable themselves which says much about Vadim's work.

D'Arcy Cain:

I know that a lot of what makes PostgreSQL what it is today is can be directly attributed to your work. Thank you. And thank you for the personal help back when I was working on the PostgreSQL trigger documentation.
Mike Mascari:
It's hard to imagine PostgreSQL with out MVCC, WAL, subselects, etc.
Hiroshi Inoue:
If you had not been here in the PostgreSQL community, I might not have been here.
Marc Fournier:
...unlike most co-workesr, I can definitely say its been a great pleasure to have known, and worked, with you ... you brought, and gave, alot to the project, and for that, we thank you ... thanks :)
Bruce Momjian:
Vadim, sorry to see you go. You baled me out of a lot of difficult problems. Thanks.
Jan Wieck:
Do swidanie i bolshoi sbaseebo, Vadim.
Andrew Sullivan
I can say for sure that if it weren't for your work, we'da been sunk.

Robert Treat summarizes the general feeling with a quote from Isaac Newton:

"If I have seen farther than others, it is by standing on the shoulders of giants", Issac Newton

There is a proposal to add a new Hall of Fame page to the PostgreSQL site to honor both Vadim and Thomas Lockhart.

Contributors: Vadim Mikheev, D'Arcy J.M. Cain, Mike Mascari, Bruce Momjian, Oleg Bartunov, Hiroshi Inoue, The Hermit Hacker, Dave Page, Stephan Szabo, Robert Treat, Tom Lane, Josh Berkus, Gaetano Mendola, Jan Wieck, Andrew Sullivan
Where's the Code?
[GENERAL] Functions and procedures code!!!! 4-Aug-2003

There are many ways to extract the code source which defines a function. It does require going through the system catalogs to do so, however. And the output is a little messy. But it certainly is helpful while you are debugging to know what version you have currently defined.

I use this little view. It combines the entire function definition into one element so that, for example, I can see all of the functions which operate on or return a type by qualifying on the function_definition. I've thrown in some newlines so the pl source can actually be read from psql, though it is not necessarily pretty.

	create or replace view showfunctions as
	t.typname || ' = ' || n.nspname || '.' || 
	  p.proname || '(' || pg_catalog.oidvectortypes(p.proargtypes) || ')'
	as function_definition,
	(select case when lanname <> 'c' 
	 then '\n\n'||prosrc ||'\n\n'
	 else '\n'||prosrc ||'.so\n\n'
	end) as source
	from pg_proc p, pg_type t, pg_namespace n, pg_language l
	where p.prorettype = t.oid and p.pronamespace = n.oid
	   and p.prolang = l.oid;

	-- shows everything in the public schema (and any function with
	-- public in its name)
	select * from showfunctions where function_definition like '%public%';

	-- shows all functions which take or return boolean values
	select * from showfunctions where function_definition like '%boolean%';

The first column of the output indicates the language. The language is 'c', 'internal', 'sql' or one of the pl languages you have defined in your database. For example, 'plpgsql' and 'plperl'.

Contributors: Freddy Menjívar M. mmfreddy at, Tom Lane 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