Saturday, December 17, 2011

SMTP Exceptions in PPAS9.0

Till PPAS 9.0, UTL_SMTP package has no specific named Exceptions for Transient_error, Invalid_operation and Permanent Error. However, Oracle provide following types of named exceptions for SMTP:
1. INVALID_OPERATION:
Raised when an invalid operation is made. In other words, calling API other than write_data(), write_raw_data() or close_data() after open_data() is called, or calling write_data(), write_raw_data() or close_data() without first calling open_data().

2. TRANSIENT_ERROR:
Raised when receiving a reply code in 400 range.

3. PERMANENT_ERROR:
Raised when receiving a reply code in 500 range.

Oracle users who use SMTP packages, they also use above exceptions extensively to track the SMTP Error/Message and perform some handling on basis of exception.

Since, till now PPAS doesn't have these exceptions, therefore people stuck on finding workaround for such exception.

To make this easy, I did some research and made following workaround, which user can use in PPAS for SMTP Exceptions as they do in Oracle.

Following are workarounds for SMTP named Exceptions:

There is no direct way to trap SMTP reply code, However PPAS SQLERRM does have description of SMTP Reply code. So using SQLERRM, user can implement or defined named Exceptions, which is pretty simple and straight.

Easiest way of doing is to create functions, which can decide if exception error is TRANSIENT, PERMANENT or INVALID_OPERATION. So, user can create following functions in PPAS:

1. Function For transient Error Message:
CREATE OR REPLACE FUNCTION SMTP_TRANSIENT_ERROR(text) RETURNS boolean
As
$$
SELECT CASE WHEN
$1 ILIKE '%Service not available%' OR 
$1 ILIKE '%Requested mail action not taken%' OR 
$1 ILIKE '%Requested action terminated%' OR
$1 ILIKE '%Requested action not taken%' OR
$1 ILIKE '%You have no mail%' OR
$1 ILIKE '%TLS not available due to temporary reason%. Encryption required for requested authentication mechanism%' OR
$1 ILIKE '%Unable to queue messages for %'  OR
$1 ILIKE '%Node%not allowed%' THEN true
ELSE false END from dual;
$$ language sql;

2. Function for Permanent Error Message:
CREATE OR REPLACE FUNCTION SMTP_PERMANENT_ERROR(text) RETURNS Boolean 
AS
$$
SELECT CASE WHEN 
$1 ILIKE '%Syntax error%command unrecognized%' OR 
$1 ILIKE '%Syntax error in parameters or arguments%' OR
$1 ILIKE '%Command not implemented%' OR
$1 ILIKE '%Bad sequence of commands%' OR
$1 ILIKE '%Command parameter not implemented%' OR
$1 ILIKE '%does not accept mail%' OR
$1 ILIKE '%Must issue a STARTTLS command first. Encryption required for requested authentication mechanism%' OR
$1 ILIKE '%Authentication mechanism is too weak%' OR
$1 ILIKE '%Encryption required for requested authentication mechanism%' OR
$1 ILIKE '%Requested action not taken%' OR
$1 ILIKE '%User not local; please try%' OR 
$1 ILIKE '%Requested mail action terminated: exceeded storage allocation%' OR
$1 ILIKE '%Requested action not taken:%' OR
$1 ILIKE '%Transaction failed%' THEN true ELSE false END FROM DUAL;
$$ language sql
3. Function for INVALID_OPERATION
CREATE OR REPLACE FUNCTION SMTP_INVALID_OPERATION(TEXT) RETURNS BOOL
AS
$$ SELECT CASE WHEN $ ILIKE '%INVALID%OPERATION%STATE%' THEN TRUE ELSE FALSE END FROM DUAL;
$$ Language sql;

Below are some working examples:

In Oracle (INVALID_OPERATION) :
CREATE OR REPLACE PROCEDURE SEND_MAIL (
 msg_to      varchar2,
 msg_subject varchar2,
 msg_text    varchar2   )
IS
 c  utl_smtp.connection;
 rc integer;
 msg_from    varchar2(50) := 'Oracle9.2';
 mailhost    VARCHAR2(30) := '127.0.0.1';   -- local database host

BEGIN
 c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25    
 utl_smtp.helo(c, mailhost);
 utl_smtp.mail(c, msg_from);
 utl_smtp.rcpt(c, msg_to);
