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

程序代写 CS代考 / Algorithm算法代写代考, concurrency, database, interpreter, Java代写代考, Python代写代考, SQL代写代考

2021/4/28 PostgreSQL Overview
PostgreSQL Overview
PostgreSQL
PostgreSQL Online
User View of PostgreSQL PostgreSQL Functionality PostgreSQL Architecture
>>
COMP9315 21T1 ♢ PostgreSQL Overview ♢ [0/13]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pgsql-overview/slides.html
1/15

2021/4/28 PostgreSQL Overview
∧ >>
❖ PostgreSQL
PostgreSQL is a full-featured open-source (O)RDBMS.
provides a relational engine with:
efcient 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 dene their own data types supports most of the SQL3 standard
COMP9315 21T1 ♢ PostgreSQL Overview ♢ [1/13]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pgsql-overview/slides.html
2/15

2021/4/28 PostgreSQL Overview
❖ 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]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pgsql-overview/slides.html
3/15

2021/4/28 PostgreSQL Overview
❖ 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 | 10 | 12 | …
or
4 | 2001-08-09 | Project Proposals due
3 | 2001-08-01 | Tute/Lab Enrolments Close
3 | 2001-09-07 | Assignment #1 Due (10pm)
$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]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pgsql-overview/slides.html
4/15

2021/4/28 PostgreSQL Overview
<< ∧ >>
❖ 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 inx syntax
aggregates … operations over whole table columns
types … user-dened data types (with own operations)
rules … for query rewriting (used e.g. to implement views) access methods … efcient access to tuples in tables
COMP9315 21T1 ♢ PostgreSQL Overview ♢ [4/13]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pgsql-overview/slides.html
5/15

2021/4/28 PostgreSQL Overview
<< ∧ >>
❖ 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]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pgsql-overview/slides.html
6/15

2021/4/28 PostgreSQL Overview
<< ∧ >>
❖ PostgreSQL Functionality (cont) OthervariationsinPostgreSQL’sCREATE TABLE
TEMPORARY tables
PARTITION’d tables GENERATEDattributevalues (derivedattributes) FOREIGN TABLE (datastoredoutsidePostgreSQL) 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]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pgsql-overview/slides.html
7/15

2021/4/28 PostgreSQL Overview
<< ∧ >>
❖ 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)
dened 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]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pgsql-overview/slides.html
8/15

2021/4/28 PostgreSQL Overview
❖ 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]
<< ∧ >>
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pgsql-overview/slides.html
9/15

2021/4/28 PostgreSQL Overview
<< ∧ >>
❖ 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 signicantly reduces the need for locking
Disadvantages of this approach: extrastorageforoldversionsoftuples (untilvacuum’d) need to check “visibility” of every tuple fetched
PostgreSQL also provides locking to enforce critical concurrency.
COMP9315 21T1 ♢ PostgreSQL Overview ♢ [9/13]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pgsql-overview/slides.html
10/15

2021/4/28 PostgreSQL Overview
<< ∧ >>
❖ PostgreSQL Functionality (cont)
PostgreSQL has a well-dened 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 dened API
for new data types, need to write input/output functions, …
for new indexes, need to implement le structures
COMP9315 21T1 ♢ PostgreSQL Overview ♢ [10/13]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pgsql-overview/slides.html
11/15

2021/4/28 PostgreSQL Overview
❖ PostgreSQL Architecture Client/server process architecture:
<< ∧ >>
The listener process is sometimes called postmaster COMP9315 21T1 ♢ PostgreSQL Overview ♢ [11/13]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pgsql-overview/slides.html
12/15

2021/4/28 PostgreSQL Overview
❖ PostgreSQL Architecture (cont) Memory/storage architecture:
<< ∧ >>
COMP9315 21T1 ♢ PostgreSQL Overview ♢ [12/13]
cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pgsql-overview/slides.html
13/15

2021/4/28 PostgreSQL Overview
❖ PostgreSQL Architecture (cont) File-system architecture:
<< ∧ COMP9315 21T1 ♢ PostgreSQL Overview ♢ [13/13] cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pgsql-overview/slides.html 14/15 2021/4/28 PostgreSQL Overview Produced: 15 Feb 2021 cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pgsql-overview/slides.html 15/15

Related Posts