Database Technologies

Postgres Plus Advanced Server 9.3 , released on Tuesday for general availability, contains all of the innovation that was part of PostgreSQL 9.3, which was released in September. These features include the following: 1. Configuration directive 'include_dir' 2. COPY FREEZE for more efficient bulk loading 3. Custom Background Workers 4. Data Checksums 5. JSON: Additional functionality 6. LATERAL JOIN 7. Parallel pg_dump for faster backups 8. 'pg_isready' server monitoring tool 9. Switch to Posix shared memory and mmap() 10. Event Triggers 11. VIEW Features:                    Materialized Views                    Recursive View Syntax                    Updatable Views 12. Writeable Foreign Tables                    postgres_fdw 13. Replication Improvements                     Streaming-Only Remastering                     Fast Failover                     Architecture-Independent Streaming                     pg_basebackup conf setup For Postgre

In series of writing further on upcoming features in 9.3, I thought about including new improvements coming in data types in PostgreSQL. 1. Increase the maximum length of large objects from 2GB to 4TB PostgreSQL has support of Large Objects from starting. However the limit of large objects in PostgreSQL was limited to 2GB. From 9.3 onwards, PostgreSQL can store large objects up to 4TB. Thats happened due to lifting the limitation of API for large object. APIs like lo_seek(),lo_tell cannot return over 2GB offset and main reason was offset parameters defined for these function is of 4 bytes and results length provided by these functions is 4 bytes. If user do the calculation, he can see 2^31-1=2GB, it resulted in 2GB. To overcome from this limitation, PostgreSQL is coming with new APIs: lo_seek64 and lo_tell64 functions. Libpq interface will check if those lo_tell64/seek64 exits then use it or use the 32 bit of lo_seek/lo_tell functions, this way compatibility has been maintain

Good News Postgres Plus Advanced Server Beta Version is now availabale, which has all the new feature of PostgreSQL 9.2, and it also has new features which are specific to Advanced Server. I will cover/explain New features of PostgreSQL 9.2 later as per release Notes. However, the following link covers major features of PostgreSQL 9.2 and can be taken as reference. http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.2 In this Blog, I am going to cover only specific features of Advanced Server 9.2 Core. Which are: 1. INSERT APPEN HINT in PPAS 9.2 PPAS 9.2, now supports INSERT append hint. This is a very interesting feature and it is very useful for users who frequently delete records in Bulk and do bulk of INSERTs. This hint can provide some benefits in INSERTs. This hint makes PPAS not use Free Space Map and Append the rows at the end of relation(table). Its usage is given below: INSERT /*+append*/ INTO tab21115 VALUES(1,'abc',sysdate); 2. Procedure Called l

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 Feature s 2. New Replication and Recovery Feature s 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.

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

1. Streaming Replication and Continuous Archiving Features. a. Synchronous Replication PostgreSQL 9.1 came with Synchronous Replication. In Synchronous Replication, all commited transaction will be transferred to standby synchronously. When Primary is in Synchronous replication, then each commit transaction will wait untill transaction get transfered to Slave/Replication Server. This reduces the loss of any committed transaction. And gives high degree of durability. For setting up synchronous replication you can look at my blog: http://vibhorkumar.wordpress.com/2011/10/20/asynchronoussynchronous-streaming-replication-in-postgresql-9-1/ 2. New parameter replication_timeout:  This is a new parameter which has been added in 9.1. Using this parameter user can terminate replication connection, if connection is inactive more than replication_timeout seconds. This is useful parameter for Primary Server to detect Replication Server Crash or network outage. 3. New role/permission

Slony 2.1 has been released on 19th Oct. 2011. So, I thought to look at some important improvement done in this new release, which can make users life easier. Before discussing about the changes, lets setup slony replication. Following Codes can be use for setting up slony replication. #### Preable Scripts: cluster name=slonytest; NODE 1 ADMIN CONNINFO = 'dbname=postgres host=localhost user=postgres port=5432 password=postgres'; NODE 2 ADMIN CONNINFO = 'dbname=repdb host=localhost user=postgres port=5432 password=postgres'; Adding Node script: ### create_nodes.slonik include ; init cluster (id=1, comment='slonytest node 1'); store node (id=2, comment='slonytest subscriber node 2', event node=1); Storing Path: ### store_paths.slonik include ; STORE PATH (SERVER=1, CLIENT=2, CONNINFO='dbname=postgres host=localhost user=postgres port=5432 password=Empid#042'); STORE PATH (SERVER=2, CLIENT=1, CONNINFO='dbname=repdb host=localhost us

1. True serializable isolation level:  Before version 9.1, PostgreSQL did not support a full serializable isolation level. A request for serializable transaction isolation actually provided snapshot isolation. This has well known anomalies which can allow inconsistent views of the data during concurrent transactions; although these anomalies only occur when certain patterns of read-write dependencies exist within a set of concurrent transactions. Where these patterns exist, the anomalies can be prevented by introducing conflicts through explicitly programmed locks or otherwise unnecessary writes to the database. Snapshot isolation is popular because performance is better than serializable isolation and the integrity guarantees which it does provide allow anomalies to be avoided or managed with reasonable effort in many environments.  Following is a link on more details: http://wiki.postgresql.org/wiki/SSI 2. INSERT/UPDATE/DELETE in WITH CLAUSE.  Now in 9.1, User would be able i