utl_smtp.write_data(c, 'MESSG'||UTL_TCP.CRLF); --Added By me

 utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
                        'To: ' || msg_to || utl_tcp.crlf ||
                        'Subject: ' || msg_subject ||
                         utl_tcp.crlf || msg_text);
 utl_smtp.quit(c);

 EXCEPTION
   WHEN UTL_SMTP.INVALID_OPERATION THEN
      dbms_output.put_line(' Invalid Operation in Mail attempt    
                             using UTL_SMTP.');
   WHEN UTL_SMTP.TRANSIENT_ERROR THEN
      dbms_output.put_line(' Temporary e-mail issue - try again'); 
   WHEN UTL_SMTP.PERMANENT_ERROR THEN
      dbms_output.put_line(' Permanent Error Encountered.'); 
END;

SQL> exec send_mail(msg_to=>'vibhor.aim@gmail.com', -
              msg_subject => 'Hello from Oracle', -
              msg_text    => 'This is the body of the message'-
           );
Invalid Operation in Mail attempt
         using UTL_SMTP.

PL/SQL procedure successfully completed.

In PPAS:
CREATE OR REPLACE PROCEDURE SEND_MAIL (
 msg_to      varchar2,
 msg_subject varchar2,
 msg_text    varchar2   )
IS
 c  utl_smtp.connection;
 rc integer;
 msg_from    varchar2(50) := 'PPAS 9.0';
 mailhost    VARCHAR2(30) := '127.0.0.1';   -- local database host

BEGIN
 c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25    
 utl_smtp.helo(c, mailhost);
 utl_smtp.mail(c, msg_from);
 utl_smtp.rcpt(c, msg_to);
utl_smtp.write_data(c, 'MESSG'||UTL_TCP.CRLF); --Added By me

 utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
                        'To: ' || msg_to || utl_tcp.crlf ||
                        'Subject: ' || msg_subject ||
                         utl_tcp.crlf || msg_text);
 utl_smtp.quit(c);

 EXCEPTION
   WHEN OTHERS THEN
          IF SMTP_INVALID_OPERATION(SQLERRM) THEN
         DBMS_OUTPUT.PUT_LINE('Invalid Operation in Mail attempt using UTL_SMTP.');
         ELSIF SMTP_TRANSIENT_ERROR(SQLERRM) THEN
              DBMS_OUTPUT.PUT_LINE(' Temporary e-mail issue - try again'); 
         ELSIF SMTP_PERMANENT_ERROR(SQLERRM) THEN
              DBMS_OUTPUT.PUT_LINE(' Permanent Error Encountered.');
         ELSE
            DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE||' Error Message: '||SQLERRM);
        END IF;
END;


EDB-SPL Procedure successfully completed
edb=# exec send_mail(msg_to=>'vibhor.aim@gmail.com', 
edb(#                msg_subject => 'Hello from PPAS', 
edb(#                msg_text    => 'This is the body of the message');
Invalid Operation in Mail attempt using UTL_SMTP.

EDB-SPL Procedure successfully completed


Example 2: In Oracle (UTL_SMTP.TRANSIENT_ERROR ):
CREATE OR REPLACE PROCEDURE SEND_MAIL (
 msg_to      varchar2,
 msg_subject varchar2,
 msg_text    varchar2   )
IS
 c  utl_smtp.connection;
 rc integer;
 msg_from    varchar2(50) := 'Oracle9.2';
 mailhost    VARCHAR2(30) := '192.168.23.25';   -- local database host

BEGIN
 c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25    
 utl_smtp.helo(c, mailhost);
 utl_smtp.mail(c, msg_from);
 utl_smtp.rcpt(c, msg_to);
utl_smtp.write_data(c, 'MESSG'||UTL_TCP.CRLF); --Added By me

 utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
                        'To: ' || msg_to || utl_tcp.crlf ||
                        'Subject: ' || msg_subject ||
                         utl_tcp.crlf || msg_text);
 utl_smtp.quit(c);

 EXCEPTION
   WHEN UTL_SMTP.INVALID_OPERATION THEN
      dbms_output.put_line(' Invalid Operation in Mail attempt    
                             using UTL_SMTP.');
   WHEN UTL_SMTP.TRANSIENT_ERROR THEN
      dbms_output.put_line(' Temporary e-mail issue - try again'); 
   WHEN UTL_SMTP.PERMANENT_ERROR THEN
      dbms_output.put_line(' Permanent Error Encountered.'); 
END;



SQL> exec send_mail(msg_to=>'vibhor.aim@gmail.com', -
              msg_subject => 'Hello from Oracle', -
              msg_text    => 'This is the body of the message'-
           );> > > 
Temporary e-mail issue - try again

PL/SQL procedure successfully completed.
In PPAS:
CREATE OR REPLACE PROCEDURE SEND_MAIL (
 msg_to      varchar2,
 msg_subject varchar2,
 msg_text    varchar2   )
IS
 c  utl_smtp.connection;
 rc integer;
 msg_from    varchar2(50) := 'PPAS 9.0';
 mailhost    VARCHAR2(30) := '192.168.23.25';   -- local database host

BEGIN
 c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25    
 utl_smtp.helo(c, mailhost);
 utl_smtp.mail(c, msg_from);
 utl_smtp.rcpt(c, msg_to);
utl_smtp.write_data(c, 'MESSG'||UTL_TCP.CRLF); --Added By me

 utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
                        'To: ' || msg_to || utl_tcp.crlf ||
                        'Subject: ' || msg_subject ||
                         utl_tcp.crlf || msg_text);
 utl_smtp.quit(c);

 EXCEPTION
   WHEN OTHERS THEN
          IF SMTP_INVALID_OPERATION(SQLERRM) THEN
         DBMS_OUTPUT.PUT_LINE('Invalid Operation in Mail attempt using UTL_SMTP.');
         ELSIF SMTP_TRANSIENT_ERROR(SQLERRM) THEN
              DBMS_OUTPUT.PUT_LINE(' Temporary e-mail issue - try again'); 
         ELSIF SMTP_PERMANENT_ERROR(SQLERRM) THEN
              DBMS_OUTPUT.PUT_LINE(' Permanent Error Encountered.');
         ELSE
            DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE||' Error Message: '||SQLERRM);
        END IF;
END;


