CREATE TYPE point (x integer, y integer) - Nordic Test Forum

cuttlefishblueData Management

Dec 16, 2012 (4 years and 6 months ago)

112 views

Testforum 2008
PostgreSQL
The world's most advanced
open source database
Presenter: Rolf Østvik
Testforum 2008
Presentation overview

Introduction

Basic information

Extending server functionality

Administration tools

Clustering/Replication/Backup

Support

Selecting database server

Other info
Testforum 2008
Introduction

PostgresSQL is for most applications
comparative to big commercial database
servers

PostgreSQL is the best open source database
server
Testforum 2008
PostgreSQL at AXXE

Used in production logging system

Register produced items

Testreports

Return for service

Repairs, ...

In use from 2000

MySQL and PostgreSQL was evaluated
Testforum 2008
Presentation overview

Introduction

Basic information

Extending server functionality

Administration tools

Clustering/Replication/Backup

Support

Selecting database server

Other info
Testforum 2008
PostgreSQL (1)

The worlds most advanced open source
datababase server

Completely free

BSD licensed

Good developement community

Not one single company

Developement spread worldwide

Developers is active on mailing lists
Testforum 2008
PostgreSQL (2)

Multiplatform

Microsoft Windows, GNU/Linux, Mac OS X, BSD,
UNIX, ...

Commercial support
Testforum 2008
Releases

Latest release is 8.3, september 2008

Major relases x.y -> x.(y+1) (new functionality)

Requires dump and restore

Minor releases x.y.z -> x.y.(z+1) (bugfixes)

Upgrade in place

It's been 15 major releases from 1997

<1 year between each major release

Oldest release supported is 7.4 (7.4.23)

7.4 was released nov 2003
Testforum 2008
Limits

Maximum Database Size
Unlimited

Maximum Table Size
32 TB

Maximum Row Size
1.6 TB

Maximum Field Size
1 GB

Maximum Rows per Table
Unlimited

Maximum Columns per Table
250 - 1600
(depending on column types)

Maximum Indexes per Table
Unlimited
Testforum 2008
Basic functionality (1)

Supports standard SQL

Supports storage of binary large objects

pictures, sounds, or video

Schemas

E.g. <shema>.<table>.<column>

Full text search
Testforum 2008
Basic functionality (2)

Sequences

Generates a unique number. Normally used to
generate a unique row identification.

Multi-Version Concurrency Control (MVCC)

Triggers

Execute code when row is inserted, updated, or
deleted

Subselects

select * from (select id, name from tbl) as a;
Testforum 2008
Indexes (1)

Different indexes storage methods

B-tree, R-tree,hash, or GiST

Index types

compound

CREATE INDEX fname_gname_idx
ON tbl_person (family_name, given_name );

unique

CREATE UNIQUE INDEX email_idx
ON tbl_person (email);
Testforum 2008
Indexes (2)

Index types (cont)

partial

CREATE INDEX date_act_idx
ON testreports (test_date) WHERE result='Failed';

functional

CREATE INDEX testname_idx
ON testreports ( lower(test_name) );
Testforum 2008
Connecting to database

There are interfaces for

ODBC

Java (JDBC)

Perl

Python

Ruby,

C, C++

PHP, Lisp, Scheme, and Qt just to name a few.
Testforum 2008
Presentation overview

Introduction

Basic information

Extending server functionality

Administration tools

Clustering/Replication/Backup

Support

Selecting database server

Other info
Testforum 2008
Extending

Custom defined data types

CREATE TYPE point (x integer, y integer);
(simplified)

Custom functions

SQL

