Database Technologies

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

Some people ask this, How to use the Nested tables in PPAS? Since there is no Constructor Function Available and Oracle Compatibility Documentation is based on Procedure/Functions. So, I thought to give an overview on this. We can use nested tables by creating some manual Constructor and Using those constructor function to fetch the data or inserting the data: Following is an example: create type test_type as object (col1 varchar(200)); create type nested_type as table of test_type; create table test_nested(col1 nested_type); Now, Create some constructor Functions which can convert the data types as given below: --- Constructor Function of test_type CREATE OR REPLACE FUNCTION test_type(varchar) return test_type as declare result test_type; Begin result=row($1); return result; END; And --Constructor Function of nested_type CREATE OR REPLACE FUNCTION nested_type(test_type[]) return nested_type as counter integer:=0; rec test_type; result nested_type; BEGIN for rec i...

This happened to me when I was trying to migrate the Oracle Database to Advanced Server & PostgreSQL. This kind of error comes if Data in Oracle have some junk/invisible Characters which conversion is unknown. select * from junk_character; 'ORA-29275: partial multibyte character' Oracle Descritption for this error is given below: ORA-29275: partial multibyte character Cause: The requested read operation could not complete because a partial multibyte character was found at the end of the input. Action: Ensure that the complete multibyte character is sent from the remote server and retry the operation. Or read the partial multibyte character as RAW. Which doesn't give much information. To find the the column and rows which have those junk/invisible character user can try following trick. a. Select the data column wise as given below: select col1 from tablename; select col2 from tablename; If you are sure about the columns which has those junk charac...

As discussed in previous Blogs about Hot Standby in following links: 1. Monitoring Hot Standby 2. HotStandby in PostgreSQL 9.0. I came up with the following Monitoring Script for Hot Standby. #!/bin/bash # Filename: monitor_hotstandby # Usage is $0 -m master:port -s slave:port -b "PostgreSQL Bin Directory" # Author: Vibhor Kumar # Date: Jan 4th 2011 # E-mail: vibhor.aim@gmail.com while getopts "m:s:b:" opt;do case $opt in m) h1=`echo $OPTARG|cut -d":" -f1` p1=`echo $OPTARG|cut -d":" -f2`;; s) h2=`echo $OPTARG|cut -d":" -f1` p2=`echo $OPTARG|cut -d":" -f2`;; b) PGHOME="$OPTARG" esac done PSQL=$PGHOME/psql function usage() { if [ -z $h1 ];then echo "USAGE: " echo "$0 -m master:port -s slave:port -b pg bin directory" exit 1 fi if [ -z $h2 ];then echo "USAGE: " echo "$0 -m master:port -s slave:port -b pg bin directory" exit 1 ...

This is a Question which I had seen people generally ask, when they Move their DBs to PG or PGPlus Advanced Server and Look for Workaround of Oracle Feature of Traverse of Directed Graph in PG. Oracle Provides CONNECT BY NOCYCLE for traversing a Directed Graph. As mentioned in Following link: http://www.adp-gmbh.ch/ora/sql/connect_by_nocycle.html However, CONNECT BY NOCYCLE implementation, some times miss some paths for Traversing Graph as you can see in following Example: create table directed_graph ( node_from char(1), node_to char(1) ); insert into directed_graph values ('A', 'C'); insert into directed_graph values ('A', 'B'); insert into directed_graph values ('B', 'E'); insert into directed_graph values ('C', 'H'); insert into directed_graph values ('H', 'I'); insert into directed_graph values ('I', 'D'); insert into directed_graph values ('D', 'F'); insert...

Lot of People think that COPY is only a Server Based Command. However its not completely true. User can copy the data of a table at client side using COPY Command. The Difference is only in syntax usage. User has to use backSlash before COPY Command as given below: testdb=# select * from test; id ---- 1 2 3 (3 rows) testdb=# \copy test to '/tmp/test.copy' testdb=# \q We can see output of a file at Client side as given below: cat /tmp/test.copy 1 2 3 Similarly User can also copy the data in a file to Server using \COPY as given below: \COPY test from '/tmp/test.copy' iclive1460=# \COPY test from '/tmp/test.copy' iclive1460=# select * from test; id ---- 1 2 3 1 2 3 (6 rows) Note:: One should remember while using \COPY Command, one should not terminate command with semicolon ';'

SQL Injection is method of Hacking Data inside the Database. Using SQL Injection, Hackers do multiple Attempt of SQLs to understand the Structure of tables and Try to get the hint of data inside a table. For example, if a Hacker knows that UserName and Password of a Website are stored in a database table say member, then he can write query something like given below to fetch all the data inside the function as given below: select * from tablename where 1=1; Or select * from tablename where 'x'='x'; Similarly, suppose Hacker wants to know the structure (Columns) of any table, then he can try multiple attempts of finding the column name by using the HAVING OR GROUP BY CLAUSE and can find the Columns Names Based on Error. As given below: postgres=# select * from test_sql_injection group by 1 having 1=1; ERROR: column "test_sql_injection.col" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select * from test_sql_injection gro...