blob: 485af493bf123686b75cc5a8aae70259dfe8d53a (
plain)
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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
|
# Copyright: (c) 2019, Andrew Klychkov (@Andersson007) <aaklychkov@mail.ru>
# GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt)
- vars:
task_parameters: &task_parameters
become_user: '{{ pg_user }}'
become: yes
register: result
pg_parameters: &pg_parameters
login_user: '{{ pg_user }}'
login_db: '{{ db_default }}'
block:
# Preparation:
# 0. create test schema
# 1. create test tables
# 2. create test indexes
# 3. create test functions
# 4. enable track_functions and restart
- name: Create schema
<<: *task_parameters
postgresql_schema:
<<: *pg_parameters
name: '{{ test_schema1 }}'
- name: Create test tables
<<: *task_parameters
postgresql_table:
<<: *pg_parameters
name: '{{ item }}'
columns:
- id int
loop:
- '{{ test_table1 }}'
- '{{ test_table2 }}'
- name: Create test table in another schema
<<: *task_parameters
postgresql_table:
<<: *pg_parameters
name: '{{ test_schema1 }}.{{ test_table3 }}'
- name: Create test indexes
<<: *task_parameters
postgresql_idx:
<<: *pg_parameters
name: '{{ item }}'
table: '{{ test_table1 }}'
columns:
- id
loop:
- '{{ test_idx1 }}'
- '{{ test_idx2 }}'
- name: Set track_function (restart is required)
<<: *task_parameters
postgresql_set:
<<: *pg_parameters
name: track_functions
value: all
- name: Restart PostgreSQL
become: yes
service:
name: "{{ postgresql_service }}"
state: restarted
- name: Create test functions
<<: *task_parameters
postgresql_query:
<<: *pg_parameters
query: 'CREATE FUNCTION {{ item }}() RETURNS boolean AS $$ BEGIN RETURN 1; END; $$ LANGUAGE PLPGSQL'
loop:
- '{{ test_func1 }}'
- '{{ test_func2 }}'
- '{{ test_schema1 }}.{{ test_func3 }}'
- name: Touch test functions
<<: *task_parameters
postgresql_query:
<<: *pg_parameters
query: 'SELECT {{ item }}()'
loop:
- '{{ test_func1 }}'
- '{{ test_func2 }}'
- '{{ test_schema1 }}.{{ test_func3 }}'
#######
# Tests
#######
# 0. Without filter
- name: Collect all stats
<<: *task_parameters
postgresql_user_obj_stat_info:
<<: *pg_parameters
- assert:
that:
- result is not changed
- result.tables.public.{{ test_table1 }}.size == 0
- result.tables.public.{{ test_table1 }}.size == 0
- result.tables.{{ test_schema1 }}.{{ test_table3 }}.size == 0
- result.functions.public.{{ test_func1 }}.calls == 1
- result.functions.public.{{ test_func2 }}.calls == 1
- result.functions.{{ test_schema1 }}.{{ test_func3 }}.calls == 1
- result.indexes.public.{{ test_idx1 }}.idx_scan == 0
- result.indexes.public.{{ test_idx2 }}.idx_scan == 0
# 1. With filter
- name: Collect stats with filter
<<: *task_parameters
postgresql_user_obj_stat_info:
<<: *pg_parameters
filter: tables, indexes
- assert:
that:
- result is not changed
- result.tables.public.{{ test_table1 }}.size == 0
- result.tables.public.{{ test_table1 }}.size == 0
- result.tables.{{ test_schema1 }}.{{ test_table3 }}.size == 0
- result.functions == {}
- result.indexes.public.{{ test_idx1 }}.idx_scan == 0
- result.indexes.public.{{ test_idx2 }}.idx_scan == 0
# 2. With schema
- name: Collect stats for objects in certain schema
<<: *task_parameters
postgresql_user_obj_stat_info:
<<: *pg_parameters
schema: public
- assert:
that:
- result is not changed
- result.tables.public.{{ test_table1 }}.size == 0
- result.tables.public.{{ test_table1 }}.size == 0
- result.indexes.public.{{ test_idx1 }}.idx_scan == 0
- result.indexes.public.{{ test_idx2 }}.idx_scan == 0
- result.functions.public.{{ test_func1 }}.calls == 1
- result.functions.public.{{ test_func2 }}.calls == 1
- result.tables.{{ test_schema1 }} is not defined
# 3. With wrong schema
- name: Try to collect data in nonexistent schema
<<: *task_parameters
postgresql_user_obj_stat_info:
<<: *pg_parameters
schema: nonexistent
ignore_errors: yes
- assert:
that:
- result is failed
- result.msg == "Schema 'nonexistent' does not exist"
##########
# Clean up
##########
- name: Drop schema
<<: *task_parameters
postgresql_schema:
<<: *pg_parameters
name: '{{ test_schema1 }}'
state: absent
cascade_drop: yes
- name: Drop test tables
<<: *task_parameters
postgresql_table:
<<: *pg_parameters
name: '{{ item }}'
state: absent
loop:
- '{{ test_table1 }}'
- '{{ test_table2 }}'
- name: Drop test functions
<<: *task_parameters
postgresql_query:
<<: *pg_parameters
query: 'DROP FUNCTION {{ item }}()'
loop:
- '{{ test_func1 }}'
- '{{ test_func2 }}'
- '{{ test_schema1 }}.{{ test_func3 }}'
ignore_errors: yes
|
