Re: Re: Better Features document?

Ok, so here's the draft I wrote a few months ago up the updated features
document.

--

pg_advocates of the world, unite!

As I've recommended it to other people, I've found myself at a loss to
explain all of
the features that recommend the system. Digging around, almost all of
these can be
identified through the various manuals and FAQs.

I'm envisioning a document that could be posted on the postgresql.org web
site
that gives more information about the server's capabilities than the
introductory
information on the site, without having to dig around in different
manuals. I think
it shouldn't attempt to be completely comprehensive (many features are
wonderful,
but obscure for first-time buyers :-) ), but should list the features,
along with
a short explanation of what they mean and why they might be useful. With
the explosion
of new Unix/Linux users, I constant come across new database
administrators that
come from Windows-database backgrounds, and have no idea what a trigger
is,
let alone what our more unique features are, and how the might be helpful.

I've written out the features I see, in some order, with some attempts
at explanation. I'd be grateful for any corrections or
suggestions. Embedded throughout are XXX for areas that need immediate
help; however, the entire document could use a critical eye for
suggestions and improvements.

At this point, its just a text document, but as it improves, I'll
translate it into
an attractive HTML document suitable for posting or framing.

Suggestions/improvements should be sent to jburton(at)scw(dot)org(dot) I will
post the list improved versions of the document.

----------------------------------------------------------

PostgreSQL is the world's most advanced Open Source database server.

-------------

INSTALLATION:

Supported Platforms:
* AIX - IBM on AIX 3.2.5 or 4.x
* BeOS - x86
* Alpha - DEC Alpha AXP on Digital Unix 2.0, 3.2, 4.0
* BSD44_derived - OSs derived from 4.4-lite BSD (NetBSD, FreeBSD)
* BSDI - BSD/OS 2.x, 3.x, 4.x
* HP-UX - HP PA-RISC on HP-UX 9.*, 10.*
* IRIX - SGI MIPS on IRIX 5.3
* Linux - Intel i86 Alpha SPARC PPC M68k
* Mac OS X - PPC
* SCO Unix - SCO 3.2v5 Unixware
* Solaris - i386, SPARC
* SunOS - SUN SPARC on SunOS 4.1.3
* SVR4 - Intel x86 on Intel SVR4 and MIPS
* Tru64 - Compaq Alpha
* ultrix4 - DEC MIPS on Ultrix 4.4
* UnixWare
* Windows NT/2000 (using Cygwin)
* (I got this list from the FAQ, is it missing anything?)

Unsupported Platforms: (previous versions have worked, not tested
recently)
* DG/UX - m88k
* MkLinux - PPC750
* NextStep - x86
* QNX - x86
* System V R4 - m88k MIPS
* Ultrix - MIPS VAX

Easy installation:
* Uses standard configure and make options for building from source.
* Binary RPMs are available for most popular Linux distributions.
* Can be installed without root permissions.
* Included in most popular Linux and BSD distributions

-----------
HISTORY, DEVELOPERS & LICENSE:
* Descended from Ingres and Postgres, cutting-edge academic database
projects
* Maintained, improved, and supported by a stable Global Development
Group, and a large number of contributors
* Source code available for download, and anonymous CVS access is
available
* Licensed under BSD license, allowing truly free modifications and
redistribution of binaries

-----------
DATA FEATURES

Rich support for data types:
* Includes most standard and exotic SQL92 and SQL3 data types,
including INT, BOOL, CHAR, VARCHAR, DATE, INTERVAL, TIMESTAMP.
* Additional types for geometric constructs (points, lines, etc.),
TCP/IP network addresses, Ethernet card IDs, and more.
* New types can be defined, along with neccessary supporting functions
and operators.

Support for BLOB / Large Objects:
* PostgreSQL supports storage of binary large objects, including
pictures, sounds, or video.
* These objects can be retrieved (in whole or part) by client
applications.

Multibyte and LOCALE-aware settings
* For storage and ordering of international characters and character
sets.

Foreign key / Referential integrity support:
* Supports CREATE TABLE ... FOREIGN KEY syntax for referential
integrity
* Supports different actions for updating and deleting, including
cascading, restricting, and restoring to default or NULL values. These
features are critical for data integrity in many applications.
* Foreign keys can be detected by database modeling tools, such as
Computer Associates' ERwin, to ease database design and documentation.

-----------

SQL FEATURES:

SELECT FEATURES:

* Support for standard SQL conditions, such as CASE WHEN THEN,
COALESCE, and NULLIF.
* Support for joins, including INNER, OUTER (LEFT and FULL) and NATURAL
JOINS.
* Support for subselects:
* Subqueries are nested queries that allow complex questions to be
answered entirely through the database
* Using subqueries can simplify and speed up database applications
* XXXAn example subquery?
* Support for SELECT DISTINCT and SELECT DISTINCT ON ( column ), to
show only unique rows, and matching data for unique values.
* Full support for GROUP BY and aggregate (domain) functions, including
COUNT(), SUM(), AVG(), MIN(), MAX(), STDDEV() and VARIANCE().
* New aggregate functions can easily be created in C or any procedural
language PostgreSQL supports.
* Sub-selects in FROM clause, such as SELECT COUNT(x), AVG(x),
SUM(x) FROM (SELECT SUM(weight) AS x FROM Items GROUP BY manufacturer) AS
Items
* Support for UNION and UNION ALL.
* Support for extensions INTERSECTS and EXCEPT, which are used like
UNION, to join two or more SELECT statements,
but find only intersecting records or missing records.
* Extensions for LIMIT and OFFSET to allow for the selection of any
arbitrary number of records, ie SELECT * FROM Items
ORDER BY cost LIMIT 5;

OTHER SQL FEATURES:

