docs for advisory locks

? advisory_docs.patch
Index: src/sgml/func.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.338
diff -u -p -r1.338 func.sgml
--- src/sgml/func.sgml	16 Sep 2006 00:30:13 -0000	1.338
+++ src/sgml/func.sgml	19 Sep 2006 18:19:56 -0000
@@ -10586,6 +10586,191 @@ SELECT * FROM pg_stat_file('filename');
 SELECT (pg_stat_file('filename')).modification;
 </programlisting>
    </para>
+   
+   <para>
+    The functions shown in <xref linkend="functions-advisory-locks"> manage
+    advisory locks.  For details about proper usage of these functions, see
+    <xref linkend="advisory-locks">.
+   </para>
+
+   <indexterm zone="functions-admin">
+    <primary>pg_column_size</primary>
+   </indexterm>
+   
+   <table id="functions-advisory-locks">
+    <title>Advisory Lock Functions</title>
+    <tgroup cols="3">
+     <thead>
+      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry>
+        <literal><function>pg_advisory_lock</function>(<parameter>value</> <type>bigint</>)</literal>
+       </entry>
+       <entry><type>void</type></entry>
+       <entry>Obtain exclusive advisory lock</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_advisory_lock</function>(<parameter>value1</> <type>int</>, <parameter>value2</> <type>int</>)</literal>
+       </entry>
+       <entry><type>void</type></entry>
+       <entry>Obtain exclusive advisory lock if available</entry>
+      </row>      
+      
+      <row>
+       <entry>
+        <literal><function>pg_advisory_lock_shared</function>(<parameter>value</> <type>bigint</>)</literal>
+       </entry>
+       <entry><type>void</type></entry>
+       <entry>Obtain shared advisory lock</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_advisory_lock_shared</function>(<parameter>value1</> <type>int</>, <parameter>value2</> <type>int</>)</literal>
+       </entry>
+       <entry><type>void</type></entry>
+       <entry>Obtain shared advisory lock</entry>
+      </row>                  
+      
+      <row>
+       <entry>
+        <literal><function>pg_try_advisory_lock</function>(<parameter>value</> <type>bigint</>)</literal>
+       </entry>
+       <entry><type>boolean</type></entry>
+       <entry>Obtain exclusive advisory lock if available</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_try_advisory_lock</function>(<parameter>value1</> <type>int</>, <parameter>value2</> <type>int</>)</literal>
+       </entry>
+       <entry><type>boolean</type></entry>
+       <entry>Obtain exclusive advisory lock if available</entry>
+      </row>      
+      
+      <row>
+       <entry>
+        <literal><function>pg_try_advisory_lock_shared</function>(<parameter>value</> <type>bigint</>)</literal>
+       </entry>
+       <entry><type>boolean</type></entry>
+       <entry>Obtain shared advisory lock if available</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_try_advisory_lock_shared</function>(<parameter>value1</> <type>int</>, <parameter>value2</> <type>int</>)</literal>
+       </entry>
+       <entry><type>boolean</type></entry>
+       <entry>Obtain shared advisory lock if available</entry>
+      </row>                  
+      
+      <row>
+       <entry>
+        <literal><function>pg_advisory_unlock</function>(<parameter>value</> <type>bigint</>)</literal>
+       </entry>
+       <entry><type>boolean</type></entry>
+       <entry>Release an exclusive advisory lock</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_advisory_unlock</function>(<parameter>value1</> <type>int</>, <parameter>value2</> <type>int</>)</literal>
+       </entry>
+       <entry><type>boolean</type></entry>
+       <entry>Release an exclusive advisory lock</entry>
+      </row>                  
+      
+      <row>
+       <entry>
+        <literal><function>pg_advisory_unlock_shared</function>(<parameter>value</> <type>bigint</>)</literal>
+       </entry>
+       <entry><type>boolean</type></entry>
+       <entry>Release a shared advisory lock</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_advisory_unlock_shared</function>(<parameter>value1</> <type>int</>, <parameter>value2</> <type>int</>)</literal>
+       </entry>
+       <entry><type>boolean</type></entry>
+       <entry>Release a shared advisory lock</entry>
+      </row>        
+      
+      <row>
+       <entry>
+        <literal><function>pg_advisory_unlock_all</function>()</literal>
+       </entry>
+       <entry><type>boolean</type></entry>
+       <entry>Releases all advisory locks held by the current session</entry>
+      </row>              
+
+     </tbody>
+    </tgroup>
+   </table>
+
+   <indexterm zone="functions-admin">
+    <primary>pg_advisory_lock</primary>
+   </indexterm>
+   <para>
+    <function>pg_advisory_lock</> locks an application defined resource 
+    (either defined by value or a combination of value1 and value2).  The 
+    function will wait until the resource becomes available.  The lock
+    is exclusive.  The locks can stack so that if the same resource is
+    locked three times it must be also unlocked three times to be completely
+    released.
+   </para>
+
+   <indexterm zone="functions-admin">
+    <primary>pg_advisory_lock_shared</primary>
+   </indexterm>
+   <para>
+    <function>pg_advisory_lock_shared</> as <function>pg_advisory_lock</>, 
+    except the lock can be shared with other sessions locking shared.
+   </para>
+   
+   <indexterm zone="functions-admin">
+    <primary>pg_try_advisory_lock</primary>
+   </indexterm>
+   <para>
+    <function>pg_try_advisory_lock</> as <function>pg_advisory_lock</>, 
+    except the function will not wait until the lock is available.  It will return
+    true if it is available (success) and false if it is not.
+   </para>   
+   
+   <indexterm zone="functions-admin">
+    <primary>pg_try_advisory_lock_shared</primary>
+   </indexterm>
+   <para>
+    <function>pg_try_advisory_lock_shared</> as 
+    <function>pg_advisory_lock_shared</>, except the function will not wait 
+    until the lock is available.  It will return true if it is available 
+    (success) and false if it is not.
+   </para>      
+   
+   <indexterm zone="functions-admin">
+    <primary>pg_advisory_unlock</primary>
+   </indexterm>
+   <para>
+    <function>pg_advisory_unlock</> will release an exclude advisory lock.  It 
+    will return true if the lock is released.  If no lock is available, it will 
+    return false.  In addition, an SQL warning will be raised by the backend.
+   </para>         
+   
+   <indexterm zone="functions-admin">
+    <primary>pg_advisory_unlock_shared</primary>
+   </indexterm>
+   <para>
+    <function>pg_advisory_unlock_shared</> as <function>pg_advisory_unlock</>,
+    except to release a shared advisory lock.
+   </para>      
+   
+   <indexterm zone="functions-admin">
+    <primary>pg_advisory_unlock_all</primary>
+   </indexterm>
+   <para>
+    <function>pg_advisory_unlock_all</> as will release all advisory locks
+    held by the current session.
+   </para>         
 
   </sect1>
 </chapter>
