Introduction

to the

PostgreSQL

Database

PostgreSQL

The foremost open-source SQL database

Best ANSI standards compliance

Most of SQL92

Parts of SQL99

Completely free

BSD licence

No charge for commercial use

Excellent support available

PostgreSQL History

UCB research project 1986-1993 – POSTGRES

1995 - Postgres95 added SQL query language

added front-ends and interface libraries

1996 – Renamed PostgreSQL

emphasis on adding SQL features, improving optimisation and reliability

1996-2004 – continual improvements in speed, features and scalability

PostgreSQL Platforms

Runs on most UNIX

Linux

BSD variants

IBM

Sun

HP

etc.

Windows

with Cygwin

Native Windows expected in 7.5 (2004)

MacOS X

What is a database?

An organised and persistent store of information

Indexing for fast retrieval

Structured data

Handles simultaneous updates and queries

Provides for higher level abstraction of the data

Database properties

ACID

Atomicity

Consistency

Isolation

Durability

This is the essential requirement for any reliable database

Database - Atomicity

Database modification must be all or nothing.

If any part of a transaction fails, none of it must be committed to the database.

Atomicity must be preserved even if the database is subject to interruption by power or hardware failure.

Database - Consistency

Only valid data must be written to the database.

If a transaction violates consistency rules, it must be rolled back.

A valid transaction takes the database from one valid state to another valid state.

Database - Isolation

Simultaneous transactions must not affect each other's execution, even if they update the same data.

Transaction A should be updated entirely before transaction B or vice versa.

Database - Durability

Data committed to the database must not be lost.

Backups and transaction logs provide for recovery in the event of damage.

PostgreSQL and ACID

PostgreSQL provides all the ACID properties

Transactions are atomic

Consistency is provided by constraints

Isolation is either

Read Committed or

Serializable

Durability through backups, replication and PITR (PITR in 7.5)

What is SQL?

SQL = Structured Query Language

Relational databases allow users to make connections between tables of data on demand.

Powerful tool for defining and extracting data

SQL Features

Joins

Multiple tables can be joined in a single query

Unions

Multiple queries can be united into a single one

Views

Queries can be treated as if they were tables

Subselects

The results of one query can be used in another

PostgreSQL supports all of these

SQL - Joins

Use the same field in two tables to join those tables:

SELECT i.invno, i.code, c.name

FROM invoice AS i,

customer AS c

WHERE i.code = c.code;

You can join multiple tables in this way.

SQL - Unions

Unions make one query out of several:

SELECT id, name FROM customer

UNION

SELECT id, name FROM supplier;

SQL - Views

Views treat preset queries as if they were tables:

CREATE VIEW product_qty AS

SELECT product,

SUM(qty) AS qty

FROM stock_allocation

GROUP BY product;

Access privileges on views may be different from

those of the underlying tables.

SQL - Subqueries

Subqueries use the result of one query in another:

SELECT product

FROM product_qty

WHERE qty = (

SELECT MAX(qty)

FROM product_qty

);

Schemas

Schemas provide separate sets of objects within a database

The PUBLIC schema is the default, for backwards compatibility.

Use schemas to provide separate areas for users, to separate business functions, and so on.

You can access objects in one schema from another: SELECT * FROM myschema.mytable;

PostgreSQL datatypes

Strings

CHAR(n)

VARCHAR(n)

TEXT

BYTEA

Numbers

Integers (3 sizes)

SERIAL

Floating point

Real

Double precision

DECIMAL

(fixed precision)

PostgreSQL datatypes

Date and time

Date

Time

Timestamp

Interval

Bit

Geometric

Box

Circle

Line

Line segment

Path

Point

Polygon

PostgreSQL datatypes

Net addresses

INET

CIDR

MAC

Arrays of all datatypes

User-defined types

Contributed types

Encrypted password

ISBN and ISSN

Tree-organised data

Measurements

Searchable indexed text

Functions

Standard mathematical functions

String functions

length, substrings, trim, upper and lower case, padding , string replacement, etc.

encoding conversions

Numbers and dates

string formatting with to_char()

Use EXTRACT() to obtain date elements

Geometric functions

Network address functions

Access and privileges

Not system users

Database users

ordinary

administrator (like root in Unix)

Groups

Privileges can be assigned to both users and groups

Authentication

socket ownership

ident

password

Kerberos

PAM

Authentication by user/database/access combination

Privileges

Privileges on

Databases & Schemas

Tables

Functions

Languages

Administer with SQL commands

GRANT

REVOKE

Privileges are

SELECT, USAGE, REFERENCES, EXECUTE

INSERT, UPDATE, DELETE

RULE, TRIGGER

CREATE, TEMPORARY

ALL PRIVILEGES

Indexing

Indexes enhance search performance

Single- and multi-column indexes

Index types

B-tree (default type) – can enforce uniqueness

R-tree (for spatial data)

GiST

Hash

Partial indexes index a subset of the data

Concurrency control

MultiVersion Concurrency Control (MVCC)

gives row-level locking

an uncommitted and a committed version of a row may co-exist

Row-level locks only block writes

Read Committed or Serializable isolation levels

Deadlock detection

Ability to lock tables at user's option

Performance

Examine a query plan with EXPLAIN

Examine actual performance with EXPLAIN ANALYZE

VACUUM tool recovers space and gathers statistics for the query planner

VACUUM can be run by hand or as a continuous background process

Extensibility

PostgreSQL can be extended to handle new types of data

CREATE TYPE

CREATE OPERATOR

CREATE OPERATOR CLASS

CREATE LANGUAGE

CREATE AGGREGATE

CREATE CAST

Rules

Rules rewrite a query to do something different

SELECT rules implement views

INSERT, UPDATE and DELETE rules can

update a view

maintain a log

prevent changes (with a DO NOTHING rule)

CREATE RULE requires RULE privilege

Triggers

Triggers execute a procedure BEFORE or AFTER an INSERT, UPDATE or DELETE on a table

More flexible than rules

Views need rules, not triggers

Fired once for each row or statement

Run a function written in a procedural language

Constraints

Constraints enable business rules to be built into the database. Use them to ensure no invalid data can be entered.

Foreign keys

Check constraints

Not null constraints

Uniqueness

No application program can bypass constraints.

Programming

Client interfaces

libpq library

pgtcl – Tcl binding

ECPG – Embedded SQL in C

JDBC

ODBC

others can be written to call libpq

Server Programming Interface (SPI)

use to run SQL commands from within functions written in C

Use procedural languages to write functions

Procedural languages

Write functions in a procedural language

PL/PGSQL

PL/Perl

PL/Tcl

PL/Python

C

PL/R (for statistical analysis)

Languages can be Trusted or Untrusted

Business Rules

PostgreSQL constraints allow business rules to be enforced by the database by means of

domains

foreign keys

check constraints

uniqueness constraints

not null constraints

The database is the proper level for doing this.

Frontend clients

psql - PostgreSQL's native text-based frontend

command stack editing

allows direct SQL commands

pgadmin3 – GUI client for Windows or X

pgaccess – Tk/Tcl client

phppgadmin

webmin

etc.

Database structure

Cluster

Database

Database

Schema

Schema

postmaster

backend

backend

Each cluster is managed by a postmaster, which forks a backend

for each connection. These programs communicate through shared

memory.

Separate clusters are managed by postmasters listening on

different ports.

Support

Community support

Mailing lists at http://www.postgresql.org

Mailing list gatewayed to Usenet

Commercial Support

UK: LFIX Ltd http://www.lfix.co.uk

Worldwide: see http://techdocs.postgresql.org

2004-04-07T13:06:00