MIME-Version: 1.0
In-Reply-To: 
 <CAKt_Zfui6MvFezCkgUJ3h56WLjZE4OS66+YNv6b12WdrmvORZA@mail.gmail.com>
References: 
 <CAKt_Zfui6MvFezCkgUJ3h56WLjZE4OS66+YNv6b12WdrmvORZA@mail.gmail.com>
Date: Wed, 15 Aug 2012 02:33:00 -0700
Message-ID: 
 <CAKt_ZfuPkq2J+H0k-BOiGXJqpcP924_tLfz1TiOCHs0v3Eg8fQ@mail.gmail.com>
Subject: Re: Would like to contribute a section to docs for 9.3. Where to
	start?
From: Chris Travers <chris.travers@gmail.com>
To: Postgres Documentation <pgsql-docs@postgresql.org>
Content-Type: multipart/alternative; boundary=e89a8ff1cb9ae35d0e04c74a9c8e

--e89a8ff1cb9ae35d0e04c74a9c8e
Content-Type: text/plain; charset=ISO-8859-1

So here is a very rough draft.  I would be interested in feedback as to
inaccuracies or omissions.  I would like to get the technical side right
before going into an editorial phase.

Any feedback on the technical side?

Best Wishes,
Chris Travers

How is PostgreSQL "Object-Relational?"

The term Object-Relational has been applied to databases which attempt to
bridge the relational and object-oriented worlds with varying degrees of
success.  Bridging this gap is typically seen as desirable because
object-oriented and relational models are very different paradigms and
programmers often do not want to switch between them.  There are, however,
fundamental differences that make this a very hard thing to do well.  The
best way to think of PostgreSQL in this way is as a relational database
management system with some object-oriented features.

PostgreSQL is a development platform in a box.  It supports stored
procedures written in entirely procedural languages like PL/PGSQL or Perl
without loaded modules, and more object-oriented languages like Python or
Java, often through third party modules.  To be sure you can't write a
graphical interface inside PostgreSQL, and it would not be a good idea to
write additional network servers, such as web servers, directly inside the
database.  However the environment allows you to create sophisticated
interfaces for managing and transforming your data. Because it is a
platform in a box the various components need to be understood as different
and yet interoperable.  In fact the primary concerns of object-oriented
programming are all supported by PostgreSQL, but this is done in a way that
is almost, but not quite, entirely unlike traditional object oriented
programming.  For this reason the "object-relational" label tends to be a
frequent source of confusion.

Data storage in PostgreSQL is entirely relational, although this can be
degraded using types which are not atomic, such as arrays, XML, JSON, and
hstore.  Before delving into object-oriented approaches, it is important to
master the relational model of databases.  For the novice, this section is
therefore entirely informational.  For the advanced developer, however, it
is hoped that it will prove inspirational.

In object-oriented terms, very relation is a class, but not every class is
a relation.  Operations are performed on sets of objects (an object being a
row), and new row structures can be created ad-hoc.  PostgreSQL is,
however, a strictly typed environment and so in many cases, polymorphism
requires some work.

Data Abstraction and Encapsulation in PostgreSQL

The relational model itself provides some tools for data abstraction and
encapsulation, and these features are taken to quite some length in
PostgreSQL.  Taken together these are very powerful tools and allow for
things like calculated fields to be simulated in relations and even indexed
for high performance.

Views are the primary tool here.  With views, you can create an API for
your data which is abstracted from the physical storage.  Using the rules
system, you can redirect inserts, updates, and deletes from the view into
underlying relations, preferably using user defined functions.  Being
relations, views are also classes.

A second important tool here is the ability to define what appear to be
calculated fields using stored procedures.  If I create a table called
"employee" with three fields (first_name, middle_name, last_name) among
others, and create a function called "name" which accepts a single employee
argument and concatenates these together as "last_name, first_name
middle_name" then if I submit a query which says:

select e.name from employee e;

it will transform this into:

select name(e) from employee e;

This gives you a way to do calculated fields in PostgreSQL without
resorting to views. Note that these can be done on views as well because
views are relations.  These are not real fields though.  Without the
relation reference, it will not do the transformation (so SELECT name from
employee will not have the same effect).

Messaging and Class API's in PostgreSQL

A relation is a class.  The class is accessed using SQL which defines a new
data structure in its output.  This data structure unless defined elsewhere
in a relation or a complex type cannot have methods attached to it and
therefore can not be used with the class.method syntax described above.
 There are exceptions to this rule, of course, but they are beyond the
scope of this introduction.  In general it is safest to assume that the
output of one query, particularly one with named output fields, cannot
safely be used as the input to another.

A second messaging aparatus in PostgreSQL is the LISTEN/NOTIFY framework
which can be used along with triggers to issue notifications to other
processes when a transaction commits.  This approach allows you to create
queue tables, use triggers to move data into these tables (creating
'objects' in the process) and then issuing a notification to another
process when the data commits and becomes visible.  This allows for very
complex and and interactive environments to be built from modular pieces.