Procedural Languages:
* Procedural languages can be used to create user-defined functions and
procedures, and are used to create triggers.
* PostgreSQL currently supports several procedural languages:
* C XXXIs it fair to call C a PL?
* SQL
* PL/PGSQL (very similar to Oracle's PL/SQL)
* PL/Tcl
* PL/Perl
* PL/Python (alpha)
* Procedural languages functions can be used by and defined by
untrusted users.

Comprehensive Library of Functions:
* Hundreds of built-in functions in its library, including rich support
for mathematical, date/time, and string manipulation.
* Additional functions: ISBN/ISSN handling, SOUNDEX sounds-like
matching
* Support functions for full-text indexing
* Online library of user functions at http://techdocs.postgresql.org
* ODBC functions

User-defined functions:
* User defined functions allow designers to create new functions.
* These can be programmed in C, or in any of the procedural languages
PostgreSQL supports.
* User-defined functions allow database designer to encapsulate
business logic in the database, rather than in the front-end.

Comprehensive operators:
* Inequalities and logical conditions
* Standard SQL LIKE matching and case-insensitive LIKE matching
* Full regular expression matching and case-insensitive regular
expression matching
* Time/date and interval comparisons
* Many more
* New operators can be defined using procedures written in C or any of
the procedural languages PostgreSQL supports.

Views:
Views are stored SQL SELECT statements that are used to abstract complex
views. For example, a common operation
that joins four tables, with subselects, a WHERE clause, an ORDER BY
clause, etc., can be given
the name EMPLOYEE_INFO, and treated as a virtual table--including (if
appropriate)
INSERTS, UPDATES, and DELETEs. This allow database users to work more
easily with the data,
and requires less sophistication in query writing for most users.
* Supports views

Triggers:
Triggers are procedures that are called by the database on defined
actions. For example, a trigger could be used to log every time a
record in inserted or deleted to or from a table, or to update a field
every time another field has been changed.
* Supports triggers before and/or after actions
* Supports triggers for every row or every action
* Trigger functions can be written in C or any of the procedural
languages PostgreSQL supports.

Rules:
Rules are an advanced feature of PostgreSQL that allows a database
designer to substitute any
SQL table operation (SELECT, INSERT, DELETE, etc.) for another
operation. Rules can be used
to return different sets based on permissions, turn an attempted update
into a delete, etc.

Transactions:
Transactions allow all data modifications to be encapsulated in a
transaction. This allows the
database to rollback and restore all original data if there is an error in
the transaction
or if the database server crashes. Transactions are a critical feature for
ensuring
data integrity in many applications.
* Support for COMMIT or ROLLBACK
* Works transparently with transaction features of interface programs,
such as Perl's DBI, Zope, JDBC and ODBC transactions, etc.
* XXXSomeone want to explain MVCC for the masses?

Inheritance:
* XXXI never use this--anyone want to explain?

-----------
EXTENSIONS:

Replication:

Contact Management:

(XXXI don't use either of these. Can anyone at pgsql inc, or anyone at
all, provide some bullets of the advantages?)

-----------

DOCUMENTATION & SUPPORT:

Documentation:
* Comes with four comprehensive manuals (online or in print formats)
* Tutorial manual
* Excellent commercial, full length book (available in print form, or
online at http://www.postgresql.org/docs/awbook.html)

Online support:
* Support is available through 13 online discussion lists.
* Comprehensive installation instructions and FAQ included in
distribution.
* Online resource listings at http://techdocs.postgresql.org

Commercial contracts:
* Commercial contracts are available from several companies, and
consulting from hundreds of companies and independent consultants.
* Commercially-packaged versions of PostgreSQL are available from
GreatBridge LLC, http://www.greatbridge.org.


-----------

SERVER ADMINISTRATION:

Backup and Recovery tools:
* PostgreSQL includes a tool (pg_dump) to handle backup of all database
data. These backups, which can be performed while the database is being
used, are platform-independent, human-readable, and can be stored as tar
archives or in compressed formats.
* Database can be restored as a whole, or (using
pg_restore) selectively to recover individual database elements.

Security:
* PostgreSQL offers a user/group security model that can restrict
operations by user and/or group.
* Privileges can be assigned for viewing and modifying data.
* Access to the database server itself can be restricted based on host,
username, database.
* Database passwords can be sent encrypted, and database connections
can be encrypted using SSH or SSL.

Temporary tables
* Temporary tables (which can be created by CREATE TABLE or SELECT
INTO) are automatically dropped at the end of a database connection.
* Easier maintenance than removing normal table manually.

GUI Administration:
* pgaccess: a platform-independent Tcl/Tk program for database
maintenance and report-writing
* pgAdmin: a comprehensive, Windows-based PostgreSQL administration
program
* XXXAnd some web programs I don't know about

-----------

CLIENT INTERFACES

Client programming languages/interfaces:
* Perl (through Pg and DBD::Pg)
* Python (through Pygres or PoPy)
* PHP (support built-in to PHP)
* Tcl
* ODBC, and therefore many ODBC client programs, including Microsoft
Access, StarOffice, Applix, etc.
* JDBC (Java Database Connectivity)
* C (through libpq and libpqeasy)
* Can use embedded SQL statements in C
* C++ (through libpq++)
* Emacs LISP
* R (Open Source statistical package)
* Zope (Open Source web application server)
* XXXand othhers, I'm sure--what's missing? ]

Web Publishing Solutions:
With its support for transactions, defined functions, and views,
PostgreSQL offers web developers
a safe and structured programming environment. Almost all web development
systems that uses Perl,
Python, ODBC, PHP, or Tcl can use PostgreSQL.
* Zope
* OpenACS, an Open Source port of ArsDigita's ACS
* ColdFusion
* Mason
* EmbPerl
* mod_perl and DBI
* XXXAnd others, no doubt
* Web groupware packages supporting PostgreSQL: Twig
(www.screwdriver.net/twig), XXXand no doubt many others

Query monitor:
* Comes with psql, a featured-filled text console-based interactive
query monitor.
* Includes full support for history and history editing,
customization, local/remote database access, and importing and exporting
of data.
* In addition, graphic query monitors available for many operating
systems and desktops, including Windows, KDE, and GNOME.

Event notification
* LISTEN and NOTIFY can be used to pass messages or notify different
clients of an event in the database.
* Can be used to coordinate different front-end clients (even across
different front-end systems).

--------
PERFORMANCE & STABILITY

Performance:
* Very competitive performance, especially for multi-user applications
and larger databases.
* Sophisticated locking models permits high scalability for concurrent
writes and reads.

Stability:
[ any info on large dbs used by PG? ]

---------
USERS

* SourgeForge
* Large open source database-backed collaborative web site
* OpenACS
* Fully open source version of ArsDigita's community-based web
system, ACS
* XXXBruce, surely you know of others.

-----
AND MORE

And More (Optional Contributions)
Distributed with PostgreSQL are many optional contributions, such as
SOUNDEX() functions
for 'sounds-like' string matching, full-text indexing, cryptographic hash
functions (including
SHA1 and MD5), user-handled long-term cooperative locking, ISBN/ISSN
number types, and more. In
addition, there are many additional functions that can be downloaded from
web sites or from the
PostgreSQL discussion lists.

--
Joel Burton <jburton(at)scw(dot)org>
Director of Information Systems, Support Center of Washington