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 |
dpavlin |
1.2 |
server serial primary key, -- slave server id |
14 |
|
|
host text not null, -- server' host |
15 |
|
|
port int4 default 5432, -- server' port |
16 |
|
|
dbase text not null, -- db name |
17 |
|
|
unique(host,port,dbase) |
18 |
dpavlin |
1.1 |
); |
19 |
|
|
|
20 |
|
|
|
21 |
|
|
-- |
22 |
|
|
-- Tables to sync |
23 |
|
|
-- |
24 |
|
|
drop table _RSERV_TABLES_; |
25 |
|
|
|
26 |
|
|
create table _RSERV_TABLES_ |
27 |
|
|
( |
28 |
dpavlin |
1.2 |
tname name not null, -- table name |
29 |
|
|
cname name not null, -- column name |
30 |
|
|
reloid oid primary key, -- table oid |
31 |
|
|
key int4 not null -- key attnum |
32 |
dpavlin |
1.1 |
); |
33 |
|
|
|
34 |
|
|
|
35 |
|
|
-- |
36 |
|
|
-- Log for inserts/updates/deletes to sync-ed tables |
37 |
|
|
-- |
38 |
|
|
drop table _RSERV_LOG_; |
39 |
|
|
|
40 |
|
|
create table _RSERV_LOG_ |
41 |
|
|
( |
42 |
dpavlin |
1.2 |
reloid oid references _RSERV_TABLES_(reloid) |
43 |
|
|
on delete cascade on update cascade, |
44 |
|
|
logid int4 not null, -- xid of last update xaction |
45 |
|
|
logtime timestamp not null, -- last update xaction start time |
46 |
dpavlin |
1.3 |
delete smallint, -- deleted |
47 |
|
|
update smallint, -- updated |
48 |
|
|
insert smallint, -- inserted |
49 |
dpavlin |
1.2 |
key text, -- primary key in table |
50 |
|
|
constraint only_one check (insert+update+delete=1) |
51 |
dpavlin |
1.1 |
); |
52 |
|
|
|
53 |
dpavlin |
1.2 |
-- This is to speedup lookup deleted, updated and inserted tuples |
54 |
dpavlin |
1.3 |
create index _RSERV_LOG_INDX_DLT_ID_ on _RSERV_LOG_ (delete, logid) |
55 |
dpavlin |
1.2 |
where delete = 1; |
56 |
|
|
|
57 |
dpavlin |
1.3 |
create index _RSERV_LOG_INDX_UPD_ID_ on _RSERV_LOG_ (update, logid) |
58 |
dpavlin |
1.2 |
where update = 1; |
59 |
|
|
|
60 |
dpavlin |
1.3 |
create index _RSERV_LOG_INDX_INS_ID_ on _RSERV_LOG_ (insert, logid) |
61 |
dpavlin |
1.2 |
where insert = 1; |
62 |
dpavlin |
1.1 |
|
63 |
|
|
-- This is to speedup cleanup |
64 |
|
|
create index _RSERV_LOG_INDX_TM_ID_ on _RSERV_LOG_ (logtime, logid); |
65 |
|
|
|
66 |
|
|
-- This is to speedup trigger and lookup updated tuples |
67 |
|
|
create index _RSERV_LOG_INDX_REL_KEY_ on _RSERV_LOG_ (reloid, key); |
68 |
|
|
|
69 |
dpavlin |
1.2 |
-- View to help managing _rserv_log_ table |
70 |
|
|
create view _RSERV_HUMAN_LOG_ as |
71 |
|
|
select log.logid, tab.tname as table_name, tab.cname as column_name, |
72 |
|
|
log.key as column_value, log.insert, log.update, log.delete, |
73 |
|
|
log.logtime from _RSERV_LOG_ log, _RSERV_TABLES_ tab |
74 |
|
|
where tab.reloid = log.reloid order by log.logtime; |
75 |
|
|
|
76 |
|
|
-- View to help logging daily transactions |
77 |
|
|
create view _RSERV_DAILY_LOG_ as |
78 |
|
|
select count(*) AS "# records", |
79 |
|
|
to_char(_rserv_log_.logtime, 'YYYY-MM-DD') as day |
80 |
|
|
from _rserv_log_ group by day; |
81 |
dpavlin |
1.1 |
|
82 |
|
|
-- |
83 |
|
|
drop table _RSERV_SYNC_; |
84 |
|
|
|
85 |
|
|
create table _RSERV_SYNC_ |
86 |
|
|
( |
87 |
dpavlin |
1.2 |
server int4 references _RSERV_SERVERS_(server) |
88 |
|
|
on delete cascade on update cascade, |
89 |
|
|
syncid int4 not null, -- from _rserv_sync_seq_ |
90 |
|
|
synctime timestamp, |
91 |
|
|
status int4 not null, -- prepared (0) | applied |
92 |
|
|
minid int4 not null, -- min xid from serializable snapshot |
93 |
|
|
maxid int4 not null, -- max xid from serializable snapshot |
94 |
|
|
active text -- list of active xactions |
95 |
dpavlin |
1.1 |
); |
96 |
|
|
|
97 |
|
|
create index _RSERV_SYNC_INDX_SRV_ID_ on _RSERV_SYNC_ (server, syncid); |
98 |
|
|
|
99 |
|
|
drop sequence _rserv_sync_seq_; |
100 |
|
|
create sequence _rserv_sync_seq_; |
101 |
|
|
|
102 |
|
|
drop function _rserv_log_(); |
103 |
|
|
|
104 |
|
|
CREATE FUNCTION _rserv_log_() |
105 |
|
|
RETURNS opaque |
106 |
|
|
AS '_OBJWD_/rserv_DLSUFFIX_' |
107 |
|
|
LANGUAGE 'c' |
108 |
|
|
; |
109 |
|
|
|
110 |
|
|
drop function _rserv_sync_(int4); |
111 |
|
|
|
112 |
|
|
CREATE FUNCTION _rserv_sync_(int4) |
113 |
|
|
RETURNS int4 |
114 |
|
|
AS '_OBJWD_/rserv_DLSUFFIX_' |
115 |
|
|
LANGUAGE 'c' |
116 |
|
|
; |
117 |
|
|
|
118 |
|
|
drop function _rserv_debug_(int4); |
119 |
|
|
|
120 |
|
|
CREATE FUNCTION _rserv_debug_(int4) |
121 |
|
|
RETURNS int4 |
122 |
|
|
AS '_OBJWD_/rserv_DLSUFFIX_' |
123 |
|
|
LANGUAGE 'c' |
124 |
|
|
; |