Database Technologies

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

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

About updateable views user ask many times. Is it supported in PostgreSQL? Can we write Complex updateable views? Answer for above is yes . Till 9.0, we have to use RULE for implementing updateable view. Again, RULE Implementation used to be a bit work, since user has to write multiple RULES to implement this feature. Following code can be use to see this. CREATE TABLE person_detail(pid NUMERIC PRIMARY KEY, pname TEXT); CREATE TABLE person_job(pid NUMERIC PRIMARY KEY references person_detail(pid), job TEXT); INSERT INTO person_detail VALUES(1,'Angela'); INSERT INTO person_detail VALUES(2,'Tom'); INSERT INTO person_detail VALUES(3,'Heikki'); INSERT INTO person_job VALUES(1,'Documenter'); INSERT INTO person_job VALUES(2,'Developer'); INSERT INTO person_job VALUES(3,'Commiter'); CREATE VIEW person_detail_job_vw AS SELECT p.pid, p.pname, j.job FROM person_detail p LEFT JOIN person_job j ON (j.pid=p.pid); SELECT * FROM person_detail

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

There is always discussion of having UPSERT/MERGE in postgresql. Since, oracle and other RDBMS has this feature, therefore people ask about this feature in PostgeSQL. In previous version of PostgreSQL, we used to implement it using functions. Now in PostgreSQL 9.1, user can implement this feature using Writable CTE. PostgreSQL 9.1, now has Writable CTE using WTH. WITH provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query. Each auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE, or DELETE; and the WITH clause itself is attached to a primary statement that can also be a SELECT, INSERT, UPDATE, or DELETE. Lets see how we can use Writable CTE for UPSERT. Following are SQL Code which can be use in Oracle and PostgreSQL for creating sample data: For oracle: create table myTable (pid numb

1. SQL function format(text, …):  This function is similar to the C function sprintf; However only the following conversion specifications are recognized: %s interpolates the corresponding argument as a string %I escapes its argument as an SQL identifier %L escapes its argument as an SQL literal %% outputs a literal %. A conversion can reference an explicit parameter position by preceding the conversion specifier with n$, where n is the argument position Some Examples are given below: postgres=# select format('%1$s %3$s', 1, 2, 3); format -------- 1 3 (1 row) postgres=# select format('Hello %s', 'World'); format ------------- Hello World (1 row) postgres=# select format('Hello %s %1$s %s', 'World', 'Hello again'); format ------------------------------- Hello World World Hello again (1 row) 2. New string functions concat(), concat_ws(), left(), right(), and reverse() (i). concat() function: