Utility Operations improvement in PostgreSQL 9.1

1. Transaction-level advisory locks:
PostgreSQL provides a means for creating locks that have application-defined meanings. These are called advisory locks, because the system does not enforce their use — it is up to the application to use them correctly.

 Till 9.0, postgreSQL had only Session level locks. Now, in PostgreSQL 9.1, we have transaction level advisory lock. Some examples are given below:

BEGIN;

-- grabbing txn locks multiple times

SELECT
        pg_advisory_xact_lock(1), pg_advisory_xact_lock(1),
        pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock_shared(2),
        pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock(1, 1),
        pg_advisory_xact_lock_shared(2, 2), pg_advisory_xact_lock_shared(2, 2);

SELECT locktype, classid, objid, objsubid, mode, granted
        FROM pg_locks WHERE locktype = 'advisory'
        ORDER BY classid, objid, objsubid;

COMMIT;

Advantage of Advisory lock are given below:
1. Advisory locks are faster
2. It avoid MVCC bloat,
 3. and It can be automatically cleaned up by the server at the end of the session.

 Due to above reason users/developers should think of using advisory lock over making Database level locks. Important Transaction advisory locks in PostgreSQL 9.1 are given below:

pg_advisory_xact_lock(key bigint)              :      Obtain exclusive transaction level advisory lock
pg_advisory_xact_lock(key1 int, key2 int)      :      Obtain exclusive transaction level advisory lock
pg_advisory_xact_lock_shared(key bigint)       :      Obtain shared transaction level advisory lock
pg_advisory_xact_lock_shared(key1 int, key2 int):     Obtain shared advisory lock for the current transaction
pg_try_advisory_xact_lock(key bigint):                Obtain exclusive transaction level advisory lock if available
pg_try_advisory_xact_lock(key1 int, key2 int:         Obtain exclusive transaction level advisory lock if available
pg_try_advisory_xact_lock_shared(key bigint):        Obtain shared transaction level advisory lock if available
pg_try_advisory_xact_lock_shared(key1 int, key2 int): Obtain shared transaction level advisory lock if available

2. New version of TRUNCATE (TRUNCATE ... RESTART IDENTITY)
 This is a new improvement in PostgreSQL 9.1. Till 9.0, "TRUNCATE … RESTART IDENTITY" used to use "ALTER SEQUENCE RESTART" in backend to rollback sequences, in which, on error between truncating and commiting, sequence may get of out of sync with the table contents. In previous approach resetting of associated sequences used to happen before TRUNCATE of table and at the time of backend crash sequence used to get out of sync.

 To fix this, in PostgreSQL 9.1, same command will create a new refilenode for a sequence with reset due to RESTART IDENTITY. If transaction aborts, then PG will automatically revert to old reflfilenode file. This approach requires exclusing lock on sequence, since TRUNCATE has already exclusive lock on TABLE, therefore having exclusive lock on associated sequence won't have any effect.

  3. COPY command improvement:

 In PostgreSQL 9.1, Now copy has ENCODING Option. ENCODING option is useful when user wants to copy data in some other ENCODING. Some example is given below:

    postgres=# COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE E'\\' ENCODING 'sql_ascii';
"Jackson, Sam","\\h"
"It is \"perfect\"."," "
"",

4. EXPLAIN VERBOSE:

 In PostgreSQL 9.1, VERBOSE Option has been included with EXPLAIN Command. Using this command user would be able to see the funcation call expression in a functionscan node. Example is given below:

   postgres=# explain (verbose,analyze) select max(sal) from emp;
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1.18..1.19 rows=1 width=5) (actual time=0.027..0.027 rows=1 loops=1)
   Output: max(sal)
   ->  Seq Scan on public.emp  (cost=0.00..1.14 rows=14 width=5) (actual time=0.008..0.012 rows=14 loops=1)
         Output: empno, ename, job, mgr, hiredate, sal, comm, deptno
 Total runtime: 0.077 ms
(5 rows)

5. New VACUUM FULL VERBOSE and CLUSTER VERBOSE

 In PostgreSQL 9.0, due to new implementation of VACUUM FULL, VERBOSE option was not giving much information. However, this is now fixed in PostgreSQL 9.1. Now VERBOSE with VACUUM FULL and CLUSTER will new information, which includes live and dead tuple and also if CLUSTER is using an index to rebuild the tablel. Some Example is given below:
CLUSTER VERBOSE Example:

postgres=# cluster verbose;
INFO:  clustering "public.emp" using sequential scan and sort
INFO:  "emp": found 0 removable, 14 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
CLUSTER
postgres=# 

VACUUM FULL VERBOSE Example

postgres=# VACUUM FULL VERBOSE emp;
INFO:  vacuuming "public.emp"
INFO:  "emp": found 0 removable, 14 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

6. autovacuum improvement.
 Now, in 9.1 autovacuum process will not wait for locked tables, on which lock already acquired by some other process. In 9.1, autovacuum process will skip locked tables and will continue for vacuuming other tables and will try to vacuum such tables later.