rserv/misc/master.sql

-- erServer
-- Master server setup for erServer demonstration implementation
-- (c) 2000 Vadim Mikheev, PostgreSQL Inc.
--

--
-- Slave servers
--
drop table _RSERV_SERVERS_;

create table _RSERV_SERVERS_
(
        server  int4,   -- slave server id
        host    text,   -- server' host
        port    int4,   -- server' port
        dbase   text    -- db name
);


--
-- Tables to sync
--
drop table _RSERV_TABLES_;

create table _RSERV_TABLES_
(
        tname  name,    -- table name
        cname  name,    -- column name
        reloid  oid,    -- table oid
        key             int4    -- key attnum 
);


--
-- Log for inserts/updates/deletes to sync-ed tables
--
drop table _RSERV_LOG_;

create table _RSERV_LOG_
(
        reloid          oid,
        logid           int4,           -- xid of last update xaction
        logtime         timestamp,      -- last update xaction start time
        deleted         int4,           -- deleted or inserted/updated
        key                     text            -- 
);

-- This is to speedup lookup deleted tuples
create index _RSERV_LOG_INDX_DLT_ID_ on _RSERV_LOG_ (deleted, logid);

-- This is to speedup cleanup
create index _RSERV_LOG_INDX_TM_ID_ on _RSERV_LOG_ (logtime, logid);

-- This is to speedup trigger and lookup updated tuples
create index _RSERV_LOG_INDX_REL_KEY_ on _RSERV_LOG_ (reloid, key);


--
-- How much each slave servers are sync-ed
--
drop table _RSERV_SYNC_;

create table _RSERV_SYNC_
(
        server          int4,
        syncid          int4,           -- from _rserv_sync_seq_
        synctime        timestamp,      -- 
        status          int4,           -- prepared (0) | applied
        minid           int4,           -- min xid from serializable snapshot
        maxid           int4,           -- max xid from serializable snapshot
        active          text            -- list of active xactions
);

create index _RSERV_SYNC_INDX_SRV_ID_ on _RSERV_SYNC_ (server, syncid);

drop sequence _rserv_sync_seq_;
create sequence _rserv_sync_seq_;

drop function _rserv_log_();

CREATE FUNCTION _rserv_log_()
        RETURNS opaque
        AS '_OBJWD_/rserv_DLSUFFIX_'
        LANGUAGE 'c'
;

drop function _rserv_sync_(int4);

CREATE FUNCTION _rserv_sync_(int4)
        RETURNS int4
        AS '_OBJWD_/rserv_DLSUFFIX_'
        LANGUAGE 'c'
;

drop function _rserv_debug_(int4);

CREATE FUNCTION _rserv_debug_(int4)
        RETURNS int4
        AS '_OBJWD_/rserv_DLSUFFIX_'
        LANGUAGE 'c'
;
1	dpavlin	1.1	-- erServer
2			-- Master server setup for erServer demonstration implementation
3			-- (c) 2000 Vadim Mikheev, PostgreSQL Inc.
4			--
5
6			--
7			-- Slave servers
8			--
9			drop table _RSERV_SERVERS_;
10
11			create table _RSERV_SERVERS_
12			(
13			server int4, -- slave server id
14			host text, -- server' host
15			port int4, -- server' port
16			dbase text -- db name
17			);
18
19
20			--
21			-- Tables to sync
22			--
23			drop table _RSERV_TABLES_;
24
25			create table _RSERV_TABLES_
26			(
27			tname name, -- table name
28			cname name, -- column name
29			reloid oid, -- table oid
30			key int4 -- key attnum
31			);
32
33
34			--
35			-- Log for inserts/updates/deletes to sync-ed tables
36			--
37			drop table _RSERV_LOG_;
38
39			create table _RSERV_LOG_
40			(
41			reloid oid,
42			logid int4, -- xid of last update xaction
43			logtime timestamp, -- last update xaction start time
44			deleted int4, -- deleted or inserted/updated
45			key text --
46			);
47
48			-- This is to speedup lookup deleted tuples
49			create index _RSERV_LOG_INDX_DLT_ID_ on _RSERV_LOG_ (deleted, logid);
50
51			-- This is to speedup cleanup
52			create index _RSERV_LOG_INDX_TM_ID_ on _RSERV_LOG_ (logtime, logid);
53
54			-- This is to speedup trigger and lookup updated tuples
55			create index _RSERV_LOG_INDX_REL_KEY_ on _RSERV_LOG_ (reloid, key);
56
57
58			--
59			-- How much each slave servers are sync-ed
60			--
61			drop table _RSERV_SYNC_;
62
63			create table _RSERV_SYNC_
64			(
65			server int4,
66			syncid int4, -- from _rserv_sync_seq_
67			synctime timestamp, --
68			status int4, -- prepared (0) \| applied
69			minid int4, -- min xid from serializable snapshot
70			maxid int4, -- max xid from serializable snapshot
71			active text -- list of active xactions
72			);
73
74			create index _RSERV_SYNC_INDX_SRV_ID_ on _RSERV_SYNC_ (server, syncid);
75
76			drop sequence _rserv_sync_seq_;
77			create sequence _rserv_sync_seq_;
78
79			drop function _rserv_log_();
80
81			CREATE FUNCTION _rserv_log_()
82			RETURNS opaque
83			AS '_OBJWD_/rserv_DLSUFFIX_'
84			LANGUAGE 'c'
85			;
86
87			drop function _rserv_sync_(int4);
88
89			CREATE FUNCTION _rserv_sync_(int4)
90			RETURNS int4
91			AS '_OBJWD_/rserv_DLSUFFIX_'
92			LANGUAGE 'c'
93			;
94
95			drop function _rserv_debug_(int4);
96
97			CREATE FUNCTION _rserv_debug_(int4)
98			RETURNS int4
99			AS '_OBJWD_/rserv_DLSUFFIX_'
100			LANGUAGE 'c'
101			;