public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jürgen Purtz <[email protected]>
To: [email protected]
Subject: Docbook 5.x
Date: Wed, 20 Apr 2016 16:30:27 +0200
Message-ID: <[email protected]> (raw)
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-docs>
Hi,
actually we use DocBook V4.2 for the PostgreSQL manuals. I suggest an
upgrade to DocBook 5.x. This sounds simple, but it will be a long
process with many sub-tasks.
Rationale:
* Sooner or later we MUST migrate as the 4.x series is outdated: V4.2
dates back to 2002. The 4.x series is no longer actively developed
since 2006. See: http://www.docbook.org/tdg5/en/html/ch01.html "In
October 2006, the DocBook Technical Committee released DocBook V4.5,
the last release planned in the 4.x series."
* V5.0 is available since 2009. See:
http://www.docbook.org/tdg5/en/html/ch01.html: "DocBook V5.0 became
an official Committee Specification in June 2009 and became an
official OASIS Standard in October 2009."
* Actually the technical committee has the third Candidate Release for
V5.1.
PROs:
* The formal part of the migration is supported by existing tools:
http://docbook.org/docs/howto/#convert4to5 (nevertheless some
scripts written by ourself will be necessary).
* The normative schema for Docbook 5.x is written in RELAX NG.
Additionally the technical committee converts this normative schema
to a XSD schema and to DTD, which are not normative but very near to
RELAX NG and will fit for most applications. Hence, we have the
choice between three schema syntaxes and everybody can use his
favourite one.
* Our source file format will switch from SGML to XML. This implies
that we have access to all XML features like XLink, XPath, XSLT,
XSL-FO, SVG, MathML, namespaces, ... .
CONs:
* The migration from 4.x to 5.x implies major changes at 3 different
levels.
o DocBook structure: Previously it was defined in SGML syntax
(DTD). Now it is defined in RELAX NG schema language plus
Schematron rules.
o DocBook files: Previously we used SGML syntax for our files. We
must convert them to a valid XML syntax, eg: tag omission.
o Tools and style sheets: All tools which operate at the native
SGML-level (editors, conversions, ...) must be replaced by XML
conforming tools. As valid XML implicitly conforms to a valid
SGML syntax this step may be accomplished by reconfiguring some
of the tools, eg.: .emacs.
What I have done so far is:
* Conversion of sgml files to valid xml syntax with a perl skript. I
failed to use 'osx' or 'spam'.
* Conversion of these xml files to Docbook5.x format using xsltproc
and Docbooks xslt-migration skripts.
* Creation of html files using xsltproc and Docbooks xslt skripts.
* Creation of fo files using xsltproc and Docbooks xslt skripts.
* Creation of pdf files using fop.
* The conversions needs less than 10 minutes on a Intel i5 processor.
This is a very first raw round-trip with one output file per sgml file
and output type. Not supported: entities (__gt__ as a surrogate),
<[CDATA and similar SGML constructs, PostgreSQL specific style sheets,
Makefile, additional errors occur, .... . I append one file of every
new format for the chapter "Advanced Features": xml (the new source),
html, fo, pdf.
Any ideas or suggestions? Shall we go further on this way? Has anybody
more experiences in SGML-->XML conversions or Docbook 4.x --> 5.x
conversions?
Kind regards
Jürgen Purtz
--
Sent via pgsql-docs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
Attachments:
[text/xml] advanced.xml (25.6K, 3-advanced.xml)
download | inline:
<!-- Converted by db4-upgrade version 1.1 -->
<chapter xmlns="http://docbook.org/ns/docbook" version="5.0" xml:id="tutorial-advanced">
<title>Advanced Features</title>
<section xml:id="tutorial-advanced-intro">
<title>Introduction</title>
<para>
In the previous chapter we have covered the basics of using
<acronym>SQL</acronym> to store and access your data in
<productname>PostgreSQL</productname>. We will now discuss some
more advanced features of <acronym>SQL</acronym> that simplify
management and prevent loss or corruption of your data. Finally,
we will look at some <productname>PostgreSQL</productname>
extensions.
</para>
<para>
This chapter will on occasion refer to examples found in <xref linkend="tutorial-sql"/> to change or improve them, so it will be
useful to have read that chapter. Some examples from
this chapter can also be found in
<filename>advanced.sql</filename> in the tutorial directory. This
file also contains some sample data to load, which is not
repeated here. (Refer to <xref linkend="tutorial-sql-intro"/> for
how to use the file.)
</para>
</section>
<section xml:id="tutorial-views">
<title>Views</title>
<indexterm zone="tutorial-views">
<primary>view</primary>
</indexterm>
<para>
Refer back to the queries in <xref linkend="tutorial-join"/>.
Suppose the combined listing of weather records and city location
is of particular interest to your application, but you do not want
to type the query each time you need it. You can create a
<firstterm>view</firstterm> over the query, which gives a name to
the query that you can refer to like an ordinary table:
<programlisting>
CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
SELECT * FROM myview;
</programlisting>
</para>
<para>
Making liberal use of views is a key aspect of good SQL database
design. Views allow you to encapsulate the details of the
structure of your tables, which might change as your application
evolves, behind consistent interfaces.
</para>
<para>
Views can be used in almost any place a real table can be used.
Building views upon other views is not uncommon.
</para>
</section>
<section xml:id="tutorial-fk">
<title>Foreign Keys</title>
<indexterm zone="tutorial-fk">
<primary>foreign key</primary>
</indexterm>
<indexterm zone="tutorial-fk">
<primary>referential integrity</primary>
</indexterm>
<para>
Recall the <classname>weather</classname> and
<classname>cities</classname> tables from <xref linkend="tutorial-sql"/>. Consider the following problem: You
want to make sure that no one can insert rows in the
<classname>weather</classname> table that do not have a matching
entry in the <classname>cities</classname> table. This is called
maintaining the <firstterm>referential integrity</firstterm> of
your data. In simplistic database systems this would be
implemented (if at all) by first looking at the
<classname>cities</classname> table to check if a matching record
exists, and then inserting or rejecting the new
<classname>weather</classname> records. This approach has a
number of problems and is very inconvenient, so
<productname>PostgreSQL</productname> can do this for you.
</para>
<para>
The new declaration of the tables would look like this:
<programlisting>
CREATE TABLE cities (
city varchar(80) primary key,
location point
);
CREATE TABLE weather (
city varchar(80) references cities(city),
temp_lo int,
temp_hi int,
prcp real,
date date
);
</programlisting>
Now try inserting an invalid record:
<programlisting>
INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
</programlisting>
<screen>
ERROR: insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL: Key (city)=(Berkeley) is not present in table "cities".
</screen>
</para>
<para>
The behavior of foreign keys can be finely tuned to your
application. We will not go beyond this simple example in this
tutorial, but just refer you to <xref linkend="ddl"/>
for more information. Making correct use of
foreign keys will definitely improve the quality of your database
applications, so you are strongly encouraged to learn about them.
</para>
</section>
<section xml:id="tutorial-transactions">
<title>Transactions</title>
<indexterm zone="tutorial-transactions">
<primary>transaction</primary>
</indexterm>
<para>
<firstterm>Transactions</firstterm> are a fundamental concept of all database
systems. The essential point of a transaction is that it bundles
multiple steps into a single, all-or-nothing operation. The intermediate
states between the steps are not visible to other concurrent transactions,
and if some failure occurs that prevents the transaction from completing,
then none of the steps affect the database at all.
</para>
<para>
For example, consider a bank database that contains balances for various
customer accounts, as well as total deposit balances for branches.
Suppose that we want to record a payment of $100.00 from Alice's account
to Bob's account. Simplifying outrageously, the SQL commands for this
might look like:
<programlisting>
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
UPDATE branches SET balance = balance - 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
</programlisting>
</para>
<para>
The details of these commands are not important here; the important
point is that there are several separate updates involved to accomplish
this rather simple operation. Our bank's officers will want to be
assured that either all these updates happen, or none of them happen.
It would certainly not do for a system failure to result in Bob
receiving $100.00 that was not debited from Alice. Nor would Alice long
remain a happy customer if she was debited without Bob being credited.
We need a guarantee that if something goes wrong partway through the
operation, none of the steps executed so far will take effect. Grouping
the updates into a <firstterm>transaction</firstterm> gives us this guarantee.
A transaction is said to be <firstterm>atomic</firstterm>: from the point of
view of other transactions, it either happens completely or not at all.
</para>
<para>
We also want a
guarantee that once a transaction is completed and acknowledged by
the database system, it has indeed been permanently recorded
and won't be lost even if a crash ensues shortly thereafter.
For example, if we are recording a cash withdrawal by Bob,
we do not want any chance that the debit to his account will
disappear in a crash just after he walks out the bank door.
A transactional database guarantees that all the updates made by
a transaction are logged in permanent storage (i.e., on disk) before
the transaction is reported complete.
</para>
<para>
Another important property of transactional databases is closely
related to the notion of atomic updates: when multiple transactions
are running concurrently, each one should not be able to see the
incomplete changes made by others. For example, if one transaction
is busy totalling all the branch balances, it would not do for it
to include the debit from Alice's branch but not the credit to
Bob's branch, nor vice versa. So transactions must be all-or-nothing
not only in terms of their permanent effect on the database, but
also in terms of their visibility as they happen. The updates made
so far by an open transaction are invisible to other transactions
until the transaction completes, whereupon all the updates become
visible simultaneously.
</para>
<para>
In <productname>PostgreSQL</productname>, a transaction is set up by surrounding
the SQL commands of the transaction with
<command>BEGIN</command> and <command>COMMIT</command> commands. So our banking
transaction would actually look like:
<programlisting>
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
-- etc etc
COMMIT;
</programlisting>
</para>
<para>
If, partway through the transaction, we decide we do not want to
commit (perhaps we just noticed that Alice's balance went negative),
we can issue the command <command>ROLLBACK</command> instead of
<command>COMMIT</command>, and all our updates so far will be canceled.
</para>
<para>
<productname>PostgreSQL</productname> actually treats every SQL statement as being
executed within a transaction. If you do not issue a <command>BEGIN</command>
command,
then each individual statement has an implicit <command>BEGIN</command> and
(if successful) <command>COMMIT</command> wrapped around it. A group of
statements surrounded by <command>BEGIN</command> and <command>COMMIT</command>
is sometimes called a <firstterm>transaction block</firstterm>.
</para>
<note>
<para>
Some client libraries issue <command>BEGIN</command> and <command>COMMIT</command>
commands automatically, so that you might get the effect of transaction
blocks without asking. Check the documentation for the interface
you are using.
</para>
</note>
<para>
It's possible to control the statements in a transaction in a more
granular fashion through the use of <firstterm>savepoints</firstterm>. Savepoints
allow you to selectively discard parts of the transaction, while
committing the rest. After defining a savepoint with
<command>SAVEPOINT</command>, you can if needed roll back to the savepoint
with <command>ROLLBACK TO</command>. All the transaction's database changes
between defining the savepoint and rolling back to it are discarded, but
changes earlier than the savepoint are kept.
</para>
<para>
After rolling back to a savepoint, it continues to be defined, so you can
roll back to it several times. Conversely, if you are sure you won't need
to roll back to a particular savepoint again, it can be released, so the
system can free some resources. Keep in mind that either releasing or
rolling back to a savepoint
will automatically release all savepoints that were defined after it.
</para>
<para>
All this is happening within the transaction block, so none of it
is visible to other database sessions. When and if you commit the
transaction block, the committed actions become visible as a unit
to other sessions, while the rolled-back actions never become visible
at all.
</para>
<para>
Remembering the bank database, suppose we debit $100.00 from Alice's
account, and credit Bob's account, only to find later that we should
have credited Wally's account. We could do it using savepoints like
this:
<programlisting>
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Wally';
COMMIT;
</programlisting>
</para>
<para>
This example is, of course, oversimplified, but there's a lot of control
possible in a transaction block through the use of savepoints.
Moreover, <command>ROLLBACK TO</command> is the only way to regain control of a
transaction block that was put in aborted state by the
system due to an error, short of rolling it back completely and starting
again.
</para>
</section>
<section xml:id="tutorial-window">
<title>Window Functions</title>
<indexterm zone="tutorial-window">
<primary>window function</primary>
</indexterm>
<para>
A <firstterm>window function</firstterm> performs a calculation across a set of
table rows that are somehow related to the current row. This is comparable
to the type of calculation that can be done with an aggregate function.
But unlike regular aggregate functions, use of a window function does not
cause rows to become grouped into a single output row __mdash__ the
rows retain their separate identities. Behind the scenes, the window
function is able to access more than just the current row of the query
result.
</para>
<para>
Here is an example that shows how to compare each employee's salary
with the average salary in his or her department:
<programlisting>
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
</programlisting>
<screen>
depname | empno | salary | avg
-----------+-------+--------+-----------------------
develop | 11 | 5200 | 5020.0000000000000000
develop | 7 | 4200 | 5020.0000000000000000
develop | 9 | 4500 | 5020.0000000000000000
develop | 8 | 6000 | 5020.0000000000000000
develop | 10 | 5200 | 5020.0000000000000000
personnel | 5 | 3500 | 3700.0000000000000000
personnel | 2 | 3900 | 3700.0000000000000000
sales | 3 | 4800 | 4866.6666666666666667
sales | 1 | 5000 | 4866.6666666666666667
sales | 4 | 4800 | 4866.6666666666666667
(10 rows)
</screen>
The first three output columns come directly from the table
<varname remap="structname">empsalary</varname>, and there is one output row for each row in the
table. The fourth column represents an average taken across all the table
rows that have the same <varname remap="structfield">depname</varname> value as the current row.
(This actually is the same function as the regular <function>avg</function>
aggregate function, but the <literal>OVER</literal> clause causes it to be
treated as a window function and computed across an appropriate set of
rows.)
</para>
<para>
A window function call always contains an <literal>OVER</literal> clause
directly following the window function's name and argument(s). This is what
syntactically distinguishes it from a regular function or aggregate
function. The <literal>OVER</literal> clause determines exactly how the
rows of the query are split up for processing by the window function.
The <literal>PARTITION BY</literal> list within <literal>OVER</literal> specifies
dividing the rows into groups, or partitions, that share the same
values of the <literal>PARTITION BY</literal> expression(s). For each row,
the window function is computed across the rows that fall into the
same partition as the current row.
</para>
<para>
You can also control the order in which rows are processed by
window functions using <literal>ORDER BY</literal> within <literal>OVER</literal>.
(The window <literal>ORDER BY</literal> does not even have to match the
order in which the rows are output.) Here is an example:
<programlisting>
SELECT depname, empno, salary,
rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
</programlisting>
<screen>
depname | empno | salary | rank
-----------+-------+--------+------
develop | 8 | 6000 | 1
develop | 10 | 5200 | 2
develop | 11 | 5200 | 2
develop | 9 | 4500 | 4
develop | 7 | 4200 | 5
personnel | 2 | 3900 | 1
personnel | 5 | 3500 | 2
sales | 1 | 5000 | 1
sales | 4 | 4800 | 2
sales | 3 | 4800 | 2
(10 rows)
</screen>
As shown here, the <function>rank</function> function produces a numerical rank
within the current row's partition for each distinct <literal>ORDER BY</literal>
value, in the order defined by the <literal>ORDER BY</literal> clause.
<function>rank</function> needs no explicit parameter, because its behavior
is entirely determined by the <literal>OVER</literal> clause.
</para>
<para>
The rows considered by a window function are those of the <quote>virtual
table</quote> produced by the query's <literal>FROM</literal> clause as filtered by its
<literal>WHERE</literal>, <literal>GROUP BY</literal>, and <literal>HAVING</literal> clauses
if any. For example, a row removed because it does not meet the
<literal>WHERE</literal> condition is not seen by any window function.
A query can contain multiple window functions that slice up the data
in different ways by means of different <literal>OVER</literal> clauses, but
they all act on the same collection of rows defined by this virtual table.
</para>
<para>
We already saw that <literal>ORDER BY</literal> can be omitted if the ordering
of rows is not important. It is also possible to omit <literal>PARTITION
BY</literal>, in which case there is just one partition containing all the rows.
</para>
<para>
There is another important concept associated with window functions:
for each row, there is a set of rows within its partition called its
<firstterm>window frame</firstterm>. Many (but not all) window functions act only
on the rows of the window frame, rather than of the whole partition.
By default, if <literal>ORDER BY</literal> is supplied then the frame consists of
all rows from the start of the partition up through the current row, plus
any following rows that are equal to the current row according to the
<literal>ORDER BY</literal> clause. When <literal>ORDER BY</literal> is omitted the
default frame consists of all rows in the partition.
<footnote>
<para>
There are options to define the window frame in other ways, but
this tutorial does not cover them. See
<xref linkend="syntax-window-functions"/> for details.
</para>
</footnote>
Here is an example using <function>sum</function>:
</para>
<programlisting>
SELECT salary, sum(salary) OVER () FROM empsalary;
</programlisting>
<screen>
salary | sum
--------+-------
5200 | 47100
5000 | 47100
3500 | 47100
4800 | 47100
3900 | 47100
4200 | 47100
4500 | 47100
4800 | 47100
6000 | 47100
5200 | 47100
(10 rows)
</screen>
<para>
Above, since there is no <literal>ORDER BY</literal> in the <literal>OVER</literal>
clause, the window frame is the same as the partition, which for lack of
<literal>PARTITION BY</literal> is the whole table; in other words each sum is
taken over the whole table and so we get the same result for each output
row. But if we add an <literal>ORDER BY</literal> clause, we get very different
results:
</para>
<programlisting>
SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
</programlisting>
<screen>
salary | sum
--------+-------
3500 | 3500
3900 | 7400
4200 | 11600
4500 | 16100
4800 | 25700
4800 | 25700
5000 | 30700
5200 | 41100
5200 | 41100
6000 | 47100
(10 rows)
</screen>
<para>
Here the sum is taken from the first (lowest) salary up through the
current one, including any duplicates of the current one (notice the
results for the duplicated salaries).
</para>
<para>
Window functions are permitted only in the <literal>SELECT</literal> list
and the <literal>ORDER BY</literal> clause of the query. They are forbidden
elsewhere, such as in <literal>GROUP BY</literal>, <literal>HAVING</literal>
and <literal>WHERE</literal> clauses. This is because they logically
execute after the processing of those clauses. Also, window functions
execute after regular aggregate functions. This means it is valid to
include an aggregate function call in the arguments of a window function,
but not vice versa.
</para>
<para>
If there is a need to filter or group rows after the window calculations
are performed, you can use a sub-select. For example:
<programlisting>
SELECT depname, empno, salary, enroll_date
FROM
(SELECT depname, empno, salary, enroll_date,
rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
FROM empsalary
) AS ss
WHERE pos __lt__ 3;
</programlisting>
The above query only shows the rows from the inner query having
<literal>rank</literal> less than 3.
</para>
<para>
When a query involves multiple window functions, it is possible to write
out each one with a separate <literal>OVER</literal> clause, but this is
duplicative and error-prone if the same windowing behavior is wanted
for several functions. Instead, each windowing behavior can be named
in a <literal>WINDOW</literal> clause and then referenced in <literal>OVER</literal>.
For example:
<programlisting>
SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
</programlisting>
</para>
<para>
More details about window functions can be found in
<xref linkend="syntax-window-functions"/>,
<xref linkend="functions-window"/>,
<xref linkend="queries-window"/>, and the
<xref linkend="sql-select"/> reference page.
</para>
</section>
<section xml:id="tutorial-inheritance">
<title>Inheritance</title>
<indexterm zone="tutorial-inheritance">
<primary>inheritance</primary>
</indexterm>
<para>
Inheritance is a concept from object-oriented databases. It opens
up interesting new possibilities of database design.
</para>
<para>
Let's create two tables: A table <classname>cities</classname>
and a table <classname>capitals</classname>. Naturally, capitals
are also cities, so you want some way to show the capitals
implicitly when you list all cities. If you're really clever you
might invent some scheme like this:
<programlisting>
CREATE TABLE capitals (
name text,
population real,
altitude int, -- (in ft)
state char(2)
);
CREATE TABLE non_capitals (
name text,
population real,
altitude int -- (in ft)
);
CREATE VIEW cities AS
SELECT name, population, altitude FROM capitals
UNION
SELECT name, population, altitude FROM non_capitals;
</programlisting>
This works OK as far as querying goes, but it gets ugly when you
need to update several rows, for one thing.
</para>
<para>
A better solution is this:
<programlisting>
CREATE TABLE cities (
name text,
population real,
altitude int -- (in ft)
);
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);
</programlisting>
</para>
<para>
In this case, a row of <classname>capitals</classname>
<firstterm>inherits</firstterm> all columns (<varname remap="structfield">name</varname>,
<varname remap="structfield">population</varname>, and <varname remap="structfield">altitude</varname>) from its
<firstterm>parent</firstterm>, <classname>cities</classname>. The
type of the column <varname remap="structfield">name</varname> is
<type>text</type>, a native <productname>PostgreSQL</productname>
type for variable length character strings. State capitals have
an extra column, <varname remap="structfield">state</varname>, that shows their state. In
<productname>PostgreSQL</productname>, a table can inherit from
zero or more other tables.
</para>
<para>
For example, the following query finds the names of all cities,
including state capitals, that are located at an altitude
over 500 feet:
<programlisting>
SELECT name, altitude
FROM cities
WHERE altitude __gt__ 500;
</programlisting>
which returns:
<screen>
name | altitude
-----------+----------
Las Vegas | 2174
Mariposa | 1953
Madison | 845
(3 rows)
</screen>
</para>
<para>
On the other hand, the following query finds
all the cities that are not state capitals and
are situated at an altitude over 500 feet:
<programlisting>
SELECT name, altitude
FROM ONLY cities
WHERE altitude __gt__ 500;
</programlisting>
<screen>
name | altitude
-----------+----------
Las Vegas | 2174
Mariposa | 1953
(2 rows)
</screen>
</para>
<para>
Here the <literal>ONLY</literal> before <literal>cities</literal>
indicates that the query should be run over only the
<classname>cities</classname> table, and not tables below
<classname>cities</classname> in the inheritance hierarchy. Many
of the commands that we have already discussed __mdash__
<command>SELECT</command>, <command>UPDATE</command>, and
<command>DELETE</command> __mdash__ support this <literal>ONLY</literal>
notation.
</para>
<note>
<para>
Although inheritance is frequently useful, it has not been integrated
with unique constraints or foreign keys, which limits its usefulness.
See <xref linkend="ddl-inherit"/> for more detail.
</para>
</note>
</section>
<section xml:id="tutorial-conclusion">
<title>Conclusion</title>
<para>
<productname>PostgreSQL</productname> has many features not
touched upon in this tutorial introduction, which has been
oriented toward newer users of <acronym>SQL</acronym>. These
features are discussed in more detail in the remainder of this
book.
</para>
<para>
If you feel you need more introductory material, please visit the PostgreSQL
<link xmlns:xlink="http://www.w3.org/1999/xlink" xlink:href="http://www.postgresql.org">web site</link>
for links to more resources.
</para>
</section>
</chapter>
[text/html] advanced.html (28.0K, 4-advanced.html)
download | inline:
<html><head><meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"><title>Chapter�1.�Advanced Features</title><meta name="generator" content="DocBook XSL Stylesheets V1.79.1"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="chapter"><div class="titlepage"><div><div><h2 class="title"><a name="tutorial-advanced"></a>Chapter�1.�Advanced Features</h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl class="toc"><dt><span class="section"><a href="#tutorial-advanced-intro">Introduction</a></span></dt><dt><span class="section"><a href="#tutorial-views">Views</a></span></dt><dt><span class="section"><a href="#tutorial-fk">Foreign Keys</a></span></dt><dt><span class="section"><a href="#tutorial-transactions">Transactions</a></span></dt><dt><span class="section"><a href="#tutorial-window">Window Functions</a></span></dt><dt><span class="section"><a href="#tutorial-inheritance">Inheritance</a></span></dt><dt><span class="section"><a href="#tutorial-conclusion">Conclusion</a></span></dt></dl></div><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="tutorial-advanced-intro"></a>Introduction</h2></div></div></div><p>
In the previous chapter we have covered the basics of using
<acronym class="acronym">SQL</acronym> to store and access your data in
<span class="productname">PostgreSQL</span>. We will now discuss some
more advanced features of <acronym class="acronym">SQL</acronym> that simplify
management and prevent loss or corruption of your data. Finally,
we will look at some <span class="productname">PostgreSQL</span>
extensions.
</p><p>
This chapter will on occasion refer to examples found in <a class="xref" href="#">???</a> to change or improve them, so it will be
useful to have read that chapter. Some examples from
this chapter can also be found in
<code class="filename">advanced.sql</code> in the tutorial directory. This
file also contains some sample data to load, which is not
repeated here. (Refer to <a class="xref" href="#">???</a> for
how to use the file.)
</p></div><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="tutorial-views"></a>Views</h2></div></div></div><a class="indexterm" name="idm46045144379056"></a><p>
Refer back to the queries in <a class="xref" href="#">???</a>.
Suppose the combined listing of weather records and city location
is of particular interest to your application, but you do not want
to type the query each time you need it. You can create a
<em class="firstterm">view</em> over the query, which gives a name to
the query that you can refer to like an ordinary table:
</p><pre class="programlisting">
CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
SELECT * FROM myview;
</pre><p>
</p><p>
Making liberal use of views is a key aspect of good SQL database
design. Views allow you to encapsulate the details of the
structure of your tables, which might change as your application
evolves, behind consistent interfaces.
</p><p>
Views can be used in almost any place a real table can be used.
Building views upon other views is not uncommon.
</p></div><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="tutorial-fk"></a>Foreign Keys</h2></div></div></div><a class="indexterm" name="idm46045144012112"></a><a class="indexterm" name="idm46045144010800"></a><p>
Recall the <code class="classname">weather</code> and
<code class="classname">cities</code> tables from <a class="xref" href="#">???</a>. Consider the following problem: You
want to make sure that no one can insert rows in the
<code class="classname">weather</code> table that do not have a matching
entry in the <code class="classname">cities</code> table. This is called
maintaining the <em class="firstterm">referential integrity</em> of
your data. In simplistic database systems this would be
implemented (if at all) by first looking at the
<code class="classname">cities</code> table to check if a matching record
exists, and then inserting or rejecting the new
<code class="classname">weather</code> records. This approach has a
number of problems and is very inconvenient, so
<span class="productname">PostgreSQL</span> can do this for you.
</p><p>
The new declaration of the tables would look like this:
</p><pre class="programlisting">
CREATE TABLE cities (
city varchar(80) primary key,
location point
);
CREATE TABLE weather (
city varchar(80) references cities(city),
temp_lo int,
temp_hi int,
prcp real,
date date
);
</pre><p>
Now try inserting an invalid record:
</p><pre class="programlisting">
INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
</pre><p>
</p><pre class="screen">
ERROR: insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL: Key (city)=(Berkeley) is not present in table "cities".
</pre><p>
</p><p>
The behavior of foreign keys can be finely tuned to your
application. We will not go beyond this simple example in this
tutorial, but just refer you to <a class="xref" href="#">???</a>
for more information. Making correct use of
foreign keys will definitely improve the quality of your database
applications, so you are strongly encouraged to learn about them.
</p></div><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="tutorial-transactions"></a>Transactions</h2></div></div></div><a class="indexterm" name="idm46045143999200"></a><p>
<em class="firstterm">Transactions</em> are a fundamental concept of all database
systems. The essential point of a transaction is that it bundles
multiple steps into a single, all-or-nothing operation. The intermediate
states between the steps are not visible to other concurrent transactions,
and if some failure occurs that prevents the transaction from completing,
then none of the steps affect the database at all.
</p><p>
For example, consider a bank database that contains balances for various
customer accounts, as well as total deposit balances for branches.
Suppose that we want to record a payment of $100.00 from Alice's account
to Bob's account. Simplifying outrageously, the SQL commands for this
might look like:
</p><pre class="programlisting">
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
UPDATE branches SET balance = balance - 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
</pre><p>
</p><p>
The details of these commands are not important here; the important
point is that there are several separate updates involved to accomplish
this rather simple operation. Our bank's officers will want to be
assured that either all these updates happen, or none of them happen.
It would certainly not do for a system failure to result in Bob
receiving $100.00 that was not debited from Alice. Nor would Alice long
remain a happy customer if she was debited without Bob being credited.
We need a guarantee that if something goes wrong partway through the
operation, none of the steps executed so far will take effect. Grouping
the updates into a <em class="firstterm">transaction</em> gives us this guarantee.
A transaction is said to be <em class="firstterm">atomic</em>: from the point of
view of other transactions, it either happens completely or not at all.
</p><p>
We also want a
guarantee that once a transaction is completed and acknowledged by
the database system, it has indeed been permanently recorded
and won't be lost even if a crash ensues shortly thereafter.
For example, if we are recording a cash withdrawal by Bob,
we do not want any chance that the debit to his account will
disappear in a crash just after he walks out the bank door.
A transactional database guarantees that all the updates made by
a transaction are logged in permanent storage (i.e., on disk) before
the transaction is reported complete.
</p><p>
Another important property of transactional databases is closely
related to the notion of atomic updates: when multiple transactions
are running concurrently, each one should not be able to see the
incomplete changes made by others. For example, if one transaction
is busy totalling all the branch balances, it would not do for it
to include the debit from Alice's branch but not the credit to
Bob's branch, nor vice versa. So transactions must be all-or-nothing
not only in terms of their permanent effect on the database, but
also in terms of their visibility as they happen. The updates made
so far by an open transaction are invisible to other transactions
until the transaction completes, whereupon all the updates become
visible simultaneously.
</p><p>
In <span class="productname">PostgreSQL</span>, a transaction is set up by surrounding
the SQL commands of the transaction with
<span class="command"><strong>BEGIN</strong></span> and <span class="command"><strong>COMMIT</strong></span> commands. So our banking
transaction would actually look like:
</p><pre class="programlisting">
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
-- etc etc
COMMIT;
</pre><p>
</p><p>
If, partway through the transaction, we decide we do not want to
commit (perhaps we just noticed that Alice's balance went negative),
we can issue the command <span class="command"><strong>ROLLBACK</strong></span> instead of
<span class="command"><strong>COMMIT</strong></span>, and all our updates so far will be canceled.
</p><p>
<span class="productname">PostgreSQL</span> actually treats every SQL statement as being
executed within a transaction. If you do not issue a <span class="command"><strong>BEGIN</strong></span>
command,
then each individual statement has an implicit <span class="command"><strong>BEGIN</strong></span> and
(if successful) <span class="command"><strong>COMMIT</strong></span> wrapped around it. A group of
statements surrounded by <span class="command"><strong>BEGIN</strong></span> and <span class="command"><strong>COMMIT</strong></span>
is sometimes called a <em class="firstterm">transaction block</em>.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Some client libraries issue <span class="command"><strong>BEGIN</strong></span> and <span class="command"><strong>COMMIT</strong></span>
commands automatically, so that you might get the effect of transaction
blocks without asking. Check the documentation for the interface
you are using.
</p></div><p>
It's possible to control the statements in a transaction in a more
granular fashion through the use of <em class="firstterm">savepoints</em>. Savepoints
allow you to selectively discard parts of the transaction, while
committing the rest. After defining a savepoint with
<span class="command"><strong>SAVEPOINT</strong></span>, you can if needed roll back to the savepoint
with <span class="command"><strong>ROLLBACK TO</strong></span>. All the transaction's database changes
between defining the savepoint and rolling back to it are discarded, but
changes earlier than the savepoint are kept.
</p><p>
After rolling back to a savepoint, it continues to be defined, so you can
roll back to it several times. Conversely, if you are sure you won't need
to roll back to a particular savepoint again, it can be released, so the
system can free some resources. Keep in mind that either releasing or
rolling back to a savepoint
will automatically release all savepoints that were defined after it.
</p><p>
All this is happening within the transaction block, so none of it
is visible to other database sessions. When and if you commit the
transaction block, the committed actions become visible as a unit
to other sessions, while the rolled-back actions never become visible
at all.
</p><p>
Remembering the bank database, suppose we debit $100.00 from Alice's
account, and credit Bob's account, only to find later that we should
have credited Wally's account. We could do it using savepoints like
this:
</p><pre class="programlisting">
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Wally';
COMMIT;
</pre><p>
</p><p>
This example is, of course, oversimplified, but there's a lot of control
possible in a transaction block through the use of savepoints.
Moreover, <span class="command"><strong>ROLLBACK TO</strong></span> is the only way to regain control of a
transaction block that was put in aborted state by the
system due to an error, short of rolling it back completely and starting
again.
</p></div><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="tutorial-window"></a>Window Functions</h2></div></div></div><a class="indexterm" name="idm46045143941808"></a><p>
A <em class="firstterm">window function</em> performs a calculation across a set of
table rows that are somehow related to the current row. This is comparable
to the type of calculation that can be done with an aggregate function.
But unlike regular aggregate functions, use of a window function does not
cause rows to become grouped into a single output row __mdash__ the
rows retain their separate identities. Behind the scenes, the window
function is able to access more than just the current row of the query
result.
</p><p>
Here is an example that shows how to compare each employee's salary
with the average salary in his or her department:
</p><pre class="programlisting">
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
</pre><p>
</p><pre class="screen">
depname | empno | salary | avg
-----------+-------+--------+-----------------------
develop | 11 | 5200 | 5020.0000000000000000
develop | 7 | 4200 | 5020.0000000000000000
develop | 9 | 4500 | 5020.0000000000000000
develop | 8 | 6000 | 5020.0000000000000000
develop | 10 | 5200 | 5020.0000000000000000
personnel | 5 | 3500 | 3700.0000000000000000
personnel | 2 | 3900 | 3700.0000000000000000
sales | 3 | 4800 | 4866.6666666666666667
sales | 1 | 5000 | 4866.6666666666666667
sales | 4 | 4800 | 4866.6666666666666667
(10 rows)
</pre><p>
The first three output columns come directly from the table
<code class="varname">empsalary</code>, and there is one output row for each row in the
table. The fourth column represents an average taken across all the table
rows that have the same <code class="varname">depname</code> value as the current row.
(This actually is the same function as the regular <code class="function">avg</code>
aggregate function, but the <code class="literal">OVER</code> clause causes it to be
treated as a window function and computed across an appropriate set of
rows.)
</p><p>
A window function call always contains an <code class="literal">OVER</code> clause
directly following the window function's name and argument(s). This is what
syntactically distinguishes it from a regular function or aggregate
function. The <code class="literal">OVER</code> clause determines exactly how the
rows of the query are split up for processing by the window function.
The <code class="literal">PARTITION BY</code> list within <code class="literal">OVER</code> specifies
dividing the rows into groups, or partitions, that share the same
values of the <code class="literal">PARTITION BY</code> expression(s). For each row,
the window function is computed across the rows that fall into the
same partition as the current row.
</p><p>
You can also control the order in which rows are processed by
window functions using <code class="literal">ORDER BY</code> within <code class="literal">OVER</code>.
(The window <code class="literal">ORDER BY</code> does not even have to match the
order in which the rows are output.) Here is an example:
</p><pre class="programlisting">
SELECT depname, empno, salary,
rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
</pre><p>
</p><pre class="screen">
depname | empno | salary | rank
-----------+-------+--------+------
develop | 8 | 6000 | 1
develop | 10 | 5200 | 2
develop | 11 | 5200 | 2
develop | 9 | 4500 | 4
develop | 7 | 4200 | 5
personnel | 2 | 3900 | 1
personnel | 5 | 3500 | 2
sales | 1 | 5000 | 1
sales | 4 | 4800 | 2
sales | 3 | 4800 | 2
(10 rows)
</pre><p>
As shown here, the <code class="function">rank</code> function produces a numerical rank
within the current row's partition for each distinct <code class="literal">ORDER BY</code>
value, in the order defined by the <code class="literal">ORDER BY</code> clause.
<code class="function">rank</code> needs no explicit parameter, because its behavior
is entirely determined by the <code class="literal">OVER</code> clause.
</p><p>
The rows considered by a window function are those of the <span class="quote">“<span class="quote">virtual
table</span>”</span> produced by the query's <code class="literal">FROM</code> clause as filtered by its
<code class="literal">WHERE</code>, <code class="literal">GROUP BY</code>, and <code class="literal">HAVING</code> clauses
if any. For example, a row removed because it does not meet the
<code class="literal">WHERE</code> condition is not seen by any window function.
A query can contain multiple window functions that slice up the data
in different ways by means of different <code class="literal">OVER</code> clauses, but
they all act on the same collection of rows defined by this virtual table.
</p><p>
We already saw that <code class="literal">ORDER BY</code> can be omitted if the ordering
of rows is not important. It is also possible to omit <code class="literal">PARTITION
BY</code>, in which case there is just one partition containing all the rows.
</p><p>
There is another important concept associated with window functions:
for each row, there is a set of rows within its partition called its
<em class="firstterm">window frame</em>. Many (but not all) window functions act only
on the rows of the window frame, rather than of the whole partition.
By default, if <code class="literal">ORDER BY</code> is supplied then the frame consists of
all rows from the start of the partition up through the current row, plus
any following rows that are equal to the current row according to the
<code class="literal">ORDER BY</code> clause. When <code class="literal">ORDER BY</code> is omitted the
default frame consists of all rows in the partition.
<a href="#ftn.idm46045143919456" class="footnote" name="idm46045143919456"><sup class="footnote">[1]</sup></a>
Here is an example using <code class="function">sum</code>:
</p><pre class="programlisting">
SELECT salary, sum(salary) OVER () FROM empsalary;
</pre><pre class="screen">
salary | sum
--------+-------
5200 | 47100
5000 | 47100
3500 | 47100
4800 | 47100
3900 | 47100
4200 | 47100
4500 | 47100
4800 | 47100
6000 | 47100
5200 | 47100
(10 rows)
</pre><p>
Above, since there is no <code class="literal">ORDER BY</code> in the <code class="literal">OVER</code>
clause, the window frame is the same as the partition, which for lack of
<code class="literal">PARTITION BY</code> is the whole table; in other words each sum is
taken over the whole table and so we get the same result for each output
row. But if we add an <code class="literal">ORDER BY</code> clause, we get very different
results:
</p><pre class="programlisting">
SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
</pre><pre class="screen">
salary | sum
--------+-------
3500 | 3500
3900 | 7400
4200 | 11600
4500 | 16100
4800 | 25700
4800 | 25700
5000 | 30700
5200 | 41100
5200 | 41100
6000 | 47100
(10 rows)
</pre><p>
Here the sum is taken from the first (lowest) salary up through the
current one, including any duplicates of the current one (notice the
results for the duplicated salaries).
</p><p>
Window functions are permitted only in the <code class="literal">SELECT</code> list
and the <code class="literal">ORDER BY</code> clause of the query. They are forbidden
elsewhere, such as in <code class="literal">GROUP BY</code>, <code class="literal">HAVING</code>
and <code class="literal">WHERE</code> clauses. This is because they logically
execute after the processing of those clauses. Also, window functions
execute after regular aggregate functions. This means it is valid to
include an aggregate function call in the arguments of a window function,
but not vice versa.
</p><p>
If there is a need to filter or group rows after the window calculations
are performed, you can use a sub-select. For example:
</p><pre class="programlisting">
SELECT depname, empno, salary, enroll_date
FROM
(SELECT depname, empno, salary, enroll_date,
rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
FROM empsalary
) AS ss
WHERE pos __lt__ 3;
</pre><p>
The above query only shows the rows from the inner query having
<code class="literal">rank</code> less than 3.
</p><p>
When a query involves multiple window functions, it is possible to write
out each one with a separate <code class="literal">OVER</code> clause, but this is
duplicative and error-prone if the same windowing behavior is wanted
for several functions. Instead, each windowing behavior can be named
in a <code class="literal">WINDOW</code> clause and then referenced in <code class="literal">OVER</code>.
For example:
</p><pre class="programlisting">
SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
</pre><p>
</p><p>
More details about window functions can be found in
<a class="xref" href="#">???</a>,
<a class="xref" href="#">???</a>,
<a class="xref" href="#">???</a>, and the
<a class="xref" href="#">???</a> reference page.
</p></div><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="tutorial-inheritance"></a>Inheritance</h2></div></div></div><a class="indexterm" name="idm46045143902640"></a><p>
Inheritance is a concept from object-oriented databases. It opens
up interesting new possibilities of database design.
</p><p>
Let's create two tables: A table <code class="classname">cities</code>
and a table <code class="classname">capitals</code>. Naturally, capitals
are also cities, so you want some way to show the capitals
implicitly when you list all cities. If you're really clever you
might invent some scheme like this:
</p><pre class="programlisting">
CREATE TABLE capitals (
name text,
population real,
altitude int, -- (in ft)
state char(2)
);
CREATE TABLE non_capitals (
name text,
population real,
altitude int -- (in ft)
);
CREATE VIEW cities AS
SELECT name, population, altitude FROM capitals
UNION
SELECT name, population, altitude FROM non_capitals;
</pre><p>
This works OK as far as querying goes, but it gets ugly when you
need to update several rows, for one thing.
</p><p>
A better solution is this:
</p><pre class="programlisting">
CREATE TABLE cities (
name text,
population real,
altitude int -- (in ft)
);
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);
</pre><p>
</p><p>
In this case, a row of <code class="classname">capitals</code>
<em class="firstterm">inherits</em> all columns (<code class="varname">name</code>,
<code class="varname">population</code>, and <code class="varname">altitude</code>) from its
<em class="firstterm">parent</em>, <code class="classname">cities</code>. The
type of the column <code class="varname">name</code> is
<span class="type">text</span>, a native <span class="productname">PostgreSQL</span>
type for variable length character strings. State capitals have
an extra column, <code class="varname">state</code>, that shows their state. In
<span class="productname">PostgreSQL</span>, a table can inherit from
zero or more other tables.
</p><p>
For example, the following query finds the names of all cities,
including state capitals, that are located at an altitude
over 500 feet:
</p><pre class="programlisting">
SELECT name, altitude
FROM cities
WHERE altitude __gt__ 500;
</pre><p>
which returns:
</p><pre class="screen">
name | altitude
-----------+----------
Las Vegas | 2174
Mariposa | 1953
Madison | 845
(3 rows)
</pre><p>
</p><p>
On the other hand, the following query finds
all the cities that are not state capitals and
are situated at an altitude over 500 feet:
</p><pre class="programlisting">
SELECT name, altitude
FROM ONLY cities
WHERE altitude __gt__ 500;
</pre><p>
</p><pre class="screen">
name | altitude
-----------+----------
Las Vegas | 2174
Mariposa | 1953
(2 rows)
</pre><p>
</p><p>
Here the <code class="literal">ONLY</code> before <code class="literal">cities</code>
indicates that the query should be run over only the
<code class="classname">cities</code> table, and not tables below
<code class="classname">cities</code> in the inheritance hierarchy. Many
of the commands that we have already discussed __mdash__
<span class="command"><strong>SELECT</strong></span>, <span class="command"><strong>UPDATE</strong></span>, and
<span class="command"><strong>DELETE</strong></span> __mdash__ support this <code class="literal">ONLY</code>
notation.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Although inheritance is frequently useful, it has not been integrated
with unique constraints or foreign keys, which limits its usefulness.
See <a class="xref" href="#">???</a> for more detail.
</p></div></div><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="tutorial-conclusion"></a>Conclusion</h2></div></div></div><p>
<span class="productname">PostgreSQL</span> has many features not
touched upon in this tutorial introduction, which has been
oriented toward newer users of <acronym class="acronym">SQL</acronym>. These
features are discussed in more detail in the remainder of this
book.
</p><p>
If you feel you need more introductory material, please visit the PostgreSQL
<a class="ulink" href="http://www.postgresql.org" target="_top">web site</a>
for links to more resources.
</p></div><div class="footnotes"><br><hr style="width:100; text-align:left;margin-left: 0"><div id="ftn.idm46045143919456" class="footnote"><p><a href="#idm46045143919456" class="para"><sup class="para">[1] </sup></a>
There are options to define the window frame in other ways, but
this tutorial does not cover them. See
<a class="xref" href="#">???</a> for details.
</p></div></div></div></body></html>
[text/x-xslfo] advanced.fo (77.0K, 5-advanced.fo)
download
[application/pdf] advanced.pdf (33.4K, 6-advanced.pdf)
download
view thread (86+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Docbook 5.x
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox