Database Technologies

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

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

Till Advanced Server 8.3, there was a function pg_get_tabledef, which some people were using to get the definition of table. This function is no more part of new versions of Advanced Server 8.3 (8.3R2AS and 8.4AS). So, they would be missing this function by default. However, they can get the same functionality using following function, which provides similar result as of pg_get_tabledef. Function code is given below: CREATE OR REPLACE Function pg_get_tabledef(text) RETURNS text AS $$ DECLARE tabledef TEXT; dotpos integer; tablename text; schemaname text; prevcol text; coltype text; notnull1 boolean; rec record; oidcheck boolean; BEGIN dotpos:=strpos($1,'.'); if dotpos = 0 then schemaname:='public'; tablename:=substr($1,dotpos+1); else schemaname:=substr($1,1,dotpos-1); tablename:=substr($1,dotpos+1); end if; select relhasoids into oidcheck from pg_class,pg_namespace where pg_class.r...