Re: Re: Better Features document?
От | Joel Burton |
---|---|
Тема | Re: Re: Better Features document? |
Дата | |
Msg-id | Pine.LNX.4.21.0104080847250.19671-100000@olympus.scw.org обсуждение исходный текст |
Ответ на | Re: Re: [GENERAL] Better Features document? (Justin Clift <jclift@iprimus.com.au>) |
Список | pgsql-docs |
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@scw.org. 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@scw.org> Director of Information Systems, Support Center of Washington
В списке pgsql-docs по дате отправления: