[SOLVED] CS algorithm SQL database concurrency python interpreter Java >>

>>
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)

PostgreSQLs 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 PostgreSQLs CREATE TABLE
TEMPORARY tables
PARTITIONd 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 dont block writers; writers dont block readers
this significantly reduces the need for locking
Disadvantages of this approach:
extra storage for old versions of tuples (until vacuumd)
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