23-07-2012, 10:57 AM
PostgreSQL
postgresql.ppt (Size: 237 KB / Downloads: 36)
What is PostgreSQL?
Object-relational database management system (ORDBMS)
Based on POSTGRES at Berkeley Computer Science Department.
Sponsored by the Defense Advanced Research Projects Agency (DARPA), the Army Research Office (ARO), the National Science Foundation (NSF), and ESL, Inc.
Implementation of POSTGRES began in 1986.
It used a query language based on QUEL, called POST-QUEL
PostgreSQL : Development History
Ingres 1977-1985 – The Beginning
● Proof of concept for relational databases.
● Michael Stonebraker, professor at Berkeley, California.
● Ingres >NonStop SQL, Sybase >Microsoft SQL server.
Postgres 19861994– As in "after Ingres“
● A project meant to break new ground in database concepts.
● “Objects relational” technologies.
● Commercialized to become Illustra.
……..Contd.
Postgres95 1994 – 1995 – New life in the OpenSource world
● Two Ph.D. students from Stonebraker's lab, Andrew Yu and Jolly Chen
started Postgres95.
● Departed from academia to a new life in the open source world with a
group of dedicated developers outside of Berkeley.
● Establishment of the PostgreSQL Global Development Team.
● Released as PostgreSQL 6.0 in 1996.
PostgreSQL 1996today – PostgreSQL project
Development Features
Stored procedures, PL/pgSQL, PL/Perl, PL/Python, PL/Tcl, PL/php,PL/java, PL/R, PL/Ruby, PL/sh, ..
Native interfaces for ODBC, JDBC, C, C++, PHP, Perl, TCL, ECPG,Python, Ruby, Lisp, Scheme, Qt, .Net, OpenOffice SDBC, ...
User defined data types, functions and operators, SPI
Open and documented API.
New Features
Synchronous replication
Foreign Data Wrappers
Collation per column
Serializable Snapshot Isolation
Unlogged tables
Writable Common table Expressions
KNN for GiST and GIN
SE-LINUX support
Upgrade Python version server side
PGXN
More stuff
Foreign Data Wrapper
Specifies a function that defines how the data will get to PostgreSQL
Optionally, also specifies
– a validator function which will sanity check server, user mapping and FDW options
– Generic options to be used by the handler
– Could use same handler function in more than
one FDW with different options
SSI
Serializable Snapshot Isolation
Postgres supports now, 3 transactional levels:
READ COMMITTED
REPEATABLE READ (old SERIALIZABLE)
Called SNAPSHOT ISOLATION
SERIALIZABLE
No more “select for update”
Simplify your code
It has cost, use predictive locking and could increase the number of rollbacks due conflicts between transactions.
PostgreSQL MySQL
Aimed to be a fully-featured database
Understands a good subset of SQL92/99 dialects
Rules, triggers, server-side functions can be written in C, PgSQL, Python, Perl and TCL
Aimed first to be a fast database
Uses SQL92 as its foundation
Has simple mechanism for server-side libraries with C function and rudimentary support for triggers
PostgreSQL MySQL
Supports subqueries, stored procedures, subqueries, cursors or views
Supports referential integrity, has transactions and rollbacks, foreign keys ON DELETE CASCADE and ON UPDATE CASCADE
Does not support subqueries, stored procedures, subqueries, cursors or views
Has basic provisions for referential integrity and transactions/rollbacks
PostgreSQL MySQL
Doesn't have binary distribution for all the support platforms
Slower on low-end but has some options for improving
Works better on Windows
Fast on both simple and complex SELECTs
Difference through Example
A Query will be written in RDBMS as follows:
SELECT InitCap(C.Surname) | ', ' | InitCap(C.FirstName), A.city FROM Customers C JOIN Addresses A ON A.Cust_Id=C.Id -- the join
WHERE A.city="New York"
But the same query will be written in PostgreSql as follow:
SELECT Formal( C.Name )
FROM Customers C
WHERE C.address.city = “New York”
“Output will be same in both cases”
Roles and Databases
#!/bin/bash
for student in $@
do
echo "CREATE ROLE $student WITH LOGIN;" | psql
echo "CREATE DATABASE $student with OWNER $student;" | psql
done
Creation Alternatives
createdb
psql --command //can't mix of psql and sql
psql --file
psql < filename