edb=# exec send_mail(msg_to=>'vibhor.aim@gmail.com', 
edb(#                msg_subject => 'Hello from PPAS', 
edb(#                msg_text    => 'This is the body of the message');
Temporary e-mail issue - try again

EDB-SPL Procedure successfully completed
edb=# 
Have fun!

Friday, December 16, 2011

Postgres Plus Advanced Server 9.1 Beta 1

PPAS 9.1 Beta 1 is released. So, I thought to write about it and New Features which are coming.

Best part of PPAS 9.1 is that it has all features of PostgreSQL 9.1, which I have already discribed in my series of 9.1. Links are given below:
1. PostgreSQL Object Manipulation Features
2. New Replication and Recovery Features
3. Queries improvement of Core PostgreSQL 9.1
4. Updateable Views using INSTEADOF Trigger in PostgreSQL 9.1
5. Utility Operation Improvements
6. Upsert/Merge using Writeable CTE
7. New Functions/Improvement
8. Object Functions Improvement in PostgreSQL 9.1
9. Client Application Improvement in PostgreSQL 9.1

10. Asynchronous/Synchronous Replication


There are many other features in PostgreSQL 9.1, which requires some time to write with example. However, below is list of those Improvements:
1. Unlogged Tables
2. FOREACH IN ARRAY in PL/pgSQL
3. Pl/Perl Improvement:
    a. Record type support
    b. Pl/perl array argument map to Perl Argument.

    c. Pl/perl Composite-type array to Perl Hash.
4. Pl/Python Improvement:
   a. Table Support in Pl/Python Function
   b. New Validator for Pl/Python
   c. SQL Exception handling in Pl/Python Exception Blocks
   d. Subtransactions in Pl/Python
   e. New Pl/Ptyhon functions for Quoting String (plpy.quote_ident, plpy.quote_literal, and plpy.quote_nullable)
   f. Traceback support for Plpython errors
   g. Exception Handling in Python3
4. ECPG Improvements:
   a. WHERE CURRENT OF in Dynamic Cursor
   b. Double digit with percision of 15
5. Libpq Improvements:
   a. client_encoding option in Connection
   b. PQlibVersion() function for knowing Version of Library
   c. PQping and PQpingParams for knowing Server Status.

With Above Feature PPAS 9.1 has additional Features and Improvement, which users would like to try. List of those Features are given below:
1.VPD (Virtual Private Database) Support
2. Parition Syntax Support :
    CREATE TABLE employees
  (empno     numeric(4,0),
   ename     varchar(10),
   job       varchar(9),
   hiredate  timestamp,
   )
PARTITION BY RANGE (hiredate)
  (PARTITION ten_year_staff
     VALUES LESS THAN('01-JAN-2001'),
  (PARTITION five_year_staff
     VALUES LESS THAN('01-JAN-2006'),
  (PARTITION one_year_staff
     VALUES LESS THAN('01-JAN-2011'));
CREATE TABLE employees
  (empno     numeric(4,0),
   ename     varchar(10),
   job       varchar(9),
   hiredate  timestamp,
)
PARTITION BY RANGE (hiredate)
SUBPARTITION BY LIST (job)
(PARTITION ten_year_staff VALUES LESS THAN('01-JAN-2001)
(SUBPARTITION mgmt VALUES ('MANAGER', 'PRESIDENT'), SUBPARTITION sales VALUES ('SALESMAN', 'CLERK'), SUBPARTITION support VALUES ('ANALYST')),
(PARTITION five_year_staff VALUES LESS THAN('01-JAN-2006') (SUBPARTITION mgmt VALUES ('MANAGER', 'PRESIDENT'),
      SUBPARTITION sales   VALUES ('SALESMAN', 'CLERK'),
SUBPARTITION support VALUES ('ANALYST')),
(PARTITION one_year_staff VALUES LESS THAN('01-JAN-2011')
(SUBPARTITION mgmt VALUES ('MANAGER', 'PRESIDENT'), SUBPARTITION sales VALUES ('SALESMAN', 'CLERK'), SUBPARTITION support VALUES ('ANALYST'));

etc.
3. HextoRaw and RAWToHEX function support
4. New Hint: ORDERED
5. Ref Cursor as OUT in Procedure and Functions
6. WHEVEVER SQLERROR Syntax Sypport
7. Advanced Server Performance
   a. Improvement in Lock Management for Read and Write workloads with more CPUs
   b. Index Advisor Support for Composite Indexes
8. New Catalog Views for Partition Table
9. OCI Function: OCIBreak() and OCIReset() for sever connections on Blocking and nonBlocking Mode.

I would be discussing more about above features in coming Blogs. So, stay tune!

Monday, December 5, 2011

PostgreSQL 9.1:: New in Object Manipulation

In Series of writing Blog on New features in PostgreSQL 9.1, I thought to write and give example on Object Manipulation Changes in 9.1. Let see what we have in Object Manipulation Features in 9.1

1. CREATE/ALTER/DROP EXTENSIONS
PostgreSQL 9.1 has different way of managing Contrib Modules. User can now install Contrib Module using Command as given below:
psql -c "CREATE EXTENSION pgcrypto;" postgres
CREATE EXTENSION
If user wants to keep its contrib/Extension module in some other schema then user can try ALTER Command as given below:
postgres=# CREATE schema contrib_modules;
CREATE SCHEMA
postgres=# alter extension pgcrypto set schema contrib_modules;
ALTER EXTENSION

Simlarly, ALTER EXTENSION has many options, some options are given below:
Description: change the definition of an extension
Syntax:
ALTER EXTENSION extension_name UPDATE [ TO new_version ]
ALTER EXTENSION extension_name SET SCHEMA new_schema
ALTER EXTENSION extension_name ADD member_object
ALTER EXTENSION extension_name DROP member_object

where member_object is:

  AGGREGATE agg_name (agg_type [, ...] ) |
  CAST (source_type AS target_type) |
  COLLATION object_name |
  CONVERSION object_name |
  DOMAIN object_name |
  FOREIGN DATA WRAPPER object_name |
  FOREIGN TABLE object_name |
  FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) |
  OPERATOR operator_name (left_type, right_type) |
  OPERATOR CLASS object_name USING index_method |
  OPERATOR FAMILY object_name USING index_method |
  [ PROCEDURAL ] LANGUAGE object_name |
  SCHEMA object_name |
  SEQUENCE object_name |
  SERVER object_name |
  TABLE object_name |
  TEXT SEARCH CONFIGURATION object_name |
  TEXT SEARCH DICTIONARY object_name |
  TEXT SEARCH PARSER object_name |
  TEXT SEARCH TEMPLATE object_name |
  TYPE object_name |
  VIEW object_name

Advantages of having this feature are given below:
i. Easy to find installed Contrib Modules and its new operators, and new index operator classes.
Following query can be use to find the installed contrib modules:
SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description"
FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
ORDER BY 1;
Short command of psql is given below (with snapshot):
postgres=# \dx
                                       List of installed extensions
   Name    | Version |     Schema      |                            Description                            
-----------+---------+-----------------+-------------------------------------------------------------------
 adminpack | 1.0     | pg_catalog      | administrative functions for PostgreSQL
 file_fdw  | 1.0     | public          | foreign-data wrapper for flat file access
 pg_trgm   | 1.0     | public          | text similarity measurement and index searching based on trigrams
 pgcrypto  | 1.0     | contrib_modules | cryptographic functions
 plperl    | 1.0     | pg_catalog      | PL/Perl procedural language
 plperlu   | 1.0     | pg_catalog      | PL/PerlU untrusted procedural language
 plpgsql   | 1.0     | pg_catalog      | PL/pgSQL procedural language
 xml2      | 1.0     | public          | XPath querying and XSLT
(8 rows)
ii. pg_dump command knows about all objects of the extension and it will just include a CREATE EXTENSION command in dumps, at the place of increasing size of dump file by including DDLs of objects of module, which will be helpful in migration to new version.
iii. Extension won't allow drop individual object of an extension, except DROP EXTENSION.

For more detail on Extension, I would recommend to go through following link:
http://www.postgresql.org/docs/9.1/static/extend-extensions.html
2. Foreign Tables.
This is a new features which has been introduced in 9.1. Foreign tables features gives ability to user for accessing data outside of database. Simple Example is, if you have data in a file on server and you want to access that data inside postgreSQL, then you can create a foreign tables to access the data of file in PostgreSQL.
For using this feature, there are multiple extensions available. For accessing of data from a file default extension file_fdw comes with PostgreSQL installation. However, there are other extension also available for accessing data from Different Databases like Oracle, MySQL, CouchDB, Redis, Twitter etc. User can get those extension and details from following link:
http://wiki.postgresql.org/wiki/Foreign_data_wrappers
Below is an example of using file_fdw for accessing data from plain file:
i. Create file_fdw extension as given below:
psql -c "CREATE EXTENSION file_fdw" -d postgres
CREATE EXTENSION
ii. Create foreign Data wrapper as given below:
psql -c "CREATE FOREIGN DATA WRAPPER file_data_wrapper HANDLER file_fdw_handler;" -d postgresCREATE FOREIGN DATA WRAPPER
iii. Create Server using Foreign data wrapper, as given below:
psql -c " CREATE SERVER file FOREIGN DATA WRAPPER file_fdw;" -d postgres
CREATE SERVER
iv. Now, lets create foreign table to access file: my_data.file content is given below:
File: my_data.file
1,3,'First Row'
4,5,'Second Row'
7,8,'Third Row'
9,10,'Fourth Row'
11,12,'Fifth Row'

psql -c "CREATE FOREIGN TABLE my_data(field1 numeric, field2 numeric, field_detail text) server file options (filename '/Users/postgres/my_data.file', format 'csv', delimiter ',');" -d postgres
Now, we have foreign table which we can use to access data of my_data.file in database as given below:
postgress-MacBook-Pro:~ postgres$ psql -c "select * from my_data;" -d postgres
 field1 | field2 | field_detail 
--------+--------+--------------
      1 |      3 | 'First Row'
      4 |      5 | 'Second Row'
      7 |      8 | 'Third Row'
      9 |     10 | 'Fourth Row'
     11 |     12 | 'Fifth Row'
(5 rows)
3. Improvement in ALTER TYPE for ENUM.

Till 9.0, ALTER TYPE for ENUM requires dropping of dependent columns and then after modification of ENUM recreating of those dependent columns, which was big block for modifying of ENUM.
Now in 9.1, user can modify ENUM without dropping dependent columns as given below:
postgres=# CREATE TYPE allowed_color AS ENUM ('RED','WHITE');
CREATE TYPE
postgres=# CREATE TABLE possible_color(color allowed_color);
CREATE TABLE
postgres=# INSERT INTO possible_color VALUES('RED');
INSERT 0 1
postgres=# INSERT INTO possible_color VALUES('WHITE');
INSERT 0 1
postgres=# INSERT INTO possible_color VALUES('BLACK');
ERROR:  invalid input value for enum allowed_color: "BLACK"
LINE 1: INSERT INTO possible_color VALUES('BLACK');
                                          ^
postgres=# ALTER TYPE allowed_color ADD VALUE 'BLACK' AFTER 'WHITE';
ALTER TYPE
postgres=# INSERT INTO possible_color VALUES('BLACK');
INSERT 0 1
postgres=# SELECT * FROM possible_color ;
 color 
-------
 RED
 WHITE
 BLACK
(3 rows)

4. ALTER TYPE ADD/DROP/ALTER/RENAME ATTRIBUTE for composite types:

In 9.1, users would be able to do modification in composite type using ALTER TYPE Command. Example is given below:

i. Create a composite type:
postgres=# CREATE TYPE test_alter_type AS (col1 int);
CREATE TYPE
ii. Create a table based on composite type created above:
postgres=# CREATE TABLE test_alter_type_table (initial_col test_alter_type);
CREATE TABLE
postgres=# \d test_alter_type_table 
   Table "public.test_alter_type_table"
   Column    |      Type       | Modifiers 
-------------+-----------------+-----------
 initial_col | test_alter_type | 

postgres=# INSERT INTO test_alter_type_table VALUES(ROW(1));
INSERT 0 1
postgres=# INSERT INTO test_alter_type_table VALUES(ROW(2));
INSERT 0 1
postgres=# SELECT * FROM test_alter_type_table ;
 initial_col 
-------------
 (1)
 (2)
(2 rows)
iii. ALTER composite type to add new attribute
postgres=# ALTER TYPE test_alter_type ADD ATTRIBUTE col2 int;
ALTER TYPE
postgres=# SELECT * FROM test_alter_type_table ;
 initial_col 
-------------
 (1,)
 (2,)
(2 rows)
iv. Create a function for Composite type to assign a Values to composite type as given below:
CREATE FUNCTION test_alter_type_constructor(test_alter_type) RETURNS test_alter_type
AS
$$                                                                                                                                   SELECT $1::test_alter_type;
$$ language sql;
v. Update values in added attribute of Composite type
postgres=# UPDATE test_alter_type_table SET initial_col=ROW(1,2) WHERE initial_col=test_alter_type_constructor(ROW(1,NULL));      
UPDATE 1
postgres=# UPDATE test_alter_type_table SET initial_col=ROW(2,2) WHERE initial_col=test_alter_type_constructor(ROW(2,NULL));
UPDATE 1
postgres=# SELECT * FROM test_alter_type_table;
 initial_col 
-------------
 (1,2)
 (2,2)
(2 rows)
vi. RENAME Attribute and Composite type as given below:
postgres=# ALTER TYPE test_alter_type RENAME ATTRIBUTE col2 to col3;
ALTER TYPE
postgres=# \d test_alter_type
Composite type "public.test_alter_type"
 Column |  Type   | Modifiers 
--------+---------+-----------
 col1   | integer | 
 col3   | integer | 


postgres=# ALTER TYPE test_alter_type RENAME TO test_alter_type3;
ALTER TYPE
postgres=# \d test_alter_type3
Composite type "public.test_alter_type3"
 Column |  Type   | Modifiers 
--------+---------+-----------
 col1   | integer | 
 col3   | integer | 

postgres=# \d test_alter_type
test_alter_type3      test_alter_type_table
postgres=# \d test_alter_type_table 
    Table "public.test_alter_type_table"
   Column    |       Type       | Modifiers 
-------------+------------------+-----------
 initial_col | test_alter_type3 | 

For more detail on supported options of ALTER TYPE for Composite types, user can use following link:
http://www.postgresql.org/docs/9.1/static/sql-altertype.html

5. ALTER TYPE {OF|NOT OF} support for Typed Table.
This is a new sytax which has been added in PostgreSQL 9.1. Using this syntax user can now switch a standalone table into typed table and can switch typed table into standalone. Example is given below:
postgres=# CREATE TABLE table_type_test(id NUMERIC, test TEXT);
CREATE TABLE

postgres=# CREATE TYPE test_type AS (id NUMERIC, test TEXT);
CREATE TYPE
postgres=# ALTER TABLE table_type_test OF test_type;
ALTER TABLE
postgres=# \d table_type_test 
Table "public.table_type_test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | numeric | 
 test   | text    | 
Typed table of type: test_type
postgres=# ALTER TABLE table_type_test NOT OF;
ALTER TABLE
postgres=# \d table_type_test
Table "public.table_type_test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | numeric | 
 test   | text    | 

6. Supported Syntax of ALTER SET SCHEMA for other objects

In PostgreSQL 9.1, ALTER SET SCHEMA is supported for following:
a. conversions, 
  b. operators, 
  c. operator classes, 
  d. operator families, 
  e. text search configurations, 
  f. text search dictionaries, 
  g. text search parsers
  h. text search templates.

Simple example is given below:
postgres=# ALTER OPERATOR %(text,text) SET SCHEMA contrib_modules;
ALTER OPERATOR
postgres=# \do contrib_modules.%
                                  List of operators
     Schema      | Name | Left arg type | Right arg type | Result type | Description 
-----------------+------+---------------+----------------+-------------+-------------
 contrib_modules | %    | text          | text           | boolean     | 
(1 row)
postgres=# ALTER OPERATOR contrib_modules.%(text,text) SET SCHEMA public;
ALTER OPERATOR
7. Modify UNIQUE/Primary KEY using Unique Index.

This is a very interesting feature which has been added in PostgreSQL 9.1.
Till 9.0, user used to use REINDEX command for Rebuilding indexes of Primary/Unique Keys OR ALTER TABLE ADD CONSTRAINT for adding Primary/Unique Key, which used to keep TABLE Level lock for long time.
Now, in 9.1, User can use CREATE UNIQUE INDEX CONCURRENTLY command to create unique index and would be able to use created index for Primay/Unique Key without having Table Level Lock for long time. Example is given below

For Adding Primary Key:
postgres=# CREATE UNIQUE INDEX CONCURRENTLY emp_pk_idx ON emp_new(empno);
CREATE INDEX
postgres=# ALTER TABLE emp_new ADD PRIMARY KEY USING INDEX emp_pk_idx;
ALTER TABLE
postgres=# \d emp_new;
            Table "public.emp_new"
  Column  |         Type          | Modifiers 
----------+-----------------------+-----------
 empno    | numeric(4,0)          | not null
 ename    | character varying(10) | 
 job      | character varying(9)  | 
 mgr      | numeric(4,0)          | 
 hiredate | date                  | 
 sal      | numeric(7,2)          | 
 comm     | numeric(7,2)          | 
 deptno   | numeric(2,0)          | 
Indexes:
    "emp_pk_idx" PRIMARY KEY, btree (empno)

Now, suppose user wants to Rebuild Primary Key Index, then he can try something like given below:
CREATE UNIQUE INDEX CONCURRENTLY emp_pk_idx_new ON emp_new(empno);
  BEGIN;
  ALTER TABLE emp_new DROP CONSTRAINT emp_pk_idx;
  ALTER TABLE emp_new ADD PRIMARY KEY USING INDEX emp_pk_idx_new;
  END;
Above Lock period would be less than rebuilding Index emp_pk_idx index.

Similar kind of idea, I had presented in my old post for PostgreSQL 8.4, which link is given below:
http://vibhorkumar.wordpress.com/2010/12/16/rebuilding-pkey-and-indexes-without-locking-table-in-pg-8-4/

8. Foreign Key without Validation.

9.1 also supports adding foreign key on table without validation of old data, which means the constraint is checked for new or modified rows, but existing data may violate the constraint.
Note:: Converting a NO VALID constraint to VALID may take a long time depending on the amount of data to be validated.
Example is given below:
CREATE TABLE emp_fk_key_table(id NUMERIC);
CREATE TABLE
Lets insert Few Data in Table:
postgres=# INSERT INTO emp_fk_key_table VALUES(1);
INSERT 0 1
postgres=# INSERT INTO emp_fk_key_table VALUES(2);
INSERT 0 1
postgres=# INSERT INTO emp_fk_key_table VALUES(3);
INSERT 0 1
Now, Create Foreign Key with NOT VALID Option:
ALTER TABLE emp_fk_key_table ADD CONSTRAINT fk_constraint FOREIGN KEY(id) REFERENCES emp_new(empno) NOT VALID;
Above Constraint will be checked for new data as given below:
postgres=# INSERT INTO emp_fk_key_table VALUES(5);
ERROR:  insert or update on table "emp_fk_key_table" violates foreign key constraint "fk_constraint"
DETAIL:  Key (id)=(5) is not present in table "emp_new".
For Old data Constraint Check will not happen. However, later if user wants to validate old data in table, then he/she can use VALIDATE CONSTRAINT option as given below:
ALTER TABLE emp_fk_key_table VALIDATE CONSTRAINT fk_constraint;

9. No rewrite in appropriate cases of Change in Datatype

Till 9.0, Any change in data type ALTER TABLE SET DATA TYPE command used to re-write whole table. Now in 9.1, Data type change may not result in re-writing of table, which will result in Fast execution of ALTER TABLE SET DATA TYPE command.
Example is given below:

In 9.0:
postgres=# CREATE TABLE test_rewrite(col VARCHAR(10));
CREATE TABLE
postgres=# 
postgres=# INSERT INTO test_rewrite SELECT * FROM generate_series(1,100);
INSERT 0 100
postgres=# SELECT relfilenode, relname FROM pg_class WHERE relname='test_rewrite';
 relfilenode |   relname    
-------------+--------------
       31318 | test_rewrite
(1 row)
postgres=# ALTER TABLE test_rewrite ALTER COLUMN col SET DATA TYPE TEXT;
ALTER TABLE
postgres=# SELECT relfilenode, relname FROM pg_class WHERE relname='test_rewrite';
 relfilenode |   relname    
-------------+--------------
       31324 | test_rewrite     ## Re-write result in new relfilenode
(1 row)

In PostgreSQL 9.1
postgres=# CREATE TABLE test_rewrite(col VARCHAR(10));
CREATE TABLE
postgres=# INSERT INTO test_rewrite SELECT * FROM generate_series(1,100);
INSERT 0 100
postgres=# SELECT relfilenode, relname FROM pg_class WHERE relname='test_rewrite';
 relfilenode |   relname    
-------------+--------------
       35164 | test_rewrite
(1 row)

postgres=# ALTER TABLE test_rewrite ALTER COLUMN col SET DATA TYPE TEXT;
ALTER TABLE
postgres=# SELECT relfilenode, relname FROM pg_class WHERE relname='test_rewrite';
 relfilenode |   relname    
-------------+--------------
       35164 | test_rewrite
(1 row)

10. IF NOT EXISTS in CREATE TABLE
This is a new option which has been added in CREATE TABLE Command. Now, user can use IF NOT EXISTS clause in CREATE TABLE command, which will not throw/show any error message if table exists. Example is given below:
postgres=# CREATE TABLE IF NOT EXISTS test_rewrite(col INTEGER);
NOTICE:  relation "test_rewrite" already exists, skipping
CREATE TABLE
Above is very useful for Programming perspective, where developer needs to verify everytime if table exists or not and accordingly create table or skip table. Following is an example of such scenario.
In 9.0,
DO $$
 DECLARE
   verify boolean;
 BEGIN
   SELECT CASE WHEN count(*) = 1 THEN true ELSE false END INTO verify FROM pg_tables WHERE schemaname='enterprisedb' AND tablename='test_rewrite';
    IF verify THEN
       RAISE NOTICE 'test_rewrite TABLE EXISTS';
    ELSE
       CREATE TABLE test_rewrite(col NUMERIC(20));
    END IF;
  END
$$ language plpgsql;


NOTICE:  test_rewrite TABLE EXISTS
DO
In 9.1, User doesn't have to write code like above, simple CREATE TABLE IF NOT EXISTS will work.