PostgreSQL Programmer’s Guide

[Pages:10]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-9 by the Postgres Global Development Group.

Table of Contents

Summary ......................................................................................................................................i 1. Introduction ............................................................................................................................1

Resources ............................................................................................................................1 Terminology........................................................................................................................2 Notation ..............................................................................................................................3 Y2K Statement....................................................................................................................3 Copyrights and Trademarks ................................................................................................4 2. Architecture ............................................................................................................................5 Postgres Architectural Concepts .........................................................................................5 3. Extending SQL: An Overview...............................................................................................7 How Extensibility Works....................................................................................................7 The Postgres Type System..................................................................................................7 About the Postgres System Catalogs ..................................................................................8 4. Extending SQL: Functions ..................................................................................................11 Query Language (SQL) Functions ....................................................................................11

SQL Functions on Base Types ................................................................................11 SQL Functions on Composite Types.......................................................................12 Programming Language Functions ...................................................................................13 Programming Language Functions on Base Types .................................................13 Programming Language Functions on Composite Types........................................15 Caveats ....................................................................................................................16 5. Extending SQL: Types .........................................................................................................18 User-Defined Types ..........................................................................................................18 Functions Needed for a User-Defined Type............................................................18 Large Objects ..........................................................................................................19 6. Extending SQL: Operators..................................................................................................20 Operator Optimization Information ..................................................................................21 COMMUTATOR ....................................................................................................21 NEGATOR..............................................................................................................22 RESTRICT .............................................................................................................. 22 JOIN .......................................................................................................................23 HASHES .................................................................................................................23 SORT1 and SORT2.................................................................................................24 7. Extending SQL: Aggregates ................................................................................................26 8. The Postgres Rule System....................................................................................................28 What is a Querytree?.........................................................................................................28 The Parts of a Querytree..........................................................................................28 Views and the Rule System ..............................................................................................30 Implementation of Views in Postgres......................................................................30 How SELECT Rules Work .....................................................................................30 View Rules in Non-SELECT Statements................................................................35 The Power of Views in Postgres .............................................................................36

i

Benefits ..........................................................................................................36 Concerns ........................................................................................................36 Implementation Side Effects ...................................................................................37 Rules on INSERT, UPDATE and DELETE .....................................................................38 Differences to View Rules ......................................................................................38 How These Rules Work ..........................................................................................38 A First Rule Step by Step ..............................................................................39 Cooperation with Views..........................................................................................42 Rules and Permissions ......................................................................................................48 Rules versus Triggers........................................................................................................49 9. Interfacing Extensions To Indices.......................................................................................52 10. GiST Indices........................................................................................................................59 11. Procedural Languages........................................................................................................61 Installing Procedural Languages.......................................................................................61 PL/pgSQL .........................................................................................................................62 Overview .................................................................................................................62 Description ..............................................................................................................63 Structure of PL/pgSQL ..................................................................................63 Comments ......................................................................................................63 Declarations ...................................................................................................63 Data Types .....................................................................................................64 Expressions ....................................................................................................65 Statements......................................................................................................66 Trigger Procedures.........................................................................................68 Exceptions......................................................................................................69 Examples .................................................................................................................69 Some Simple PL/pgSQL Functions ...............................................................70 PL/pgSQL Function on Composite Type.......................................................70 PL/pgSQL Trigger Procedure ........................................................................70 PL/Tcl ...............................................................................................................................71 Overview .................................................................................................................71 Description ..............................................................................................................71 Postgres Functions and Tcl Procedure Names ...............................................71 Defining Functions in PL/Tcl ........................................................................71 Global Data in PL/Tcl....................................................................................72 Trigger Procedures in PL/Tcl.........................................................................72 Database Access from PL/Tcl........................................................................74 12. Linking Dynamically-Loaded Functions ..........................................................................76 ULTRIX............................................................................................................................77 DEC OSF/1 .......................................................................................................................77 SunOS 4.x, Solaris 2.x and HP-UX ..................................................................................78 13. Triggers ...............................................................................................................................79 Trigger Creation................................................................................................................79 Interaction with the Trigger Manager ...............................................................................80 Visibility of Data Changes................................................................................................81 Examples...........................................................................................................................82 14. Server Programming Interface .........................................................................................85 Interface Functions ...........................................................................................................86

ii

SPI_connect.............................................................................................................86 SPI_finish ................................................................................................................87 SPI_exec..................................................................................................................89 SPI_prepare .............................................................................................................91 SPI_saveplan ...........................................................................................................92 SPI_execp................................................................................................................93 Interface Support Functions ..............................................................................................95 SPI_copytuple .........................................................................................................95 SPI_modifytuple......................................................................................................96 SPI_fnumber............................................................................................................97 SPI_fname ...............................................................................................................98 SPI_getvalue............................................................................................................99 SPI_getbinval ........................................................................................................100 SPI_gettype ...........................................................................................................100 SPI_gettypeid ........................................................................................................102 SPI_getrelname .....................................................................................................103 SPI_palloc .............................................................................................................104 SPI_repalloc ..........................................................................................................105 SPI_pfree ............................................................................................................... 106 Memory Management.....................................................................................................106 Visibility of Data Changes..............................................................................................107 Examples.........................................................................................................................107

15. Large Objects....................................................................................................................110

Historical Note................................................................................................................110 Inversion Large Objects..................................................................................................110 Large Object Interfaces...................................................................................................110

Creating a Large Object ........................................................................................111 Importing a Large Object ......................................................................................111 Exporting a Large Object ......................................................................................111 Opening an Existing Large Object ........................................................................111 Writing Data to a Large Object .............................................................................111 Seeking on a Large Object ....................................................................................112 Closing a Large Object Descriptor ........................................................................112 Built in registered functions............................................................................................112 Accessing Large Objects from LIBPQ ...........................................................................112 Sample Program..............................................................................................................113

16. libpq ...................................................................................................................................117

Database Connection Functions......................................................................................117 Query Execution Functions.............................................................................................120 Asynchronous Query Processing ....................................................................................124 Fast Path..........................................................................................................................126 Asynchronous Notification .............................................................................................126 Functions Associated with the COPY Command ...........................................................127 libpq Tracing Functions ..................................................................................................129 libpq Control Functions ..................................................................................................129 User Authentication Functions........................................................................................129 Environment Variables ...................................................................................................130 Caveats............................................................................................................................131 Sample Programs ............................................................................................................131

Sample Program 1 .................................................................................................131

iii

Sample Program 2 .................................................................................................133 Sample Program 3 .................................................................................................134

17. libpq C++ Binding ............................................................................................................138 Control and Initialization ................................................................................................138 Environment Variables..........................................................................................138 libpq++ Classes...............................................................................................................139 Connection Class: PgConnection ..............................................................139 Database Class: PgDatabase .......................................................................139 Database Connection Functions......................................................................................140 Query Execution Functions.............................................................................................140 Asynchronous Notification .............................................................................................144 Functions Associated with the COPY Command ...........................................................144 Caveats............................................................................................................................145

18. pgtcl ...................................................................................................................................146 Commands ......................................................................................................................146 Examples.........................................................................................................................147 pgtcl Command Reference Information..........................................................................147 pg_connect ............................................................................................................147 pg_disconnect........................................................................................................149 pg_conndefaults.....................................................................................................150 pg_exec .................................................................................................................151 pg_result ................................................................................................................152 pg_select................................................................................................................153 pg_listen ................................................................................................................155 pg_lo_creat ............................................................................................................156 pg_lo_open ............................................................................................................157 pg_lo_close............................................................................................................158 pg_lo_read ............................................................................................................. 158 pg_lo_write............................................................................................................160 pg_lo_lseek............................................................................................................161 pg_lo_tell...............................................................................................................162 pg_lo_unlink..........................................................................................................162 pg_lo_import .........................................................................................................163 pg_lo_export..........................................................................................................164

19. ecpg - Embedded SQL in C .............................................................................................165 Why Embedded SQL? ....................................................................................................165 The Concept....................................................................................................................165 How To Use egpc ...........................................................................................................165 Preprocessor ..........................................................................................................165 Library...................................................................................................................166 Error handling .......................................................................................................166 Limitations ...................................................................................................................... 168 Porting From Other RDBMS Packages ..........................................................................168 Installation ......................................................................................................................169 For the Developer ...........................................................................................................169 ToDo List ..............................................................................................................169 The Preprocessor ...................................................................................................170 A Complete Example ............................................................................................173 The Library............................................................................................................173

iv

