← Back to branch summary

~vcs-imports/mammoth-replicator/trunk

« back to all changes in this revision

Viewing changes to doc/src/sgml/advanced.sgml

  • Committer: alvherre
  • Date: 2005-12-16 21:24:52 UTC
  • Revision ID: svn-v4:db760fc0-0f08-0410-9d63-cc6633f64896:trunk:1
Initial import of the REL8_0_3 sources from the Pgsql CVS repository.

Show diffs side-by-side

added added

removed removed

Lines of Context:
doc/src/sgml/advanced.sgml
 
1
<!--
 
2
$PostgreSQL: pgsql/doc/src/sgml/advanced.sgml,v 1.47 2004-12-17 04:50:32 tgl Exp $
 
3
-->
 
4
 
 
5
 <chapter id="tutorial-advanced">
 
6
  <title>Advanced Features</title>
 
7
 
 
8
  <sect1 id="tutorial-advanced-intro">
 
9
   <title>Introduction</title>
 
10
 
 
11
   <para>
 
12
    In the previous chapter we have covered the basics of using
 
13
    <acronym>SQL</acronym> to store and access your data in
 
14
    <productname>PostgreSQL</productname>.  We will now discuss some
 
15
    more advanced features of <acronym>SQL</acronym> that simplify
 
16
    management and prevent loss or corruption of your data.  Finally,
 
17
    we will look at some <productname>PostgreSQL</productname>
 
18
    extensions.
 
19
   </para>
 
20
 
 
21
   <para>
 
22
    This chapter will on occasion refer to examples found in <xref
 
23
    linkend="tutorial-sql"> to change or improve them, so it will be
 
24
    of advantage if you have read that chapter.  Some examples from
 
25
    this chapter can also be found in
 
26
    <filename>advanced.sql</filename> in the tutorial directory.  This
 
27
    file also contains some example data to load, which is not
 
28
    repeated here.  (Refer to <xref linkend="tutorial-sql-intro"> for
 
29
    how to use the file.)
 
30
   </para>
 
31
  </sect1>
 
32
 
 
33
 
 
34
  <sect1 id="tutorial-views">
 
35
   <title>Views</title>
 
36
 
 
37
   <indexterm zone="tutorial-views">
 
38
    <primary>view</primary>
 
39
   </indexterm>
 
40
 
 
41
   <para>
 
42
    Refer back to the queries in <xref linkend="tutorial-join">.
 
43
    Suppose the combined listing of weather records and city location
 
44
    is of particular interest to your application, but you do not want
 
45
    to type the query each time you need it.  You can create a
 
46
    <firstterm>view</firstterm> over the query, which gives a name to
 
47
    the query that you can refer to like an ordinary table.
 
48
 
 
49
<programlisting>
 
50
CREATE VIEW myview AS
 
51
    SELECT city, temp_lo, temp_hi, prcp, date, location
 
52
        FROM weather, cities
 
53
        WHERE city = name;
 
54
 
 
55
SELECT * FROM myview;
 
56
</programlisting>
 
57
   </para>
 
58
 
 
59
   <para>
 
60
    Making liberal use of views is a key aspect of good SQL database
 
61
    design.  Views allow you to encapsulate the details of the
 
62
    structure of your tables, which may change as your application
 
63
    evolves, behind consistent interfaces.
 
64
   </para>
 
65
 
 
66
   <para>
 
67
    Views can be used in almost any place a real table can be used.
 
68
    Building views upon other views is not uncommon.
 
69
   </para>
 
70
  </sect1>
 
71
 
 
72
 
 
73
  <sect1 id="tutorial-fk">
 
74
   <title>Foreign Keys</title>
 
75
 
 
76
   <indexterm zone="tutorial-fk">
 
77
    <primary>foreign key</primary>
 
78
   </indexterm>
 
79
 
 
80
   <indexterm zone="tutorial-fk">
 
81
    <primary>referential integrity</primary>
 
82
   </indexterm>
 
83
 
 
84
   <para>
 
85
    Recall the <classname>weather</classname> and
 
86
    <classname>cities</classname> tables from <xref
 
87
    linkend="tutorial-sql">.  Consider the following problem:  You
 
