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

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