Downloading a website using wget

For a few days I had to work on a very expensive and yet very slow internet connection, which required me to connect/disconnect manually since it charged me for every minute I was connected.

And during that time I wanted to read the Git book at http://progit.org/book/ . So I thought about downloading the whole book using wget. It gave me a few extra things like the blog, the Japanese/German translations etc. but since that load was not too much I didn't really mind.

So here's the command to get the job done:

wget -r -p -k http://progit.org/book/

Installing Virtualbox Guest Additions on Fedora 12

I had trouble installing VBoxLinuxAdditions on Fedora12. I installed all the requisites that I could find anywhere on the web:

$ sudo yum install binutils gcc patch make libgomp glibc-headres glibc-devel kernel-devel

And yet my kernel module build failed with a message like

Unable to find sources for your current Linux kernel, try setting KERN_DIR and try recompiling.

A lot of research showed me that my kernel version and kernel-devel versions were different:

yum list installed | grep kernel

So I had to install the correct version of kernel-devel package.

yum install kernel-devel-$(uname -r)

And voila, my Guest Additions compiled and installed the kernel modules successfully.

Postgres can, Oracle can't (create duplicate indexes)

This is in the series of "Postgres can, Oracle can't" articles. This one is not really something Oracle can't do, but probably something they chose not to.

If you try to create an index on a set of columns that already have an index, then Oracle will throw an error. But Postgres allows you to do this (probably because of partial indexes, or probably in the spirit of Open Source).

This can be leveraged in routine REINDEX operations. If you try to reindex an index in Postgres, it takes such a lock on the underlying table that INSERT/UPDATE/DELETE operations on that table are blocked, hence causing probable application downtime.

So, combining three of Postgres' unique features, we can re-index indexes without causing application downtime; these features are:
1. DDL obeys transactions
2. Ability to create duplicate indexes
3. Ability to create indexes concurrently.

CREATE INDEX CONCURRENTLY command allows you to create an index in such a way that other sessions are allowed INSERT/UPDATE/DELETE operations.

/* Concurrent index creation canot work in a transaction */

create index concurrently temp_emp_deptno on emp(dept);
begin transaction;
alter index emp_deptno rename to dropped_emp_deptno;
alter index temp_emp_deptno rename to emp_dropped;
drop index dropped_emp_deptno;
commit transaction;

Please refer to he CREATE INDEX documentation for caveats of using CREATE INDEX CONCURRENTLY command. Also, this method does not lend itself to reindexing Primary Key indexes.

Enable Hibernate option on Windows Vista

By default, Windows Vista does not provide the "Hibernate" option anymore. To enable this, run the following command from cmd.exe:

powercfg -H on

Note that to enable hibernation, you should have enough free space on your system partition (this is generally c:\).

To disable hibernation, run the following command:

powercfg -H off

Postgres' SET ROLE usage example

create group G1;
create group G2;

create user U1 password 'u1';

alter group G1 add user U1;
alter group G2 add user U1;

create table T1( a int );
create table T2( a int );

grant select on T1 to G1;
grant select on T2 to G2;

\c - u1

select * from t1;
select * from t2;

set role g1;

/* Should throw error, since we have explicitly taken on the garb of G1, hence giving up permissions of group G2 */

select * from t2;

Postgres query to find if foreign-key columns match the data type of the referenced table

create or replace function find_array_element( el anyelement, arr anyarray  ) returns integer as $$
declare
    i int;
begin
    for i in 1..array_upper( arr, 1 ) loop
        if( el = arr[i] ) then
            return i;
        end if;
    end loop;

    return 0;
end;
$$ language plpgsql;

select
    (select relname
        from pg_class
        where oid = conrelid) as table,
    (select relname
        from pg_class
        where oid = confrelid ) as parent,
    conname as foriegn_key,
    array(select attname
            from pg_attribute
            where attrelid = C.conrelid
                and attnum = ANY( C.conkey )
            order by find_array_element( attnum, C.conkey ) ) as table_columns,
    array(select (select typname
                    from pg_type
                    where oid = A.atttypid)
            from pg_attribute as A
            where attrelid = C.conrelid
                and attnum = ANY( C.conkey )
            order by find_array_element( attnum, C.conkey ) ) as table_datatypes,
    array(select attname
            from pg_attribute
            where attrelid = C.confrelid
                and attnum = ANY( C.confkey )
            order by find_array_element( attnum, C.confkey ) ) as parent_columns,
    array(select (select typname
                    from pg_type
                    where oid = A.atttypid)
            from pg_attribute as A
            where attrelid = C.confrelid
                and attnum = ANY( C.confkey )
            order by find_array_element( attnum, C.confkey ) ) as parent_datatypes
from pg_constraint as C
where contype = 'f'
and array(select (atttypid, attlen, atttypmod)::text
            from pg_attribute
            where attrelid = C.conrelid
                and attnum = any ( C.conkey )
            order by find_array_element( attnum, C.conkey ) )
    <> array(select (atttypid, attlen, atttypmod)::text
                from pg_attribute
                where attrelid = C.confrelid
                    and attnum = any ( C.confkey )
                    order by find_array_element( attnum, C.confkey ) );

Creating an unmodifiable table in Postgres

Here's a simple and effective way of avoiding any accidental INSERT/UPDATE/DELETE operation against any table:

(developed this as part of Postgres porting of Spacewalk )

CREATE TABLE dual ( dummy char );
INSERT INTO dual values ( 'X' );
CREATE OR REPLACE RULE insert_dual AS ON INSERT TO dual DO INSTEAD NOTHING;
CREATE OR REPLACE RULE update_dual AS ON UPDATE TO dual DO INSTEAD NOTHING;
CREATE OR REPLACE RULE delete_dual AS ON DELETE TO dual DO INSTEAD NOTHING;