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