PostgreSQL

9.1 overview

logo

Wath is it?

O RDBMS
- Object / type
- Inheritance
Open Source (PostgreSQL licence)
ACID, ANSI SQL compliant
a lot of extensions

Used by

Yahoo!: heavily customize version. +2P b of storage
MySpace (Aster nCluster)
Sony online, Skype, Heroku...

History

1986: POSTGRES University of California Berkeley (Professor Michael Stonebraker)
1993: POSTGRES 4.2
1994: POSTGRES95 = SQL + Open Source + C + 30/50% faster + psql + libpgtcl + GNU make (GCC)
(Andrew Yu and Jolly Chen)
1996: PostgreSQL (v6.0)
2011/09: PostgreSQL 9.1

Installation

$ apt-get install postgresql
$ /etc/init.d/postgresql start
$ psql
> CREATE DATABASE my_first_db;
> \c my_first_db

CLI

$ psql

> help

> \h \?

> \e \ef \s \i \o \!

> \d \c \a \q

Features

Types
Schema
Inheritance
Transaction
Role
Functions
Trigger
Rule system
Replication
Foreign Data Wrappers

Types

all the classics, plus
Floating point: NaN, -/+ Infinity
Serial
Money = decimal + local
DateTime input output format + period + interval
Geometric, network addresses
XML
UUID (RFC 4122, ISO/IEC 9834-8:2005)
Array: integer[], text[][]

↩

Full text search types

tsvector

> SELECT 'Hello world'::tsvector;
tsvector
-----------------
'Hello' 'world'
(1 row)

> select to_tsvector('french', 'J''aime les Princes aux CHOCOLAT');
to_tsvector
---------------------------------------
'aim':2 'chocolat':6 'le':3 'princ':4
(1 row)

↩

Full text search types

tsquery

> select 'prince & (chocolat|!vanille)'::tsquery;
tsquery
----------------------------------------
'prince' & ( 'chocolat' | !'vanille' )
(1 row)

> select to_tsvector('french', 'J''aime les Princes aux CHOCOLAT')
@@ to_tsquery('prince & (chocolat | ! vanille)');
?column?
----------
t
(1 row)

↩

Composite types

> CREATE TYPE complex AS (
r double precision,
i double precision
);

> CREATE TABLE storage (
label text,
value complex
);

↩

Composite types...

> SELECT value.r FROM storage
WHERE value.i IS NOT NULL;

> INSERT INTO storage (label, value)
VALUES ('My label', (1,2));

Schema

Cluster > Database > Schema > table

$ postgres -D /usr/local/pgsql/data

$ psql my_db

$ psql
> \c my_db

↩

Schema

> CREATE TABLE my_table (...);
> SELECT * FROM my_table;
> CREATE TABLE my_schema.my_table (...);
> SELECT * FROM my_schema.my_table;
> SELECT * FROM public.my_table;

> SHOW search_path;
search_path
----------------
"$user",public

Inheritance

> CREATE TABLE city (...);
> CREATE TABLE capital (...) INHERITS (city);

> INSERT ...
> SELECT name FROM city;
name
-------
'Marseille'
'Paris'
'Lyon'

↩

Inheritance...

> SELECT name FROM ONLY city;
name
-------
'Marseille'
'Lyon'

Transaction

> BEGIN;
> INSERT INTO ...;
> UPDATE ...;
> COMMIT;

> ROLLBACK;

SET TRANSACTION transaction_mode;

↩

Nested transactions

> BEGIN;
> INSERT ...;
> SAVEPOINT save_point_1;
> INSERT ...;
> UPDATE ...;
ERROR: 01007 PRIVILEGE NOT GRANTED
> ROLLBACK TO save_point_1;
> COMMIT;

Role

CREATE ROLE name [LOGIN [REPLICATION], SUPERUSER, CREATEDB, CREATEROLE, PASSWORD 'pa$$word'];

CREATE ROLE xavier LOGIN INHERIT;
CREATE ROLE editor NOINHERIT;
CREATE ROLE admin NOINHERIT;
GRANT editor TO xavier;
GRANT admin TO editor;
SET ROLE admin;
RESET ROLE;

Functions

Internal functions
Query language functions
Procedural functions
(C functions)

↩

Internal Functions

Math: ceil, floor, round, trunc, ...
String: substring, convert, encode, regex_replace, ...
Pattern matching: LIKE, SIMILAR TO, ~ (POSIX regex)
Type formating: to_date, to_timestamp, ...
Geometric: center, area, isclosed, ...
XML: xmlelement, xmlattributes, IS DOCUMENT, xpath, table_to_xml, query_to_xml, ...
Conditional; CASE, GREATEST, COALESCE, ...

↩

Query Language (SQL) Functions

> CREATE FUNCTION debit (integer, numeric) RETURNS integer AS $$
  UPDATE account
    SET amount = amount - $2
    WHERE uid = $1;
  SELECT 1;
$$ LANGUAGE SQL;

> SELECT debit(467392, '100.99');

↩

Procedural Languages

> CREATE FUNCTION refresh_mviews() RETURNS integer AS $$
DECLARE
  mviews RECORD;
BEGIN
  RAISE NOTICE 'Refreshing materialized views...';
  FOR mviews IN SELECT * FROM mat_views ORDER BY sort_key LOOP
    RAISE NOTICE 'Refreshing %s ...', quote_ident(mviews.mv_name);
    EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
    EXECUTE 'INSERT INTO '
      || quote_ident(mviews.mv_name) || ' '
      || mviews.mv_query;
  END LOOP;
  RAISE NOTICE 'Done refreshing materialized views.';
  RETURN 1;
EXCEPTION
  WHEN SQLSTATE '56488' THEN
    RETURN 0;
END;
$$ LANGUAGE plpgsql;

Triggers

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
  BEGIN
  IF (TG_OP = 'DELETE') THEN
     INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
     RETURN OLD;
  ELSIF (TG_OP = 'UPDATE') THEN
     INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
     RETURN NEW;
  ELSIF (TG_OP = 'INSERT') THEN
     INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
     RETURN NEW;
  END IF;
  RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();

Rule system

aka query rewrite rule system

The query loop

Lexer/parser
Rewriter
Planner

↩

Select rule

aka view

CREATE VIEW myview AS SELECT * FROM mytab;

CREATE TABLE myview (same column list as mytab);
CREATE RULE "_RETURN" AS ON SELECT TO myview
DO INSTEAD SELECT * FROM mytab;

Views define interfaces.
Define view upon other views.

↩

Insert, update, delete rules

CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

CREATE RULE product_log AS ON UPDATE TO product
  WHERE NEW.is_available <> OLD.is_available
  DO INSERT INTO product_log VALUES (
    NEW.label,
    NEW.is_available,
    current_user, current_timestamp
);

Replication

Warm/Hot Standby (log (WAL) shipping) async per chunk
Streaming replication (per transaction) async
Master/primary server and slaves/standby servers (third)
Multi-master asynchronous
Multi-master synchronous

Foreign data wrappers

Icing on the cake

SQL/MED ("SQL Management of External Data") ISO/IEC 9075-9:2003

Oracle
MySQL
ODBC
CouchDB
Redis
File (CSV)
Twitter, LDAP, Amazon S3

This is the end

Questions?

troll

https://github.com/xavierbriand/training-pgsql.git