Contents of /bin/MasterInit

#!/usr/bin/perl
# 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 Pg;
use Getopt::Long;

$lib = '/usr/lib/postgresql/lib/rserv.so';

$| = 1;

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

my $debug = $opt_debug || 0;
my $verbose = $opt_verbose || 0;

if (defined($opt_help) || (scalar(@ARGV) < 1)) {
    print "Usage: $0 --masterhost=name --masterport=port\n\t--masteruser=name --masterpassword=string\n\t--lib=libpath masterdb\n";
    exit ((scalar(@ARGV) < 1)? 1:0);
}

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

my $minfo = "dbname=$master";
$minfo = "$minfo host=$opt_masterhost" if (defined($opt_masterhost));
$minfo = "$minfo user=$opt_masteruser" if (defined($opt_masteruser));
$minfo = "$minfo port=$opt_masterport" if (defined($opt_masterport));
$minfo = "$minfo password=$opt_masterpassword" if (defined($opt_masterpassword));

$lib = $opt_lib if (defined($opt_lib));

sub RollbackAndQuit {
    $conn = shift @_;

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

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

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

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

# List of slave servers
$result = $conn->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($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);

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

## should always call MasterDelTable
#$result = $conn->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($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);

# Bookkeeping log for row replication   
$result = $conn->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, ".
                      "CONSTRAINT only_one CHECK (insert+update+delete=1))");
RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);

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

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

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

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

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

# View to help managing _rserv_log_ table
$result = $conn->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($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);

# View to help logging daily transactions
$result = $conn->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($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);

# Sync point for each slave server
$result = $conn->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($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);

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

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

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

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

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

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

