PostgreSQL Programmer's Guide

The PostgreSQL Development Team

Edited by

Thomas Lockhart

Table of Contents

Problem Reporting Guidelines

Identifying Bugs
What to report
Where to report bugs

Y2K Statement

Copyrights and Trademarks

2. Architecture

Postgres Architectural Concepts

3. Extending SQL: An Overview

How Extensibility Works
The Postgres Type System
About the Postgres System Catalogs

4. Extending SQL: Functions

Query Language (SQL) Functions

Examples
SQL Functions on Base Types
SQL Functions on Composite Types

Procedural Language Functions

Internal Functions

Compiled (C) Language Functions

C Language Functions on Base Types
C Language Functions on Composite Types
Writing Code

Function Overloading

Name Space Conflicts

5. Extending SQL: Types

User-Defined Types

Functions Needed for a User-Defined Type
Large Objects

6. Extending SQL: Operators

Operator Optimization Information

COMMUTATOR
NEGATOR
RESTRICT
JOIN
HASHES
SORT1 and SORT2

7. Extending SQL: Aggregates

8. The Postgres Rule System

What is a Querytree?

The Parts of a Querytree

Views and the Rule System

Implementation of Views in Postgres
How SELECT Rules Work
View Rules in Non-SELECT Statements
The Power of Views in Postgres
Implementation Side Effects

Rules on INSERT, UPDATE and DELETE

Differences to View Rules
How These Rules Work
Cooperation with Views

Rules and Permissions

Rules versus Triggers

9. Interfacing Extensions To Indices

10. Index Cost Estimation Functions

11. GiST Indices

12. Procedural Languages

Installing Procedural Languages

13. Linking Dynamically-Loaded Functions

Linux
DEC OSF/1
SunOS 4.x, Solaris 2.x and HP-UX

14. Triggers

Trigger Creation
Interaction with the Trigger Manager
Visibility of Data Changes
Examples

15. Server Programming Interface

Interface Functions

SPI_connect
SPI_finish
SPI_exec
SPI_prepare
SPI_saveplan
SPI_execp

Interface Support Functions

SPI_copytuple
SPI_modifytuple
SPI_fnumber
SPI_fname
SPI_getvalue
SPI_getbinval
SPI_gettype
SPI_gettypeid
SPI_getrelname
SPI_palloc
SPI_repalloc
SPI_pfree

Memory Management

Visibility of Data Changes

Examples

16. Large Objects

Historical Note

Implementation Features

Interfaces

Creating a Large Object
Importing a Large Object
Exporting a Large Object
Opening an Existing Large Object
Writing Data to a Large Object
Reading Data from a Large Object
Seeking on a Large Object
Closing a Large Object Descriptor

Built in registered functions

Accessing Large Objects from LIBPQ

Sample Program

17. libpq - C Library

Database Connection Functions

Query Execution Functions

Asynchronous Query Processing

Fast Path

Asynchronous Notification

Functions Associated with the COPY Command

libpq Tracing Functions

libpq Control Functions

Environment Variables

Threading Behavior

Sample Programs

Sample Program 1
Sample Program 2
Sample Program 3

18. libpq - C++ Binding Library

Control and Initialization

Environment Variables

libpq++ Classes

Connection Class: PgConnection
Database Class: PgDatabase

Database Connection Functions

Query Execution Functions

Asynchronous Notification

Functions Associated with the COPY Command

19. pgtcl - TCL Binding Library

Commands

Examples

pgtcl Command Reference Information

pg_connect
pg_disconnect
pg_conndefaults
pg_exec
pg_result
pg_select
pg_listen
pg_lo_creat
pg_lo_open
pg_lo_close
pg_lo_read
pg_lo_write
pg_lo_lseek
pg_lo_tell
pg_lo_unlink
pg_lo_import
pg_lo_export

20. libpgeasy - Simplified C Binding Library

21. ecpg - Embedded SQL in C

Why Embedded SQL?

The Concept

How To Use ecpg

Preprocessor
Library
Error handling

Limitations

Porting From Other RDBMS Packages

Installation

For the Developer

ToDo List
The Preprocessor
A Complete Example
The Library

22. ODBC Interface

Background

Windows Applications

Writing Applications

Unix Installation

Building the Driver

Configuration Files

ApplixWare

Configuration
Common Problems
Debugging ApplixWare ODBC Connections
Running the ApplixWare Demo
Useful Macros
Supported Platforms

23. JDBC Interface

Building the JDBC Interface

Compiling the Driver
Installing the Driver

Preparing the Database for JDBC

Using the Driver

Importing JDBC

Loading the Driver

Connecting to the Database

Issuing a Query and Processing the Result

Using the Statement Interface
Using the ResultSet Interface

Performing Updates

Closing the Connection

Using Large Objects

Postgres Extensions to the JDBC API

Further Reading

24. Lisp Programming Interface

25. Postgres Source Code

Formatting

26. Overview of PostgreSQL Internals

The Path of a Query

How Connections are Established

The Parser Stage

Parser
Transformation Process

The Postgres Rule System

The Rewrite System

Planner/Optimizer

Generating Possible Plans
Data Structure of the Plan

Executor

27. pg_options

28. Genetic Query Optimization in Database Systems

Query Handling as a Complex Optimization Problem

Genetic Algorithms (GA)

Genetic Query Optimization (GEQO) in Postgres

Future Implementation Tasks for Postgres GEQO

Basic Improvements
References

29. Frontend/Backend Protocol

Overview

Protocol

Startup
Query
Function Call
Notification Responses
Cancelling Requests in Progress
Termination

Message Data Types

Message Formats

30. Postgres Signals

31. gcc Default Optimizations

32. Backend Interface

BKI File Format
General Commands
Macro Commands
Debugging Commands
Example

33. Page Files

Page Structure
Files
Bugs

DG1. The CVS Repository

CVS Tree Organization

Getting The Source Via Anonymous CVS

Getting The Source Via CVSup

Preparing A CVSup Client System
Running a CVSup Client
Installing CVSup
Installation from Sources

DG2. Documentation

Documentation Roadmap

The Documentation Project

Documentation Sources

Document Structure
Styles and Conventions
SGML Authoring Tools

Building Documentation

Manpages

Hardcopy Generation for v7.0

Text Hardcopy
Postscript Hardcopy

Toolsets

Linux RPM Installation
FreeBSD Installation
Debian Installation
Manual Installation of Tools

Alternate Toolsets

Bibliography

		Next
		Summary