echo "started execution update index field 1s delay"
echo "DATE: $(date)"
./psql postgres -p 5432 -c "CREATE EXTENSION pageinspect;"
./psql postgres -p 5432 -c "CREATE EXTENSION pg_freespacemap;"

# THE FILE "TABLE.SQL" SHOULD HAVE BEEN RUN PRIOR TO THE EXECUTION OF THIS SCRIPT

# TRUNCATE THE TABLE w_g_reset_data

./psql postgres -p 5432 -c "TRUNCATE TABLE w_g_reset_data;"


#DECLARE AND INITIALIZE LOOP ITERATOR VARIABLES

i=0
m=0

# INSERT 100000 RECORDS IN w_g_reset_data
{
./psql postgres -p 5432 -c "SELECT insert() FROM tbl;"
}>/dev/null 2>&1

echo "INSERTED...."

# EXECUTING THE QUERY TO GET THE SIZE OF THE DATA AND INDEX FILES FROM DISK
./psql postgres -p 5432 -c " SELECT
             t.tablename,
             indexname,
             c.reltuples::bigint AS num_rows,
             pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
             pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
             CASE WHEN x.is_unique = 1  THEN 'Y'
                ELSE 'N'
             END AS UNIQUE,
             idx_scan AS number_of_scans,
             idx_tup_read AS tuples_read,
             idx_tup_fetch AS tuples_fetched
         FROM pg_tables t
         LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
         LEFT OUTER JOIN
                (SELECT indrelid,
                    max(CAST(indisunique AS integer)) AS is_unique
                FROM pg_index
                GROUP BY indrelid) x
                ON c.oid = x.indrelid
         LEFT OUTER JOIN
             ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch,indexrelname FROM pg_index x
                    JOIN pg_class c ON c.oid = x.indrelid
                    JOIN pg_class ipg ON ipg.oid = x.indexrelid
                    JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
             AS foo
             ON t.tablename = foo.ctablename
         WHERE t.schemaname='public' and t.tablename = 'w_g_reset_data';"



# EXECUTING THE QUERY TO GET THE TOTAL SIZE OF THE FREE SPACE AND THE TOTAL NUMBER OF INDEX PAGES PRESENT IN FSM FOR THE RELATION xpkw_g_reset_data

./psql postgres -p 5432 -c "select sum(avail)/(1024*1024) AS IDX_FREE_SPACE, count(blkno) as IDX_FSM_PAGES from pg_freespace('xpkw_g_reset_data');"

# EXECUTING THE QUERY TO GET TOTAL NUMBER OF FULLY EMPTY PAGES IN FSM FOR THE RELATION xpkw_g_reset_data

./psql postgres -p 5432 -c "select count(blkno) as FULLY_EMPTY_PAGES_COUNT from pg_freespace('xpkw_g_reset_data') WHERE avail = 8160;"

echo "starting UPDATE..."


# THE OUTER LOOP: EACH ITERATION DOES
#		200 'UPDATES' ON THE TABLE: w_g_reset_data (UPDATING ALL RECORDS), QUERYING SOME DATA EVERY 20 SUCH UPDATES
#               PERFORMS SOME SELECT OPERATIONS (GETTING DATA FROM FSM) FOR 15 MINUTES

for m in {1..1000}
do


# THE INNER LOOP: EACH ITERATION DOES
#            AN UPDATE ON THE TABLE: w_g_reset_data CHANGING THE FIELD VALUE OF HLR_INDEX OF ALL THE RECORDS,
#            SLEEP 1 SECOND
#            AFTER EVERY 10 UPDATE FUNCTION IS INVOKED (AFTER 10 x 100000 RECORDS UPDATED)
#            		EXECUTE THE QUERY TO GET THE SIZE OF THE DATA AND INDEX FILES FROM DISK,
#            		EXECUTE THE QUERY TO GET THE TOTAL SIZE OF THE FREE SPACE AND THE TOTAL NUMBER OF INDEX PAGES PRESENT IN FSM FOR THE RELATION xpkw_g_reset_data
#            		EXECUTE THE QUERY TO GET TOTAL NUMBER OF FULLY EMPTY PAGES IN FSM FOR THE RELATION xpkw_g_reset_data

for j in {1..200}
do
{

# AN UPDATE ON THE TABLE: w_g_reset_data CHANGING THE FIELD VALUE OF HLR_INDEX OF ALL THE RECORDS
./psql postgres -p 5432 -c "select update() from tbl;"
}>/dev/null 2>&1

sleep 1

i=`expr $i + 1 `
if [ "$i" = "20" ]
then
echo "$i UPDATES DONE...CHECK INDEX STAT...DATE: $(date)"

