Re: Some Documentation Changes - Mailing list pgsql-patches

Patch applied.  Thanks.

---------------------------------------------------------------------------


Christopher Browne wrote:
> 1.  In keeping with the recent discussion that there should be more
> said about views, stored procedures, and triggers, in the tutorial, I
> have added a bit of verbiage to that end.
>
> 2.  Some formatting changes to the datetime discussion, as well as
> addition of a citation of a relevant book on calendars.
>
> Index: advanced.sgml
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/advanced.sgml,v
> retrieving revision 1.38
> diff -c -u -r1.38 advanced.sgml
> --- advanced.sgml    29 Nov 2003 19:51:36 -0000    1.38
> +++ advanced.sgml    30 Dec 2003 01:58:24 -0000
> @@ -65,10 +65,24 @@
>
>     <para>
>      Views can be used in almost any place a real table can be used.
> -    Building views upon other views is not uncommon.
> +    Building views upon other views is not uncommon.  You may cut down
> +    on the difficulty of building complex queries by constructing them
> +    in smaller, easier-to-verify pieces, using views.  Views may be
> +    used to reveal specific table columns to users that legitimately
> +    need access to some of the data, but who shouldn't be able to look
> +    at the whole table.
>     </para>
> -  </sect1>
>
> +   <para>
> +    Views differ from <quote> real tables </quote> in that they are
> +    not, by default, updatable.  If they join together several tables,
> +    it may be troublesome to update certain columns since the
> +    <emphasis>real</emphasis> update that must take place requires
> +    identifying the relevant rows in the source tables.  This is
> +    discussed further in <xref linkend="rules-views-update">.
> +    </para>
> +
> +  </sect1>
>
>    <sect1 id="tutorial-fk">
>     <title>Foreign Keys</title>
> @@ -387,6 +401,169 @@
>     </para>
>    </sect1>
>
> +  <sect1 id="tutorial-storedprocs">
> +   <title> Stored Procedures </title>
> +
> +   <indexterm zone="tutorial-storedprocs">
> +     <primary>stored procedures</primary>
> +   </indexterm>
> +
> +   <para> Stored procedures are code that runs inside the database
> +   system.  Numerous languages may be used to implement functions and
> +   procedures; most built-in code is implemented in C.  The
> +   <quote>basic</quote> loadable procedural language for
> +   <productname>PostgreSQL</productname> is <xref linkid="plpgsql">.
> +   Numerous other languages may also be used, including <xref
> +   linkid="plperl">, <xref linkid="pltcl">, and <xref
> +   linkid="plpython">.
> +   </para>
> +
> +   <para> There are several ways that stored procedures are really
> +   helpful:
> +
> +   <itemizedlist>
> +
> +   <listitem><para> To centralize data validation code into the
> +   database </para>
> +
> +   <para> Your system may use client software written in several
> +   languages, perhaps with a <quote>web application</quote>
> +   implemented in PHP, a <quote>server application</quote> implemented
> +   in Java, and a <quote> report writer</quote> implemented in Perl.
> +   In the absence of stored procedures, you will likely find that data
> +   validation code must be implemented multiple times, in multiple
> +   languages, once for each application.</para>
> +
> +   <para> By implementing data validation in stored procedures,
> +   running in the database, it can behave uniformly for all these
> +   systems, and you do not need to worry about synchronizing
> +   validation procedures across the languages.</para>
> +
> +   </listitem>
> +
> +   <listitem><para> Reducing round trips between client and server
> +   </para>
> +
> +   <para>A stored procedure may submit multiple queries, looking up
> +   information and adding in links to additional tables.  This takes
> +   place without requiring that the client submit multiple queries,
> +   and without requiring any added network traffic.
> +   </para>
> +
> +   <para> As a matter of course, the queries share a single
> +   transaction context, and there may also be savings in the
> +   evaluation of query plans, that will be similar between invocations
> +   of a given stored procedure.  </para></listitem>
> +
> +   <listitem><para> To simplify queries. </para>
> +
> +   <para> For instance, if you are commonly checking the TLD on domain
> +   names, you might create a stored procedure for this purpose, and so
> +   be able to use queries such as <command> select domain, tld(domain)
> +   from domains; </command> instead of having to put verbose code
> +   using <function>substr()</function> into each query.
> +   </para>
> +
> +   <para> It is particularly convenient to use scripting languages
> +   like Perl, Tcl, and Python to <quote>grovel through strings</quote>
> +   since they are designed for <quote>text processing.</quote></para>
> +
> +   <para> The binding to the R statistical language allows
> +   implementing complex statistical queries inside the database,
> +   instead of having to draw the data out.
> +   </listitem>
> +
> +   <listitem><para> Increasing the level of abstraction</para>
> +
> +   <para> If data is accessed exclusively through stored procedures,
> +   then the structures of tables may be changed without there needing
> +   to be any visible change in the API used by programmers.  In some
> +   systems, users are <emphasis>only</emphasis> allowed access to
> +   stored procedures to update data, and cannot do direct updates to
> +   tables.
> +   </para>
> +
> +   </listitem>
> +
> +   </itemizedlist>
> +   </para>
> +
> +   <para> These benefits build on one another: careful use of stored
> +   procedures can simultaneously improve reliability and performance,
> +   whilst simplifying database access code and improving portability
> +   across client platforms and languages.  For instance, consider that
> +   a stored procedure can cheaply query tables in the database to
> +   validate the correctness of data provided as input. </para>
> +
> +   <para> Instead of requiring a whole series of queries to create an
> +   object, and to look up parent/subsidiary objects to link it to, a
> +   stored procedure can do all of this efficiently in the database
> +   server, improving performance, and eliminating whole classes of
> +   errors. </para>
> +
> +  </sect1>
> +
> +  <sect1 id="tutorial-triggers">
> +   <title> Triggers </title>
> +
> +   <indexterm zone="tutorial-triggers">
> +     <primary>triggers</primary>
> +   </indexterm>
> +
> +   <para> Triggers allow running a function either before or after
> +   update (<command>INSERT</command>, <command>DELETE</command>,
> +   <command>UPDATE</command>) operations, which can allow you to do
> +   some very clever things. </para>
> +
> +   <itemizedlist>
> +
> +   <listitem><para> Data Validation  </para>
> +
> +   <para> Instead of explicitly coding validation checks as part of a
> +   stored procedure, they may be introduced as <command>BEFORE</command>
> +   triggers.  The trigger function checks the input values, raising an
> +   exception if it finds invalid input.</para>
> +
> +   <para> Note that this is how foreign key checks are implemented in
> +   <productname>PostgreSQL</productname>; when you define a foreign
> +   key, you will see a message similar to the following:
> +<screen>
> +NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> +</screen></para>
> +
> +   <para> In some cases, it may be appropriate for a trigger function
> +   to insert data in order to <emphasis>make</emphasis> the input valid.  For
> +   instance, if a newly created object needs a status code in a status
> +   table, the trigger might automatically do that.</para>
> +   </listitem>
> +
> +   <listitem><para> Audit logs </para>
> +
> +   <para> One may use <command>AFTER</command> triggers to monitor updates to
> +   vital tables, and <command>INSERT</command> entries into log tables to
> +   provide a more permanent record of those updates.  </para>
> +   </listitem>
> +
> +   <listitem><para> Replication </para>
> +
> +   <para> The <application>RServ</application> replication system uses
> +   <command>AFTER</command> triggers to track which rows have changed on the
> +   <quote>master</quote> system and therefore need to be copied over to
> +   <quote>slave</quote> systems.</para>
> +
> +   <para> <command>
> +     CREATE TRIGGER "_rserv_trigger_t_" AFTER INSERT OR DELETE OR UPDATE ON "my_table"
> +        FOR EACH ROW EXECUTE PROCEDURE "_rserv_log_" ('10');
> +   </command></para>
> +   </listitem>
> +
> +   </itemizedlist>
> +
> +   <para> Notice that there are strong parallels between what can be
> +   accomplished using triggers and stored procedures, particularly in
> +   regards to data validation.  </para>
> +
> +  </sect1>
>
>    <sect1 id="tutorial-conclusion">
>     <title>Conclusion</title>
> Index: datetime.sgml
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/datetime.sgml,v
> retrieving revision 2.39
> diff -c -u -r2.39 datetime.sgml
> --- datetime.sgml    1 Dec 2003 20:34:53 -0000    2.39
> +++ datetime.sgml    30 Dec 2003 01:58:25 -0000
> @@ -11,8 +11,8 @@
>     strings, and are broken up into distinct fields with a preliminary
>     determination of what kind of information may be in the
>     field. Each field is interpreted and either assigned a numeric
> -   value, ignored, or rejected.
> -   The parser contains internal lookup tables for all textual fields,
> +   value, ignored, or rejected.</para>
> +   <para> The parser contains internal lookup tables for all textual fields,
>     including months, days of the week, and time
>     zones.
>    </para>
> @@ -1056,21 +1056,21 @@
>     years.
>    </para>
>
> -  <para>
> -   The papal bull of February 1582 decreed that 10 days should be dropped
> -   from October 1582 so that 15 October should follow immediately after
> -   4 October.
> -   This was observed in Italy, Poland, Portugal, and Spain. Other Catholic
> -   countries followed shortly after, but Protestant countries were
> -   reluctant to change, and the Greek orthodox countries didn't change
> -   until the start of the 20th century.
> -
> -   The reform was observed by Great Britain and Dominions (including what is
> -   now the USA) in 1752.
> -   Thus 2 September 1752 was followed by 14 September 1752.
> +  <para> The papal bull of February 1582 decreed that 10 days should
> +  be dropped from October 1582 so that 15 October should follow
> +  immediately after 4 October.</para>
> +
> +  <para> This was observed in Italy, Poland, Portugal, and Spain.
> +  Other Catholic countries followed shortly after, but Protestant
> +  countries were reluctant to change, and the Greek orthodox countries
> +  didn't change until the start of the 20th century.</para>
> +
> +  <para> The reform was observed by Great Britain and Dominions
> +  (including what is now the USA) in 1752.  Thus 2 September 1752 was
> +  followed by 14 September 1752.</para>
>
> -   This is why Unix systems have the <command>cal</command> program
> -   produce the following:
> +   <para> This is why Unix systems have the <command>cal</command>
> +   program produce the following:
>
>  <screen>
>  $ <userinput>cal 9 1752</userinput>
> @@ -1094,19 +1094,24 @@
>      </para>
>     </note>
>
> -  <para>
> -   Different calendars have been developed in various parts of the
> -   world, many predating the Gregorian system.
> +  <para> Different calendars have been developed in various parts of
> +  the world, many predating the Gregorian system.</para>
>
> -   For example,
> -   the beginnings of the Chinese calendar can be traced back to the 14th
> -   century BC. Legend has it that the Emperor Huangdi invented the
> -   calendar in 2637 BC.
> +  <para> For example, the beginnings of the Chinese calendar can be
> +  traced back to the 14th century BC. Legend has it that the Emperor
> +  Huangdi invented the calendar in 2637 BC.</para>
>
> -   The People's Republic of China uses the Gregorian calendar
> -   for civil purposes. The Chinese calendar is used for determining
> -   festivals.
> +  <para> The People's Republic of China uses the Gregorian calendar
> +  for civil purposes. The Chinese calendar is used for determining
> +  festivals.
>    </para>
> +
> +  <para> If you are interested in this sort of thing, <citation>
> +  Calendrical Calculations: The Millennium Edition </citation> by by
> +  Edward M. Reingold and Nachum Dershowitz is an excellent reference,
> +  describing some 25 calendars, and providing software for displaying
> +  them and converting between them.</para>
> +
>   </sect1>
>  </appendix>
> --
> let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];;
> <http://dev6.int.libertyrms.com/>
> Christopher Browne
> (416) 646 3304 x124 (land)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073