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	-- 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	;