20. ODBC Interface ................................................................................................................175

Background ..................................................................................................................... 175 Windows Applications....................................................................................................175

Writing Applications .............................................................................................175 Unix Installation .............................................................................................................176

Building the Driver................................................................................................176 Configuration Files .........................................................................................................179 ApplixWare.....................................................................................................................180

Configuration ........................................................................................................180 Common Problems ................................................................................................181 Debugging ApplixWare ODBC Connections........................................................181 Running the ApplixWare Demo............................................................................182 Useful Macros .......................................................................................................183 Supported Platforms ..............................................................................................183

21. JDBC Interface .................................................................................................................184

Building the JDBC Interface...........................................................................................184 Compiling the Driver.............................................................................................184 Installing the Driver ..............................................................................................184

Preparing the Database for JDBC ...................................................................................184 Using the Driver..............................................................................................................185 Importing JDBC..............................................................................................................185 Loading the Driver..........................................................................................................185 Connecting to the Database ............................................................................................186 Issuing a Query and Processing the Result .....................................................................186

Using the Statement Interface ...............................................................................186 Using the ResultSet Interface ................................................................................187 Performing Updates ........................................................................................................187 Closing the Connection...................................................................................................187 Using Large Objects .......................................................................................................187 Postgres Extensions to the JDBC API ............................................................................188 Further Reading ..............................................................................................................189

22. Overview of PostgreSQL Internals .................................................................................190

The Path of a Query ........................................................................................................190 How Connections are Established...................................................................................191 The Parser Stage .............................................................................................................191

Parser.....................................................................................................................191 Transformation Process.........................................................................................193 The Postgres Rule System ..............................................................................................193 The Rewrite System ..............................................................................................193

Techniques To Implement Views ................................................................194 Planner/Optimizer ........................................................................................................... 195

Generating Possible Plans .....................................................................................195 Data Structure of the Plan .....................................................................................195 Executor ..........................................................................................................................196

23. pg_options .........................................................................................................................197

24. Genetic Query Optimization in Database Systems........................................................200

Query Handling as a Complex Optimization Problem....................................................200 Genetic Algorithms (GA) ...............................................................................................200 Genetic Query Optimization (GEQO) in Postgres..........................................................201

v

Future Implementation Tasks for Postgres GEQO .........................................................202 Basic Improvements ..............................................................................................202 Improve freeing of memory when query is already processed.....................202 Improve genetic algorithm parameter settings.............................................202 Find better solution for integer overflow .....................................................202 Find solution for exhausted memory ...........................................................202 References .............................................................................................................202

25. Frontend/Backend Protocol.............................................................................................203 Overview.........................................................................................................................203 Protocol ........................................................................................................................... 203 Startup ...................................................................................................................204 Query.....................................................................................................................205 Function Call .........................................................................................................206 Notification Responses..........................................................................................207 Cancelling Requests in Progress ...........................................................................207 Termination ...........................................................................................................208 Message Data Types .......................................................................................................208 Message Formats ............................................................................................................209

26. Postgres Signals ................................................................................................................217 27. gcc Default Optimizations................................................................................................219 28. Backend Interface.............................................................................................................220

BKI File Format..............................................................................................................220 General Commands.........................................................................................................220 Macro Commands...........................................................................................................221 Debugging Commands....................................................................................................222 Example ..........................................................................................................................222 29. Page Files...........................................................................................................................223 Page Structure .................................................................................................................223 Files ..............................................................................................................................224 Bugs ..............................................................................................................................224 DG1. The CVS Repository.....................................................................................................225 CVS Tree Organization...................................................................................................225 Getting The Source Via Anonymous CVS .....................................................................226 Getting The Source Via CVSup......................................................................................228

Preparing A CVSup Client System .......................................................................228 Running a CVSup Client.......................................................................................228 Installing CVSup ...................................................................................................230 Installation from Sources.......................................................................................230 DG2. Documentation..............................................................................................................233 Documentation Roadmap................................................................................................233 The Documentation Project ............................................................................................234 Documentation Sources ..................................................................................................234 Document Structure...............................................................................................235 Styles and Conventions .........................................................................................236 SGML Authoring Tools ........................................................................................236

emacs/psgml ................................................................................................236 Building Documentation.................................................................................................237 Hardcopy Generation for v6.5 ........................................................................................237

vi

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download