The second edition of the best-selling PostgreSQL has been updated to completely cover new features and capabilities of the 8.0 version of PostgreSQL. You will be lead through the internals of the powerful PostgreSQL open source database chapter, offering an easy-to-read, code-based approach that makes it easy to understand how each feature is implemented, how to best use each feature, and how to get more performance from database applications. This definitive guide to building, programming and administering the powerful PostgreSQL open-source database system will help you harness one of the most widely used open source, enterprise-level database systems.
Introduction.
PostgreSQL Features
What Versions Does This Book Cover?
Who Is This Book For?
What Topics Does This Book Cover?
What's New in the Second Edition?
I. GENERAL POSTGRESQL USE.
1. Introduction to PostgreSQL and SQL.
A Sample Database
Basic Database Terminology
Prerequisites
Installing PostgreSQL Using an RPM
Connecting to a Database
A (Very) Simple Query
Creating Tables
Viewing Table Descriptions
Adding New Records to a Table
Using the INSERT Command
Using the COPY Command
Installing the Sample Database
Retrieving Data from the Sample Database
SELECT Expression
SELECT FROM Table
SELECT Single-Column FROM Table
SELECT Column-List FROM Table
SELECT Expression-List FROM Table
Selecting Specific Rows
The CASE Expression
Formatting Column Results
Matching Patterns
Aggregates
COUNT()
SUM()
AVG()
MIN() and MAX()
Other Aggregate Functions
Grouping Results
Multi-Table Joins
Join Types
UPDATE
DELETE
A (Very) Short Introduction to Transaction Processing
Creating New Tables Using CREATE TABLE...AS
Using VIEW
Summary
2. Working with Data in PostgreSQL.
NULL Values
Character Values
Syntax for Literal Values
Supported Operators
Numeric Values
Size, Precision, and Range-of-Values
Syntax for Literal Values
Supported Operators
Date/Time Values
Syntax for Literal Values
Supported Operators
Boolean (Logical) Values
Size and Valid Values
Syntax for Literal Values
Supported Operators
Geometric Data Types
Syntax for Literal Values
Sizes and Valid Values
Supported Operators
Object IDs (OID)
Syntax for Literal Values
Size and Valid Values
Supported Operators
BLOBs
Syntax for Literal Values
Supported Operators
Large-Objects
Network Address Data Types
MACADDR
CIDR
INET
Syntax for Literal Values
Supported Operators
Sequences
Arrays
Column Constraints
NULL/NOT NULL
UNIQUE
PRIMARY KEY
REFERENCES
CHECK()
Expression Evaluation and Type Conversion
Creating Your Own Data Types
Refining Data Types with CREATE DOMAIN
Creating and Using Composite Types
Summary
3. PostgreSQL SQL Syntax and 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.
PostgreSQL is a relational database with a long history. In the late 1970s, the University of California at Berkeley began development of PostgreSQL's ancestor—a relational database known as Ingres. Relational Technologies turned Ingres into a commercial product. Relational Technologies became Ingres Corporation and was later acquired by Computer Associates. Around 1986, Michael Stonebraker from UC Berkeley led a team that added object-oriented features to the core of Ingres; the new version became known as Postgres. Postgres was again commercialized; this time by a company named Illustra, which became part of the Informix Corporation. Andrew Yu and Jolly Chen added SQL support to Postgres in the mid-'90s. Prior versions had used a different, Postgres-specific query language known as Postquel. In 1996, many new features were added, including the MVCC transaction model, more adherence to the SQL92 standard, and many performance improvements. Postgres once again took on a new name: PostgreSQL.
Today, PostgreSQL is developed by an international group of open-source software proponents known as the PostgreSQL Global Development group. PostgreSQL is an open-source product—it is not proprietary in any way. Red Hat has recently commercialized PostgreSQL, creating the Red Hat Database, but PostgreSQL itself will remain free and open source.
PostgreSQL has benefited well from its long history. Today, PostgreSQL is one of the most advanced database servers available. Here are a few of the features found in a standard PostgreSQL distribution:
The first edition of this book covered versions 7.1 through 7.3. In this edition, we've updated the basics and added coverage for the new features introduced in versions 7.4 and 8.0. Throughout the book, I'll be sure to let you know which features work only in new releases, and, in a few cases, I'll explain features that have been deprecated (that is, features that are obsolete). You can use this book to install, configure, tune, program, and manage PostgreSQL versions 7.1 through 8.0.
Fortunately, the PostgreSQL developers try very hard to maintain forward compatibility—new features tend not to break existing applications. This means that all the features discussed in this book should still be available and substantially similar in later versions of PostgreSQL. I have tried to avoid talking about features that have not been released at the time of writing—where I have mentioned future developments, I will point them out.
If you are already using PostgreSQL, you should find this book a useful guide to some of the features that you might be less familiar with. The first part of the book provides an introduction to SQL and PostgreSQL for the new user. You'll also find information that shows how to obtain and install PostgreSQL on a Unix/Linux host, as well as on Microsoft Windows.
If you are developing an application that will store data in PostgreSQL, the second part of this book will provide you with a great deal of information relating to PostgreSQL programming. You'll find information on both server-side and client-side programming in a variety of languages.
Every database needs occasional administrative work. The final part of the book should be of help if you are a PostgreSQL administrator, or a developer or user that needs to do occasional administration. You will also find information on how to secure your data against inappropriate use.
Finally, if you are trying to decide which database to use for your current project (or for future projects), this book should provide all the information you need to evaluate whether PostgreSQL will fit your needs.
PostgreSQL is a huge product. It's not easy to find the right mix of topics when you are trying to fit everything into a single book. This book is divided into three parts.
The first part, "General PostgreSQL Use," is an introduction and user's guide for PostgreSQL. Chapter 1, "Introduction to PostgreSQL and SQL," covers the basics—how to obtain and install PostgreSQL (if you are running Linux, chances are you already have PostgreSQL and it may be installed). The first chapter also provides a gentle introduction to SQL and discusses the sample database we'll be using throughout the book. Chapter 2, "Working with Data in PostgreSQL," describes the many data types supported by a standard PostgreSQL distribution; you'll learn how to enter values (literals) for each data type, what kind of data you can store with each type, and how those data types are combined into expressions. Chapter 3, "PostgreSQL SQL Syntax and Use," fills in some of the details we glossed over in the first two chapters. You'll learn how to create new databases, new tables and indexes, and how PostgreSQL keeps your data safe through the use of transactions. Chapter 4, "Performance," describes the PostgreSQL optimizer. I'll show you how to get information about the decisions made by the optimizer, how to decipher that information, and how to influence those decisions.
Part II, "Programming with PostgreSQL," is all about PostgreSQL programming. In Chapter 5, "Introduction to PostgreSQL Programming," we start off by describing the options you have when developing a database application that works with PostgreSQL (and there are a lot of options). Chapter 6, "Extending PostgreSQL," briefly describes how to extend PostgreSQL by adding new functions, data types, and operators. Chapter 7, "PL/pgSQL," describes the PL/pgSQL language. PL/pgSQL is a server-based procedural language. Code that you write in PL/pgSQL executes within the PostgreSQL server and has very fast access to data. Each chapter in the remainder of the programming section deals with a client-based API. You can connect to a PostgreSQL server using a number of languages. I show you how to interface to PostgreSQL using C, C++, ecpg, ODBC, JDBC, Perl, PHP, Tcl/Tk, Python, and Microsoft's .NET. Chapters 8 through 18 all follow the same pattern: you develop a series of client applications in a given language. The first client application shows you how to establish a connection to the database (and how that connection is represented by the language in question). The next client adds error checking so that you can intercept and react to unusual conditions. The third client in each chapter demonstrates how to process SQL commands from within the client. The final client wraps everything together and shows you how to build an interactive query processor using the language being discussed. Even if you program in only one or two languages, I would encourage you to study the other chapters in this section. I think you'll find that looking at the same application written in a variety of languages will help you understand the philosophy followed by the PostgreSQL development team, and it's a great way to start learning a new language. Chapter 19, "Other Useful Programming Tools," introduces you to a few programming tools (and interfaces) that you might find useful: PL/Java and PL/Perl. I'll also show you how to use PostgreSQL inside of bash shell scripts.
The final part of this book (Part III, "PostgreSQL Administration") deals with administrative issues. The final six chapters of this book show you how to perform the occasional duties required of a PostgreSQL administrator. In the first two chapters, Chapter 20, "Introduction to PostgreSQL Administration," and Chapter 21, "PostgreSQL Administration," you'll learn how to start up, shut down, back up, and restore a server. In Chapter 22, "Internationalization and Localization," you will learn how PostgreSQL supports internationalization and localization. PostgreSQL understands how to store and process a variety of single-byte and multi-byte character sets including Unicode, ASCII, and Japanese, Chinese, Korean, and Taiwan EUC. In Chapter 23, "Security," I'll show you how to secure your data against unauthorized uses (and unauthorized users). In Chapter 24, "Replicating PostgreSQL with Slony," you'll learn how to replicate data with PostgreSQL's Slony replication system. Chapter 25, "Contributed Modules," introduces a few open-source projects that work well with PostgreSQL. I'll show you how to query a PostgreSQL database using XML, how to configure and use TSEARCH2 (a full-text indexing and search system), and how to install and use PgAdmin III, a graphical user interface specifically designed for PostgreSQL.
The first edition of this book hit the shelves in February 2003—at that time, the PostgreSQL developers had just released version 7.3.2. Release 7.4 was unleashed in November 2003. In January 2005, the PostgreSQL developers released version 8.0—a major release full of new features. We timed the second edition of this book to coincide with the release of version 8.0 (the book will appear in bookstores a few months after 8.0 hits the streets). In this edition, we've added coverage for all of the (major) new features in 7.3, 7.4, and 8.0, including
We hope you enjoy this book and find it useful. The PostgreSQL developers have done an incredible job of enhancing what was already a world-class database product. Now dig in.
© Copyright Pearson Education. All rights reserved.
Overview
The second edition of the best-selling PostgreSQL has been updated to completely cover new features and capabilities of the 8.0 version of PostgreSQL. You will be lead through the internals of the powerful PostgreSQL open source database chapter, offering an easy-to-read, code-based approach that makes it easy to understand how each feature is implemented, how to best use each feature, and how to get more performance from database applications. This definitive guide to building, programming and administering the ...