Polymorphism in PostgreSQL

PostgreSQL is very extensible in terms of all sorts of aspects of the
database.  Not only can types be created and defined, but also operators
can be defined or overloaded.

A more important polymorphism feature is the ability to cast one data type
as another.  Casts can be implicit or explicit.  Implicit casts, which have
largely been removed from many areas of PostgreSQL, allow for PostgreSQL to
cast data types when necessary to find functions or operators that are
applicable.  Implicit casting can be dangerous because it can lead to
unexpected behavior because minor errors  can lead to unexpected results.
 '2012-05-31' is not 2012-05-31.  The latter is an integer expression that
reduces to 1976.  If you create an implicit cast that turns an integer into
a date being the first of the year, the lack of quoting will insert
incorrect dates into your database without raising an error ('1976-01-01'
instead of the intended '2012-05-31').  Implicit casts can still have some
uses.

Inheritance in PostgreSQL

In PostgreSQL tables can inherit from other tables.  Their methods are
inherited but their castes are not, nor are their indexes.  This allows you
develop object inheritance hierarchies in PostgreSQL.  Multiple inheritance
is possible.

Table inheritance is an advanced concept and has many gotchas.  Please
refer to the proper sections of the manual for more on this topic.  On the
whole it is probably best to work with table inheritance first in areas
where it is more typically used, such as table partitioning, and later look
at it in terms of object-relational capabilities.

Overall the best way to look at PostgreSQL as an object-relational database
is a database which provides very good relational capabilities plus some
advanced features that allows one do create object-relational systems on
top of it.  These systems can then move freely between object-oriented and
relational worldviews but are still more relational than object-oriented.
 At any rate they bear little resemblance to object-oriented programming
environments today.  With PostgreSQL this is very much a toolkit approach
for object-relational databases building on a solid relational foundation.
 This means that these are advanced functions which are powerful in the
hands of experienced architects, but may be skipped over at first.

--e89a8ff1cb9ae35d0e04c74a9c8e
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

So here is a very rough draft. =A0I would be interested in feedback as to i=
naccuracies or omissions. =A0I would like to get the technical side right b=
efore going into an editorial phase.<div><br></div><div>Any feedback on the=
 technical side?</div>
<div><br>Best Wishes,</div><div>Chris Travers<br><div><br></div><div><div>H=
ow is PostgreSQL &quot;Object-Relational?&quot;</div><div><br></div><div>Th=
e term Object-Relational has been applied to databases which attempt to bri=
dge the relational and object-oriented worlds with varying degrees of succe=
ss. =A0Bridging this gap is typically seen as desirable because object-orie=
nted and relational models are very different paradigms and programmers oft=
en do not want to switch between them. =A0There are, however, fundamental d=
ifferences that make this a very hard thing to do well. =A0The best way to =
think of PostgreSQL in this way is as a relational database management syst=
em with some object-oriented features.</div>
<div><br></div><div>PostgreSQL is a development platform in a box. =A0It su=
pports stored procedures written in entirely procedural languages like PL/P=
GSQL or Perl without loaded modules, and more object-oriented languages lik=
e Python or Java, often through third party modules. =A0To be sure you can&=
#39;t write a graphical interface inside PostgreSQL, and it would not be a =
good idea to write additional network servers, such as web servers, directl=
y inside the database. =A0However the environment allows you to create soph=
isticated interfaces for managing and transforming your data.=A0Because it =
is a platform in a box the various components need to be understood as diff=
erent and yet interoperable. =A0In fact the primary concerns of object-orie=
nted programming are all supported by PostgreSQL, but this is done in a way=
 that is almost, but not quite, entirely unlike traditional object oriented=
 programming. =A0For this reason the &quot;object-relational&quot; label te=
nds to be a frequent source of confusion.</div>
<div><br></div><div>Data storage in PostgreSQL is entirely relational, alth=
ough this can be degraded using types which are not atomic, such as arrays,=
 XML, JSON, and hstore. =A0Before delving into object-oriented approaches, =
it is important to master the relational model of databases. =A0For the nov=
ice, this section is therefore entirely informational. =A0For the advanced =
developer, however, it is hoped that it will prove inspirational.</div>
<div><br></div><div>In object-oriented terms, very relation is a class, but=
 not every class is a relation. =A0Operations are performed on sets of obje=
cts (an object being a row), and new row structures can be created ad-hoc. =
=A0PostgreSQL is, however, a strictly typed environment and so in many case=
s, polymorphism requires some work.</div>
<div><br></div><div>Data Abstraction and Encapsulation in PostgreSQL</div><=
div><br></div><div>The relational model itself provides some tools for data=
 abstraction and encapsulation, and these features are taken to quite some =
length in PostgreSQL. =A0Taken together these are very powerful tools and a=
llow for things like calculated fields to be simulated in relations and eve=
n indexed for high performance.</div>
<div><br></div><div>Views are the primary tool here. =A0With views, you can=
 create an API for your data which is abstracted from the physical storage.=
 =A0Using the rules system, you can redirect inserts, updates, and deletes =
from the view into underlying relations, preferably using user defined func=
tions. =A0Being relations, views are also classes.</div>
<div><br></div><div>A second important tool here is the ability to define w=
hat appear to be calculated fields using stored procedures. =A0If I create =
a table called &quot;employee&quot; with three fields (first_name, middle_n=
ame, last_name) among others, and create a function called &quot;name&quot;=
 which accepts a single employee argument and concatenates these together a=
s &quot;last_name, first_name middle_name&quot; then if I submit a query wh=
ich says: =A0</div>
<div><br></div><div>select <a href=3D"http://e.name">e.name</a> from employ=
ee e;=A0</div><div><br></div><div>it will transform this into:</div><div><b=
r></div><div>select name(e) from employee e;</div><div><br></div><div>This =
gives you a way to do calculated fields in PostgreSQL without resorting to =
views. Note that these can be done on views as well because views are relat=
ions. =A0These are not real fields though. =A0Without the relation referenc=
e, it will not do the transformation (so SELECT name from employee will not=
 have the same effect).</div>
<div><br></div><div>Messaging and Class API&#39;s in PostgreSQL</div><div><=
br></div><div>A relation is a class. =A0The class is accessed using SQL whi=
ch defines a new data structure in its output. =A0This data structure unles=
s defined elsewhere in a relation or a complex type cannot have methods att=
ached to it and therefore can not be used with the class.method syntax desc=
ribed above. =A0There are exceptions to this rule, of course, but they are =
beyond the scope of this introduction. =A0In general it is safest to assume=
 that the output of one query, particularly one with named output fields, c=
annot safely be used as the input to another.</div>
<div><br></div><div>A second messaging aparatus in PostgreSQL is the LISTEN=
/NOTIFY framework which can be used along with triggers to issue notificati=
ons to other processes when a transaction commits. =A0This approach allows =
you to create queue tables, use triggers to move data into these tables (cr=
eating &#39;objects&#39; in the process) and then issuing a notification to=
 another process when the data commits and becomes visible. =A0This allows =
for very complex and and interactive environments to be built from modular =
pieces.</div>
<div><br></div><div>Polymorphism in PostgreSQL</div><div><br></div><div>Pos=
tgreSQL is very extensible in terms of all sorts of aspects of the database=
. =A0Not only can types be created and defined, but also operators can be d=
efined or overloaded.</div>
<div><br></div><div>A more important polymorphism feature is the ability to=
 cast one data type as another. =A0Casts can be implicit or explicit. =A0Im=
plicit casts, which have largely been removed from many areas of PostgreSQL=
, allow for PostgreSQL to cast data types when necessary to find functions =
or operators that are applicable. =A0Implicit casting can be dangerous beca=
use it can lead to unexpected behavior because minor errors =A0can lead to =
unexpected results. =A0&#39;2012-05-31&#39; is not 2012-05-31. =A0The latte=
r is an integer expression that reduces to 1976. =A0If you create an implic=
it cast that turns an integer into a date being the first of the year, the =
lack of quoting will insert incorrect dates into your database without rais=
ing an error (&#39;1976-01-01&#39; instead of the intended &#39;2012-05-31&=
#39;). =A0Implicit casts can still have some uses.</div>
<div><br></div><div>Inheritance in PostgreSQL</div><div><br></div><div>In P=
ostgreSQL tables can inherit from other tables. =A0Their methods are inheri=
ted but their castes are not, nor are their indexes. =A0This allows you dev=
elop object inheritance hierarchies in PostgreSQL. =A0Multiple inheritance =
is possible.</div>
<div><br></div><div>Table inheritance is an advanced concept and has many g=
otchas. =A0Please refer to the proper sections of the manual for more on th=
is topic. =A0On the whole it is probably best to work with table inheritanc=
e first in areas where it is more typically used, such as table partitionin=
g, and later look at it in terms of object-relational capabilities.</div>
<div><br></div><div>Overall the best way to look at PostgreSQL as an object=
-relational database is a database which provides very good relational capa=
bilities plus some advanced features that allows one do create object-relat=
ional systems on top of it. =A0These systems can then move freely between o=
bject-oriented and relational worldviews but are still more relational than=
 object-oriented. =A0At any rate they bear little resemblance to object-ori=
ented programming environments today. =A0With PostgreSQL this is very much =
a toolkit approach for object-relational databases building on a solid rela=
tional foundation. =A0This means that these are advanced functions which ar=
e powerful in the hands of experienced architects, but may be skipped over =
at first.</div>
</div></div>

--e89a8ff1cb9ae35d0e04c74a9c8e--