exit (0);
1	#!/usr/bin/perl
2	# 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	use Pg;
10	use Getopt::Long;
11
12	$lib = '/usr/lib/postgresql/lib/rserv.so';
13
14	$\| = 1;
15
16	$result = GetOptions("debug!", "verbose!", "help",
17	"masterhost=s", "masterport=i",
18	"masteruser=s", "masterpassword=s", "lib=s");
19
20	my $debug = $opt_debug \|\| 0;
21	my $verbose = $opt_verbose \|\| 0;
22
23	if (defined($opt_help) \|\| (scalar(@ARGV) < 1)) {
24	print "Usage: $0 --masterhost=name --masterport=port\n\t--masteruser=name --masterpassword=string\n\t--lib=libpath masterdb\n";
25	exit ((scalar(@ARGV) < 1)? 1:0);
26	}
27
28	my $master = $ARGV[0] \|\| "master";
29
30	my $minfo = "dbname=$master";
31	$minfo = "$minfo host=$opt_masterhost" if (defined($opt_masterhost));
32	$minfo = "$minfo user=$opt_masteruser" if (defined($opt_masteruser));
33	$minfo = "$minfo port=$opt_masterport" if (defined($opt_masterport));
34	$minfo = "$minfo password=$opt_masterpassword" if (defined($opt_masterpassword));
35
36	$lib = $opt_lib if (defined($opt_lib));
37
38	sub RollbackAndQuit {
39	$conn = shift @_;
40
41	print STDERR "Error in query: ", $conn->errorMessage;
42	$conn->exec("ROLLBACK");
43	exit (-1);
44	}
45
46	my $conn = Pg::connectdb($minfo);
47	if ($conn->status != PGRES_CONNECTION_OK) {
48	print STDERR "Failed opening $minfo\n";
49	exit 1;
50	}
51
52	my $result = $conn->exec("BEGIN");
53	RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
54
55	$result = $conn->exec("set transaction isolation level serializable");
56	RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
57
58	# List of slave servers
59	$result = $conn->exec("create table _RSERV_SERVERS_" .
60	" (server serial primary key, host text not ".
61	"null, port int4 default 5432, dbase text not ".
62	"null, unique(host,port,dbase))");
63	RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
64
65	# List of replicated tables
66	$result = $conn->exec("create table _RSERV_TABLES_" .
67	" (tname name not null, cname name not null, ".
68	"reloid oid primary key, key int4 not null)");
69	RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
70
71	## should always call MasterDelTable
72	#$result = $conn->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)");
73	#RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
74
75	# Bookkeeping log for row replication
76	$result = $conn->exec("create table _RSERV_LOG_" .
77	" (reloid oid REFERENCES _RSERV_TABLES_(reloid) ON ".
78	"DELETE CASCADE ON UPDATE CASCADE, logid int4 not ".
79	"null, logtime timestamp not null, insert smallint, ".
80	"update smallint, delete smallint, key text, ".
81	"CONSTRAINT only_one CHECK (insert+update+delete=1))");
82	RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
83
84	# This is to speedup lookup of deleted tuples
85	$result = $conn->exec("create index _RSERV_LOG_INDX_DLT_ID_ on _RSERV_LOG_ ".
86	"(delete, logid) WHERE delete = 1");
87	RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
88
89	# This is to speedup lookup of updated tuples
90	$result = $conn->exec("create index _RSERV_LOG_INDX_UPD_ID_ on _RSERV_LOG_ ".
91	"(update, logid) WHERE update = 1");
92	RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
93
94	# This is to speedup lookup of insert tuples
95	$result = $conn->exec("create index _RSERV_LOG_INDX_INS_ID_ on _RSERV_LOG_ ".
96	"(insert, logid) WHERE insert = 1");
97	RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
98
99	# This is to speedup cleanup
100	$result = $conn->exec("create index _RSERV_LOG_INDX_TM_ID_ on _RSERV_LOG_ ".
101	"(logtime, logid)");
102	RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
103
104	# This is to speedup trigger
105	$result = $conn->exec("create index _RSERV_LOG_INDX_REL_KEY_ on _RSERV_LOG_ ".
106	"(reloid, key)");
107	RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
108
109	# View to help managing _rserv_log_ table
110	$result = $conn->exec("CREATE VIEW _RSERV_HUMAN_LOG_ AS SELECT log.logid, ".
111	"tab.tname AS table_name, tab.cname AS column_name, ".
112	"log.key AS column_value, log.insert, log.update, ".
113	"log.delete, log.logtime FROM _RSERV_LOG_ log, ".
114	"_RSERV_TABLES_ tab WHERE tab.reloid ".
115	"= log.reloid ORDER BY log.logtime");
116	RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
117
118	# View to help logging daily transactions
119	$result = $conn->exec("CREATE VIEW _RSERV_DAILY_LOG_ AS ".
120	"SELECT count(*) AS \"# records\", ".
121	"to_char(_rserv_log_.logtime, 'YYYY-MM-DD') ".
122	"AS day FROM _rserv_log_ GROUP BY day");
123	RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
124
125	# Sync point for each slave server
126	$result = $conn->exec("create table _RSERV_SYNC_ " .
127	"(server int REFERENCES _RSERV_SERVERS_(server) ".
128	"ON DELETE CASCADE ON UPDATE CASCADE, syncid int4 ".
129	"not null, synctime timestamp, status int4 not null,".
130	" minid int4 not null, maxid int4 not null, active text)");
131	RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
132
133	$result = $conn->exec("create index _RSERV_SYNC_INDX_SRV_ID_ on _RSERV_SYNC_ ".
134	"(server, syncid)");
135	RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
136
137	# Sync point reference numbers
138	$result = $conn->exec("create sequence _rserv_sync_seq_");
139	RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
140
141	$result = $conn->exec("CREATE FUNCTION _rserv_log_() RETURNS opaque" .
142	" AS '$lib' LANGUAGE 'c'");
143	RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
144
145	$result = $conn->exec("CREATE FUNCTION _rserv_sync_(int4) RETURNS int4" .
146	" AS '$lib' LANGUAGE 'c'");
147	RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
148
149	$result = $conn->exec("CREATE FUNCTION _rserv_debug_(int4) RETURNS int4" .
150	" AS '$lib' LANGUAGE 'c'");
151	RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
152
153	$result = $conn->exec("COMMIT");
154	RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
155
156	exit (0);