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
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
|
# 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)
# Preparation for tests.
# To implement the next steps, create the test table:
- name: postgresql_idx - create test table called test_table
become_user: "{{ pg_user }}"
become: yes
shell: psql postgres -U "{{ pg_user }}" -t -c "CREATE TABLE test_table (id int, story text);"
ignore_errors: yes
# Create a directory for test tablespace:
- name: postgresql_idx - drop test tablespace called ssd if exists
become_user: "{{ pg_user }}"
become: yes
shell: psql postgres -U "{{ pg_user }}" -t -c "DROP TABLESPACE IF EXISTS ssd;"
ignore_errors: yes
- name: postgresql_idx - drop dir for test tablespace
become: yes
file:
path: /mnt/ssd
state: absent
ignore_errors: yes
- name: postgresql_idx - create dir for test tablespace
become: yes
file:
path: /mnt/ssd
state: directory
owner: "{{ pg_user }}"
mode: 0755
ignore_errors: yes
# Then create a test tablespace:
- name: postgresql_idx - create test tablespace called ssd
become_user: "{{ pg_user }}"
become: yes
shell: psql postgres -U "{{ pg_user }}" -t -c "CREATE TABLESPACE ssd LOCATION '/mnt/ssd';"
ignore_errors: yes
register: tablespace
# Create a test schema:
- name: postgresql_idx - create test schema
become_user: "{{ pg_user }}"
become: yes
shell: psql postgres -U "{{ pg_user }}" -t -c "CREATE SCHEMA foo;"
ignore_errors: yes
# Create a table in schema foo:
- name: postgresql_idx - create table in non-default schema
become_user: "{{ pg_user }}"
become: yes
shell: psql postgres -U "{{ pg_user }}" -t -c "CREATE TABLE foo.foo_table (id int, story text);"
ignore_errors: yes
###############
# Do main tests
#
# Create btree index if not exists test_idx concurrently covering id and story columns
- name: postgresql_idx - create btree index concurrently
become_user: "{{ pg_user }}"
become: yes
postgresql_idx:
db: postgres
login_user: "{{ pg_user }}"
table: test_table
columns: id, story
idxname: test0_idx
register: result
ignore_errors: yes
- assert:
that:
- result.changed == true
- result.tblname == 'test_table'
- result.name == 'test0_idx'
- result.state == 'present'
- result.valid != ''
- result.tblspace == ''
- result.storage_params == []
- result.schema == 'public'
- result.query == 'CREATE INDEX CONCURRENTLY test0_idx ON public.test_table USING BTREE (id, story)'
# Check that if index exists that changes nothing
- name: postgresql_idx - try to create existing index again
become_user: "{{ pg_user }}"
become: yes
postgresql_idx:
db: postgres
login_user: "{{ pg_user }}"
table: test_table
columns: id, story
idxname: test0_idx
register: result
ignore_errors: yes
- assert:
that:
- result.changed == false
- result.tblname == 'test_table'
- result.name == 'test0_idx'
- result.state == 'present'
- result.valid != ''
- result.tblspace == ''
- result.storage_params == []
- result.schema == 'public'
- result.query == ''
# Create btree index foo_test_idx concurrently with tablespace called ssd,
# storage parameter, and non-default schema
- name: postgresql_idx - create btree index - non-default schema, tablespace, storage parameter
become_user: "{{ pg_user }}"
become: yes
postgresql_idx:
db: postgres
login_user: "{{ pg_user }}"
schema: foo
table: foo_table
columns:
- id
- story
idxname: foo_test_idx
tablespace: ssd
storage_params: fillfactor=90
register: result
ignore_errors: yes
- assert:
that:
- result.changed == true
- result.tblname == 'foo_table'
- result.name == 'foo_test_idx'
- result.state == 'present'
- result.valid != ''
- result.tblspace == 'ssd'
- result.storage_params == [ "fillfactor=90" ]
- result.schema == 'foo'
- result.query == 'CREATE INDEX CONCURRENTLY foo_test_idx ON foo.foo_table USING BTREE (id,story) WITH (fillfactor=90) TABLESPACE ssd'
when: tablespace.rc == 0
# Create brin index not in concurrently mode
- name: postgresql_idx - create brin index not concurrently
become_user: "{{ pg_user }}"
become: yes
postgresql_idx:
db: postgres
login_user: "{{ pg_user }}"
schema: public
table: test_table
state: present
type: brin
columns: id
idxname: test_brin_idx
concurrent: no
register: result
ignore_errors: yes
- assert:
that:
- result.changed == true
- result.tblname == 'test_table'
- result.name == 'test_brin_idx'
- result.state == 'present'
- result.valid != ''
- result.tblspace == ''
- result.storage_params == []
- result.schema == 'public'
- result.query == 'CREATE INDEX test_brin_idx ON public.test_table USING brin (id)'
when: postgres_version_resp.stdout is version('9.5', '>=')
# Create index where column id > 1
- name: postgresql_idx - create index with condition
become_user: "{{ pg_user }}"
become: yes
postgresql_idx:
db: postgres
login_user: "{{ pg_user }}"
table: test_table
columns: id
idxname: test1_idx
cond: id > 1 AND id != 10
register: result
ignore_errors: yes
- assert:
that:
- result.changed == true
- result.tblname == 'test_table'
- result.name == 'test1_idx'
- result.state == 'present'
- result.valid != ''
- result.tblspace == ''
- result.storage_params == []
- result.schema == 'public'
- result.query == 'CREATE INDEX CONCURRENTLY test1_idx ON public.test_table USING BTREE (id) WHERE id > 1 AND id != 10'
# Drop index from spacific schema with cascade
- name: postgresql_idx - drop index from specific schema cascade
become_user: "{{ pg_user }}"
become: yes
postgresql_idx:
db: postgres
login_user: "{{ pg_user }}"
schema: foo
name: foo_test_idx
cascade: yes
state: absent
concurrent: no
register: result
ignore_errors: yes
- assert:
that:
- result.changed == true
- result.name == 'foo_test_idx'
- result.state == 'absent'
- result.schema == 'foo'
- result.query == 'DROP INDEX foo.foo_test_idx CASCADE'
when: tablespace.rc == 0
# Try to drop not existing index
- name: postgresql_idx - try to drop not existing index
become_user: "{{ pg_user }}"
become: yes
postgresql_idx:
db: postgres
login_user: "{{ pg_user }}"
schema: foo
name: foo_test_idx
state: absent
register: result
ignore_errors: yes
- assert:
that:
- result.changed == false
- result.query == ''
|
