Database Technologies

PostgreSQL has pg_stat_activity view which can be use to get the session details. This view gives following information: 1. datid: database OID 2. datname: database name 3. procpid: process ID 4. usesysid: user OID 5. usename: user name 6. application_name: application name 7. client_addr: client's address 8. client_hostname: host name (if available) 9. client_port: Clients port number 10. backend_start: time at which the server process started 11. xact_start: time at which current transaction started 12: query_start: time at which current query began execution started 13: waiting: process's waiting status 14. current_query: text of the current query. Above is good for monitoring sessions in postgresql. However, suppose user wants to know about percentage of CPU & Memory used by a particular session/user, then he can get this information by using plperlu function. To Create the pleperlu function, user needs to have plperlu language installed in DB. For creating the p

This is second post of Postgres Enterprise Manager 2.1 series. Question which people ask, Is Postgres Enterprise Manager tool useful for Companies, which provide services for PostgreSQL? Answer is yes. What about companies, which provide infrastructure support, where they provide server and PostgreSQL database, for them, is this is a good tool for monitoring PostgreSQL? Answer is yes, you can use it. Companies, which are giving Infrastructure support and providing Database As service always want everything to be automated. i.e with provisioning server, server should have installed PostgreSQL and its components plus they want automatic installation of pemagent, which is also acheivable. However, they stumped on Automatic Bidning of PEM Agent with PostgreSQL. For binding PostgreSQL with PEM Agent, Simple Method is using PEM Client. In PEM Client, Add a PostgreSQL in PEM Directory and then user can bind the PEM agent with PostgreSQL Cluster. File -> Add Server -> which will

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