Annotation of /bin/MasterInit

#!/usr/bin/perl -w
# MasterInit
# Vadim Mikheev, (c) 2000, PostgreSQL Inc.

eval '(exit $?0)' && eval 'exec perl -S $0 ${1+"$@"}'
    & eval 'exec perl -S $0 $argv:q'
    if 0;

use strict;
use Pg;
use Getopt::Long;

$| = 1;

my ($debug,$verbose) = (0,0);
my ($help,$masterhost,$masterport,$masteruser,$masterpassword);
my $lib;

my $result = GetOptions(
        "debug!" => \$debug, "verbose!" => \$verbose, "help" => \$help,
        "masterhost=s" => \$masterhost, "masterport=i" => \$masterport,
        "masteruser=s" => \$masteruser, "masterpassword=s" => \$masterpassword,
        "lib=s" => \$lib,
);

if (defined($help) || (scalar(@ARGV) < 1)) {
    print "Usage: $0 [options] masterdb
Options:
        --masterhost=hostname --masterport=port
        --masteruser=username --masterpassword=string
        --lib=libpath
        [--masterserver=master_number]
";
    exit ((scalar(@ARGV) < 1)? 1:0);
}

my $master = $ARGV[0] || "master";

my $minfo = "dbname=$master";
$minfo = "$minfo host=$masterhost" if (defined($masterhost));
$minfo = "$minfo port=$masterport" if (defined($masterport));
$minfo = "$minfo user=$masteruser" if (defined($masteruser));
$minfo = "$minfo password=$masterpassword" if (defined($masterpassword));

if (!defined($lib) || !-e $lib) {
        # find my compiled rserv.so module
        $lib = $0; $lib =~ s#/[^/]+$#/../lib#;
        if ($lib =~ m#^\.#) {
                my $pwd = `pwd`;
                chomp($pwd);
                $lib = "$pwd/$lib/";
        }
        while ($lib =~ s#/[^/]+/\.\./#/#g) {};
        while ($lib =~ s#/\./#/#g) {};
        $lib =~ s#//#/#g;

        if (-e "$lib/rserv.so") {
                $lib .= "rserv.so";
        } else {
                print STDERR "Can't find compiled rserv.so in $lib. Go there and type make.\n";
                exit 1;
        }
}
print "Using lib '$lib'\n" if ($verbose);

sub RollbackAndQuit {
    my $conn = shift @_;

    print STDERR "Error in query: ", $conn->errorMessage;
    $conn->exec("ROLLBACK");
    exit (-1);
}

my $mconn = Pg::connectdb($minfo);
if ($mconn->status != PGRES_CONNECTION_OK) {
    print STDERR "Failed opening $minfo\n";
    exit 1;
}

$result = $mconn->exec("BEGIN");
RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

$result = $mconn->exec("set transaction isolation level serializable");
RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

# List of slave servers
$result = $mconn->exec("create table _RSERV_SERVERS_" .
                      " (server serial primary key, host text not ".
                      "null, port int4 default 5432, dbase text not ".
                      "null, unique(host,port,dbase))");
RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

# insert master server in list
$result = $mconn->exec("insert into _RSERV_SERVERS_" .
                      " (server, host, port, dbase) ".
                      " values (0,".
                      ($masterhost || "'localhost'").",".
                      ($masterport || 5432).",".
                      "'$master')");
RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

# List of replicated tables
$result = $mconn->exec("create table _RSERV_TABLES_" .
                      " (tname name not null, cname name not null, ".
                      "reloid oid primary key, key int4 not null)");
RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

## should always call MasterDelTable
#$result = $mconn->exec("CREATE RULE _rserv_deltrig_ AS ON delete to _RSERV_TABLES_ DO (DELETE FROM pg_trigger WHERE tgname='_rserv_trigger_t_' AND tgrelid=(SELECT oid FROM pg_class WHERE relname=old.tname);UPDATE pg_class SET reltriggers=reltriggers-1 WHERE relname=old.tname)");
#RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

# Bookkeeping log for row replication   
$result = $mconn->exec("create table _RSERV_LOG_" .
                      " (reloid oid REFERENCES _RSERV_TABLES_(reloid) ON ".
                      "DELETE CASCADE ON UPDATE CASCADE, logid int4 not ".
                      "null, logtime timestamp not null, insert smallint, ".
                      "update smallint, delete smallint, key text, ".
                      "server int4, ".
                      "CONSTRAINT only_one CHECK (insert+update+delete=1))");
RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

# This is to speedup lookup of deleted tuples
$result = $mconn->exec("create index _RSERV_LOG_INDX_DLT_ID_ on _RSERV_LOG_ ".
                      "(delete, logid) WHERE delete = 1");
RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

# This is to speedup lookup of updated tuples
$result = $mconn->exec("create index _RSERV_LOG_INDX_UPD_ID_ on _RSERV_LOG_ ".
                      "(update, logid) WHERE update = 1");
RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

# This is to speedup lookup of insert tuples
$result = $mconn->exec("create index _RSERV_LOG_INDX_INS_ID_ on _RSERV_LOG_ ".
                      "(insert, logid) WHERE insert = 1");
RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

# This is to speedup cleanup
$result = $mconn->exec("create index _RSERV_LOG_INDX_TM_ID_ on _RSERV_LOG_ ".
                      "(logtime, logid)");
RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

# This is to speedup trigger 
$result = $mconn->exec("create index _RSERV_LOG_INDX_REL_KEY_ on _RSERV_LOG_ ".
                      "(reloid, key)");
RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

# View to help managing _rserv_log_ table
$result = $mconn->exec("CREATE VIEW _RSERV_HUMAN_LOG_ AS SELECT log.logid, ".
                      "tab.tname AS table_name, tab.cname AS column_name, ".
                      "log.key AS column_value, log.insert, log.update, ".
                      "log.delete, log.logtime FROM _RSERV_LOG_ log, ".
                      "_RSERV_TABLES_ tab WHERE tab.reloid ".
                      "= log.reloid ORDER BY log.logtime");
RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

# View to help logging daily transactions
$result = $mconn->exec("CREATE VIEW _RSERV_DAILY_LOG_ AS ".
                      "SELECT count(*) AS \"# records\", ".
                      "to_char(_rserv_log_.logtime, 'YYYY-MM-DD') ".
                      "AS day FROM _rserv_log_ GROUP BY day");
RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

# Sync point for each slave server
$result = $mconn->exec("create table _RSERV_SYNC_ " .
                      "(server int REFERENCES _RSERV_SERVERS_(server) ".
                      "ON DELETE CASCADE ON UPDATE CASCADE, syncid int4 ".
                      "not null, synctime timestamp, status int4 not null,".
                      " minid int4 not null, maxid int4 not null, active text)");
RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

$result = $mconn->exec("create index _RSERV_SYNC_INDX_SRV_ID_ on _RSERV_SYNC_ ".
                      "(server, syncid)");
RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

# Sync point reference numbers
$result = $mconn->exec("create sequence _rserv_sync_seq_");
RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

$result = $mconn->exec("CREATE FUNCTION _rserv_log_() RETURNS opaque" .
                      " AS '$lib' LANGUAGE 'c'");
RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

$result = $mconn->exec("CREATE FUNCTION _rserv_sync_(int4) RETURNS int4" .
                      " AS '$lib' LANGUAGE 'c'");
RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

$result = $mconn->exec("CREATE FUNCTION _rserv_debug_(int4) RETURNS int4" .
                      " AS '$lib' LANGUAGE 'c'");
RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

$result = $mconn->exec("COMMIT");
RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);

exit (0);
1	dpavlin	1.5	#!/usr/bin/perl -w
2	dpavlin	1.1	# MasterInit
3			# Vadim Mikheev, (c) 2000, PostgreSQL Inc.
4
5			eval '(exit $?0)' && eval 'exec perl -S $0 ${1+"$@"}'
6			& eval 'exec perl -S $0 $argv:q'
7			if 0;
8
9	dpavlin	1.5	use strict;
10	dpavlin	1.1	use Pg;
11			use Getopt::Long;
12
13			$\| = 1;
14
15	dpavlin	1.5	my ($debug,$verbose) = (0,0);
16			my ($help,$masterhost,$masterport,$masteruser,$masterpassword);
17	dpavlin	1.6	my $lib;
18	dpavlin	1.1
19	dpavlin	1.5	my $result = GetOptions(
20			"debug!" => \$debug, "verbose!" => \$verbose, "help" => \$help,
21			"masterhost=s" => \$masterhost, "masterport=i" => \$masterport,
22			"masteruser=s" => \$masteruser, "masterpassword=s" => \$masterpassword,
23	dpavlin	1.8	"lib=s" => \$lib,
24			);
25	dpavlin	1.5
26			if (defined($help) \|\| (scalar(@ARGV) < 1)) {
27			print "Usage: $0 [options] masterdb
28			Options:
29			--masterhost=hostname --masterport=port
30			--masteruser=username --masterpassword=string
31			--lib=libpath
32	dpavlin	1.8	[--masterserver=master_number]
33	dpavlin	1.5	";
34	dpavlin	1.1	exit ((scalar(@ARGV) < 1)? 1:0);
35			}
36
37			my $master = $ARGV[0] \|\| "master";
38
39			my $minfo = "dbname=$master";
40	dpavlin	1.5	$minfo = "$minfo host=$masterhost" if (defined($masterhost));
41			$minfo = "$minfo port=$masterport" if (defined($masterport));
42			$minfo = "$minfo user=$masteruser" if (defined($masteruser));
43			$minfo = "$minfo password=$masterpassword" if (defined($masterpassword));
44	dpavlin	1.3
45	dpavlin	1.6	if (!defined($lib) \|\| !-e $lib) {
46			# find my compiled rserv.so module
47			$lib = $0; $lib =~ s#/[^/]+$#/../lib#;
48			if ($lib =~ m#^\.#) {
49			my $pwd = `pwd`;
50			chomp($pwd);
51			$lib = "$pwd/$lib/";
52			}
53			while ($lib =~ s#/[^/]+/\.\./#/#g) {};
54			while ($lib =~ s#/\./#/#g) {};
55			$lib =~ s#//#/#g;
56
57			if (-e "$lib/rserv.so") {
58			$lib .= "rserv.so";
59			} else {
60			print STDERR "Can't find compiled rserv.so in $lib. Go there and type make.\n";
61			exit 1;
62			}
63			}
64			print "Using lib '$lib'\n" if ($verbose);
65
66	dpavlin	1.1	sub RollbackAndQuit {
67	dpavlin	1.5	my $conn = shift @_;
68	dpavlin	1.1
69			print STDERR "Error in query: ", $conn->errorMessage;
70			$conn->exec("ROLLBACK");
71			exit (-1);
72			}
73
74	dpavlin	1.9	my $mconn = Pg::connectdb($minfo);
75			if ($mconn->status != PGRES_CONNECTION_OK) {
76	dpavlin	1.1	print STDERR "Failed opening $minfo\n";
77			exit 1;
78			}
79
80	dpavlin	1.9	$result = $mconn->exec("BEGIN");
81			RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
82	dpavlin	1.1
83	dpavlin	1.9	$result = $mconn->exec("set transaction isolation level serializable");
84			RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
85	dpavlin	1.1
86			# List of slave servers
87	dpavlin	1.9	$result = $mconn->exec("create table _RSERV_SERVERS_" .
88	dpavlin	1.3	" (server serial primary key, host text not ".
89			"null, port int4 default 5432, dbase text not ".
90			"null, unique(host,port,dbase))");
91	dpavlin	1.9	RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
92	dpavlin	1.1
93	dpavlin	1.8	# insert master server in list
94	dpavlin	1.9	$result = $mconn->exec("insert into _RSERV_SERVERS_" .
95	dpavlin	1.8	" (server, host, port, dbase) ".
96	dpavlin	1.9	" values (0,".
97	dpavlin	1.8	($masterhost \|\| "'localhost'").",".
98			($masterport \|\| 5432).",".
99			"'$master')");
100	dpavlin	1.9	RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
101	dpavlin	1.8
102	dpavlin	1.1	# List of replicated tables
103	dpavlin	1.9	$result = $mconn->exec("create table _RSERV_TABLES_" .
104	dpavlin	1.3	" (tname name not null, cname name not null, ".
105			"reloid oid primary key, key int4 not null)");
106	dpavlin	1.9	RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
107	dpavlin	1.1
108	dpavlin	1.3	## should always call MasterDelTable
109	dpavlin	1.9	#$result = $mconn->exec("CREATE RULE _rserv_deltrig_ AS ON delete to _RSERV_TABLES_ DO (DELETE FROM pg_trigger WHERE tgname='_rserv_trigger_t_' AND tgrelid=(SELECT oid FROM pg_class WHERE relname=old.tname);UPDATE pg_class SET reltriggers=reltriggers-1 WHERE relname=old.tname)");
110			#RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
111	dpavlin	1.3
112			# Bookkeeping log for row replication
113	dpavlin	1.9	$result = $mconn->exec("create table _RSERV_LOG_" .
114	dpavlin	1.3	" (reloid oid REFERENCES _RSERV_TABLES_(reloid) ON ".
115			"DELETE CASCADE ON UPDATE CASCADE, logid int4 not ".
116			"null, logtime timestamp not null, insert smallint, ".
117			"update smallint, delete smallint, key text, ".
118	dpavlin	1.7	"server int4, ".
119	dpavlin	1.3	"CONSTRAINT only_one CHECK (insert+update+delete=1))");
120	dpavlin	1.9	RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
121	dpavlin	1.1
122			# This is to speedup lookup of deleted tuples
123	dpavlin	1.9	$result = $mconn->exec("create index _RSERV_LOG_INDX_DLT_ID_ on _RSERV_LOG_ ".
124	dpavlin	1.3	"(delete, logid) WHERE delete = 1");
125	dpavlin	1.9	RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
126	dpavlin	1.3
127			# This is to speedup lookup of updated tuples
128	dpavlin	1.9	$result = $mconn->exec("create index _RSERV_LOG_INDX_UPD_ID_ on _RSERV_LOG_ ".
129	dpavlin	1.3	"(update, logid) WHERE update = 1");
130	dpavlin	1.9	RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
131	dpavlin	1.3
132			# This is to speedup lookup of insert tuples
133	dpavlin	1.9	$result = $mconn->exec("create index _RSERV_LOG_INDX_INS_ID_ on _RSERV_LOG_ ".
134	dpavlin	1.3	"(insert, logid) WHERE insert = 1");
135	dpavlin	1.9	RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
136	dpavlin	1.1
137			# This is to speedup cleanup
138	dpavlin	1.9	$result = $mconn->exec("create index _RSERV_LOG_INDX_TM_ID_ on _RSERV_LOG_ ".
139	dpavlin	1.3	"(logtime, logid)");
140	dpavlin	1.9	RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
141	dpavlin	1.3
142			# This is to speedup trigger
143	dpavlin	1.9	$result = $mconn->exec("create index _RSERV_LOG_INDX_REL_KEY_ on _RSERV_LOG_ ".
144	dpavlin	1.3	"(reloid, key)");
145	dpavlin	1.9	RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
146	dpavlin	1.3
147			# View to help managing _rserv_log_ table
148	dpavlin	1.9	$result = $mconn->exec("CREATE VIEW _RSERV_HUMAN_LOG_ AS SELECT log.logid, ".
149	dpavlin	1.3	"tab.tname AS table_name, tab.cname AS column_name, ".
150			"log.key AS column_value, log.insert, log.update, ".
151			"log.delete, log.logtime FROM _RSERV_LOG_ log, ".
152			"_RSERV_TABLES_ tab WHERE tab.reloid ".
153			"= log.reloid ORDER BY log.logtime");
154	dpavlin	1.9	RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
155	dpavlin	1.1
156	dpavlin	1.3	# View to help logging daily transactions
157	dpavlin	1.9	$result = $mconn->exec("CREATE VIEW _RSERV_DAILY_LOG_ AS ".
158	dpavlin	1.3	"SELECT count(*) AS \"# records\", ".
159			"to_char(_rserv_log_.logtime, 'YYYY-MM-DD') ".
160			"AS day FROM _rserv_log_ GROUP BY day");
161	dpavlin	1.9	RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
162	dpavlin	1.1
163			# Sync point for each slave server
164	dpavlin	1.9	$result = $mconn->exec("create table _RSERV_SYNC_ " .
165	dpavlin	1.3	"(server int REFERENCES _RSERV_SERVERS_(server) ".
166			"ON DELETE CASCADE ON UPDATE CASCADE, syncid int4 ".
167			"not null, synctime timestamp, status int4 not null,".
168			" minid int4 not null, maxid int4 not null, active text)");
169	dpavlin	1.9	RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
170	dpavlin	1.1
171	dpavlin	1.9	$result = $mconn->exec("create index _RSERV_SYNC_INDX_SRV_ID_ on _RSERV_SYNC_ ".
172	dpavlin	1.3	"(server, syncid)");
173	dpavlin	1.9	RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
174	dpavlin	1.1
175			# Sync point reference numbers
176	dpavlin	1.9	$result = $mconn->exec("create sequence _rserv_sync_seq_");
177			RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
178	dpavlin	1.1
179	dpavlin	1.9	$result = $mconn->exec("CREATE FUNCTION _rserv_log_() RETURNS opaque" .
180	dpavlin	1.3	" AS '$lib' LANGUAGE 'c'");
181	dpavlin	1.9	RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
182	dpavlin	1.1
183	dpavlin	1.9	$result = $mconn->exec("CREATE FUNCTION _rserv_sync_(int4) RETURNS int4" .
184	dpavlin	1.3	" AS '$lib' LANGUAGE 'c'");
185	dpavlin	1.9	RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
186	dpavlin	1.1
187	dpavlin	1.9	$result = $mconn->exec("CREATE FUNCTION _rserv_debug_(int4) RETURNS int4" .
188	dpavlin	1.3	" AS '$lib' LANGUAGE 'c'");
189	dpavlin	1.9	RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
190	dpavlin	1.1
191	dpavlin	1.9	$result = $mconn->exec("COMMIT");
192			RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
193	dpavlin	1.1
194			exit (0);