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 cox.net, Andrew L. Gould algould at datawok.com, elein at varlena.com

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( 
	   "dbi:Pg:dbname=db;host=db_server;port=5432",
	   "test", 
	   "test", 
	   {RaiseError => 1} )
	or die "Unable to connect: $DBI::errstr\n";
	$dbh->disconnect;

Contributors: David Siebert dsiebert at eclipsecat.com, Thomas A. Lowery tl-lists at stlowery.net, Sean Chittenden sean at chittenden.org, Dennis Gearon gearond at cvc.net

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:

[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

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

Constraint Specification
Deferability	Check time	Effective Result
none	none	NOT DEFERRABLE, INITIALLY IMMEDIATE
NOT DEFERRABLE	none	NOT DEFERRABLE, INITIALLY IMMEDIATE
DEFERRABLE	none	DEFERRABLE, INITIALLY IMMEDIATE
none	INITIALLY IMMEDIATE	NOT DEFERRABLE, INITIALLY IMMEDIATE
none	INITIALLY DEFERRED	DEFERRABLE, INITIALLY DEFERRED
NOT DEFERRABLE	INITIALLY IMMEDIATE	NOT DEFERRABLE, INITIALLY IMMEDIATE
DEFERRABLE	INITIALLY IMMEDIATE	DEFERRABLE, INITIALLY IMMEDIATE
DEFERRABLE	INITIALLY DEFERRED	DEFERRABLE, INITIALLY DEFERRED
NOT DEFERRABLE	INITIALLY DEFERRED	ERROR

Contributors: Ron Johnson ron.l.johnson at cox.net, Stephan Szabo sszabo at megazone.bigpanda.com, Dennis Gearon gearond at cvc.net

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.

It's hard to imagine PostgreSQL with out MVCC, WAL, subselects, etc.

If you had not been here in the PostgreSQL community, I might not have been here.

...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 :)

Vadim, sorry to see you go. You baled me out of a lot of difficult problems. Thanks.

Do swidanie i bolshoi sbaseebo, Vadim.

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 vmikheev@reveredata.com, D'Arcy J.M. Cain darcy@druid.net, Mike Mascari mascarm@mascari.com, Bruce Momjian pgman@candle.pha.pa.us, Oleg Bartunov oleg@sai.msu.su, Hiroshi Inoue Inoue@tpf.co.jp, The Hermit Hacker scrappy@postgresql.org, Dave Page dpage@vale-housing.co.uk, Stephan Szabo sszabo@megazone.bigpanda.com, Robert Treat xzilla@users.sourceforge.net, Tom Lane tgl@sss.pgh.pa.us, Josh Berkus josh@agliodbs.com, Gaetano Mendola mendola@bigfoot.com, Jan Wieck janwieck@yahoo.com, Andrew Sullivan andrew@libertyrms.info

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
	select
	l.lanname,
	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 hotmail.com, Tom Lane tgl@sss.pgh.pa.us elein at varlena.com