Database Technologies

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

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

In my Blog on get_number_of_segments, I had used the simple query to find the number of segments. Since, from PostgreSQL 9.0, system admin function pg_relation_filepath can be use to find the location of relfilenode, therefore I thought to reduce the code of get_number_function and use the pg_relation_filepath function. Following is a modified plperl function get_number_of_segments(text) using system admin function pg_relation_filepath(relation reglcass): CREATE OR REPLACE FUNCTION get_number_of_segments(text) returns table(tablename text, segments int) as $$ my $sql = spi_prepare("select 'ls -1 '||pg_relation_filepath(\$1)||'.*' as cmd",'TEXT'); my $q = spi_query_prepared($sql,$_[0]); my $rv = spi_fetchrow($q); my $cmd = $rv->{rows}[0]; my $command = $cmd -> {location}; open(CMD, "$command |"); $count = ; close(CMD); @count=split(/[\n\r]+/,$count); return_next...

pg_reorg is a utility which I had mentioned in my previous Blog. This utility can be use for : 1. Online Reorganising of Tables 2. Online VACUUM FULL (i.e removing bloats) (if table is having primary key) 3. Online Clustering of tables Since, in production environment, we cannot perform VACUUM FULL/CLUSTER, because it locks the table, therefore DBAs always need a way which they can use to perform maintenance activity without locking. So, I thought to use pg_reorg and compile it against Advanced Server. Advanced Server is a prebuilt binary, so we cannot compile any module or tool with it, without modifying the makefile. Following are some steps, if someone wants can try, to compile pg_reorg with Advanced Server. Donwload pg_reorg utility from following location: http://pgfoundry.org/frs/?group_id=1000411&release_id=1721 1. Execute pg_config of PPAS to find the gcc(location) which used to build binary of Advanced Server, as given below: pg_config |grep "CC =...