PostgreSQL Programmer's Guide - BITS-Dubai, III Year CSE Notes

frightenedfroggeryΔιαχείριση Δεδομένων

16 Δεκ 2012 (πριν από 4 χρόνια και 8 μήνες)

733 εμφανίσεις

PostgreSQL Programmer’s Guide

The PostgreSQL Development Team

Edited by
Thomas Lockhart
PostgreSQL Programmer’s Guide
by The PostgreSQL Development Team
Edited by Thomas Lockhart
PostgreSQL
is Copyright © 1996-2000 by PostgreSQL Inc.
i
Table of Contents
Table of Contents. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . i
List of Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix
List of Figures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x
List of Examples. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . i
Chapter 1. Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Terminology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Notation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Problem Reporting Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Identifying Bugs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
What to report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Where to report bugs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Y2K Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Copyrights and Trademarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Chapter 2. Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Postgres Architectural Concepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Chapter 3. Extending SQL: An Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
How Extensibility Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
The Postgres Type System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
About the Postgres System Catalogs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Chapter 4. Extending SQL: Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Query Language (SQL) Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Examples. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
SQL Functions on Base Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
SQL Functions on Composite Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Procedural Language Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Internal Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Compiled (C) Language Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
C Language Functions on Base Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
C Language Functions on Composite Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Writing Code. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Function Overloading. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Name Space Conflicts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Chapter 5. Extending SQL: Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
User-Defined Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Functions Needed for a User-Defined Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Large Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Chapter 6. Extending SQL: Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Operator Optimization Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
COMMUTATOR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
NEGATOR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
RESTRICT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
JOIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
HASHES. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
SORT1 and SORT2. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Chapter 7. Extending SQL: Aggregates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Chapter 8. The Postgres Rule System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
What is a Querytree?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
The Parts of a Querytree . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Views and the Rule System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Implementation of Views in Postgres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
ii
How SELECT Rules Work. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
View Rules in Non-SELECT Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
The Power of Views in Postgres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Implementation Side Effects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
Rules on INSERT, UPDATE and DELETE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Differences to View Rules. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
How These Rules Work. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Cooperation with Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
Rules and Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Rules versus Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Chapter 9. Interfacing Extensions To Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
Chapter 10. Index Cost Estimation Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Chapter 11. GiST Indices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Chapter 12. Procedural Languages. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Installing Procedural Languages. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Chapter 13. Linking Dynamically-Loaded Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Linux. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
DEC OSF/1. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
SunOS 4.x, Solaris 2.x and HP-UX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Chapter 14. Triggers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Trigger Creation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Interaction with the Trigger Manager. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Visibility of Data Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
Examples. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
Chapter 15. Server Programming Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Interface Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
SPI_connect . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
Algorithm . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
SPI_finish . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Algorithm . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
SPI_exec. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Algorithm . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
SPI_prepare. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
SPI_saveplan. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
SPI_execp. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
iii
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
Interface Support Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
SPI_copytuple. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
SPI_modifytuple. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
SPI_fnumber. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
SPI_fname. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
Algorithm . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
SPI_getvalue. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
Algorithm . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
SPI_getbinval . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Algorithm . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
SPI_gettype. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Algorithm . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
SPI_gettypeid . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Algorithm . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
SPI_getrelname. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
iv
Algorithm . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
SPI_palloc. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
SPI_repalloc . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
SPI_pfree . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
Memory Management. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
Visibility of Data Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Examples. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Chapter 16. Large Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
Historical Note . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
Implementation Features. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
Interfaces. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
Creating a Large Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
Importing a Large Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
Exporting a Large Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
Opening an Existing Large Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
Writing Data to a Large Object. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Reading Data from a Large Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Seeking on a Large Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Closing a Large Object Descriptor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Built in registered functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Accessing Large Objects from LIBPQ. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Sample Program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Chapter 17. libpq - C Library. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
Database Connection Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
Query Execution Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Asynchronous Query Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
Fast Path . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
Asynchronous Notification. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
Functions Associated with the COPY Command. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
libpq Tracing Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
libpq Control Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
Environment Variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Threading Behavior . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Sample Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
Sample Program 1. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
Sample Program 2. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
Sample Program 3. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
Chapter 18. libpq - C++ Binding Library. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
Control and Initialization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
Environment Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
libpq++ Classes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
Connection Class: PgConnection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
Database Class: PgDatabase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
v
Database Connection Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
Query Execution Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Asynchronous Notification. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
Functions Associated with the COPY Command. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
Chapter 19. pgtcl - TCL Binding Library. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
Examples. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
pgtcl Command Reference Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
pg_connect . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
pg_disconnect. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
pg_conndefaults . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
pg_exec. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
pg_result. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
pg_select. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
pg_listen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
pg_lo_creat . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
pg_lo_open . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
pg_lo_close. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
pg_lo_read . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
vi
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
pg_lo_write. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
pg_lo_lseek. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
pg_lo_tell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
pg_lo_unlink. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
pg_lo_import. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
pg_lo_export. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
Name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
Synopsis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
Chapter 20. libpgeasy - Simplified C Binding Library. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
Chapter 21. ecpg - Embedded SQL in C . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
Why Embedded SQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
The Concept . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
How To Use ecpg. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
Preprocessor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
Library. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
Error handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
Porting From Other RDBMS Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
For the Developer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
ToDo List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188
The Preprocessor. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188
A Complete Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
The Library . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Chapter 22. ODBC Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Background. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Windows Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Writing Applications. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Unix Installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
vii
Building the Driver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
Configuration Files. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
ApplixWare. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
Configuration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
Common Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202
Debugging ApplixWare ODBC Connections . . . . . . . . . . . . . . . . . . . . . . . . . . 203
Running the ApplixWare Demo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204
Useful Macros. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204
Supported Platforms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
Chapter 23. JDBC Interface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
Building the JDBC Interface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
Compiling the Driver. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
Installing the Driver. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
Preparing the Database for JDBC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
Using the Driver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
Importing JDBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
Loading the Driver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
Connecting to the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
Issuing a Query and Processing the Result. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
Using the Statement Interface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
Using the ResultSet Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
Performing Updates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
Closing the Connection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
Using Large Objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
Postgres Extensions to the JDBC API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
Further Reading . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
Chapter 24. Lisp Programming Interface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
Chapter 25. Postgres Source Code. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253
Formatting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253
Chapter 26. Overview of PostgreSQL Internals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
The Path of a Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
How Connections are Established. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
The Parser Stage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
Parser. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
Transformation Process. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257
The Postgres Rule System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257
The Rewrite System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258
Planner/Optimizer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259
Generating Possible Plans. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259
Data Structure of the Plan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259
Executor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
Chapter 27. pg_options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262
Chapter 28. Genetic Query Optimization in Database Systems. . . . . . . . . . . . . . . . . . . . . . . . 265
Query Handling as a Complex Optimization Problem . . . . . . . . . . . . . . . . . . . . . . . . . 265
Genetic Algorithms (GA). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265
Genetic Query Optimization (GEQO) in Postgres . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266
Future Implementation Tasks for Postgres GEQO. . . . . . . . . . . . . . . . . . . . . . . . . . . . 267
Basic Improvements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267
References. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 268
Chapter 29. Frontend/Backend Protocol . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
Protocol. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
Startup. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270
Query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271
Function Call. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273
viii
Notification Responses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273
Cancelling Requests in Progress . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
Termination. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
Message Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
Message Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275
Chapter 30. Postgres Signals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284
Chapter 31. gcc Default Optimizations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286
Chapter 32. Backend Interface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
BKI File Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
General Commands. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
Macro Commands. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288
Debugging Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289
Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289
Chapter 33. Page Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290
Page Structure. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290
Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291
Bugs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291
Appendix DG1. The CVS Repository . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292
CVS Tree Organization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292
Getting The Source Via Anonymous CVS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
Getting The Source Via CVSup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
Preparing A CVSup Client System. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
Running a CVSup Client. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
Installing CVSup. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297
Installation from Sources. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
Appendix DG2. Documentation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
Documentation Roadmap . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
The Documentation Project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
Documentation Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
Document Structure. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302
Styles and Conventions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302
SGML Authoring Tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302
Building Documentation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304
Manpages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305
Hardcopy Generation for v7.0 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305
Text Hardcopy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305
Postscript Hardcopy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306
Toolsets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308
Linux RPM Installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308
FreeBSD Installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308
Debian Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309
Manual Installation of Tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310
Alternate Toolsets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
Bibliography. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316
SQL Reference Books. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316
PostgreSQL-Specific Documentation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316
Proceedings and Articles. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
ix
List of Tables
3-1. Postgres System Catalogs...................................................................................................12
4-1. Equivalent C Types for Built-In Postgres Types.................................................................20
9-1. Index Schema......................................................................................................................67
9-2. B-tree Strategies..................................................................................................................68
19-1. pgtcl Commands...........................................................................................................163
30-1. Postgres Signals..............................................................................................................284
33-1. Sample Page Layout........................................................................................................290
DG2-1. Postgres Documentation Products...............................................................................300
x
List of Figures
2-1. How a connection is established...........................................................................................9
3-1. The major Postgres system catalogs....................................................................................13
xi
List of Examples
26-1. A Simple Select...............................................................................................................256
i
Summary
Postgres, developed originally in the UC Berkeley Computer Science Department, pioneered
many of the object-relational concepts now becoming available in some commercial databases.
It provides SQL92/SQL3 language support, transaction integrity, and type extensibility.
PostgreSQL is an open-source descendant of this original Berkeley code.
1
Chapter 1. Introduction
This document is the programmer’s manual for the PostgreSQL (http://postgresql.org/)
database management system, originally developed at the University of California at Berkeley.
PostgreSQL is based on Postgres release 4.2
(http://s2k-ftp.CS.Berkeley.EDU:8000/postgres/postgres.html). The Postgres project, led by
Professor Michael Stonebraker, has been sponsored by the Defense Advanced Research
Projects Agency (DARPA), the Army Research Office (ARO), the National Science
Foundation (NSF), and ESL, Inc.
The first part of this manual explains the Postgres approach to extensibility and describe how
users can extend Postgres by adding user-defined types, operators, aggregates, and both query
language and programming language functions. After a discussion of the Postgres rule system,
we discuss the trigger and SPI interfaces. The manual concludes with a detailed description of
the programming interfaces and support libraries for various languages.
We assume proficiency with Unix and C programming.
Resources
This manual set is organized into several parts:
Tutorial
An introduction for new users. Does not cover advanced features.
User’s Guide
General information for users, including available commands and data types.
Programmer’s Guide
Advanced information for application programmers. Topics include type and function
extensibility, library interfaces, and application design issues.
Administrator’s Guide
Installation and management information. List of supported machines.
Developer’s Guide
Information for Postgres developers. This is intended for those who are contributing to the
Postgres project; application development information should appear in the Programmer’s
Guide. Currently included in the Programmer’s Guide.
Reference Manual
Detailed reference information on command syntax. Currently included in the User’s
Guide.
Chapter 1. Introduction
2
In addition to this manual set, there are other resources to help you with Postgres installation
and use:
man pages
The man pages have general information on command syntax.
FAQs
The Frequently Asked Questions (FAQ) documents address both general issues and some
platform-specific issues.
READMEs
README files are available for some contributed packages.
Web Site
The Postgres (postgresql.org) web site might have some information not appearing in the
distribution. There is a mhonarc catalog of mailing list traffic which is a rich resource for
many topics.
Mailing Lists
The pgsql-general (mailto:pgsql-general@postgresql.org) (archive
(http://www.PostgreSQL.ORG/mhonarc/pgsql-general/)) mailing list is a good place to
have user questions answered. Other mailing lists are available; consult the Info Central
section of the PostgreSQL web site for details.
Yourself!
Postgres is an open source product. As such, it depends on the user community for
ongoing support. As you begin to use Postgres, you will rely on others for help, either
through the documentation or through the mailing lists. Consider contributing your
knowledge back. If you learn something which is not in the documentation, write it up and
contribute it. If you add features to the code, contribute it.
Even those without a lot of experience can provide corrections and minor changes in the
documentation, and that is a good way to start. The pgsql-docs
(mailto:pgsql-docs@postgresql.org) (archive
(http://www.PostgreSQL.ORG/mhonarc/pgsql-docs/)) mailing list is the place to get
going.
Terminology
In the following documentation, site may be interpreted as the host machine on which Postgres
is installed. Since it is possible to install more than one set of Postgres databases on a single
host, this term more precisely denotes any particular set of installed Postgres binaries and
databases.
The Postgres superuser is the user named postgres who owns the Postgres binaries and
database files. As the database superuser, all protection mechanisms may be bypassed and any
data accessed arbitrarily. In addition, the Postgres superuser is allowed to execute some support
Chapter 1. Introduction
3
programs which are generally not available to all users. Note that the Postgres superuser is not
the same as the Unix superuser (which will be referred to as root). The superuser should have a
non-zero user identifier (UID) for security reasons.
The database administrator or DBA, is the person who is responsible for installing Postgres
with mechanisms to enforce a security policy for a site. The DBA can add new users by the
method described below and maintain a set of template databases for use by createdb.
The postmaster is the process that acts as a clearing-house for requests to the Postgres system.
Frontend applications connect to the postmaster, which keeps tracks of any system errors and
communication between the backend processes. The postmaster can take several command-line
arguments to tune its behavior. However, supplying arguments is necessary only if you intend
to run multiple sites or a non-default site.
The Postgres backend (the actual executable program postgres) may be executed directly from
the user shell by the Postgres super-user (with the database name as an argument). However,
doing this bypasses the shared buffer pool and lock table associated with a postmaster/site,
therefore this is not recommended in a multiuser site.
Notation
?...? or /usr/local/pgsql/ at the front of a file name is used to represent the path to the
Postgres superuser’s home directory.
In a command synopsis, brackets (?[? and ?]?) indicate an optional phrase or keyword. Anything in
braces (?{? and ?}?) and containing vertical bars (?|?) indicates that you must choose one.
In examples, parentheses (?(? and ?)?) are used to group boolean expressions. ?|? is the boolean
operator OR.
Examples will show commands executed from various accounts and programs. Commands
executed from the root account will be preceeded with ?>?. Commands executed from the
Postgres superuser account will be preceeded with ?%?, while commands executed from an
unprivileged user’s account will be preceeded with ?$?. SQL commands will be preceeded with
?=>? or will have no leading prompt, depending on the context.
Note: At the time of writing (Postgres v7.0) the notation for flagging commands is not
universally consistant throughout the documentation set. Please report problems to the
Documentation Mailing List (mailto:docs@postgresql.org).
Problem Reporting Guidelines
When you encounter a problem in PostgreSQL we want to hear about it. Your bug reports are
an important part in making PostgreSQL more reliable because even the utmost care cannot
guarantee that every part of PostgreSQL will work on every platform under every
circumstance.
The following suggestions are intended to assist you in forming bug reports that can be
handled in an effective fashion. No one is required to follow them but it tends to be to
everyone’s advantage.
Chapter 1. Introduction
4
We cannot promise to fix every bug right away. If the bug is obvious, critical, or affects a lot
of users, chances are good that someone will look into it. It could also happen that we tell you
to update to a newer version to see if the bug happens there. Or we might decide that the bug
cannot be fixed before some major rewrite we might be planning is done. Or perhaps it’s
simply too hard and there are more important things on the agenda. If you need help
immediately, consider obtaining a commercial support contract.
Identifying Bugs
Before you ask "?Is this a bug??", please read and re-read the documentation to verify that you
can really do whatever it is you are trying. If it is not clear from the documentation whether you
can do something or not, please report that too; it’s a bug in the documentation. If it turns out
that the program does something different from what the documentation says, that’s a bug. That
might include, but is not limited to, the following circumstances:
A program terminates with a fatal signal or an operating system error message that would
point to a problem in the program (a counterexample might be a ?disk full? message, since that
must be fixed outside of Postgres).
A program produces the wrong output for any given input.
A program refuses to accept valid input.
A program accepts invalid input without a notice or error message.
PostgreSQL fails to compile, build, or install according to the instructions on supported
platforms.
Here ?program? refers to any executable, not only the backend server.
Being slow or resource-hogging is not necessarily a bug. Read the documentation or ask on
one of the mailing lists for help in tuning your applications. Failing to comply to SQL is not a
bug unless compliance for the specific feature is explicitly claimed.
Before you continue, check on the TODO list and in the FAQ to see if your bug is already
known. If you can’t decode the information on the TODO list, report your problem. The least
we can do is make the TODO list clearer.
What to report
The most important thing to remember about bug reporting is to state all the facts and only
facts. Do not speculate what you think went wrong, what ?it seemed to do?, or which part of the
program has a fault. If you are not familiar with the implementation you would probably guess
wrong and not help us a bit. And even if you are, educated explanations are a great supplement
to but no substitute for facts. If we are going to fix the bug we still have to see it happen for
ourselves first. Reporting the bare facts is relatively straightforward (you can probably copy
and paste them from the screen) but all too often important details are left out because someone
thought it doesn’t matter or the report would ?ring a bell? anyway.
The following items should be contained in every bug report:
The exact sequence of steps from program startup necessary to reproduce the problem. This
should be self-contained; it is not enough to send in a bare select statement without the
preceeding create table and insert statements, if the output should depend on the data in the
Chapter 1. Introduction
5
tables. We do not have the time to decode your database schema, and if we are supposed to
make up our own data we would probably miss the problem. The best format for a test case
for query-language related problems is a file that can be run through the psql frontend that
shows the problem. (Be sure to not have anything in your ~/.psqlrc startup file.) You are
encouraged to minimize the size of your example, but this is not absolutely necessary. If the
bug is reproduceable, we’ll find it either way.
If your application uses some other client interface, such as PHP, then please try to isolate
the offending queries. We probably won’t set up a web server to reproduce your problem. In
any case remember to provide the exact input files, do not guess that the problem happens
for ?large files? or ?mid-size databases?, etc.
The output you got. Please do not say that it ?didn’t work? or ?failed?. If there is an error
message, show it, even if you don’t understand it. If the program terminates with an
operating system error, say which. If nothing at all happens, say so. Even if the result of your
test case is a program crash or otherwise obvious it might not happen on our platform. The
easiest thing is to copy the output from the terminal, if possible.
Note: In case of fatal errors, the error message provided by the client might not contain
all the information available. In that case, also look at the output of the database server.
If you do not keep your server output, this would be a good time to start doing so.
The output you expected is very important to state. If you just write ?This command gives me
that output.? or ?This is not what I expected.?, we might run it ourselves, scan the output, and
think it looks okay and is exactly what we expected. We shouldn’t have to spend the time to
decode the exact semantics behind your commands. Especially refrain from merely saying
that ?This is not what SQL says/Oracle does.? Digging out the correct behavior from SQL is
not a fun undertaking, nor do we all know how all the other relational databases out there
behave. (If your problem is a program crash you can obviously omit this item.)
Any command line options and other startup options, including concerned environment
variables or configuration files that you changed from the default. Again, be exact. If you are
using a pre-packaged distribution that starts the database server at boot time, you should try
to find out how that is done.
Anything you did at all differently from the installation instructions.
The PostgreSQL version. You can run the command SELECT version(); to find out what
version you are currently running. If this function does not exist, say so, then we know that
your version is old enough. If you can’t start up the server or a client, look into the
README file in the source directory or at the name of your distribution file or package
name. If your version is older than 7.0 we will almost certainly tell you to upgrade. There are
tons of bug fixes in each new version, that’s why we write them.
If you run a pre-packaged version, such as RPMs, say so, including any subversion the
package may have. If you are talking about a CVS snapshot, mention that, including its date
and time.
Platform information. This includes the kernel name and version, C library, processor,
memory information. In most cases it is sufficient to report the vendor and version, but do
not assume everyone knows what exactly ?Debian? contains or that everyone runs on
Chapter 1. Introduction
6
Pentiums. If you have installation problems information about compilers, make, etc. is also
necessary.
Do not be afraid if your bug report becomes rather lengthy. That is a fact of life. It’s better to
report everything the first time than us having to squeeze the facts out of you. On the other
hand, if your input files are huge, it is fair to ask first whether somebody is interested in looking
into it.
Do not spend all your time to figure out which changes in the input make the problem go
away. This will probably not help solving it. If it turns out that the bug can’t be fixed right
away, you will still have time to find and share your work around. Also, once again, do not
waste your time guessing why the bug exists. We’ll find that out soon enough.
When writing a bug report, please choose non-confusing terminology. The software package as
such is called ?PostgreSQL?, sometimes ?Postgres? for short. (Sometimes the abbreviation ?Pgsql? is
used but don’t do that.) When you are specifically talking about the backend server, mention
that, don’t just say ?Postgres crashes?. The interactive frontend is called ?psql? and is for all intends
and purposes completely separate from the backend.
Where to report bugs
In general, send bug reports to pgsql-bugs@postgresql.org
(mailto:pgsql-bugs@postgresql.org). You are invited to find a descriptive subject for your
email message, perhaps parts of the error message.
Do not send bug reports to any of the user mailing lists, such as pgsql-sql@postgresql.org
(mailto:pgsql-sql@postgresql.org) or pgsql-general@postgresql.org
(mailto:pgsql-general@postgresql.org). These mailing lists are for answering user questions
and their subscribers normally do not wish to receive bug reports. More importantly, they are
unlikely to fix them.
Also, please do not send reports to pgsql-hackers@postgresql.org
(mailto:pgsql-hackers@postgresql.org). This list is for discussing the development of
PostgreSQL and it would be nice if we could keep the bug reports separate. We might choose
to take up a discussion about your bug report on it, if the bug needs more review.
If you have a problem with the documentation, send email to pgsql-docs@postgresql.org
(mailto:pgsql-docs@postgresql.org). Mention the document, chapter, and sections in your
problem report.
If your bug is a portability problem on a non-supported platform, send mail to
pgsql-ports@postgresql.org (mailto:pgsql-ports@postgresql.org), so we (and you) can work on
porting PostgreSQL to your platform.
Note: Due to the unfortunate amount of spam going around, all of the above email
addresses are closed mailing lists. That is, you need to be subscribed to them in order to
be allowed to post. If you simply want to send mail but do not want to receive list traffic,
you can subscribe to the special pgsql-loophole ?list?, which allows you to post to all
PostgreSQL mailing lists without receiving any messages. Send email to
pgsql-loophole-request@postgresql.org (mailto:pgsql-loophole-request@postgresql.org) to
subscribe.
Chapter 1. Introduction
7
Y2K Statement
Author: Written by Thomas Lockhart (mailto:lockhart@alumni.caltech.edu) on 1998-10-22.
Updated 2000-03-31.
The PostgreSQL Global Development Team provides the Postgres software code tree as a
public service, without warranty and without liability for it’s behavior or performance.
However, at the time of writing:
The author of this statement, a volunteer on the Postgres support team since November,
1996, is not aware of any problems in the Postgres code base related to time transitions
around Jan 1, 2000 (Y2K).
The author of this statement is not aware of any reports of Y2K problems uncovered in
regression testing or in other field use of recent or current versions of Postgres. We might
have expected to hear about problems if they existed, given the installed base and the active
participation of users on the support mailing lists.
To the best of the author’s knowledge, the assumptions Postgres makes about dates
specified with a two-digit year are documented in the current User’s Guide
(http://www.postgresql.org/docs/user/datatype.htm) in the chapter on data types. For
two-digit years, the significant transition year is 1970, not 2000; e.g. ?70-01-01? is interpreted
as ?1970-01-01?, whereas ?69-01-01? is interpreted as ?2069-01-01?.
Any Y2K problems in the underlying OS related to obtaining "the current time" may
propagate into apparent Y2K problems in Postgres.
Refer to The Gnu Project (http://www.gnu.org/software/year2000.html) and The Perl Institute
(http://language.perl.com/news/y2k.html) for further discussion of Y2K issues, particularly as it
relates to open source, no fee software.
Copyrights and Trademarks
PostgreSQL is Copyright © 1996-2000 by PostgreSQL Inc. and is distributed under the terms
of the Berkeley license.
Postgres95 is Copyright © 1994-5 by the Regents of the University of California. Permission
to use, copy, modify, and distribute this software and its documentation for any purpose,
without fee, and without a written agreement is hereby granted, provided that the above
copyright notice and this paragraph and the following two paragraphs appear in all copies.
In no event shall the University of California be liable to any party for direct, indirect, special,
incidental, or consequential damages, including lost profits, arising out of the use of this
software and its documentation, even if the University of California has been advised of the
possibility of such damage.
The University of California specifically disclaims any warranties, including, but not limited
to, the implied warranties of merchantability and fitness for a particular purpose. The software
provided hereunder is on an "as-is" basis, and the University of California has no obligations to
provide maintainance, support, updates, enhancements, or modifications.
All trademarks are the property of their respective owners.
8
Chapter 2. Architecture
Postgres Architectural Concepts
Before we continue, you should understand the basic Postgres system architecture.
Understanding how the parts of Postgres interact will make the next chapter somewhat clearer.
In database jargon, Postgres uses a simple "process per-user" client/server model. A Postgres
session consists of the following cooperating Unix processes (programs):
A supervisory daemon process (postmaster),
the user’s frontend application (e.g., the psql program), and
the one or more backend database servers (the postgres process itself).
A single postmaster manages a given collection of databases on a single host. Such a collection
of databases is called an installation or site. Frontend applications that wish to access a given
database within an installation make calls to the library. The library sends user requests over
the network to the postmaster (How a connection is established(a)), which in turn starts a new
backend server process (How a connection is established(b))
Chapter 2. Architecture
9
Figure 2-1. How a connection is established
(a) frontend sends request to postmaster
via well-known network socket
POSTMASTER
SERVER
server host
client host
User
App
LIBPQ
POSTMASTER
SERVER
server host
client host
User
App
LIBPQ
POSTMASTER
SERVER
server host
client host
User
App
LIBPQ
POSTMASTER
server host
client host
User
App
LIBPQ
(b) postmaster creates backend server
(c) frontend connected to backend server
POSTMASTER
SERVER
server host
(d) frontend connected
to multiple backend servers
And multiple connections
can be established...
and connects the frontend process to the new server (How a connection is established(c)).
From that point on, the frontend process and the backend server communicate without
intervention by the postmaster. Hence, the postmaster is always running, waiting for requests,
whereas frontend and backend processes come and go. The libpq library allows a single
frontend to make multiple connections to backend processes. However, the frontend application
is still a single-threaded process. Multithreaded frontend/backend connections are not currently
supported in libpq. One implication of this architecture is that the postmaster and the backend
always run on the same machine (the database server), while the frontend application may run
Chapter 2. Architecture
10
anywhere. You should keep this in mind, because the files that can be accessed on a client
machine may not be accessible (or may only be accessed using a different filename) on the
database server machine. You should also be aware that the postmaster and postgres servers run
with the user-id of the Postgres "superuser." Note that the Postgres superuser does not have to
be a special user (e.g., a user named "postgres"), although many systems are installed that way.
Furthermore, the Postgres superuser should definitely not be the Unix superuser, "root"! In any
case, all files relating to a database should belong to this Postgres superuser.
11
Chapter 3. Extending SQL: An Overview
In the sections that follow, we will discuss how you can extend the Postgres SQL query
language by adding:
functions
types
operators
aggregates

How Extensibility Works
Postgres is extensible because its operation is catalog-driven. If you are familiar with standard
relational systems, you know that they store information about databases, tables, columns, etc.,
in what are commonly known as system catalogs. (Some systems call this the data dictionary).
The catalogs appear to the user as classes, like any other, but the DBMS stores its internal
bookkeeping in them. One key difference between Postgres and standard relational systems is
that Postgres stores much more information in its catalogs -- not only information about tables
and columns, but also information about its types, functions, access methods, and so on. These
classes can be modified by the user, and since Postgres bases its internal operation on these
classes, this means that Postgres can be extended by users. By comparison, conventional
database systems can only be extended by changing hardcoded procedures within the DBMS or
by loading modules specially-written by the DBMS vendor.
Postgres is also unlike most other data managers in that the server can incorporate user-written
code into itself through dynamic loading. That is, the user can specify an object code file (e.g.,
a compiled .o file or shared library) that implements a new type or function and Postgres will
load it as required. Code written in SQL are even more trivial to add to the server. This ability
to modify its operation "on the fly" makes Postgres uniquely suited for rapid prototyping of
new applications and storage structures.
The Postgres Type System
The Postgres type system can be broken down in several ways. Types are divided into base
types and composite types. Base types are those, like int4, that are implemented in a language
such as C. They generally correspond to what are often known as "abstract data types";
Postgres can only operate on such types through methods provided by the user and only
understands the behavior of such types to the extent that the user describes them. Composite
types are created whenever the user creates a class. EMP is an example of a composite type.
Postgres stores these types in only one way (within the file that stores all instances of the class)
but the user can "look inside" at the attributes of these types from the query language and
optimize their retrieval by (for example) defining indices on the attributes. Postgres base types
are further divided into built-in types and user-defined types. Built-in types (like int4) are those
Chapter 3. Extending SQL: An Overview
12
that are compiled into the system. User-defined types are those created by the user in the
manner to be described below.
About the Postgres System Catalogs
Having introduced the basic extensibility concepts, we can now take a look at how the catalogs
are actually laid out. You can skip this section for now, but some later sections will be
incomprehensible without the information given here, so mark this page for later reference. All
system catalogs have names that begin with pg_. The following classes contain information
that may be useful to the end user. (There are many other system catalogs, but there should
rarely be a reason to query them directly.)
Table 3-1. Postgres System Catalogs
Catalog Name
Description
pg_database
databases
pg_class
classes
pg_attribute
class attributes
pg_index
secondary indices
pg_proc
procedures (both C and SQL)
pg_type
types (both base and complex)
pg_operator
operators
pg_aggregate
aggregates and aggregate functions
pg_am
access methods
pg_amop
access method operators
pg_amproc
access method support functions
pg_opclass
access method operator classes


Chapter 3. Extending SQL: An Overview
13
Figure 3-1. The major Postgres system catalogs
[8]
[8]
REFERS-TO
non-key
1
0:N
identified by the non-oid primary key in other contexts).
1
13:N
1
0:N
1
0:N
111
0:N
0:N
11
0:N
1
0:N
0:N
1
0:N
1
0:N
1
0:N
1
0:1
optional
mandatory
0:1
0:N
0:N
1
1
0:N
0:N
1
1
1
0:N
0:N
0:N
0:N
1
1
0:N
KEY:
atttypid
typrelid
typinput
typoutput
typreceive
typsend
indexrelid
amopstrategy
prolang
amproc
oprcom
oprnegate
oprlsortop
oprrsortop
oprcode
oprrest
oprjoin
amgettuple
aminsert
amdelete
amgetattr
ambeginscan
amrescan
amendscan
ammarkpos
amrestrpos
ambuild
DEPENDENT
INDEPENDENT
pg_attribute
pg_class
pg_index
pg_type
pg_am
pg_proc
pg_language
pg_amop
pg_opclass
pg_amproc
pg_operator
attrelid
attnum
relam
oid
indrelid
indkey
indproc
indpred
oid
oid
oid
oid
amopid
amopclaid
amopopr
oid
amid
amopclaid
amprocnum
oid
primary key
foreign key
non-oid primary
key (if any)
oprname
oprleft
oprright
oprresult
proname
prorettype
proargtypes
indicates these key values are alternate primary keys
(i.e., this class is generally identified by oid but may be
amcreate
amdestroy
amcostestimate
The Reference Manual gives a more detailed explanation of these catalogs and their attributes.
However, The major Postgres system catalogs shows the major entities and their relationships
in the system catalogs. (Attributes that do not refer to other entities are not shown unless they
are part of a primary key.) This diagram is more or less incomprehensible until you actually
Chapter 3. Extending SQL: An Overview
14
start looking at the contents of the catalogs and see how they relate to each other. For now, the
main things to take away from this diagram are as follows:
In several of the sections that follow, we will present various join queries on the system
catalogs that display information we need to extend the system. Looking at this diagram
should make some of these join queries (which are often three- or four-way joins) more
understandable, because you will be able to see that the attributes used in the queries form
foreign keys in other classes.
Many different features (classes, attributes, functions, types, access methods, etc.) are
tightly integrated in this schema. A simple create command may modify many of these
catalogs.
Types and procedures are central to the schema.
Note: We use the words procedure and function more or less interchangably.
Nearly every catalog contains some reference to instances in one or both of these classes.
For example, Postgres frequently uses type signatures (e.g., of functions and operators) to
identify unique instances of other catalogs.
There are many attributes and relationships that have obvious meanings, but there are many
(particularly those that have to do with access methods) that do not. The relationships
between pg_am, pg_amop, pg_amproc, pg_operator and pg_opclass are particularly hard to
understand and will be described in depth (in the section on interfacing types and operators
to indices) after we have discussed basic extensions.

15
Chapter 4. Extending SQL: Functions
As it turns out, part of defining a new type is the definition of functions that describe its
behavior. Consequently, while it is possible to define a new function without defining a new
type, the reverse is not true. We therefore describe how to add new functions to Postgres before
describing how to add new types.
Postgres SQL provides three types of functions:
query language functions (functions written in SQL)
procedural language functions (functions written in, for example, PLTCL or PLSQL)
programming language functions (functions written in a compiled programming language
such as C)
Every kind of function can take a base type, a composite type or some combination as
arguments (parameters). In addition, every kind of function can return a base type or a
composite type. It’s easiest to define SQL functions, so we’ll start with those. Examples in this
section can also be found in funcs.sql and funcs.c.
Query Language (SQL) Functions
SQL functions execute an arbitrary list of SQL queries, returning the results of the last query
in the list. SQL functions in general return sets. If their returntype is not specified as a setof,
then an arbitrary element of the last query’s result will be returned.
The body of a SQL function following AS should be a list of queries separated by whitespace
characters and bracketed within quotation marks. Note that quotation marks used in the queries
must be escaped, by preceding them with two backslashes.
Arguments to the SQL function may be referenced in the queries using a $n syntax: $1 refers
to the first argument, $2 to the second, and so on. If an argument is complex, then a dot
notation (e.g. "$1.emp") may be used to access attributes of the argument or to invoke
functions.
Examples
To illustrate a simple SQL function, consider the following, which might be used to debit a
bank account:
create function TP1 (int4, float8) returns int4
as ’update BANK set balance = BANK.balance - $2
where BANK.acctountno = $1
select(x = 1)’
language ’sql’;

A user could execute this function to debit account 17 by $100.00 as follows:
select (x = TP1( 17,100.0));
Chapter 4. Extending SQL: Functions
16


The following more interesting example takes a single argument of type EMP, and retrieves
multiple results:
select function hobbies (EMP) returns set of HOBBIES
as ’select (HOBBIES.all) from HOBBIES
where $1.name = HOBBIES.person’
language ’sql’;


SQL Functions on Base Types
The simplest possible SQL function has no arguments and simply returns a base type, such as
int4:
CREATE FUNCTION one() RETURNS int4
AS ’SELECT 1 as RESULT’ LANGUAGE ’sql’;
SELECT one() AS answer;
+-------+
|answer |
+-------+
|1 |
+-------+


Notice that we defined a target list for the function (with the name RESULT), but the target
list of the query that invoked the function overrode the function’s target list. Hence, the result is
labelled answer instead of one.
It’s almost as easy to define SQL functions that take base types as arguments. In the example
below, notice how we refer to the arguments within the function as $1 and $2:
CREATE FUNCTION add_em(int4, int4) RETURNS int4
AS ’SELECT $1 + $2;’ LANGUAGE ’sql’;
SELECT add_em(1, 2) AS answer;
+-------+
|answer |
+-------+
|3 |
+-------+


Chapter 4. Extending SQL: Functions
17
SQL Functions on Composite Types
When specifying functions with arguments of composite types (such as EMP), we must not
only specify which argument we want (as we did above with $1 and $2) but also the attributes
of that argument. For example, take the function double_salary that computes what your salary
would be if it were doubled:
CREATE FUNCTION double_salary(EMP) RETURNS int4
AS ’SELECT $1.salary * 2 AS salary;’ LANGUAGE ’sql’;
SELECT name, double_salary(EMP) AS dream
FROM EMP
WHERE EMP.cubicle ~= ’(2,1)’::point;

+-----+-------+
|name | dream |
+-----+-------+
|Sam | 2400 |
+-----+-------+


Notice the use of the syntax $1.salary. Before launching into the subject of functions that
return composite types, we must first introduce the function notation for projecting attributes.
The simple way to explain this is that we can usually use the notation attribute(class) and
class.attribute interchangably:
--
-- this is the same as:
-- SELECT EMP.name AS youngster FROM EMP WHERE EMP.age < 30
--
SELECT name(EMP) AS youngster
FROM EMP
WHERE age(EMP) < 30;
+----------+
|youngster |
+----------+
|Sam |
+----------+


As we shall see, however, this is not always the case. This function notation is important when
we want to use a function that returns a single instance. We do this by assembling the entire
Chapter 4. Extending SQL: Functions
18
instance within the function, attribute by attribute. This is an example of a function that returns
a single EMP instance:
CREATE FUNCTION new_emp() RETURNS EMP