tablearg.sh

application/x-shellscript

Download

#!/bin/bash
#
# Test that multiple --table arguments are allowed in:
# pg_restore
# clusterdb
# vacuumdb
# reindexdb
#
# Also test multiple --index options in:
# reindexdb
#
# Karl O. Pinc <kop@meme.com>
#
# To use:
# Run before and after patch, diff the results.
# E.g.: ./tablearg.sh  > /tmp/unpatched 2>&1

# Watch what we're doing
set -v

export dumpfile=/tmp/$0.$$
export dumpsql=/tmp/$0.sql.$$

cleanup () {
  rm -f $dumpfile $dumpsql
}

trap cleanup exit quit

sendmsg () {
  printf ' ------------------------------------------------------------\n'
  printf "\n$1\n\n"
}

maketestdb () {
  psql postgres <<\EOF
create database testdb;
EOF
}
setup () {
  sendmsg 'Creating testdb'
  maketestdb
  populate
  sendmsg 'Done creating testdb'
}

populate () {
  psql testdb <<\EOF
create table foo (id serial primary key
                , data text);
create table bar (id serial primary key
                , fookey int constraint "fookey" references foo
                , data text);
create table baz (id serial primary key
                , fookey int constraint "fookey" references foo
                , barkey int constraint "barkey" references bar
                , data text);

create index "bar_foo_id" on bar (fookey, id);
create unique index "baz_foo_bar" on baz (id, fookey, barkey);

cluster foo using foo_pkey;
cluster bar using bar_foo_id;
cluster baz using baz_foo_bar;

insert into foo (data)
  values ('row1'), ('row2'), ('row3');
insert into bar (fookey, data)
  values (1, 'bar1'), (1, 'bar2'), (1, 'bar3');
insert into baz (fookey, barkey, data)
  values (1, 3, 'baz1'), (2, 2, 'baz2'), (3, 3, 'baz3');

EOF
}

teardown () {
  sendmsg 'Dropping testdb'
  psql postgres <<\EOF
drop database testdb;
EOF
  sendmsg 'Done dropping testdb'
}

diffdump () {
diff -u $dumpsql <(pg_dump testdb)
}

# Test pg_restore (0-3 --table arguments)

sendmsg 'Testing pg_restore help output'
pg_restore --help

sendmsg 'Creating test dump'
setup
pg_dump testdb > $dumpsql
pg_dump -Fc testdb > $dumpfile
teardown

sendmsg 'restoring whole test dump'
maketestdb
pg_restore -d testdb $dumpfile
pg_dump testdb
teardown

sendmsg 'restoring table foo'
maketestdb
pg_restore -d testdb --table foo $dumpfile
pg_dump testdb
teardown

sendmsg 'restoring tables foo, bar'
maketestdb
pg_restore -d testdb --table foo --table bar $dumpfile
pg_dump testdb
teardown

sendmsg 'restoring tables foo, bar, baz'
maketestdb
pg_restore -d testdb --table foo --table bar --table baz $dumpfile
pg_dump testdb
teardown

sendmsg 'restoring tables baz, bar, foo'
maketestdb
pg_restore -d testdb --table baz --table bar --table foo $dumpfile
pg_dump testdb
teardown

# Test clusterdb, (0-3 --table arguments)
sendmsg 'testing clusterdb --help'
clusterdb --help

sendmsg 'cluster of everything'
setup
clusterdb testdb
diffdump
teardown

sendmsg 'cluster baz'
setup
clusterdb --table baz testdb
diffdump
teardown

sendmsg 'cluster baz, bar'
setup
clusterdb --table baz --table bar testdb
diffdump
teardown

sendmsg 'cluster baz, foo, bar'
setup
clusterdb --table baz --table bar --table foo testdb
diffdump
teardown

# Test vacuumdb (0-3 --table arguments)
sendmsg 'testing vacuumdb --help'
vacuumdb --help

setup

sendmsg 'vacuumdb of everything'
vacuumdb testdb

sendmsg 'vacuumdb of foo'
vacuumdb --table foo testdb

sendmsg 'vacuumdb of foo, bar'
vacuumdb --table foo --table bar testdb

sendmsg 'vacuumdb of foo, bar, baz'
vacuumdb --table foo --table bar --table baz testdb

teardown

# Test reindexdb (0-3 --table arguments)

setup

sendmsg 'reindexdb of everything'
reindexdb testdb

sendmsg 'reindexdb of foo'
reindexdb --table foo testdb

sendmsg 'reindexdb of foo, bar'
reindexdb --table foo --table bar testdb

sendmsg 'reindexdb of foo, bar, baz'
reindexdb --table foo --table bar --table baz testdb

teardown

# Test reindexdb (0-3 --index arguments)

setup

sendmsg 'reindexdb of foo'
reindexdb --index foo_pkey testdb

sendmsg 'reindexdb of foo, bar'
reindexdb --index foo_pkey --index bar_foo_id testdb

sendmsg 'reindexdb of foo, bar, baz'
reindexdb --index foo_pkey --index bar_foo_id --index baz_foo_bar testdb

teardown