Pl/pqSQL (similar to Oracle's PL/SQL)

C

perl, .....

Triggers
Testforum 2008
Installing function languages

Some languages is simple to install

E.g. command line:
postgres@server # createlang plpgsql template1

Other languages may require some extra steps
Testforum 2008
Example PL/pgSQL
CREATE OR REPLACE FUNCTION cf_min(integer, integer) RETURNS integer
as $$
declare
retval integer
begin
retval := case
when $1 isnull then $2
when $2 isnull then $1
when $1<$2 then $1
else $2
end;
return retval;
end;
$$ LANGUAGE 'plpgsql';
Testforum 2008
Example trigger (1)

Example of trigger funtion
CREATE OR REPLACE FUNCTION cf_stamp_last_date() RETURNS trigger
as $$
begin
new.last_date := current_timestamp;
new.last_user := current_user;
return new;
end
$$ LANGUAGE 'plpgsql';
Testforum 2008
Example trigger (2)

Example of table
CREATE TABLE tbl_test (
id integer,
....
last_user text,
last_date text
);

Connect trigger function to table
CREATE TRIGGER ct_tbl_test_update
BEFORE INSERT OR UPDATE ON tbl_test FOR EACH ROW
EXECUTE PROCEDURE cf_stamp_last_date();
Testforum 2008
Presentation overview

Introduction

Basic information

Extending server functionality

Administration tools

Clustering/Replication/Backup

Support

Selecting database server

Other info
Testforum 2008
Database administration

psql

command line, standard

Web based

Standalone

Commercial and free

Some examples follows
Testforum 2008
pgAdmin III (1)

Multiplatform

C++ (wxWidgets)

Support for Slony-I
Testforum 2008
pgAdmin III (2)
Testforum 2008
phpPgAdmin

Web-based

Javascript, PHP

Similar to phpMyAdmin
Testforum 2008
Presentation overview

Introduction

Basic information

Extending server functionality

Administration tools

Clustering/Replication/Backup

Support

Selecting database server

Other info
Testforum 2008
Clustering/Replication... (1)

It is not built in

Commercial and free solutions exist

Some examples

Database dump

Dump database regularly to file

Warm standby/Log shipping

Simplest to set up

Backup/standby server

Not available for querying
Testforum 2008
Clustering/Replication... (2)

Some examples (cont)

slony-I

Single master only

Slave is for security and paralellizing queries

Powerful
Testforum 2008
Presentation overview

Introduction

Basic information

Extending server functionality

Administration tools

Clustering/Replication/Backup

Support

Selecting database server

Other info
Testforum 2008
Documentation

PostgreSQL manual

~1200 pages

Articles/guides on web

Books
Testforum 2008
Support

Free

Mailing lists

Developers is active

Web resources

Commercial
Testforum 2008
Presentation overview

Introduction

Basic information

Extending server functionality

Administration tools

Clustering/Replication/Backup

Support

Selecting database server

Other info
Testforum 2008
Comparison to other servers (1)

Compared to commercial servers

PostgreSQL will be suitable in most cases

Commercial servers may be needed for very
special needs

Compared to free and open source

Best functionality

Best scalability
Testforum 2008
Comparison to other servers (2)

Oracle

Multiplatform

Commercial license

Microsoft SQL server

Single platform

Commercial license
Testforum 2008
Comparison to other servers (3)

Mysql

«The world's most popular open source database»

Multiplatform

Owned by one company

GPL and commercial license

Some functionality depends on correct selection of
table type

Some serious gotchas exist(ed)
Testforum 2008
Selecting database server

Use whatever database server you are used to and
familiar with.

If you want a free database server, or select a
database server for the first time then select
PostgreSQL.

Even if you only need limited functionality today,
maybe you need more funtionality in another project
next year.

PostgreSQL should be compared to the big
commercial database servers. I think it is unfair to
compare it to MySQL because MySQL is no match ;-)
Testforum 2008
Other notes

No database server conforms completely to the
standard

All database servers have functionality which
may be difficult to port to another server
Testforum 2008
Presentation overview

Introduction

Basic information

Extending server functionality

Administration tools

Clustering/Replication/Backup

Support

Selecting database server

Links
Testforum 2008
Links (1)

PostgreSQL

http://www.postgresql.org/

MySQL Gotchas

http://sql-info.de/mysql/gotchas.html
(NB: Not updated for version 5.0)

PostgreSQL Gotchas

http://sql-info.de/postgresql/postgres-gotchas.html
Testforum 2008
Links (2)

Features added for each version from 7.4 up to
8.4dev

http://www.postgresql.org/about/featurematrix
Testforum 2008
Questions/comments
?