CS计算机代考程序代写 algorithm SQL database concurrency python interpreter Java PostgreSQL Overview

PostgreSQL Overview

>>
PostgreSQL Overview

PostgreSQL

PostgreSQL Online

User View of PostgreSQL

PostgreSQL Functionality

PostgreSQL Architecture

COMP9315 21T1 ♢ PostgreSQL Overview ♢ [0/13]

∧ >>
❖ PostgreSQL

PostgreSQL is a full-featured open-source (O)RDBMS.

provides a relational engine with:
efficient implementation of relational operations

transaction processing (concurrent access)

backup/recovery (from application/system failure)

novel query optimisation (based on genetic algorithm)

replication, JSON, extensible indexing, etc. etc.

already supports several non-standard data types

allows users to define their own data types

supports most of the SQL3 standard

COMP9315 21T1 ♢ PostgreSQL Overview ♢ [1/13]

<< ∧ >>
❖ PostgreSQL Online

Web site:
www.postgresql.org

Key developers: Tom Lane, Andres Freund, Bruce Momjian, …

Full list of developers:
postgresql.org/contributors/

Source code:
~cs9315/21T1/postgresql/src.tar.bz2

Documentation:
postgresql.org/docs/12/index.html

COMP9315 21T1 ♢ PostgreSQL Overview ♢ [2/13]

<< ∧ >>
❖ User View of PostgreSQL

Users interact via SQL in a client process, e.g.

$ psql webcms
psql (12.5)
Type “help” for help.
webcms2=# select * from calendar;
id | course | evdate | event
—-+——–+————+—————————
1 | 4 | 2001-08-09 | Project Proposals due
10 | 3 | 2001-08-01 | Tute/Lab Enrolments Close
12 | 3 | 2001-09-07 | Assignment #1 Due (10pm)
…

or

$dbconn = pg_connect(“dbname=webcms”);
$result = pg_query($dbconn,”select * from calendar”);
while ($tuple = pg_fetch_array($result))
{ … $tuple[“event”] … }

COMP9315 21T1 ♢ PostgreSQL Overview ♢ [3/13]

<< ∧ >>
❖ PostgreSQL Functionality

PostgreSQL systems deal with various kinds of entities:

users … who can access the system

groups … groups of users, for role-based privileges

databases … collections of schemas/tables/views/…

namespaces … to uniquely identify objects (schema.table.attr)

tables … collection of tuples (standard relational notion)

views … “virtual” tables (can be made updatable)

functions … operations on values from/in tables

triggers … operations invoked in response to events

operators … functions with infix syntax

aggregates … operations over whole table columns

types … user-defined data types (with own operations)

rules … for query rewriting (used e.g. to implement views)

access methods … efficient access to tuples in tables

COMP9315 21T1 ♢ PostgreSQL Overview ♢ [4/13]

<< ∧ >>
❖ PostgreSQL Functionality (cont)

PostgreSQL’s dialect of SQL is mostly standard
(but with extensions).

attributes containing arrays of atomic values

create table R ( id integer, values integer[] );
insert into R values ( 123, ‘{5,4,3,2,1}’ );

table-valued functions

create function f(integer) returns setof TupleType;

multiple langauges available for functions
PLpgSQL, Python, Perl, Java, R, Tcl, …

function bodies are strings in whatever language

COMP9315 21T1 ♢ PostgreSQL Overview ♢ [5/13]

<< ∧ >>
❖ PostgreSQL Functionality (cont)

Other variations in PostgreSQL’s CREATE TABLE

TEMPORARY tables

PARTITION’d tables

GENERATED attribute values   (derived attributes)

FOREIGN TABLE   (data stored outside PostgreSQL)

table type inheritance

create table R ( a integer, b text);
create table S ( x float, y float);
create table T inherits ( R, S );

COMP9315 21T1 ♢ PostgreSQL Overview ♢ [6/13]

<< ∧ >>
❖ PostgreSQL Functionality (cont)

PostgreSQL stored procedures differ from SQL standard:

only provides functions, not procedures

(but functions can return void, effectively a procedure)

allows function overloading

(same function name, different argument types)

defined at different “lexical level” to SQL

provides own PL/SQL-like language for functions

create function ( Args ) returns ResultType
as $$
… body of function definition …
$$ language FunctionBodyLanguage;

where each Arg has a Name and Type

COMP9315 21T1 ♢ PostgreSQL Overview ♢ [7/13]

<< ∧ >>
❖ PostgreSQL Functionality (cont)

Example:

create or replace function
barsIn(suburb text) returns setof Bars
as $$
declare
r record;
begin
for r in
select * from Bars where location = suburb
loop
return next r;
end loop;
end;
$$ language plpgsql;
used as e.g.
select * from barsIn(‘Randwick’);

COMP9315 21T1 ♢ PostgreSQL Overview ♢ [8/13]

<< ∧ >>
❖ PostgreSQL Functionality (cont)

Uses multi-version concurrency control (MVCC)

multiple “versions” of the database exist together

a transaction sees the version that was valid at its start-time

readers don’t block writers; writers don’t block readers

this significantly reduces the need for locking

Disadvantages of this approach:
extra storage for old versions of tuples   (until vacuum’d)

need to check “visibility” of every tuple fetched

PostgreSQL also provides locking to enforce critical concurrency.

COMP9315 21T1 ♢ PostgreSQL Overview ♢ [9/13]

<< ∧ >>
❖ PostgreSQL Functionality (cont)

PostgreSQL has a well-defined and open extensibility model:

stored procedures are held in database as strings
allows a variety of languages to be used

language interpreters can be integrated into engine

can add new data types, operators, aggregates, indexes
typically requires code written in C, following defined API

for new data types, need to write input/output functions, …

for new indexes, need to implement file structures

COMP9315 21T1 ♢ PostgreSQL Overview ♢ [10/13]

<< ∧ >>
❖ PostgreSQL Architecture

Client/server process architecture:

The listener process is sometimes called postmaster

COMP9315 21T1 ♢ PostgreSQL Overview ♢ [11/13]

<< ∧ >>
❖ PostgreSQL Architecture (cont)

Memory/storage architecture:

COMP9315 21T1 ♢ PostgreSQL Overview ♢ [12/13]

<< ∧ ❖ PostgreSQL Architecture (cont) File-system architecture: COMP9315 21T1 ♢ PostgreSQL Overview ♢ [13/13] Produced: 15 Feb 2021