Thursday, December 16, 2010

Drop user which objects are in different databases of PG Instance.

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 owned by the user and then drop the user.
This is useful if admin don't want to keep the users objects and wants to drop all the objects owned by user:
Command which can be use are following:
DROP OWNED BY name [, ...] [ CASCADE | RESTRICT ];
DROP user username;
Note:: DROP OWNED BY NAME need to be executed for all the database.


To make it further better, I have written following Function, which uses the dblink to make connections to other database and execute the command to drop the user:

CREATE OR REPLACE function reassign_drop_user(text,text) returns integer
AS
$$
Declare
    db record;
    cmd text;
    status text;
BEGIN
   cmd:='REASSIGN OWNED BY '||$1||' TO '||$2||';';
   for db in select datname from pg_database where datname!='template0'
   Loop
      execute 'select dblink_connect('||''''||'dbname='||db.datname||''''||')' into status;
      RAISE NOTICE 'CONNCETION STATUS % :- %',db.datname,status;
      execute 'select dblink_exec('||''''||cmd||''''||')' into status;
      RAISE NOTICE 'COMMAND " % " STATUS :- %',cmd,status;
      execute 'select dblink_disconnect()' into status;
   END Loop;
   execute 'DROP USER '||$1;
   exception when others then
     return 1;
   Return 0;
END;
$$ language plpgsql;
I have kept return value integer, so that I can verify if the function is successful (0) or unsuccessful (1)
Output:
select reassign_drop_user('fromuser','touser');
NOTICE:  CONNCETION STATUS template1 :- OK
NOTICE:  COMMAND " REASSIGN OWNED BY fromuser TO touser; " STATUS :- REASSIGN OWNED
NOTICE:  CONNCETION STATUS template_postgis :- OK
NOTICE:  COMMAND " REASSIGN OWNED BY fromuser TO touser; " STATUS :- REASSIGN OWNED
NOTICE:  CONNCETION STATUS test :- OK
NOTICE:  COMMAND " REASSIGN OWNED BY fromuser TO touser; " STATUS :- REASSIGN OWNED
NOTICE:  CONNCETION STATUS postgres :- OK
NOTICE:  COMMAND " REASSIGN OWNED BY fromuser TO touser; " STATUS :- REASSIGN OWNED 
reassign_drop_user 
--------------------                  
0
(1 row)


Similarly for Dropping Objects with User following function can be use:
REATE OR REPLACE function drop_user_withobjects(text) returns integer
AS
$$
Declare
    db record;
    cmd text;
    status text;
BEGIN
   cmd:='DROP OWNED BY '||$1||' CASCADE;';
   for db in select datname from pg_database where datname!='template0'
   Loop
      execute 'select dblink_connect('||''''||'dbname='||db.datname||''''||')' into status;
      RAISE NOTICE 'CONNCETION STATUS % :- %',db.datname,status;
      execute 'select dblink_exec('||''''||cmd||''''||')' into status;
      RAISE NOTICE 'COMMAND " % " STATUS :- %',cmd,status;
      execute 'select dblink_disconnect()' into status;
   END Loop;
   execute 'DROP USER '||$1;
   exception when others then
     return 1;
   Return 0;
END;
$$ language plpgsql;

Wednesday, December 15, 2010

Rebuilding Pkey and Indexes without locking table in PG 8.4.

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: %',oid2;
      select relfilenode::integer into filenode2 from pg_class where oid=oid2;
      select (indrelid::regclass)::text into relation from pg_index where indexrelid=oid1;
    RAISE NOTICE 'RELATION NAME: %',relation;
      cmd:='LOCK '||relation||';';
      RAISE NOTICE 'Executing :- %',cmd;
      Execute cmd;      
      cmd:='UPDATE pg_class SET relfilenode='||filenode2|| ' WHERE oid='||oid1||';';
      RAISE NOTICE 'Executing :- %',cmd;
      Execute cmd;      
      cmd:='UPDATE pg_class SET relfilenode='||filenode1|| ' WHERE oid='||oid2||';';
      RAISE NOTICE 'Executing :- %',cmd;
      Execute cmd;
      cmd:='DROP INDEX '||$1||'.'||$3||';';
      RAISE NOTICE 'Executing :- %',cmd;
      Execute cmd;
      return 0;
   END;
$$language plpgsql;


Concept is simple, Create a UNIQUE INDEX on primary key columns and swap the relfilenode
CREATE OR REPLACE Function rebuild_pkey_index(text,text) returns setof text
AS
$$
  DECLARE
     reloid integer;
     cmd text;
     rec record;
     oid1 integer;
     oid2 integer;
     filenode1 integer;
     filenode2 integer;
     relname1 text;
  BEGIN
    select oid into reloid from pg_class where relname=$2 and relnamespace=(select oid from pg_namespace where nspname=$1);
    for rec in SELECT c2.relname as indexname, substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),0, position( 'INDEX'in pg_catalog.pg_get_indexdef(i.indexrelid, 0, true))+6) ||'CONCURRENTLY '|| c2.relname||'_new '|| substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),position( 'ON 'in pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)))||';' as command FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE c.oid=reloid  AND c.oid = i.indrelid AND i.indexrelid = c2.oid and i. indisprimary=true ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
    LOOP
       return next rec.command;
       cmd:='SELECT swap_for_pkey('||''''||$1||''''||','||''''||rec.indexname||''''||','||''''||rec.indexname||'_new'||''''||');';
       return next cmd;
   END LOOP;
   END;
$$language plpgsql;
Following is an Output:
postgres=# select rebuild_pkey_index('public','test');                            
rebuild_pkey_index                             
--------------------------------------------------------------------------- 
CREATE UNIQUE INDEX CONCURRENTLY test_pkey1_new ON test USING btree (id); 
SELECT swap_for_pkey('public','test_pkey1','test_pkey1_new');
(2 rows)
Executing the command provided by the above function would give following result:
CREATE INDEX
NOTICE:  PKEY OID: 260669
NOTICE:  PKEY OID: 260679
NOTICE:  RELATION NAME: test
NOTICE:  Executing :- LOCK test;
NOTICE:  Executing :- UPDATE pg_class SET relfilenode=260679 WHERE oid=260669;
NOTICE:  Executing :- UPDATE pg_class SET relfilenode=260678 WHERE oid=260679;
NOTICE:  Executing :- DROP INDEX public.test_pkey1_new;
 swap_for_pkey 
---------------
             0
(1 row)


Similarly for Non Pkey Indices, I have written following function, which can provide the all the command for rebuilding the non-pkey index:

CREATE OR REPLACE Function rebuild_nonpkey_index(text,text) returns setof text
AS
$$
  DECLARE
     reloid integer;
     cmd text;
     rec record;
  BEGIN
 
    select oid into reloid from pg_class where relname=$2 and relnamespace=(select oid from pg_namespace where nspname=$1);
    for rec in SELECT c2.relname as indexname, substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),0, position( 'INDEX'in pg_catalog.pg_get_indexdef(i.indexrelid, 0, true))+6) ||'CONCURRENTLY '|| c2.relname||'_new '|| substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),position( 'ON 'in pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)))||';' as command FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE c.oid=reloid  AND c.oid = i.indrelid AND i.indexrelid = c2.oid and i. indisprimary=false ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
    LOOP
       return next rec.command;
       cmd:= 'DROP INDEX '||rec.indexname||';';
       return next cmd;
       cmd:='ALTER INDEX '||rec.indexname||'_new'||' RENAME TO '||rec.indexname||';';
       return next cmd;
     END LOOP;
   END;
$$language plpgsql;

Output:
postgres=# select rebuild_nonpkey_index('public','test');                                                                                           
rebuild_nonpkey_index                        
-------------------------------------------------------------------- 
CREATE INDEX CONCURRENTLY test_idx_new ON test USING btree (col1); 
DROP INDEX test_idx; 
ALTER INDEX test_idx_new RENAME test_idx;
(3 rows)

Above functions can also be use in PG9.0. However, for PG9.1 Developers are working on ALTER TABLE command which can be use for Swapping the Unique index for Primary key.