Database Technologies

This has been asked me many times. Many users has asked me how they can create READ-ONLY user, READ-ONLY Database or How they can create backup user which can be use only for backup (no modification) Well answer is in parameter called default_transaction_read_only. If you want to make a user READ-ONLY, then you can follow steps given below: 1. CREATE normal user. 2. Use ALTER USER command to set this parameter for this user as given below: ALTER USER set default_transaction_read_only = on; 3. GRANT SELECT to this user for tables/object using GRANT. And you are good to go. Below is snapshot: postgres=# create user readonly password 'test'; CREATE ROLE postgres=# alter user readonly set default_transaction_read_only = on; ALTER ROLE postgres=# GRANT select on employees to readonly; GRANT edbs-MacBook-Pro:data postgres$ psql -U readonly -W Password for user readonly: psql (9.1.1) Type "help" for help. postgres=> select * from employees ; employee_name | e...

In Series of New Features in Advanced Server 9.1, today I tought to write about Parition Table Syntax Supported in PPAS. In PostgreSQL and till PPAS 9.0, user has to follow method given below for partitioning a table: 1. CREATE PARENT Table, 2. Create Child tables using Inherit feature 3. Create Trigger on Partition on Parent, so that it can re-direct insert to Right Partition. 4. And if user has to add new child table, then it has to do 2 and 3 steps again. Now, in PPAS 9.0, user doesn't have to perform above activities. PPAS 9.0 supports PARTITION TABLE syntax. Lets see how PARTITION syntax in PPAS can make users/DBAs life easier. We know that PPAS/PostgreSQL supports two types of partition (Range and List). So, we will see how its simple with CREATE PARTITION SYNTAX. Lets CREATE RANGE PARTITION as we used to do in PPAS 9.0/PostgreSQL 1. Create Master table as given below: CREATE TABLE partition_master(id numeric primary key,val text); CREATE TABLE partition_child1(C...

Great News is Postgres Plus Advanced 9.1 is now available for users. So, I thought to write something about Virtual Private Database features, which is part of 9.1 Virtual Private Database (VPD) is a feature which enables Administrator to create security around actual data (i.e row/columns) so that multiple users can access data which is relevant to them. Steps which is require to create Virtual Private database is given below: 1. Create an Application Context 2. Create security policies functions 3. Apply security policies to tables Lets see how user can implement it in Advanced Server. 1. Setup an environment as given below: CREATE user merry identified by edb; CREATE user john identified by edb; CREATE TABLE public.john_merry(userid varchar2(200),val numeric); grant select,update,delete,insert on john_merry to john; grant select,update,delete,insert on john_merry to merry; 2. Now create a Policy Function as given below: CREATE OR REPLACE FUNCTION verify_user ( p_sche...

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 ...

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/permiss...