#!/bin/bash

me=$(basename $0)

usage() {
    cat <<EOF 2>&1
usage: $me [options] DATADIR
  $me checks the postgresql 9.3 instance at DATADIR for missing 
  oldestMultiXactId information. It then attempts to repair this by
  updating pg_database.datminmxid for each database to the mininum value
  of relminmxid for any table in that database. Finally it runs a checkpoint
  to cause oldestMultiXactId in pg_controldata to be updated.

  CAUTION: This may be unsafe. Make sure you are backed up.
  If nextMultiXactId in pg_controldata is < min(relminmxid) it may
  mean that the MultiXactId has wrapped around. This script does not
  know what to do then so it just gives up.

  options:
   -x        eXecute the update statements instead of just displaying them.
   -p PORT   port to connect to.
EOF
}

# defaults
execute=cat

while getopts \?xp: OPT ; do
    case $OPT in
       x) execute=psql ;;
       p) export PGPORT=$OPTARG ;;
       \?)
          usage
          exit 1
          ;;
    esac
done
shift $(($OPTIND - 1))

datadir=$1

if [ ! -r $datadir/PG_VERSION ] ; then
    echo "$datadir does not appear to be a postgresql data directory."
    exit 1
fi

dbs=$(psql -tAq postgres <<SQL
select datname
  from pg_database
  where  datminmxid = 1 and datallowconn
  order by datname
SQL
)

ctl_next_mxid=$(pg_controldata $datadir | sed -n -e 's/.* NextMultiXactId:  *//p')
ctl_oldest_mxid=$(pg_controldata $datadir | sed -n -e 's/.* oldestMultiXid:  *//p')
cat <<EOF

------ $me $datadir -----
--
-- NextMultiXactId = $ctl_next_mxid
-- OldestMultiXid  = $ctl_oldest_mxid
--
EOF

if [ "$ctl_oldest_mxid" != "1" ] ; then
    echo "-- OldestMultiXid appears to be valid, no action needed."
    exit 0
fi

( echo "begin;"
  for db in $dbs ; do
      relminmxid=$(psql $db -tAqc "select min(relminmxid::text::bigint) from pg_class where relkind = 'r' and not relminmxid = 1")
      datminmxid=$(psql $db -tAqc "select min(datminmxid::text::bigint) from pg_database where datname = current_database()")
      echo ""
      echo "-- $db  datminmxid = $datminmxid  min(relminmxid) = $relminmxid  mxid_age = $(($ctl_next_mxid - ${relminmxid:-0}))"
      if [ $relminmxid ] ; then
          echo "update pg_database set datminmxid = '$relminmxid' where datname = '$db';"
      fi
  done
  cat <<SQL

-- Set datminxid for any remaining databases with datminmxid = 1,
-- ie those that had min(relminmxid) = 1 or that do not accept connections."
update pg_database
    set datminmxid = (select min(datminmxid::text::bigint)::text::xid
                        from pg_database where not datminmxid = 1)
  where datminmxid = 1;
commit;
checkpoint;
SQL
) | $execute