#!/bin/bash

# PG_lesslog test script.
# Please note that we need every WAL record associated with
# physical log (page backup, or full page write) as much
# as possible.
#
# NOTE: This is only for PostgreSQL 8.3.x.
#
# ==== BEFORE YOU BEGIN =================================
#
# The test needs btree_gist contrib module
# to test GiST index WAL records respectively.
# Before the test, these modules have to be built and installed
# to appropriate directories.
#
# After you've built and installed PostgreSQL, do the following
# at the top level directory of PostgreSQL source.
#
# $ cd btree_gist
# $ make
# $ sudo make install
#
# To run this test, the user must be a superuser of the database
# to create op_class etc.Functions
#
# So typical way of the test is:
#
# 1) Review the settings of imporatnt variables. Make change to adjust
#    to your environment.
#
# 2) Run pg_lesslog_test -C to prepare working directories.
#
# 3) Run pg_lesslog_test with other arguments (-N or -Y, -c or -u)
#
# 4) Review the output and PostgreSQL operation logs.
#
# ==== ARGUMENTS =======================================
#
# This tool accepts several arguments to control the test.
#
# -Y : Run CLOG truncate test.  This takes very very long, even a
#      couple of days because we have to run millions of transaction
#      to make it happen.
#
# -N : Does not run CLOG truncate test.
#
# -C : Setup the environment.  You will be prompted for the password
#      to run "sudo".  You must be a menber of "sudoers" to run this
#      option.   This option does not actually run the test, but
#      only arranges ownership and directories.
#
# -v : Turn on the checkpoint option.  With this option,
#      the test will run checkpoint->vacuum->vacuum analyze->checkpoint
#      at each chance of checkpoint.   This increase the chance
#      that WALs have backup block.
#
# -n : Specifies the number of tuples to create for each test table.
#      Specify the value after this option like "-n 100000".   The
#      default value is 10,000 and is sufficient to create WALs.
#
# -c : use pg_compresslog to archive WAL segments.  With this option,
#      all WALs will be decompressed for recovery and compressed again
#      for WAL analysis.
#
# -u : use cp to archive WAL segments.  At the last of the test,
#      WAL segments will be compressed for analysis, decompressed
#      again for recovery.
#
# -F : Same as -Y.
#
# [Notice]
#
# 1) You must specify either -c or -u options.
#
# 2) If you don't specify -Y (-F) or -N option, you will be prompted
#    to specify either.
#
# ===== IMPORTANT VARIABLES =====================================
#
# This test includes several imporatant variables.  You change change
# these values to fit to your test environment.
#
# PGROOT: Root directory of PostgreSQL installation.  Default is
#         /usr/local/pgsql
#
#         All the PostgreSQL executables are supposed to be in 
#         $PGROOT/bin.  All the runtime libraries are supposed
#         to be in $PGROOT/lib.
#
# PGDATA: Root of PostgreSQL databsae cluster.
#
#      [NOTE] This tool runs "initdb" to this cluster root.
#             You should not specify the value for existing database 
#             in use.
#
# ARCHLOGDIR: Directory to store WAL archives.  Compression/decompression
#             will be done using this directory too.
#
#      [NOTE] Be careful.  If you choose to run CLOG truncate test,
#             you may need a couple of hundred gigabyte of disk space
#             here.
#
# BASEBACKUPDIR: Directory to store PostgreSQL base backup for recovery test.
#
# PGLOGDIR: Directory to store PostgreSQL operation log.
#
# TABLESPACE: Directory for the test tablespace.
#
#     [NOT]  Above four directories will not be created by this tool.
#            Please create them in advance.  They must have write privilege
#            by the user you are using for the test.
#
# During the test, $PGROOT owner and group will be changed to $TESTUSR and
# $TESTDB and all the working directory will be created if you specify -C
# option.
# -------------------------
#
# PGSRC: Directory of PostgreSQL source code.
#
# BTREE_GIST: Directory of the source of btree_gist contrib module.
#
# TESTDB: Database name used in the test.
#
# TESTUSR: Database owner name used in the test.  It should be the
#          same as linux user name you're using.
#
# TESTGRP: Group of $TESTUSR
#
# ================================================================

# Functions


function dircheck
{
  if [ -d $1 ]
    then
      if [ -r $1 ]
        then
          if [ -w $1 ]
            then
              return 0
            else
              echo "No write previlege to $1"
	      return 1
          fi
        else
          echo "No read previlege to $1"
          return 1
      fi
    else
      echo "$1 is not a directory"
      return 1
  fi
}

function dirtest
{
  echo $2 $1
  if (dircheck $1)
    then
      return 0
    else
      echo "Exiting the test."
      exit 1
  fi
}

function ckprog
{
  echo $2 $1
  if (which $1 > /dev/null)
    then
      return 0
    else
      echo "$1 not found, exiting the test"
      exit 1
   fi
}


# Parameter settings

# All the directories below should exist at the time of test
# with appropriate previlege.
# If not, the test will terminate with error.

