echo "started execution update index field 5s delay"
echo "DATE: $(date)"

# 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
inc=1

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

echo "INSERTED...."

# Number of autovacuum occured on this table w_g_reset_data
./psql postgres -p 5432 -c "SELECT n_live_tup, autovacuum_count, n_dead_tup FROM pg_stat_all_tables where relname = 'w_g_reset_data';"

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

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)

for j in {1..60}
do
{

# AN UPDATE ON THE TABLE: w_g_reset_data CHANGING THE FIELD VALUE OF HLR_INDEX OF ALL THE RECORDS
if [ "$inc" = 1 ]
then
	./psql postgres -p 5432 -c "select update_inc($i) from tbl;"
else
	./psql postgres -p 5432 -c "select update_dec($i) from tbl;"
fi

}>/dev/null 2>&1

sleep 3

i=`expr $i + 1 `

if [ "$i" = "5" ]
then
echo "$i UPDATES DONE...CHECK INDEX STAT...DATE: $(date)"

if [ "$inc" = 1 ]
then
	inc=0
else
	inc=1
fi

# Number of autovacuum occured on this table w_g_reset_data
./psql postgres -p 5432 -c "SELECT n_live_tup, autovacuum_count, n_dead_tup FROM pg_stat_all_tables where relname = 'w_g_reset_data';"

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 1 MINUTES
for j in {1..60}
do

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

#PRINT ON EVERY 1 mins

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

# Number of autovacuum occured on this table w_g_reset_data
./psql postgres -p 5432 -c "SELECT n_live_tup, autovacuum_count, n_dead_tup FROM pg_stat_all_tables where relname = 'w_g_reset_data';"

fi


# SLEEP FOR 2 SEC
sleep 2

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

i=0

echo "one loop done "

# 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';"
done