Add transaction-level advisory locks.

They share the same locking namespace with the existing session-level
advisory locks, but they are automatically released at the end of the
current transaction and cannot be released explicitly via unlock
functions.

Marko Tiikkaja, reviewed by me.

2 files changed, 133 insertions, 30 deletions

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 70a1bd9ee9f..736eb67dfff 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14623,91 +14623,147 @@ SELECT (pg_stat_file('filename')).modification;
         <literal><function>pg_advisory_lock(<parameter>key</> <type>bigint</>)</function></literal>
        </entry>
        <entry><type>void</type></entry>
-       <entry>Obtain exclusive advisory lock</entry>
+       <entry>Obtain exclusive session level advisory lock</entry>
       </row>
       <row>
        <entry>
         <literal><function>pg_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
        </entry>
        <entry><type>void</type></entry>
-       <entry>Obtain exclusive advisory lock</entry>
+       <entry>Obtain exclusive session level advisory lock</entry>
       </row>
       <row>
        <entry>
         <literal><function>pg_advisory_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
        </entry>
        <entry><type>void</type></entry>
-       <entry>Obtain shared advisory lock</entry>
+       <entry>Obtain shared session level advisory lock</entry>
       </row>
       <row>
        <entry>
         <literal><function>pg_advisory_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
        </entry>
        <entry><type>void</type></entry>
-       <entry>Obtain shared advisory lock</entry>
+       <entry>Obtain shared session level advisory lock</entry>
       </row>
       <row>
        <entry>
         <literal><function>pg_advisory_unlock(<parameter>key</> <type>bigint</>)</function></literal>
        </entry>
        <entry><type>boolean</type></entry>
-       <entry>Release an exclusive advisory lock</entry>
+       <entry>Release an exclusive session level advisory lock</entry>
       </row>
       <row>
        <entry>
         <literal><function>pg_advisory_unlock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
        </entry>
        <entry><type>boolean</type></entry>
-       <entry>Release an exclusive advisory lock</entry>
+       <entry>Release an exclusive session level advisory lock</entry>
       </row>
       <row>
        <entry>
         <literal><function>pg_advisory_unlock_all()</function></literal>
        </entry>
        <entry><type>void</type></entry>
-       <entry>Release all advisory locks held by the current session</entry>
+       <entry>Release all session level advisory locks held by the current session</entry>
       </row>
       <row>
        <entry>
         <literal><function>pg_advisory_unlock_shared(<parameter>key</> <type>bigint</>)</function></literal>
        </entry>
        <entry><type>boolean</type></entry>
-       <entry>Release a shared advisory lock</entry>
+       <entry>Release a shared session level advisory lock</entry>
       </row>
       <row>
        <entry>
         <literal><function>pg_advisory_unlock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
        </entry>
        <entry><type>boolean</type></entry>
-       <entry>Release a shared advisory lock</entry>
+       <entry>Release a shared session level advisory lock</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_advisory_xact_lock(<parameter>key</> <type>bigint</>)</function></literal>
+       </entry>
+       <entry><type>void</type></entry>
+       <entry>Obtain exclusive transaction level advisory lock</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_advisory_xact_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
+       </entry>
+       <entry><type>void</type></entry>
+       <entry>Obtain exclusive transaction level advisory lock</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_advisory_xact_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
+       </entry>
+       <entry><type>void</type></entry>
+       <entry>Obtain shared transaction level advisory lock</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_advisory_xact_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
+       </entry>
+       <entry><type>void</type></entry>
+       <entry>Obtain shared advisory lock for the current transaction</entry>
       </row>
       <row>
        <entry>
         <literal><function>pg_try_advisory_lock(<parameter>key</> <type>bigint</>)</function></literal>
        </entry>
        <entry><type>boolean</type></entry>
-       <entry>Obtain exclusive advisory lock if available</entry>
+       <entry>Obtain exclusive session level advisory lock if available</entry>
       </row>
       <row>
        <entry>
         <literal><function>pg_try_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
        </entry>
        <entry><type>boolean</type></entry>
-       <entry>Obtain exclusive advisory lock if available</entry>
+       <entry>Obtain exclusive session level advisory lock if available</entry>
       </row>
       <row>
        <entry>
         <literal><function>pg_try_advisory_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
        </entry>
        <entry><type>boolean</type></entry>
-       <entry>Obtain shared advisory lock if available</entry>
+       <entry>Obtain shared session level advisory lock if available</entry>
       </row>
       <row>
        <entry>
         <literal><function>pg_try_advisory_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
        </entry>
        <entry><type>boolean</type></entry>
-       <entry>Obtain shared advisory lock if available</entry>
+       <entry>Obtain shared session level advisory lock if available</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_try_advisory_xact_lock(<parameter>key</> <type>bigint</>)</function></literal>
+       </entry>
+       <entry><type>boolean</type></entry>
+       <entry>Obtain exclusive transaction level advisory lock if available</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_try_advisory_xact_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
+       </entry>
+       <entry><type>boolean</type></entry>
+       <entry>Obtain exclusive transaction level advisory lock if available</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_try_advisory_xact_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
+       </entry>
+       <entry><type>boolean</type></entry>
+       <entry>Obtain shared transaction level advisory lock if available</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_try_advisory_xact_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
+       </entry>
+       <entry><type>boolean</type></entry>
+       <entry>Obtain shared transaction level advisory lock if available</entry>
       </row>
      </tbody>
     </tgroup>