PG_LESSLOG_VER=1.4

C_OPTION=n

# This parameter controls CLOG truncate test.   This takes very long time.
# If this parameter is set to "y" by -F option, CLOG truncate test will be
# performed automatically.
# If it is set to "n" by default, this test will be skipped.
# If it is set to "c", you will be prompted to enter "y" if you'd like
# to run the test.

CLOG_TRUNCATE_TEST=c
#
# PostgreSQL installation info
#
PGROOT=/usr/local/pgsql
export PGDATA=$PGROOT/data
export PATH=$PGROOT/bin:$PATH
LD_LIBRARY_PATH=$PGROOT/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
PGCONFIG=$PGDATA/postgresql.conf
#
# Base Backup, Active/Archive Log Directory
#
ARCHLOGDIR=/var/postgresql/archivedir
BASEBACKUPDIR=/var/postgresql/basebackup
PGLOGDIR=/var/postgresql/pg_log
#
# User name and DB name to use in the test
#
TESTDB=koichi
TESTUSR=koichi
TESTGRP=koichi
NEWDB=koichi2
#
# Log Archiver Selection
#
PGCOMPRESSLOG_ALT=cp
PGCOMPRESSLOG_ORG=pg_compresslog
PGCOMPRESSLOG=$PG_COMPRESSLOG_ORG
PGDECOMPRESSLOG_ALT=cp
PGDECOMPRESSLOG_ORG=pg_decompresslog
PGDECOMPRESSLOG=$PG_DECOMPRESSLOG_ORG
#
# Backup
#
BACKUPTEXT='test'
#
# Control the wait time: not used at present
#
SLEEPSEC=60
#
# PostgreSQL source information: needed to install btree_gist,
# which simplifies the whole test.
#
PGSRC=/home/common/PostgreSQL/postgresql-8.4.3
BTREE_GIST=$PGSRC/contrib/btree_gist

#
# Tablespace info for the test
#
TABLESPACE=/var/postgresql/tablespace.org
TABLESPACENAME=tabspace_test
TABLESPACENAME_NEW=tabspace_test_new

#
# If CLOG_TRUNCATE Test is inluded?
#
# This was made option because it takes very long.
# If it is not set to y or c by command line option,
# it will prompt to type y or n to confirm to run
# CLOG_TRUNCATE test.
#
CLOG_TRUNCATE_OPTION=c

#
# Number of tuple of each table
#
NUM_TUPLES=50000
# NUM_TUPLES=200000
#
# Checkpoint option
#
CHECKPOINT_OPT=0

#
# Put the function here because it uses variable above.
#
function MY_CKPT
{
  if [ $CHECKPOINT_OPT = 0 ]
    then
      return 0
    else
      psql -a -c "CHECKPOINT" $TESTDB $TESTUSR
      psql -a -c "VACUUM " $TESTDB $TESTUSR
      psql -a -c "VACUUM ANALYZE" $TESTDB $TESTUSR
      psql -a -c "CHECKPOINT" $TESTDB $TESTUSR
      return 0
  fi
}


#
# Handle options
#
while getopts ":YNFNvcCun:" opt; do
  case $opt in
    Y ) CLOG_TRUNCATE_OPTION=y;;
    N ) CLOG_TRUNCATE_OPTION=n;;
    v ) CHECKPOINT_OPT=1;;
    n ) NUM_TUPLES=$OPTARG;;
    c ) PGCOMPRESSLOG=$PGCOMPRESSLOG_ORG
        PGDECOMPRESSLOG=$PGDECOMPRESSLOG_ORG;;
    u ) PGCOMPRESSLOG=$PGCOMPRESSLOG_ALT
        PGDECOMPRESSLOG=$PGDECOMPRESSLOG_ALT;;
    F ) CLOG_TRUNCATE_TEST=y;;
    N ) CLOG_TRUNCATE_TEST=n;;
    C ) C_OPTION=y;;
    \? ) echo '??? Unknown option.'
         exit 1;;
  esac
done

# Construct directories

if [ $C_OPTION = y ]
then
echo '*** Building Directories ***'
cat > _dir_make <<EOF
#!/bin/bash
chown $TESTUSR $PGROOT
chgrp $TESTGRP $PGROOT
rm -rf $PGDATA
mkdir -p $PGDATA
chown $TESTUSR $PGDATA
chgrp $TESTGRP $PGDATA
rm -rf $ARCHLOGDIR
mkdir -p $ARCHLOGDIR
chown $TESTUSR $ARCHLOGDIR
chgrp $TESTGRP $ARCHLOGDIR
rm -rf $BASEBACKUPDIR
mkdir -p $BASEBACKUPDIR
chown $TESTUSR $BASEBACKUPDIR
chgrp $TESTGRP $BASEBACKUPDIR
rm -rf $PGLOGDIR
mkdir -p $PGLOGDIR
chown $TESTUSR $PGLOGDIR
chgrp $TESTGRP $PGLOGDIR
rm -rf $TABLESPACE
mkdir -p $TABLESPACE
chown $TESTUSR $TABLESPACE
chgrp $TESTGRP $TABLESPACE
EOF
chmod +x _dir_make
sudo ./_dir_make
rm _dir_make
exit
fi

