advanced.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

    statements surrounded by <command>BEGIN</> and <command>COMMIT</>
    is sometimes called a <firstterm>transaction block</>.
   </para>

   <note>
    <para>
     Some client libraries issue <command>BEGIN</> and <command>COMMIT</>
     commands automatically, so that you may 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</>.  Savepoints
    allow you to selectively discard parts of the transaction, while
    committing the rest.  After defining a savepoint with
    <command>SAVEPOINT</>, you can if needed roll back to the savepoint
    with <command>ROLLBACK TO</>.  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
    to be had over a transaction block through the use of savepoints.
    Moreover, <command>ROLLBACK TO</> 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>

  </sect1>


  <sect1 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 (<structfield>name</>,
    <structfield>population</>, and <structfield>altitude</>) from its
    <firstterm>parent</firstterm>, <classname>cities</classname>.  The
    type of the column <structfield>name</structfield> is
    <type>text</type>, a native <productname>PostgreSQL</productname>
    type for variable length character strings.  State capitals have
    an extra column, state, 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 ft.:

<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 of 500 ft. or higher:

<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>
  </sect1>


  <sect1 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
    <ulink url="http://www.postgresql.org">web site</ulink>
    for links to more resources.
   </para>
  </sect1>
 </chapter>

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->