# THE QUERY TO GET THE SIZE OF THE DATA AND INDEX FILES FROM DISK
./psql postgres -p 5432 -c " SELECT
             t.tablename,
             indexname,
             c.reltuples::bigint AS num_rows,
             pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
             pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
             CASE WHEN x.is_unique = 1  THEN 'Y'
                ELSE 'N'
             END AS UNIQUE,
             idx_scan AS number_of_scans,
             idx_tup_read AS tuples_read,
             idx_tup_fetch AS tuples_fetched
         FROM pg_tables t
         LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
         LEFT OUTER JOIN
                (SELECT indrelid,
                    max(CAST(indisunique AS integer)) AS is_unique
                FROM pg_index
                GROUP BY indrelid) x
                ON c.oid = x.indrelid
         LEFT OUTER JOIN
             ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch,indexrelname FROM pg_index x
                    JOIN pg_class c ON c.oid = x.indrelid
                    JOIN pg_class ipg ON ipg.oid = x.indexrelid
                    JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
             AS foo
             ON t.tablename = foo.ctablename
         WHERE t.schemaname='public' and t.tablename = 'w_g_reset_data';"

# EXECUTE THE QUERY TO GET THE TOTAL SIZE OF THE FREE SPACE AND THE TOTAL NUMBER OF INDEX PAGES PRESENT IN FSM FOR THE RELATION xpkw_g_reset_data
./psql postgres -p 5432 -c "select sum(avail)/(1024*1024) AS IDX_FREE_SPACE, count(blkno) as IDX_FSM_PAGES from pg_freespace('xpkw_g_reset_data');"

# EXECUTE THE QUERY TO GET TOTAL NUMBER OF FULLY EMPTY PAGES IN FSM FOR THE RELATION xpkw_g_reset_data
./psql postgres -p 5432 -c "select count(blkno) as FULLY_EMPTY_PAGES_COUNT from pg_freespace('xpkw_g_reset_data') WHERE avail = 8160;"

i=0
fi

# END OF LOOP: for j in {1..200}
done

# RESETTING THE ITERATOR VARIABLE
i=0


# ENTERING THE SLEEPING OR SELECT ONLY PHASE
# EACH ITERATION WILL DO TWO SELECT OPERATIONS TO GET FSM DETAILS AND SLEEP FOR 2 SECONDS. (SELECT WILL DISPLAY THE RESULT ONLY AT EVERY 2 MINUTES
for j in {1..450}
do

i=`expr $i + 1 `
if [ "$i" = "150" ]
then
i=0

#PRINT ON EVERY 5 mins

echo "AFTER 5 mins CHECK FSM STAT FOR INDEX...DATE: $(date)"

# THE QUERY TO GET THE SIZE OF THE DATA AND INDEX FILES FROM DISK
./psql postgres -p 5432 -c " SELECT
             t.tablename,
             indexname,
             c.reltuples::bigint AS num_rows,
             pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
             pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
             CASE WHEN x.is_unique = 1  THEN 'Y'
                ELSE 'N'
             END AS UNIQUE,
             idx_scan AS number_of_scans,
             idx_tup_read AS tuples_read,
             idx_tup_fetch AS tuples_fetched
         FROM pg_tables t
         LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
         LEFT OUTER JOIN
                (SELECT indrelid,
                    max(CAST(indisunique AS integer)) AS is_unique
                FROM pg_index
                GROUP BY indrelid) x
                ON c.oid = x.indrelid
         LEFT OUTER JOIN
             ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch,indexrelname FROM pg_index x
                    JOIN pg_class c ON c.oid = x.indrelid
                    JOIN pg_class ipg ON ipg.oid = x.indexrelid
                    JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
             AS foo
             ON t.tablename = foo.ctablename
         WHERE t.schemaname='public' and t.tablename = 'w_g_reset_data';"

./psql postgres -p 5432 -c "select sum(avail)/(1024*1024) AS IDX_FREE_SPACE, count(blkno) as IDX_FSM_PAGES from pg_freespace('xpkw_g_reset_data');"
./psql postgres -p 5432 -c "select count(blkno) as FULLY_EMPTY_PAGES_COUNT from pg_freespace('xpkw_g_reset_data') WHERE avail = 8160;"

fi

{

# QUERYING (BUT NOT DISPLAYING THE RESULT) THE TOTAL SIZE OF THE FREE SPACE AND THE TOTAL NUMBER OF INDEX PAGES PRESENT IN FSM FOR THE RELATION xpkw_g_reset_data
./psql postgres -p 5432 -c "select sum(avail)/(1024*1024) AS IDX_FREE_SPACE, count(blkno) as IDX_FSM_PAGES from pg_freespace('xpkw_g_reset_data');"

# QUERYING (BUT NOT DISPLAYING THE RESULT) THE TOTAL NUMBER OF FULLY EMPTY PAGES IN FSM FOR THE RELATION xpkw_g_reset_data
./psql postgres -p 5432 -c "select count(blkno) as FULLY_EMPTY_PAGES_COUNT from pg_freespace('xpkw_g_reset_data') WHERE avail = 8160;"
}>/dev/null 2>&1

# SLEEP FOR 2 SEC
sleep 2

#END OF LOOP: for j in {1..450}
done

i=0

echo "one loop done "

# END OF THE OUTER LOOP: for m in {1..1000}
done