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.