#
# Confirm wheter to run CLOG_TRUNCATE test or not.
# It need to run several tens of millions of transaction and takes
# very long.   So for handy check, I made this optional.
#
while [ $CLOG_TRUNCATE_OPTION != y ] && [ $CLOG_TRUNCATE_OPTION != n ]
do
   echo "Please specify if you'd like to run CLOG_TRUNCATE test."
   echo "It will take very long, maybe whole day, so be careful."
   echo -n "Type y or n: "
   read CLOG_TRUNCATE_OPTION
done

# Start of the pg_lesslog test

echo -e "\n==================================================="
echo '       PG_LESSLOG TEST'
echo '       VERSION = ' $PG_LESSLOG_VER
echo '       Date: ' `date`
echo -e "===================================================\n"

# Parameter Check

echo -e "===== Parameter Check ===================\n"
echo 'PG_LESSLOG_VER = '  $PG_LESSLOG_VER 
echo "CLOG_TRUNCATE_OPTION = $CLOG_TRUNCATE_OPTION"
dirtest $ARCHLOGDIR "Archive Log Dir = "
dirtest $BASEBACKUPDIR "Base Backup Dir = "
dirtest $PGLOGDIR "PostgreSQL Log Dir = "
dirtest $PGSRC "PostgreSQL Source Dir = "
dirtest $BTREE_GIST "PostgreSQL btree_gist source Dir = "

dirtest $TABLESPACE "Original Tablespace Dir = "

echo "pg_compresslog = $PGCOMPRESSLOG"
ckprog $PGCOMPRESSLOG
PGCOMPRESSLOGPATH=`which $PGCOMPRESSLOG`
echo "pg_decompresslog = $PGDECOMPRESSLOG"
ckprog $PGDECOMPRESSLOG
PGDECOMPRESSLOGPATH=`which $PGDECOMPRESSLOG`

ckprog pg_ctl
ckprog psql
ckprog initdb
ckprog createdb
ckprog dropdb

echo -e "\n===== Parameter Check Complete ==========\n"

# ========== Preparation ==============================
#
# Edit postgresql.conf to activate archive log.

echo -e "\n===== Initialize the database from the scartch =============\n"



echo "pg_ctl stop"
pg_ctl stop
echo "pg_ctl stop -- complete"

# Clear Everything
echo -n 'removing $ARCHLOGDIR/*'
rm -rf $ARCHLOGDIR/*
echo -e '\t--- complete'
echo -n 'removing $BASEBACKUPDIR/*'
rm -rf $BASEBACKUPDIR/*
echo -e '\t--- complete'
echo -n 'removing $PGDATA'
rm -rf $PGDATA
echo -e '\t--- complete'
echo -n 'removing $TABLESPACE/*'
rm -rf $TABLESPACE/*
echo -e '\t--- complete'
rm -rf $PGLOGDIR/*

#
# ======= Initialize a list of RMID and WAL records =================
#

echo -n '---- Building WAL record list file '

cat > record_list << EOF
RM_XLOG_ID
XLOG_CHECKPOINT_SHUTDOWN
XLOG_CHECKPOINT_ONLINE
XLOG_NOOP
XLOG_NEXTOID
XLOG_SWITCH
RM_XACT_ID
XLOG_XACT_COMMIT
XLOG_XACT_PREPARE
XLOG_XACT_ABORT
XLOG_XACT_COMMIT_PREPARED
XLOG_XACT_ABORT_PREPARED
RM_SMGR_ID
XLOG_SMGR_CREATE
XLOG_SMGR_TRUNCATE
XLOG_SMGR_CREATE
XLOG_SMGR_TRUNCATE
RM_CLOG_ID
CLOG_ZEROPAGE
CLOG_TRUNCATE
RM_DBASE_ID
XLOG_DBASE_CREATE
XLOG_DBASE_DROP
RM_TBLSPC_ID
XLOG_TBLSPC_CREATE
XLOG_TBLSPC_DROP
RM_MULTIXACT_ID
XLOG_MULTIXACT_ZERO_OFF_PAGE
XLOG_MULTIXACT_ZERO_MEM_PAGE
XLOG_MULTIXACT_CREATE_ID
RM_HEAP2_ID
XLOG_HEAP2_FREEZE
XLOG_HEAP2_CLEAN
XLOG_HEAP2_CLEAN_MOVE
RM_HEAP_ID
XLOG_HEAP_INSERT
XLOG_HEAP_DELETE
XLOG_HEAP_UPDATE
XLOG_HEAP_MOVE
XLOG_HEAP_HOT_UPDATE
XLOG_HEAP_NEWPAGE
XLOG_HEAP_LOCK
XLOG_HEAP_INPLACE
XLOG_HEAP_INIT_PAGE
RM_BTREE_ID
XLOG_BTREE_INSERT_LEAF
XLOG_BTREE_INSERT_UPPER
XLOG_BTREE_INSERT_META
XLOG_BTREE_SPLIT_L
XLOG_BTREE_SPLIT_R
XLOG_BTREE_SPLIT_L_ROOT
XLOG_BTREE_SPLIT_R_ROOT
XLOG_BTREE_DELETE
XLOG_BTREE_DELETE_PAGE
XLOG_BTREE_DELETE_PAGE_META
XLOG_BTREE_NEWROOT
XLOG_BTREE_DELETE_PAGE_HALF
RM_HASH_ID
RM_GIN_ID
XLOG_GIN_CREATE_INDEX
XLOG_GIN_CREATE_PTREE
XLOG_GIN_INSERT
XLOG_GIN_SPLIT
XLOG_GIN_VACUUM_PAGE
XLOG_GIN_DELETE_PAGE
RM_GIST_ID
XLOG_GIST_PAGE_UPDATE
XLOG_GIST_NEW_ROOT
XLOG_GIST_PAGE_SPLIT
XLOG_GIST_INSERT_COMPLETE
XLOG_GIST_CREATE_INDEX
XLOG_GIST_PAGE_DELETE
RM_SEQ_ID
XLOG_SEQ_LOG
EOF


echo "--- Done."

# Build transaction generator for CLOG_TRUNCATE test


echo -n '---- Building Transaction Generator for CLOG_TRUNCATE test '

cat > gen_tx.c << EOF
#include <stdio.h>
#include <stdlib.h>
#include <sys/types.h>
#include <unistd.h>

int		num_tx;
int		interval1 = 4096;
pid_t	pid;
int		key;
int		maxkey;
int		minkey;

static int getkey();

main(int ac, char *av[])
{
    int ii;
    int jj;
    int kk;

    num_tx = atoi(av[1]);
    minkey = atoi(av[2]);
    maxkey = atoi(av[3]);
    pid = getpid();
    srand((int)pid);
 
    for (ii = 0; ii < num_tx; ) {
        printf("\\\\! echo \\"%8d:TXN:%8d/%d (%d%%).\\"\\n", pid, ii, num_tx, ii * 100/num_tx);
        fflush(stderr);
        for (jj = 0; jj < interval1 && ii < num_tx;ii++, jj++) {
                printf("UPDATE T9 SET B=%d WHERE A=%d;\\n", getkey(), getkey());
        }
    }
    printf("\\\\! echo \\"%8d: Finishing\\"\\n", pid);
}


static int getkey()
{
    int keyval;

    keyval = rand() / (RAND_MAX/(maxkey-minkey));
    keyval += minkey;
    return(keyval > maxkey ? maxkey : keyval);
}
EOF

cc -o gen_tx gen_tx.c
rm gen_tx.c

echo '--- Done.'

# Initialize Database

echo 'initdb'
initdb
echo -e '\t--- complete'

# Configure PGSQL for archive_log

echo -e 'Setting server parameters in postgresql.org'
echo "archive_mode = on" >> $PGCONFIG
cat >> $PGCONFIG << EOF
archive_command = '$PGCOMPRESSLOGPATH %p $ARCHLOGDIR/%f </dev/null'
log_destination = 'stderr'
logging_collector = on
log_directory = '$PGLOGDIR'
log_min_messages = info
log_line_prefix = '%d,%m:'
log_checkpoints = on
log_autovacuum_min_duration = 0
max_prepared_transactions = 500
autovacuum_freeze_max_age = 100000000
EOF
echo -e '\t--- complete'

# Start

echo 'pg_ctl start'
pg_ctl start
echo -e '\t--- complete'

sleep 1

echo -e "\n===== Database Initialization Complete ===============\n"

# Create Database for test

createdb $TESTDB

# Get base backup.

psql -a $TESTDB $TESTUSR << EOF
SELECT pg_start_backup('$BACKUPTEXT');
\q
EOF
rsync -a $PGDATA $BASEBACKUPDIR
psql -a $TESTDB $TESTUSER << EOF
SELECT pg_stop_backup();
\q
EOF

# ========= Database WAL record test =================
#
# Make "DB" rmgr WAL records;

psql -a $TESTDB $TESTUSR << EOF
CREATE DATABASE $NEWDB;
EOF

MY_CKPT

psql -a $TESTDB $TESTUSR << EOF
DROP DATABASE $NEWDB
EOF

# Need checkpoint before vacuum to formce vacuum to write
# full page to WAL records.

MY_CKPT

# ======== Usual table and BTree Index =============

# Test of usual table with BTree Index
#

psql -a -c "DROP TABLE IF EXISTS T1" $TESTDB $TESTUSR

psql -a $TESTDB $TESTUSR << EOF
CREATE TABLE T1 
	(A INTEGER PRIMARY KEY, 
         B CHAR(128), 
	 C VARCHAR(256), 
         D INTEGER);
CREATE INDEX T1_D_IDX ON T1 USING BTREE (D);
EOF

#
# Next block may cause a deadlock.  Don't worry.
# This is needed to enforce XLOG_HEAP_LOCK WAL record.
#

psql -a $TESTDB $TESTUSR << EOF
CHECKPOINT;
INSERT INTO T1(A) 
	SELECT * FROM generate_series(1, $NUM_TUPLES);
CHECKPOINT;
UPDATE T1 
	SET B = 'xxxxxxxxxx', C = 'yyyyyyyyyyyyyyyyyyy', D = A;
DELETE FROM T1 WHERE A%10=0;
CHECKPOINT;
BEGIN;
SELECT * FROM T1 WHERE A%1=1 FOR UPDATE;
UPDATE T1 
	SET B = 'zzzZZZZZZZ', C = 'zzzuyyyyyyyyyyyyyyy' 
	WHERE A%10=1;
COMMIT;
UPDATE T1 
	SET B = 'zzzxxxxxxx', C = 'zzzuyyyyyyyyyyyyyyy' 
	WHERE A%10=1;
EOF

#
# ======== Usual table with concurrent FOR SHARE ========
#
#              --- Writes RM_MULTIXACT_ID family WALs

for i in 1 2 3 4 5
do
echo "FOR SHARE: turn ($i)"
# ./bg_select_for_share $TESTDB $TESTUSR &
# !/bin/bash
psql -a $TESTDB $TESTUSR << EOF > /dev/null &
BEGIN;
SELECT * FROM T1 WHERE A%10=1 FOR SHARE;
\! sleep 5
COMMIT;
EOF
done

sleep 10

#
# ======== Usual table with concurrent FOR UPDATE ========
#
#              --- Writes XLOG_HEAP_LOCK WALs

for i in 1 2 3
do
echo "FOR UPDATE: turn ($i)"
# ./bg_select_for_update $TESTDB $TESTUSR &
# !/bin/bash
psql -a $TESTDB $TESTUSR << EOF > /dev/null &
BEGIN;
SELECT * FROM T1 WHERE A%10=1 FOR UPDATE;
\! sleep 5
COMMIT;
EOF
done

sleep 20

#
# ======= ABORT =======================
#
psql -a $TESTDB $TESTUSR << EOF
CHECKPOINT;
BEGIN;
DELETE FROM T1;
ABORT;
EOF

#
# ====== 2PC -> ABORT =================
#
psql -a $TESTDB $TESTUSR << EOF
CHECKPOINT;
BEGIN;
UPDATE T1 
	SET B = 'xxxxxxxxxx', C = 'wwwwwwwwwwwwwwwwwww', D = A;
DELETE FROM T1 WHERE A%10=2;
PREPARE TRANSACTION  'foobar';
ROLLBACK PREPARED 'foobar';
EOF

#
# ======= SAVEPOINT -> ROLLBACK ========
#
psql -a $TESTDB $TESTUSR << EOF
CHECKPOINT;
BEGIN;
SAVEPOINT foobar2;
UPDATE T1 
	SET B = 'xxxxxxxxxx', C = 'wwwwwwwwwwwwwwwwwww', D = A;
DELETE FROM T1 WHERE A%10=2;
ROLLBACK TO SAVEPOINT foobar2;
ABORT;
EOF

#
# ====== 2PC -> COMMIT ================
#
psql -a $TESTDB $TESTUSR << EOF
CHECKPOINT;
BEGIN;
UPDATE T1 
	SET B = 'xxxxxxxxxx', C = 'wwwwwwwwwwwwwwwwwww', D = A;
DELETE FROM T1 WHERE A%10=2;
PREPARE TRANSACTION  'foobar1';
COMMIT PREPARED 'foobar1';
EOF

#
# ====== SAVEPOINT -> RELEASE =========
#
psql -a $TESTDB $TESTUSR << EOF
CHECKPOINT;
BEGIN;
SAVEPOINT foobar3;
UPDATE T1 
	SET B = 'xxxxxxxxxx', C = 'wwwwwwwwwwwwwwwwwww', D = A;
DELETE FROM T1 WHERE A%10=3;
RELEASE SAVEPOINT foobar3;
COMMIT;
EOF

#
# ====== LOCK TABLE ================
#
psql -a $TESTDB $TESTUSR << EOF
BEGIN;
LOCK TABLE T1;
SELECT COUNT(*) FROM T1;
COMMIT;
EOF

MY_CKPT

#
# ===== DELETE all Btree entries and vacuum
#
psql -a $TESTDB $TESTUSR << EOF
BEGIN;
DELETE FROM T1;
COMMIT;
CHECKPOINT;
VACUUM T1;
VACUUM T1;
EOF

MY_CKPT

# ======= Usual table and Hash index =================

psql -a -c "DROP TABLE IF EXISTS T2" $TESTDB $TESTUSR

psql -a $TESTDB $TESTUSR << EOF
CHECKPOINT;
CREATE TABLE T2
	(A INTEGER PRIMARY KEY, 
	 B CHAR(128),
	 C VARCHAR(256), 
         d INTEGER);
CREATE	INDEX T2_D_IDX ON T2
	USING HASH (D);
INSERT INTO T2(A) 
	SELECT * 
          FROM generate_series(1, $NUM_TUPLES);
CHECKPOINT;
UPDATE T2 
        SET B = 'xxxxxxxxxx', C = 'yyyyyyyyyyyyyyyyyyy', D = A;
DELETE FROM T2 WHERE A%10=0;
CHECKPOINT;
UPDATE T2 
        SET B = 'zzzxxxxxxx', C = 'zzzzyyyyyyyyyyyyyyy' 
          WHERE A%10=1;
CHECKPOINT;
EOF


MY_CKPT

# ===== Usual Table and Gist index ======================
#
# Note that we use "btree_gist" contrib module for this purpose
#

psql -a -f $BTREE_GIST/btree_gist.sql $TESTDB $TESTUSR

psql -a -c "DROP TABLE IF EXISTS T3" $TESTDB $TESTUSR

psql -a $TESTDB $TESTUSR << EOF
CREATE TABLE T3
	(A INTEGER PRIMARY KEY, 
         B CHAR(128),
	 C VARCHAR(256), 
         D INTEGER);
CREATE INDEX T3_D_IDX ON T3
	USING GIST(D);
EOF

psql -a $TESTDB $TESTUSR << EOF
CHECKPOINT;
INSERT INTO T3(A) 
        SELECT * 
          FROM generate_series(1, $NUM_TUPLES);
CHECKPOINT;
UPDATE T3 
        SET B = 'xxxxxxxxxx', C = 'yyyyyyyyyyyyyyyyyyy', D = A;
CHECKPOINT;
DELETE FROM T3 
        WHERE A%10=0;
CHECKPOINT;
UPDATE T3 
        SET B = 'zzzxxxxxxx', C = 'zzzuyyyyyyyyyyyyyyy' 
          WHERE A%10=1;
CHECKPOINT;
EOF

MY_CKPT

#
# ===== DELETE all Gist entries and vacuum
#
psql -a $TESTDB $TESTUSR << EOF
BEGIN;
DELETE FROM T3;
COMMIT;
CHECKPOINT;
VACUUM T3;
VACUUM T3;
VACUUM FULL T3;
VACUUM FULL T3;
EOF

MY_CKPT

#
# ===== Now additional GiST for remaining WAL records =======
#
# Thank you Oleg-san.
#

psql -a $TESTDB $TESTUSR << EOF
create table a (i1 int, i2 int, i3 int, i4 int, i5 int, i6 int);
insert into a select n, n, n, n, n, n from generate_series(1, 100000) as n;
create index arr_gin on a using gin ( (array[i1, i2, i3, i4, i5, i6]) );
truncate a;
drop index arr_gin ;
create index arr_gin on a using gin ( (array[i1, i2, i3, i4, i5, i6]) );
insert into a select n, n, n, n, n, n from generate_series(1, 100000) as n;
select pg_size_pretty(pg_total_relation_size('a')) as total,
       pg_size_pretty(pg_relation_size('a')) as table;
EOF

MY_CKPT

#
# ===== Additional GIN test for remaining WAL records. ===========
#
# XLOG_GIN_CREATE_PTREE.sql -- Thank you Theodor.
# 
psql -a $TESTDB $TESTUSR << EOF
DROP TABLE IF EXISTS foo;

CREATE TABLE foo (
    bar int[]
) WITH (autovacuum_enabled = off);

INSERT INTO foo VALUES
('{0,1,2,3,4,5,6,7,8,9}'),
('{0,1,2,3,4,5,6,7,8}'),
('{0,1,2,3,4,5,6,7}'),
('{0,1,2,3,4,5,6}'),
('{0,1,2,3,4,5}'),
('{0,1,2,3,4}'),
('{0,1,2,3}'),
('{0,1,2}'),
('{0,1}'),
('{0,1}');


CREATE OR REPLACE FUNCTION dupcontent() 
RETURNS VOID AS
\$\$
INSERT INTO foo (SELECT * FROM foo);
\$\$ 
LANGUAGE SQL VOLATILE;

CREATE INDEX foo_bar_idx ON foo USING gin (bar) WITH (fastupdate=off);

SELECT dupcontent() FROM generate_series(1, 8);
EOF
#
# XLOG_GIN_DELETE_PAGE.sql -- Thank you Theodor.
# 
psql -a $TESTDB $TESTUSR << EOF
DROP TABLE IF EXISTS foo;

CREATE TABLE foo (
    bar int[]
) WITH (autovacuum_enabled = off);

INSERT INTO foo VALUES
('{0,1,2}'),
('{0,1}'),
('{0}');


CREATE OR REPLACE FUNCTION dupcontent() 
RETURNS VOID AS
\$\$
INSERT INTO foo (SELECT * FROM foo);
\$\$ 
LANGUAGE SQL VOLATILE;


SELECT dupcontent() FROM generate_series(1, 11);

CREATE INDEX foo_bar_idx ON foo USING gin (bar);

DELETE FROM foo;

VACUUM foo;
EOF

# ===== Tablespace =================================================

psql -a $TESTDB $TESTUSR << EOF
CREATE TABLESPACE $TABLESPACENAME LOCATION '$TABLESPACE'
EOF

MY_CKPT

# ======== Usual table and BTree Index with tablespace =============

# Test of usual table with BTree Index
#

psql -a -c "DROP TABLE IF EXISTS T5" $TESTDB $TESTUSR

psql -a $TESTDB $TESTUSR << EOF
DROP TABLE IF EXISTS T5;
CREATE TABLE T5 
	(A INTEGER PRIMARY KEY, 
         B CHAR(128), 
	 C VARCHAR(256), 
         D INTEGER)
	TABLESPACE $TABLESPACENAME;
CREATE INDEX T5_D_IDX ON T5 USING BTREE (D);
EOF

psql -a $TESTDB $TESTUSR << EOF
CHECKPOINT;
INSERT INTO T5(A) 
	SELECT * FROM generate_series(1, $NUM_TUPLES);
CHECKPOINT;
UPDATE T5 
	SET B = 'xxxxxxxxxx', C = 'yyyyyyyyyyyyyyyyyyy', D = A;
CHECKPOINT;
DELETE FROM T5 WHERE A%10=0;
CHECKPOINT;
UPDATE T5 
	SET B = 'zzzxxxxxxx', C = 'zzzuyyyyyyyyyyyyyyy' 
	WHERE A%10=1;
CHECKPOINT;
EOF

MY_CKPT

#
# ======= ALTER and DROP TABLESPACE ===========================

psql -a $TESTDB $TESTUSR << EOF
ALTER TABLESPACE $TABLESPACENAME RENAME TO $TABLESPACENAME_NEW
EOF

MY_CKPT

psql -a -c "DROP TABLE T5" $TESTDB $TESTUSR

psql -a $TESTDB $TESTUSR << EOF
DROP TABLESPACE $TABLESPACENAME_NEW
EOF

MY_CKPT

# ========== Sequence ===========================================
#

psql -a -c "DROP TABLE IF EXISTS T6" $TESTDB $TESTUSR

psql -a $TESTDB $TESTUSR << EOF
DROP TABLE IF EXISTS T6;
CREATE TABLE T6 
	(A INTEGER PRIMARY KEY, 
         B CHAR(128), 
	 C VARCHAR(256), 
         D SERIAL);
CREATE INDEX T6_D_IDX ON T6 USING BTREE (D);
EOF

psql -a $TESTDB $TESTUSR << EOF
CHECKPOINT;
INSERT INTO T6(A) 
	SELECT * FROM generate_series(1, $NUM_TUPLES);
CHECKPOINT;
UPDATE T6 
	SET B = 'xxxxxxxxxx', C = 'yyyyyyyyyyyyyyyyyyy';
CHECKPOINT;
DELETE FROM T6 WHERE A%10=0;
CHECKPOINT;
UPDATE T6 
	SET B = 'zzzxxxxxxx', C = 'zzzuyyyyyyyyyyyyyyy' 
	WHERE A%10=1;
CHECKPOINT;
EOF

MY_CKPT

#
# ======== XLOG_BTREE_INSERT_META =======
#
#

psql -a $TESTDB $TESTUSR <<EOF
DROP TABLE T7 IF EXISTS;
CREATE TABLE T7
       (A INTEGER PRIMARY KEY,
	B CHAR(128),
	C VARCHAR(256));
EOF

psql -a $TESTDB $TESTUSR <<EOF
INSERT INTO T7(A)
	SELECT * FROM generate_series(1, $NUM_TUPLES);
DELETE FROM T7;
VACUUM T7;
INSERT INTO T7(A)
	SELECT * FROM generate_series(1, $NUM_TUPLES);
EOF

#
# ======= CLOG Truncate Test ===========================
#
# It takes very long, maybe whole day, to generate
# tens of millions of transaction.
#
if [ $CLOG_TRUNCATE_OPTION = y ]
then
  NUM_TUP=500000
  NUM_TX=3000000
  MINKEY=1
  MAXKEY=$NUM_TUP

  echo '***** Beginning CLOG_TRUNCATE test ****'
  echo '   This may take very long.'

  psql -a $TESTDB $TESTUSR <<EOF
DROP TABLE IF EXISTS T9;
CREATE TABLE T9 (A INT PRIMARY KEY, B INT);
INSERT INTO T9(A) SELECT * FROM generate_series(1, $NUM_TUP);
EOF

  for i in 1 2 3 4 5 6 7 8 9 10 11 13 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 36 38 39 40 41 42 43 44 45 46 47 48 49 50
  do
    (./gen_tx $NUM_TX $MINKEY $MAXKEY | psql -q $TESTDB $TESTUSR )&
  done
  wait

fi

psql -a -c "VACUUM FREEZE" $TESTDB $TESTUSR

psql -a -c "select pg_switch_xlog()" $TESTDB $TESTUSR
psql -a -c "CHECKPOINT" $TESTDB $TESTUSR
psql -a -c "select pg_switch_xlog()" $TESTDB $TESTUSR
psql -a -c "CHECKPOINT" $TESTDB $TESTUSR
psql -a -c "select pg_switch_xlog()" $TESTDB $TESTUSR
psql -a -c "CHECKPOINT" $TESTDB $TESTUSR
psql -a -c "select pg_switch_xlog()" $TESTDB $TESTUSR
psql -a -c "CHECKPOINT" $TESTDB $TESTUSR
psql -a -c "select pg_switch_xlog()" $TESTDB $TESTUSR

pg_ctl stop

# ==========================================================
#
# Now begin to analyze the test result.
#
# ==========================================================

#
# -- Test pg_compresslog if archived by "cp" command
#

echo ""
echo "====== Compression/Decompression Test ==========="
echo ""

if [ $PGCOMPRESSLOG = PGCOMPRESSLOG_ALT ]
then
    pwd=`pwd`
    cd $ARCHLOGDIR
    for i in *[0-9A-F]
    do
      echo compress:$i "->" $i.compressed
      export LESSLOG_LOG=$i.log
      pg_compresslog $i $i.compressed
      echo decompress:$i.compressed "->" $i.decompressed
      pg_decompresslog $i.compressed $i.decompressed
      echo decompress:$i.decompressed "->" $i.bak
      export LESSLOG_LOG=$i.decompressed.log
      pg_compresslog $i.decompressed $i.bak
    done
    echo '=== Testing original XLOG compression'
    for i in `cat $pwd/record_list`
    do
      grep -q $i *[0-9A-F].log > /dev/null
      if [ $? = 0 ]
	  then
	    echo $i OK
	  else
	    echo $i NOT FOUND
      fi
    done
    grep -q Unknown *[0-9A-F].log > /dev/null
    if [ $? = 0 ]
       then
         echo "Unkown WAL record found."
       else
         echo "Unknown WAL record NOT found"
    fi
    echo '=== Testing compressed XLOG ==='
    for i in `cat $pwd/record_list`
    do
      (grep -q $i *.decompressed.log > /dev/null)
      if [ $? = 0 ]
	  then
	    echo $i OK
          else
	    echo $i NOT FOUND
      fi
    done
    grep -q Unknown *[0-9A-F].decompressed.log > /dev/null
    if [ $? = 0 ]
       then
         echo "Unkown WAL record found."
       else
         echo "Unknown WAL record NOT found"
    fi
    cd $pwd
else
    pwd=`pwd`
    cd $ARCHLOGDIR
    for i in *[0-9A-F]
    do
      echo decompress:$i "->" $i.decompressed
      pg_decompresslog $i $i.decompressed
      echo decompress:$i.decompressed "->" $i.bak
      export LESSLOG_LOG=$i.decompressed.log
      pg_compresslog $i.decompressed $i.bak
    done
    echo '=== Testing compressed XLOG ==='
    for i in `cat $pwd/record_list`
    do
      (grep -q $i *.decompressed.log > /dev/null)
      if [ $? = 0 ]
	  then
	    echo $i OK
          else
	    echo $i NOT FOUND
      fi
    done
    grep -q Unknown *.decompressed.log > /dev/null
    if [ $? = 0 ]
       then
         echo "Unkown WAL record found."
       else
         echo "Unknown WAL record NOT found"
    fi
    cd $pwd
    echo ""
    echo '*********** Starting Archive Recovery *****************'
    echo ""
    unset LESSLOG_LOG
    rm -rf $PGDATA
    rsync -a $BASEBACKUPDIR/data $PGROOT
    cat > $PGDATA/recovery.conf << _EOF_
restore_command = 'pg_decompresslog /var/postgresql/archivedir/%f "%p"'
_EOF_
    pg_ctl start
    psql -a $TESTDB $TESTUSER << EOF
SELECT pg_stop_backup();
\q
EOF
    pg_ctl stop
    echo ""
    echo '******************************************************************'
    echo "Now the recovery has been done."
    echo ""
    echo 'Depending on the number of tuples inserted (see $NUM_TUPLES value),'
    echo "it may take very long to replay the WAL and stop PG server."
    echo 'In this case, please try to run "pg_ctl stop" manually, or'
    echo "look into $PGLOGDIR log files to see that PG server stopped."
    echo ""
    echo "Check $PGDATA as well so that you have recovery.done file."
    echo "Detail of the recovery will be found in the operation log in "
    echo "$PGLOGDIR"
    echo '******************************************************************'
fi

rm gen_tx
rm record_list

echo ""
echo "======== Compression/Decompression Test Finished ============"
echo ""