Index: src/sgml/mvcc.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/mvcc.sgml,v
retrieving revision 2.62
diff -u -p -r2.62 mvcc.sgml
--- src/sgml/mvcc.sgml	18 Sep 2006 12:11:36 -0000	2.62
+++ src/sgml/mvcc.sgml	19 Sep 2006 18:19:57 -0000
@@ -25,10 +25,10 @@
    </indexterm>
 
    <para>
-    Unlike traditional database systems which use locks for concurrency control,
-    <productname>PostgreSQL</productname>
-    maintains data consistency by using a multiversion model
-    (Multiversion Concurrency Control, <acronym>MVCC</acronym>). 
+    <productname>PostgreSQL</productname> provides a rich set of tools 
+    for developers to manage concurrent access to data.  Internal
+    integrity data consistency is maintained by using a multiversion 
+    model (Multiversion Concurrency Control, <acronym>MVCC</acronym>). 
     This means that while querying a database each transaction sees
     a snapshot of data (a <firstterm>database version</firstterm>)
     as it was some
@@ -36,7 +36,11 @@
     This protects the transaction from viewing inconsistent data that
     could be caused by (other) concurrent transaction updates on the same
     data rows, providing <firstterm>transaction isolation</firstterm>
-    for each database session.
+    for each database session.  <acronym>MVCC</acronym>, by eschewing
+    explicit locking methodologies of traditional database systems,
+    minimizes lock contention in order to allow for reasonable 
+    performance in multiuser environments.
+    
    </para>
 
    <para>
@@ -52,7 +56,9 @@
     <productname>PostgreSQL</productname> for applications that cannot
     adapt easily to <acronym>MVCC</acronym> behavior.  However, proper
     use of <acronym>MVCC</acronym> will generally provide better
-    performance than locks.
+    performance than locks.  In addition, application defined advisory
+    locks provide a mechanism for acquiring locks that are not scoped
+    to a transaction.
    </para>
   </sect1>
 
@@ -859,6 +865,66 @@ UPDATE accounts SET balance = balance - 
      (e.g., while waiting for user input).
     </para>
    </sect2>
+   
+   <sect2 id="advisory-locks">
+    <title>Advisory Locks</title>
+
+    <indexterm zone="advisory-locks">
+     <primary>advisory</primary>
+    </indexterm>
+
+    <para>
+     <productname>PostgreSQL</productname> provides a means for 
+     creating locks that have an application defined meaning, called 
+     advisory locks.  Advisory locks are suitable for locking strategies
+     that are an awkward fit for the MVCC model.  They have a lifetime 
+     that is scoped to the end of the session or the lock is released,
+     whichever is sooner.  Unlike standard locks, advisory locks do not
+     honor transaction semantics.  For example, a lock acquired during a 
+     transaction that is rolled back will still be held following the 
+     rollback.  The same lock can be acquired multiple times by its
+     owning process: for each lock there must be a corresponding unlock
+     for the lock to be fully released.  Like all locks in 
+     <productname>PostgreSQL</productname>, a complete list of advisory 
+     locks held by the system can be listed in the system catalog 
+     <structname>pg_locks</structname>.
+    </para>
+    
+    <para>
+     Advisory locks are allocated out of a shared buffer pool whose size
+     is defined by the configuration variables max_connections and
+     max_locks_per_transaction.  Care must be given not to exhaust this 
+     memory or the server will not be able to grant any locks at all.  
+     This imposes an aper limit on the amount of advisory locks 
+     grantable by the server in the tens or hundreds of thousands 
+     depending on how the server is configured.
+    </para>
+
+    <para>
+     A common use of advisory locks is to emulate pessimistic locking 
+     strategies typical of so called 'flat file' data management systems.
+     While a table based flag could be used for the same purpose, 
+     advisory locks are faster, avoid MVCC bloat, and are cleaned up
+     by the server at the end of the session.
+     Integer based keys are especially well suited for this type of lock
+     management.  In certain cases using this method, especially in queries 
+     involving explicit ordering and limit, care must be given to control 
+     the amount of locks required because of the order in which the SQL 
+     expressions are evaluated.  For example:
+<screen>
+SELECT pg_advisory_lock(id, 0, 0) FROM foo WHERE id = 12345; -- ok
+SELECT pg_advisory_lock(id, 0, 0) FROM foo WHERE id > 12345 LIMIT 100; -- danger!
+SELECT pg_advisory_lock(q.id, 0, 0) FROM 
+(
+  select * foo WHERE id > 12345 LIMIT 100; 
+) q; -- ok
+</screen>
+   In the above queries, the second form is dangerous because the limit
+   expression is not guaranteed to evaluate before the locking function.  
+   In this case, from the point of view of the application, the locks
+   would be dangling, although still viewable in pg_locks. 
+   </sect2>   
+   
   </sect1>
 
   <sect1 id="applevel-consistency">