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