1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
|
SET client_min_messages=ERROR;
/*
Create a table to store snapshots of the pg_stat_user_tables or the
pg_stat_all_tables views.
\d pg_stat_user_tables
View "pg_catalog.pg_stat_user_tables"
Column | Type | Modifiers
------------------+--------------------------+-----------
relid | oid |
schemaname | name |
relname | name |
seq_scan | bigint |
seq_tup_read | bigint |
idx_scan | bigint |
idx_tup_fetch | bigint |
n_tup_ins | bigint |
n_tup_upd | bigint |
n_tup_del | bigint |
n_tup_hot_upd | bigint |
n_live_tup | bigint |
n_dead_tup | bigint |
last_vacuum | timestamp with time zone |
last_autovacuum | timestamp with time zone |
last_analyze | timestamp with time zone |
last_autoanalyze | timestamp with time zone |
*/
CREATE TABLE DatabaseTableStats AS
SELECT
CAST(NULL AS timestamp without time zone) AS date_created,
schemaname, relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch,
n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup,
last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE FALSE;
ALTER TABLE DatabaseTableStats
ADD CONSTRAINT databasetablestats_pkey
PRIMARY KEY (date_created, schemaname, relname),
ALTER COLUMN date_created
SET DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
ALTER COLUMN date_created SET NOT NULL,
ALTER COLUMN schemaname SET NOT NULL,
ALTER COLUMN relname SET NOT NULL,
ALTER COLUMN seq_scan SET NOT NULL,
ALTER COLUMN seq_tup_read SET NOT NULL,
ALTER COLUMN idx_scan SET NOT NULL,
ALTER COLUMN idx_tup_fetch SET NOT NULL,
ALTER COLUMN n_tup_ins SET NOT NULL,
ALTER COLUMN n_tup_upd SET NOT NULL,
ALTER COLUMN n_tup_del SET NOT NULL,
ALTER COLUMN n_tup_hot_upd SET NOT NULL,
ALTER COLUMN n_live_tup SET NOT NULL,
ALTER COLUMN n_dead_tup SET NOT NULL;
CREATE TABLE DatabaseCpuStats (
date_created timestamp without time zone NOT NULL
DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
username text NOT NULL,
cpu integer NOT NULL,
CONSTRAINT databasecpustats_pkey PRIMARY KEY (date_created, username)
);
INSERT INTO LaunchpadDatabaseRevision VALUES (2207, 51, 0);
|