3
# This perl script removes entries from the pending,pendingKeys,
4
# pendingDeleteData tables that have already been mirrored to all hosts.
8
# Written by Steven Singer (ssinger@navtechinc.com)
9
# (c) 2001-2002 Navtech Systems Support Inc.
10
# Released under the GNU Public License version 2. See COPYING.
13
# This program is distributed in the hope that it will be useful,
14
# but WITHOUT ANY WARRANTY; without even the implied warranty of
15
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16
# GNU General Public License for more details.
18
##############################################################################
19
# $PostgreSQL: pgsql/contrib/dbmirror/clean_pending.pl,v 1.5 2004-09-10 04:31:06 neilc Exp $
20
##############################################################################
26
clean_pending.pl - A Perl script to remove old entries from the
27
pending, pendingKeys, and pendingDeleteData tables.
33
clean_pending.pl databasename
39
This Perl script connects to the database specified as a command line argument
40
on the local system. It uses a hard-coded username and password.
41
It then removes any entries from the pending, pendingDeleteData, and
42
pendingKeys tables that have already been sent to all hosts in mirrorHosts.
48
# add in a global path to files
49
#Ensure that Pg is in the path.
56
die "usage: clean_pending.pl configFile\n";
59
if( ! defined do $ARGV[0]) {
60
die("Invalid Configuration file $ARGV[0]");
63
#connect to the database.
65
my $connectString = "host=$::masterHost dbname=$::masterDb user=$::masterUser password=$::masterPassword";
67
my $dbConn = Pg::connectdb($connectString);
68
unless($dbConn->status == PGRES_CONNECTION_OK) {
69
printf("Can't connect to database\n");
72
my $result = $dbConn->exec("BEGIN");
73
unless($result->resultStatus == PGRES_COMMAND_OK) {
74
die $dbConn->errorMessage;
78
#delete all transactions that have been sent to all mirrorhosts
79
#or delete everything if no mirror hosts are defined.
80
# Postgres takes the "SELECT COUNT(*) FROM dbmirror_MirrorHost and makes it into
81
# an InitPlan. EXPLAIN show's this.
82
my $deletePendingQuery = 'DELETE FROM dbmirror_Pending WHERE (SELECT ';
83
$deletePendingQuery .= ' COUNT(*) FROM dbmirror_MirroredTransaction WHERE ';
84
$deletePendingQuery .= ' XID=dbmirror_Pending.XID) = (SELECT COUNT(*) FROM ';
85
$deletePendingQuery .= ' dbmirror_MirrorHost) OR (SELECT COUNT(*) FROM ';
86
$deletePendingQuery .= ' dbmirror_MirrorHost) = 0';
88
my $result = $dbConn->exec($deletePendingQuery);
89
unless ($result->resultStatus == PGRES_COMMAND_OK ) {
90
printf($dbConn->errorMessage);
93
$dbConn->exec("COMMIT");
94
$result = $dbConn->exec('VACUUM dbmirror_Pending');
95
unless ($result->resultStatus == PGRES_COMMAND_OK) {
96
printf($dbConn->errorMessage);
98
$result = $dbConn->exec('VACUUM dbmirror_PendingData');
99
unless($result->resultStatus == PGRES_COMMAND_OK) {
100
printf($dbConn->errorMessage);
102
$result = $dbConn->exec('VACUUM dbmirror_MirroredTransaction');
103
unless($result->resultStatus == PGRES_COMMAND_OK) {
104
printf($dbConn->errorMessage);