Database Technologies

One of my colleague has asked about this. She was doing some testing and wanted to drop a user which has objects across the databases. While dropping user/role, DBA/Admin will get following error messages: postgres=# drop user test; ERROR: role "test" cannot be dropped because some objects depend on it DETAIL: 2 objects in database test Above error messages gives the sufficient information to Admin that there are some objects depend/Owned by the user exists in other database. For dropping such user, there are two methods: 1. Reassign all the objects owned by the user to some other user and then drop the user. Above is very useful, if employee, who left the company, has written some Procedure/objects, which is getting used in Application/process. Command Which can be are following: REASSIGN OWNED BY old_role to new_role; DROP USER old_role; Note:: reassign command need to be executed for all the databases under one PG instance. 2. First Drop all the objects own...

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

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