@@ -14759,11 +14815,49 @@ SELECT (pg_stat_file('filename')).modification;
    </para>
 
    <indexterm>
+    <primary>pg_advisory_xact_lock</primary>
+   </indexterm>
+   <para>
+    <function>pg_advisory_xact_lock</> works the same as
+    <function>pg_advisory_lock</>, expect the lock is automatically released
+    at the end of the current transaction and can not be released explicitly.
+   </para>
+
+   <indexterm>
+    <primary>pg_advisory_xact_lock_shared</primary>
+   </indexterm>
+   <para>
+    <function>pg_advisory_xact_lock_shared</> works the same as
+    <function>pg_advisory_lock_shared</>, expect the lock is automatically released
+    at the end of the current transaction and can not be released explicitly.
+   </para>
+
+   <indexterm>
+    <primary>pg_try_advisory_xact_lock</primary>
+   </indexterm>
+   <para>
+    <function>pg_try_advisory_xact_lock</> works the same as
+    <function>pg_try_advisory_lock</>, expect the lock, if acquired,
+    is automatically released at the end of the current transaction and
+    can not be released explicitly.
+   </para>
+
+   <indexterm>
+    <primary>pg_try_advisory_xact_lock_shared</primary>
+   </indexterm>
+   <para>
+    <function>pg_try_advisory_xact_lock_shared</> works the same as
+    <function>pg_try_advisory_lock_shared</>, expect the lock, if acquired,
+    is automatically released at the end of the current transaction and
+    can not be released explicitly.
+   </para>
+
+   <indexterm>
     <primary>pg_advisory_unlock</primary>
    </indexterm>
    <para>
     <function>pg_advisory_unlock</> will release a previously-acquired
-    exclusive advisory lock.  It
+    exclusive session level advisory lock.  It
     returns <literal>true</> if the lock is successfully released.
     If the lock was not held, it will return <literal>false</>,
     and in addition, an SQL warning will be raised by the server.
@@ -14775,15 +14869,15 @@ SELECT (pg_stat_file('filename')).modification;
    <para>
     <function>pg_advisory_unlock_shared</> works the same as
     <function>pg_advisory_unlock</>,
-    except it releases a shared advisory lock.
+    except it releases a shared session level advisory lock.
    </para>
 
    <indexterm>
     <primary>pg_advisory_unlock_all</primary>
    </indexterm>
    <para>
-    <function>pg_advisory_unlock_all</> will release all advisory locks
-    held by the current session.  (This function is implicitly invoked
+    <function>pg_advisory_unlock_all</> will release all session level advisory
+    locks held by the current session.  (This function is implicitly invoked
     at session end, even if the client disconnects ungracefully.)
    </para>
 
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
index f42bb091c13..785c9d75218 100644
--- a/doc/src/sgml/mvcc.sgml
+++ b/doc/src/sgml/mvcc.sgml
@@ -1199,19 +1199,28 @@ UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;
      called <firstterm>advisory locks</>, because the system does not
      enforce their use &mdash; it is up to the application to use them
      correctly.  Advisory locks can be useful for locking strategies
-     that are an awkward fit for the MVCC model.  Once acquired, an
-     advisory lock is held until explicitly released or the session ends.
-     Unlike standard locks, advisory locks do not
-     honor transaction semantics: a lock acquired during a
-     transaction that is later rolled back will still be held following the
+     that are an awkward fit for the MVCC model.</para>
+
+    <para>
+     There are two different types of advisory locks in
+     <productname>PostgreSQL</productname>: session level and transaction level.
+     Once acquired, a session level advisory lock is held until explicitly
+     released or the session ends.  Unlike standard locks, session level
+     advisory locks do not honor transaction semantics: a lock acquired during
+     a transaction that is later rolled back will still be held following the
      rollback, and likewise an unlock is effective even if the calling
-     transaction fails later.  The same lock can be acquired multiple times by
-     its owning process: for each lock request there must be a corresponding
-     unlock request before the lock is actually released.  (If a session
-     already holds a given lock, additional requests will always succeed, even
-     if other sessions are awaiting the lock.)  Like all locks in
-     <productname>PostgreSQL</productname>, a complete list of advisory
-     locks currently held by any session can be found in the
+     transaction fails later.  The same session level lock can be acquired
+     multiple times by its owning process: for each lock request there must be
+     a corresponding unlock request before the lock is actually released.  (If a
+     session already holds a given lock, additional requests will always succeed,
+     even if other sessions are awaiting the lock.)  Transaction level locks on
+     the other hand behave more like regular locks; they are automatically
+     released at the end of the transaction, and can not be explicitly unlocked.
+     Session and transaction level locks share the same lock space, which means
+     that a transaction level lock will prevent another session from obtaining
+     a session level lock on that same resource and vice versa.
+     Like all locks in <productname>PostgreSQL</productname>, a complete list of
+     advisory locks currently held by any session can be found in the
      <link linkend="view-pg-locks"><structname>pg_locks</structname></link>
      system view.
     </para>
@@ -1233,7 +1242,7 @@ UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;
      strategies typical of so called <quote>flat file</> data management
      systems.
      While a flag stored in a table could be used for the same purpose,
-     advisory locks are faster, avoid MVCC bloat, and are automatically
+     advisory locks are faster, avoid MVCC bloat, and can be automatically
      cleaned up by the server at the end of the session.
      In certain cases using this advisory locking method, especially in queries
      involving explicit ordering and <literal>LIMIT</> clauses, care must be