Database Technologies

On production System, some times DBAs have to rebuild the indices, since, Rebuilding indices lock the table therefore DBA can use Option CONCURRENTLY. For normal Indices CONCURRENTLY is a best option, however Primary Key Indices Rebuild require Lock on table. To overcome this issue, I have made following function: SWAP Index with Pkey: CREATE OR REPLACE FUNCTION swap_for_pkey(text,text,text) returns integer AS $$ DECLARE cmd text; oid1 integer; oid2 integer; filenode1 integer; filenode2 integer; relation text; BEGIN select oid::integer into oid1 from pg_class where relname=$2 and relnamespace = (select oid from pg_namespace where nspname=$1); RAISE NOTICE 'PKEY OID: %',oid1; select relfilenode::integer into filenode1 from pg_class where oid=oid1; select oid::integer into oid2 from pg_class where relname=$3 and relnamespace = (select oid from pg_namespace where nspname=$1); RAISE NOTICE 'PKEY OID: %',oi

For using these query DBA has to install the adminpack contrib module of PostgreSQL. If you are using one-click installer then user can find the adminpack.sql in /PostgreSQL Installation Directory/share/postgresql/contrib directory. Following are some SQLs Based on adminpack contrib module DBA can use to find the last logfile, size of the logfile and can ready using PG Client applications (psql,pgAdmin etc.) SELECT * FROM ( SELECT pg_ls_dir('pg_log')) AS t (filename)ORDER BY 1 DESC LIMIT 1 SELECT size FROM pg_stat_file('pg_log/postgresql-2010-05-13.log') SELECT pg_read_file('pg_log/postgresql-2010-05-13.log', 0, 21604) Sometimes, user doesn’t have the access to the Server and for reading the PG logfile user has to login on the server. Now they don;t have to. If you have adminpack.sql installed in PostgreSQL user can easily read the last logfile by creating following function: CREATE OR REPLACE FUNCTION pg_read_last_logfile() returns text AS $$ DECLARE

EnterpriseDB Replication tool has been now modified and add new functionality in it. Previous Replication Console, which had been made, was using the DBA Management server and Publication & Subscriptions were dependent. Now, xDB Replication has been divided into two components: 1. Publication Server : (Master Server) 2. Subscription Server: (Slave Server) Some Background of Publication Server. Publication Server has following components: 1. JAVA Based Daemon. 2. MetaDatabase of Publication Server. Publication Server keeps its information (Information about the Primary Database, Tables Details etc.) in the MetaDatabase under schema _edb_replicator_pub Publication server is independent and has no dependency on Subscriptions Server. By default publication server uses the 9011 port. However, if user wants, [S]he can use different ports too. For running Publication Server on different port, use the following command: $EDBHOME/jre/bin/java -Djava.awt.headless=true -jar $

There was a Question put by one of user about loading data in PostgreSQL skipping column which has default value using Copy Command. With above requirement, he has also mentioned that table’s column get changed and default values too. So, he wanted a plpgsql function code, which can be use for any table and file to load the data. Following is a sample plpgsql code which can be use to make things happen: CREATE OR REPLACE FUNCTION Copy_test(text,text,text) returns boolean AS $$ DECLARE rec record; cmd text; colstring text; BEGIN cmd:='select array_to_string(array(select column_name::text from information_schema.columns where table_name='||''''||$2||''''||' and table_schema='||''''||$1||''''||' and column_default is null),'||''''||','||''''||')'; EXECUTE cmd into colstring; cmd:='COPY "'||$1||'&qu

Yesterday, there was a discussion which was going on OTN (Oracle Forum). I was also part of that discussion. Discussion was about the "Does UPDATE statement change rowid in Oracle?" As per my comment of "UPDATE does not change rowid", one of the user has pointed me that UPDATE statement sometime changes the rowid in Oracle and given me following example. SQL> ed Wrote file afiedt.buf 1 create table moving_rowid ( 2 col1 number primary key, 3 col2 number, 4 col3 number, 5 col4 varchar2(10) 6 ) 7* organization index SQL> / Table created. SQL> insert into moving_rowid values( 1, 2, 3, 'foo' ); 1 row created. SQL> insert into moving_rowid values( 2, 3, 4, 'bar' ); 1 row created. SQL> select rowid, col1, col2, col3, col4 2 from moving_rowid; ROWID COL1 COL2 COL3 COL4 ---------------- ---------- ---------- ---------- ---------- *BAEADxsCwQL+ 1 2

Database Technologies: Some trigger and programming feature in PostgreSQL

Anonymous Block Support: Now, user would be able to create anonymous block using plpgsql as given below: do $$ declare id1 varchar; begin select id into id1 from test_sort limit 1; raise notice '%',id1; end; $$ language plpgsql; Output: NOTICE: 1.1 DO Column Trigger: PG9.0 is also supports the column level and When Triggers. Following is an example: create or replace function test_col_trigger() returns trigger as $$ BEGIN RAISE NOTICE 'INSERTED VALUE in Column a %',new.id; RETURN null; END; $$ language plpgsql; create trigger col_trigger after update of "a" on test_def FOR EACH ROW EXECUTE procedure test_col_trigger(); Output: postgres=# update test_def set a='Second'; NOTICE: INSERTED VALUE in Column a 1 UPDATE 1 When Triggers in PG9.0: PG9.0 has "When trigger" which allowed user to create trigger to execute procedure for a particular condition. create or replace function test_col_trigger() returns t