88
    want to make sure that no one can insert rows in the
 
89
    <classname>weather</classname> table that do not have a matching
 
90
    entry in the <classname>cities</classname> table.  This is called
 
91
    maintaining the <firstterm>referential integrity</firstterm> of
 
92
    your data.  In simplistic database systems this would be
 
93
    implemented (if at all) by first looking at the
 
94
    <classname>cities</classname> table to check if a matching record
 
95
    exists, and then inserting or rejecting the new
 
96
    <classname>weather</classname> records.  This approach has a
 
97
    number of problems and is very inconvenient, so
 
98
    <productname>PostgreSQL</productname> can do this for you.
 
99
   </para>
 
100
 
 
101
   <para>
 
102
    The new declaration of the tables would look like this:
 
103
 
 
104
<programlisting>
 
105
CREATE TABLE cities (
 
106
        city     varchar(80) primary key,
 
107
        location point
 
108
);
 
109
 
 
110
CREATE TABLE weather (
 
111
        city      varchar(80) references cities(city),
 
112
        temp_lo   int,
 
113
        temp_hi   int,
 
114
        prcp      real,
 
115
        date      date
 
116
);
 
117
</programlisting>
 
118
 
 
119
    Now try inserting an invalid record:
 
120
 
 
121
<programlisting>
 
122
INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
 
123
</programlisting>
 
124
 
 
125
<screen>
 
126
ERROR:  insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
 
127
DETAIL:  Key (city)=(Berkeley) is not present in table "cities".
 
128
</screen>
 
129
   </para>
 
130
 
 
131
   <para>
 
132
    The behavior of foreign keys can be finely tuned to your
 
133
    application.  We will not go beyond this simple example in this
 
134
    tutorial, but just refer you to <xref linkend="ddl">
 
135
    for more information.  Making correct use of
 
136
    foreign keys will definitely improve the quality of your database
 
137
    applications, so you are strongly encouraged to learn about them.
 
138
   </para>
 
139
  </sect1>
 
140
 
 
141
 
 
142
  <sect1 id="tutorial-transactions">
 
143
   <title>Transactions</title>
 
144
 
 
145
   <indexterm zone="tutorial-transactions">
 
146
    <primary>transaction</primary>
 
147
   </indexterm>
 
148
 
 
149
   <para>
 
150
    <firstterm>Transactions</> are a fundamental concept of all database
 
151
    systems.  The essential point of a transaction is that it bundles
 
152
    multiple steps into a single, all-or-nothing operation.  The intermediate
 
153
    states between the steps are not visible to other concurrent transactions,
 
154
    and if some failure occurs that prevents the transaction from completing,
 
155
    then none of the steps affect the database at all.
 
156
   </para>
 
157
 
 
158
   <para>
 
159
    For example, consider a bank database that contains balances for various
 
160
    customer accounts, as well as total deposit balances for branches.
 
161
    Suppose that we want to record a payment of $100.00 from Alice's account
 
162
    to Bob's account.  Simplifying outrageously, the SQL commands for this
 
163
    might look like
 
164
 
 
165
<programlisting>
 
166
UPDATE accounts SET balance = balance - 100.00
 
167
    WHERE name = 'Alice';
 
168
UPDATE branches SET balance = balance - 100.00
 
169
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
 
170
UPDATE accounts SET balance = balance + 100.00
 
171
    WHERE name = 'Bob';
 
172
UPDATE branches SET balance = balance + 100.00
 
173
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
 
174
</programlisting>
 
175
   </para>
 
176
 
 
177
   <para>
 
178
    The details of these commands are not important here; the important
 
179
    point is that there are several separate updates involved to accomplish
 
180
    this rather simple operation.  Our bank's officers will want to be
 
181
    assured that either all these updates happen, or none of them happen.
 
182
    It would certainly not do for a system failure to result in Bob
 
183
    receiving $100.00 that was not debited from Alice.  Nor would Alice long
 
184
    remain a happy customer if she was debited without Bob being credited.
 
185
    We need a guarantee that if something goes wrong partway through the
 
186
    operation, none of the steps executed so far will take effect.  Grouping
 
187
    the updates into a <firstterm>transaction</> gives us this guarantee.
 
