10 |
|
|
11 |
create table _RSERV_SERVERS_ |
create table _RSERV_SERVERS_ |
12 |
( |
( |
13 |
server int4, -- slave server id |
server serial primary key, -- slave server id |
14 |
host text, -- server' host |
host text not null, -- server' host |
15 |
port int4, -- server' port |
port int4 default 5432, -- server' port |
16 |
dbase text -- db name |
dbase text not null, -- db name |
17 |
|
unique(host,port,dbase) |
18 |
); |
); |
19 |
|
|
20 |
|
|
25 |
|
|
26 |
create table _RSERV_TABLES_ |
create table _RSERV_TABLES_ |
27 |
( |
( |
28 |
tname name, -- table name |
tname name not null, -- table name |
29 |
cname name, -- column name |
cname name not null, -- column name |
30 |
reloid oid, -- table oid |
reloid oid primary key, -- table oid |
31 |
key int4 -- key attnum |
key int4 not null -- key attnum |
32 |
); |
); |
33 |
|
|
34 |
|
|
39 |
|
|
40 |
create table _RSERV_LOG_ |
create table _RSERV_LOG_ |
41 |
( |
( |
42 |
reloid oid, |
reloid oid references _RSERV_TABLES_(reloid) |
43 |
logid int4, -- xid of last update xaction |
on delete cascade on update cascade, |
44 |
logtime timestamp, -- last update xaction start time |
logid int4 not null, -- xid of last update xaction |
45 |
deleted int4, -- deleted or inserted/updated |
logtime timestamp not null, -- last update xaction start time |
46 |
key text -- |
delete smallint, -- deleted |
47 |
|
update smallint, -- updated |
48 |
|
insert smallint, -- inserted |
49 |
|
key text, -- primary key in table |
50 |
|
constraint only_one check (insert+update+delete=1) |
51 |
); |
); |
52 |
|
|
53 |
-- This is to speedup lookup deleted tuples |
-- This is to speedup lookup deleted, updated and inserted tuples |
54 |
create index _RSERV_LOG_INDX_DLT_ID_ on _RSERV_LOG_ (deleted, logid); |
create index _RSERV_LOG_INDX_DLT_ID_ on _RSERV_LOG_ (delete, logid) |
55 |
|
where delete = 1; |
56 |
|
|
57 |
|
create index _RSERV_LOG_INDX_UPD_ID_ on _RSERV_LOG_ (update, logid) |
58 |
|
where update = 1; |
59 |
|
|
60 |
|
create index _RSERV_LOG_INDX_INS_ID_ on _RSERV_LOG_ (insert, logid) |
61 |
|
where insert = 1; |
62 |
|
|
63 |
-- This is to speedup cleanup |
-- This is to speedup cleanup |
64 |
create index _RSERV_LOG_INDX_TM_ID_ on _RSERV_LOG_ (logtime, logid); |
create index _RSERV_LOG_INDX_TM_ID_ on _RSERV_LOG_ (logtime, logid); |
66 |
-- This is to speedup trigger and lookup updated tuples |
-- This is to speedup trigger and lookup updated tuples |
67 |
create index _RSERV_LOG_INDX_REL_KEY_ on _RSERV_LOG_ (reloid, key); |
create index _RSERV_LOG_INDX_REL_KEY_ on _RSERV_LOG_ (reloid, key); |
68 |
|
|
69 |
|
-- 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 |
|
|
82 |
-- |
-- |
|
-- How much each slave servers are sync-ed |
|
|
-- |
|
83 |
drop table _RSERV_SYNC_; |
drop table _RSERV_SYNC_; |
84 |
|
|
85 |
create table _RSERV_SYNC_ |
create table _RSERV_SYNC_ |
86 |
( |
( |
87 |
server int4, |
server int4 references _RSERV_SERVERS_(server) |
88 |
syncid int4, -- from _rserv_sync_seq_ |
on delete cascade on update cascade, |
89 |
synctime timestamp, -- |
syncid int4 not null, -- from _rserv_sync_seq_ |
90 |
status int4, -- prepared (0) | applied |
synctime timestamp, |
91 |
minid int4, -- min xid from serializable snapshot |
status int4 not null, -- prepared (0) | applied |
92 |
maxid int4, -- max xid from serializable snapshot |
minid int4 not null, -- min xid from serializable snapshot |
93 |
active text -- list of active xactions |
maxid int4 not null, -- max xid from serializable snapshot |
94 |
|
active text -- list of active xactions |
95 |
); |
); |
96 |
|
|
97 |
create index _RSERV_SYNC_INDX_SRV_ID_ on _RSERV_SYNC_ (server, syncid); |
create index _RSERV_SYNC_INDX_SRV_ID_ on _RSERV_SYNC_ (server, syncid); |