PostgreSQL

Use. PostgreSQL Naming Rules The Importance of the COMMENT Command Creating, Destroying, and Viewing Databases Tablespaces Creating New Databases Dropping a Database Viewing Databases Creating New Tables Temporary Tables Table Constraints Dropping Tables Inheritance ALTER TABLE Adding Indexes to a Table Tradeoffs Creating an Index Functional Indexes and Partial Indexes Creating Indexes on Array Values Indexes and Tablespaces Getting Information About Databases and Tables Transaction Processing Persistence Transaction Isolation Multi-Versioning and Locking Summary 4. Performance. How PostgreSQL Organizes Data Page Caching Summary Gathering Performance Information Dead Tuples Index Performance Understanding How PostgreSQL Executes a Query EXPLAIN Seq Scan Index Scan Sort Unique LIMIT Aggregate Append Result Nested Loop Merge Join Hash and Hash Join Group Subquery Scan and Subplan Tid Scan Materialize Setop (Intersect, Intersect All, Except, Except All) Execution Plans Generated by the Planner The ARC Buffer Manager Table Statistics Performance Tips II. PROGRAMMING WITH POSTGRESQL. 5. Introduction to PostgreSQL Programming. Server-Side Programming PL/pgSQL Other Procedural Languages Supported by PostgreSQL Extending PostgreSQL Using External Languages Client-Side APIs General Structure of Client Applications Connection Properties LISTEN/NOTIFY Choosing an Application Environment Server-Side Code Client-Side Code Mixing Server-Side and Client-Side Code Summary 6. Extending PostgreSQL. Extending the PostgreSQL Server with Custom Functions Returning Multiple Values from an Extension Function The PostgreSQL SRF Interface Returning Complete Rows from an Extension Function Extending the PostgreSQL Server with Custom Data Types Internal and External Forms Defining a Simple Data Type in PostgreSQL Defining the Data Type in C Defining the Input and Output Functions in C Defining the Input and Output Functions in PostgreSQL Defining the Data Type in PostgreSQL Indexing Custom Data Types Summary 7. PL/pgSQL. Installing PL/pgSQL Language Structure Quoting Embedded Strings CREATE FUNCTION DROP FUNCTION Function Body Comments Variables PL/pgSQL Statement Types Cursors FETCH Parameterized Cursors Cursor References Triggers TRIGGER Return Values TRIGGER Function Arguments Polymorphic Functions PL/pgSQL and Security Summary 8. The PostgreSQL C API-libpq. Prerequisites Client 1-Connecting to the Server Compiling the Client Identifying the Server Client 2-Adding Error Checking Viewing Connection Attributes Client 3-Simple Processing-PQexec() and PQprint() Results Returned by PQexec() The Prepare/Execute Model Client 4-An Interactive Query Processor Processing Multiple Result Sets Asynchronous Processing Summary 9. A Simpler C API-libpgeasy. Prerequisites Client 1-Connecting to the Server Client 2-Adding Error Checking Client 3-Processing Queries Working with Binary Cursors Byte Ordering and NULL Values Client 4-An Interactive Query Processor Summary 10. The New PostgreSQL C++ API-libpqxx. Prerequisites Client 1-Connecting to the Server Using pqxx-config to Create a Simple Makefile connection Member Functions Client 2-Adding Error Checking Other Exceptions Thrown by libpqxx Handling Informational/Warning Messages with Notice Processor Objects Client 3-Processing Queries Working with Transactions Working with Result Sets Working with Large-Objects LISTEN/NOTIFY set_variable() and get_variable() Client 4-Working with transactors Summary 11. Embedding SQL Commands in C Programs-ecpg. Prerequisites Client 1-Connecting to the Server The ecpg Preprocessor Connection Strings Client 2-Adding Error Checking The sqlca Structure Client 3-Processing SQL Commands ecpg Data Types Client 4-An Interactive Query Processor Summary 12. Using PostgreSQL from an ODBC Client Application. ODBC Architecture Overview The ODBC Client Application The ODBC Driver Manager The ODBC Driver The ODBC-Compliant Database The Data Source Setting Up a Data Source on Unix Systems Setting Up a Data Source in Windows Datasource Connection Properties Prerequisites Client 1-Connecting to the Server Client 2-Adding Error Checking Client 3-Processing Queries Client 4-An Interactive Query Processor Summary Resources 13. Using PostgreSQL from a Java Client Application. JDBC Architecture Overview The JDBC DriverManager The JDBC Driver The JDBC-Compliant Database Prerequisites Client 1-Connecting to the Server JDBC URLs Client 2-Adding Error Checking JNDI and the DataSource Class Client 3-Processing Queries Statement Classes Metadata Client 4-An Interactive Query Processor Summary 14. Using PostgreSQL with Perl. DBI Architecture Overview The DBI The DBD Driver The DBI-Compliant Database Prerequisites Client 1-Connecting to the Server DBI URLs Client 2-Adding Error Checking Client 3-Processing Queries The Prepare/Execute Model Metadata and Result Set Processing Other Statement and Database Handle Attributes Client 4-An Interactive Query Processor Summary 15. Using PostgreSQL with PHP. PHP Architecture Overview Prerequisites Client 1-Connecting to the Server Client 2-Adding Error Checking Client 3-Query Processing Other Ways to Retrieve Result Set Values Metadata Access Client 4-An Interactive Query Processor Other Features Summary 16. Using PostgreSQL with Tcl and Tcl/Tk. Prerequisites Client 1-Connecting to the Server Making the Connection Dialog Reusable Client 2-Query Processing Result Set Processing Client 3-An Interactive Query Processor The libpgtcl Large-Object API Summary 17. Using PostgreSQL with Python. Python/PostgreSQL Interface Architecture Prerequisites Client 1-Connecting to the Server Client 2-Adding Error Checking Client 3-Query Processing Client 4-An Interactive Command Processor Summary 18. Npgsql: The .NET Data Provider. Prerequisites Preparing Visual Studio Understanding the ADO.NET Class Hierarchy Creating an Npgsql-enabled VB Project Client 1-Connecting to the Server Client 2-An Interactive Query Processor Client 3-Updating the Database with a DataSet Client 4-A More Robust Query Processor Client 5-Using a Typed DataSet Creating a Typed DataSet Summary 19. Other Useful Programming Tools. PL/Java-Writing Stored Procedures in Java Installing PL/Java Writing a Simple PL/Java Function Accessing the Database from a PL/Java Function Returning Multiple Results from a PL/Java Function Writing PL/Java Trigger Functions Adding Install/Uninstall Commands to a Jar File pgcurl-Web-enabling Your PostgreSQL Server pgbash-Writing PostgreSQL-enabled Shell Scripts III. POSTGRESQL ADMINISTRATION. 20. Introduction to PostgreSQL Administration. Security User Accounts Backup and Restore Server Startup and Shutdown Running PostgreSQL on a Windows Host Tuning Installing Updates Localization Summary 21. PostgreSQL Administration. Roadmap (Where's All My Stuff?) Installing PostgreSQL Unix/Linux Windows Managing Databases Creating a New Cluster Creating a New Database Routine Maintenance Logfile Rotation The PostgreSQL BGWRITER Process Managing User Accounts CREATE USER Managing Groups Configuring Your PostgreSQL Runtime Environment File Locations Security-Related Parameters Connection-Related Parameters Operational Parameters Write-Ahead Log Parameters Optimizer Parameters Debugging/Logging Parameters Performance Statistics Per-session Parameters Miscellaneous Parameters Read-only Parameters Arranging for PostgreSQL Startup and Shutdown Using pg_ctl Shutdown Modes Configuring PostgreSQL Startup on Unix/Linux Hosts Backing Up and Copying Databases Using pg_dump Using pg_dumpall Using pg_restore Point-in-time Recovery Summary 22. Internationalization and Localization. Locale Support Enabling Locale Support Effects of Locale Support PostgreSQL Locale Summary Multi-Byte Character Sets Encodings Supported by PostgreSQL Enabling Multi-Byte Support Selecting an Encoding Client/Server Translation Summary 23. Security. Securing the PostgreSQL Data Files Securing PostgreSQL Data Files in Windows Securing Network Access local Connections host, hostssl, and hostnossl Connections The trust Authentication Method The ident Authentication Method The password Authentication Method The crypt Authentication Method The md5 Authentication Method The pam Authentication Method The krb4 and krb5 Authentication Methods The reject Authentication Method Securing Tables Securing Functions Summary 24. Replicating PostgreSQLData with Slony. Overview Requirements Creating a Replication Cluster Starting the Replication Daemons Creating a Replication Set Subscribing to a Replication Set Copying Table and Sequence Definitions Creating a Subscriber Changing the Cluster Topology (Re-mastering and Failover) Summary 25. Contributed Modules. Exchanging PostgreSQL Data with XML XPath Queries Converting XML Data with XSLT Using Full-text Search Searching Multiple Columns Simplifying tsearch2 with Customized Functions Searching for Phrases Configuring tsearch2 Index.