188
    A transaction is said to be <firstterm>atomic</>: from the point of
 
189
    view of other transactions, it either happens completely or not at all.
 
190
   </para>
 
191
 
 
192
   <para>
 
193
    We also want a
 
194
    guarantee that once a transaction is completed and acknowledged by
 
195
    the database system, it has indeed been permanently recorded
 
196
    and won't be lost even if a crash ensues shortly thereafter.
 
197
    For example, if we are recording a cash withdrawal by Bob,
 
198
    we do not want any chance that the debit to his account will
 
199
    disappear in a crash just after he walks out the bank door.
 
200
    A transactional database guarantees that all the updates made by
 
201
    a transaction are logged in permanent storage (i.e., on disk) before
 
202
    the transaction is reported complete.
 
203
   </para>
 
204
 
 
205
   <para>
 
206
    Another important property of transactional databases is closely
 
207
    related to the notion of atomic updates: when multiple transactions
 
208
    are running concurrently, each one should not be able to see the
 
209
    incomplete changes made by others.  For example, if one transaction
 
210
    is busy totalling all the branch balances, it would not do for it
 
211
    to include the debit from Alice's branch but not the credit to
 
212
    Bob's branch, nor vice versa.  So transactions must be all-or-nothing
 
213
    not only in terms of their permanent effect on the database, but
 
214
    also in terms of their visibility as they happen.  The updates made
 
215
    so far by an open transaction are invisible to other transactions
 
216
    until the transaction completes, whereupon all the updates become
 
217
    visible simultaneously.
 
218
   </para>
 
219
 
 
220
   <para>
 
221
    In <productname>PostgreSQL</>, a transaction is set up by surrounding
 
222
    the SQL commands of the transaction with
 
223
    <command>BEGIN</> and <command>COMMIT</> commands.  So our banking
 
224
    transaction would actually look like
 
225
 
 
226
<programlisting>
 
227
BEGIN;
 
228
UPDATE accounts SET balance = balance - 100.00
 
229
    WHERE name = 'Alice';
 
230
-- etc etc
 
231
COMMIT;
 
232
</programlisting>
 
233
   </para>
 
234
 
 
235
   <para>
 
236
    If, partway through the transaction, we decide we do not want to
 
237
    commit (perhaps we just noticed that Alice's balance went negative),
 
238
    we can issue the command <command>ROLLBACK</> instead of
 
239
    <command>COMMIT</>, and all our updates so far will be canceled.
 
240
   </para>
 
241
 
 
242
   <para>
 
243
    <productname>PostgreSQL</> actually treats every SQL statement as being
 
244
    executed within a transaction.  If you do not issue a <command>BEGIN</>
 
245
    command, 
 
246
    then each individual statement has an implicit <command>BEGIN</> and
 
247
    (if successful) <command>COMMIT</> wrapped around it.  A group of
 
248
    statements surrounded by <command>BEGIN</> and <command>COMMIT</>
 
249
    is sometimes called a <firstterm>transaction block</>.
 
250
   </para>
 
251
 
 
252
   <note>
 
253
    <para>
 
254
     Some client libraries issue <command>BEGIN</> and <command>COMMIT</>
 
255
     commands automatically, so that you may get the effect of transaction
 
256
     blocks without asking.  Check the documentation for the interface
 
257
     you are using.
 
258
    </para>
 
259
   </note>
 
260
 
 
261
   <para>
 
262
    It's possible to control the statements in a transaction in a more
 
263
    granular fashion through the use of <firstterm>savepoints</>.  Savepoints
 
264
    allow you to selectively discard parts of the transaction, while
 
265
    committing the rest.  After defining a savepoint with
 
266
    <command>SAVEPOINT</>, you can if needed roll back to the savepoint
 
267
    with <command>ROLLBACK TO</>.  All the transaction's database changes
 
268
    between defining the savepoint and rolling back to it are discarded, but
 
269
    changes earlier than the savepoint are kept.
 
270
   </para> 
 
271
 
 
272
   <para>
 
273
    After rolling back to a savepoint, it continues to be defined, so you can
 
274
    roll back to it several times.  Conversely, if you are sure you won't need
 
275
    to roll back to a particular savepoint again, it can be released, so the
 
276
    system can free some resources.  Keep in mind that either releasing or
 
277
    rolling back to a savepoint
 
278
    will automatically release all savepoints that were defined after it.
 
279
   </para> 
 
280
 
 
281
   <para>
 
282
    All this is happening within the transaction block, so none of it
 
283
    is visible to other database sessions.  When and if you commit the
 
284
    transaction block, the committed actions become visible as a unit
 
285
    to other sessions, while the rolled-back actions never become visible
 
286
    at all.
 
287
   </para> 
 
288
 
 
289
   <para>
 
290
    Remembering the bank database, suppose we debit $100.00 from Alice's
 
291
    account, and credit Bob's account, only to find later that we should
 
292
    have credited Wally's account.  We could do it using savepoints like
 
293
    this:
 
294
 
 
295
<programlisting>
 
296
BEGIN;
 
297
UPDATE accounts SET balance = balance - 100.00
 
298
    WHERE name = 'Alice';
 
299
SAVEPOINT my_savepoint;
 
300
UPDATE accounts SET balance = balance + 100.00
 
301
    WHERE name = 'Bob';
 
302
-- oops ... forget that and use Wally's account
 
303
ROLLBACK TO my_savepoint;
 
304
UPDATE accounts SET balance = balance + 100.00
 
305
    WHERE name = 'Wally';
 
306
COMMIT;
 
307
</programlisting>
 
308
   </para>
 
309
 
 
310
   <para>
 
311
    This example is, of course, oversimplified, but there's a lot of control
 
312
    to be had over a transaction block through the use of savepoints.
 
313
    Moreover, <command>ROLLBACK TO</> is the only way to regain control of a
 
314
    transaction block that was put in aborted state by the
 
315
    system due to an error, short of rolling it back completely and starting
 
316
    again.
 
317
   </para>
 
318
 
 
319
  </sect1>
 
320
 
 
321
 
 
322
  <sect1 id="tutorial-inheritance">
 
323
   <title>Inheritance</title>
 
324
 
 
325
   <indexterm zone="tutorial-inheritance">
 
326
    <primary>inheritance</primary>
 
327
   </indexterm>
 
328
 
 
329
   <para>
 
330
    Inheritance is a concept from object-oriented databases.  It opens
 
331
    up interesting new possibilities of database design.
 
332
   </para>
 
333
 
 
334
   <para>
 
335
    Let's create two tables:  A table <classname>cities</classname>
 
336
    and a table <classname>capitals</classname>.  Naturally, capitals
 
337
    are also cities, so you want some way to show the capitals
 
338
    implicitly when you list all cities.  If you're really clever you
 
339
    might invent some scheme like this:
 
340
 
 
341
<programlisting>
 
342
CREATE TABLE capitals (
 
343
  name       text,
 
344
  population real,
 
345
  altitude   int,    -- (in ft)
 
346
  state      char(2)
 
347
);
 
348
 
 
349
CREATE TABLE non_capitals (
 
350
  name       text,
 
351
  population real,
 
352
  altitude   int     -- (in ft)
 
353
);
 
354
 
 
355
CREATE VIEW cities AS
 
356
  SELECT name, population, altitude FROM capitals
 
357
    UNION
 
358
  SELECT name, population, altitude FROM non_capitals;
 
359
</programlisting>
 
360
 
 
361
    This works OK as far as querying goes, but it gets ugly when you
 
362
    need to update several rows, for one thing.
 
363
   </para>
 
364
 
 
365
   <para>
 
366
    A better solution is this:
 
367
 
 
368
<programlisting>
 
369
CREATE TABLE cities (
 
370
  name       text,
 
371
  population real,
 
372
  altitude   int     -- (in ft)
 
373
);
 
374
 
 
375
CREATE TABLE capitals (
 
376
  state      char(2)
 
377
) INHERITS (cities);
 
378
</programlisting>
 
379
   </para>
 
380
 
 
381
   <para>
 
382
    In this case, a row of <classname>capitals</classname>
 
383
    <firstterm>inherits</firstterm> all columns (<structfield>name</>,
 
384
    <structfield>population</>, and <structfield>altitude</>) from its
 
385
    <firstterm>parent</firstterm>, <classname>cities</classname>.  The
 
386
    type of the column <structfield>name</structfield> is
 
387
    <type>text</type>, a native <productname>PostgreSQL</productname>
 
388
    type for variable length character strings.  State capitals have
 
389
    an extra column, state, that shows their state.  In
 
390
    <productname>PostgreSQL</productname>, a table can inherit from
 
391
    zero or more other tables.
 
392
   </para>
 
393
 
 
394
   <para>
 
395
    For example, the  following  query finds the  names  of  all  cities,
 
396
    including  state capitals, that are located at an altitude 
 
397
    over 500 ft.:
 
398
 
 
399
<programlisting>
 
400
SELECT name, altitude
 
401
  FROM cities
 
402
  WHERE altitude &gt; 500;
 
403
</programlisting>
 
404
 
 
405
    which returns:
 
406
 
 
407
<screen>
 
408
   name    | altitude
 
409
-----------+----------
 
410
 Las Vegas |     2174
 
411
 Mariposa  |     1953
 
412
 Madison   |      845
 
413
(3 rows)
 
414
</screen>
 
415
   </para>
 
416
 
 
417
   <para>
 
418
    On the other hand, the  following  query  finds
 
419
    all  the cities that are not state capitals and
 
420
    are situated at an altitude of 500 ft. or higher:
 
421
 
 
422
<programlisting>
 
423
SELECT name, altitude
 
424
    FROM ONLY cities
 
425
    WHERE altitude &gt; 500;
 
426
</programlisting>
 
427
 
 
428
<screen>
 
429
   name    | altitude
 
430
-----------+----------
 
431
 Las Vegas |     2174
 
432
 Mariposa  |     1953
 
433
(2 rows)
 
434
</screen>
 
435
   </para>
 
436
 
 
437
   <para>
 
438
    Here the <literal>ONLY</literal> before <literal>cities</literal>
 
439
    indicates that the query should be run over only the
 
440
    <classname>cities</classname> table, and not tables below
 
441
    <classname>cities</classname> in the inheritance hierarchy.  Many
 
442
    of the commands that we have already discussed &mdash;
 
443
    <command>SELECT</command>, <command>UPDATE</command>, and
 
444
    <command>DELETE</command> &mdash; support this <literal>ONLY</literal>
 
445
    notation.
 
446
   </para>
 
447
 
 
448
   <note>
 
449
    <para>
 
450
     Although inheritance is frequently useful, it has not been integrated
 
451
     with unique constraints or foreign keys, which limits its usefulness.
 
452
     See <xref linkend="ddl-inherit"> for more detail.
 
453
    </para>
 
454
   </note>
 
455
  </sect1>
 
456
 
 
457
 
 
458
  <sect1 id="tutorial-conclusion">
 
459
   <title>Conclusion</title>
 
460
 
 
461
   <para>
 
462
    <productname>PostgreSQL</productname> has many features not
 
463
    touched upon in this tutorial introduction, which has been
 
464
    oriented toward newer users of <acronym>SQL</acronym>.  These
 
465
    features are discussed in more detail in the remainder of this
 
466
    book.
 
467
   </para>
 
468
 
 
469
   <para>
 
470
    If you feel you need more introductory material, please visit the
 
471
    <ulink url="http://www.postgresql.org">PostgreSQL web
 
472
    site</ulink> for links to more resources.
 
473
   </para>
 
474
  </sect1>
 
475
 </chapter>
 
476
 
 
477
<!-- Keep this comment at the end of the file
 
478
Local variables:
 
479
mode:sgml
 
480
sgml-omittag:nil
 
481
sgml-shorttag:t
 
482
sgml-minimize-attributes:nil
 
483
sgml-always-quote-attributes:t
 
484
sgml-indent-step:1
 
485
sgml-indent-data:t
 
486
sgml-parent-document:nil
 
487
sgml-default-dtd-file:"./reference.ced"
 
488
sgml-exposed-tags:nil
 
489
sgml-local-catalogs:("/usr/lib/sgml/catalog")
 
490
sgml-local-ecat-files:nil
 
491
End:
 
492
-->