1#!/usr/local/bin/python3.8
2# -*- coding: utf-8 -*-
3
4# Copyright: (c) 2018-2019, Andrew Klychkov (@Andersson007) <aaklychkov@mail.ru>
5# GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt)
6
7from __future__ import absolute_import, division, print_function
8__metaclass__ = type
9
10DOCUMENTATION = r'''
11---
12module: postgresql_idx
13short_description: Create or drop indexes from a PostgreSQL database
14description:
15- Create or drop indexes from a PostgreSQL database.
16
17options:
18  idxname:
19    description:
20    - Name of the index to create or drop.
21    type: str
22    required: true
23    aliases:
24    - name
25  db:
26    description:
27    - Name of database to connect to and where the index will be created/dropped.
28    type: str
29    aliases:
30    - login_db
31  session_role:
32    description:
33    - Switch to session_role after connecting.
34      The specified session_role must be a role that the current login_user is a member of.
35    - Permissions checking for SQL commands is carried out as though
36      the session_role were the one that had logged in originally.
37    type: str
38  schema:
39    description:
40    - Name of a database schema where the index will be created.
41    type: str
42  state:
43    description:
44    - Index state.
45    - C(present) implies the index will be created if it does not exist.
46    - C(absent) implies the index will be dropped if it exists.
47    type: str
48    default: present
49    choices: [ absent, present ]
50  table:
51    description:
52    - Table to create index on it.
53    - Mutually exclusive with I(state=absent).
54    type: str
55  columns:
56    description:
57    - List of index columns that need to be covered by index.
58    - Mutually exclusive with I(state=absent).
59    type: list
60    elements: str
61    aliases:
62    - column
63  cond:
64    description:
65    - Index conditions.
66    - Mutually exclusive with I(state=absent).
67    type: str
68  idxtype:
69    description:
70    - Index type (like btree, gist, gin, etc.).
71    - Mutually exclusive with I(state=absent).
72    type: str
73    aliases:
74    - type
75  concurrent:
76    description:
77    - Enable or disable concurrent mode (CREATE / DROP INDEX CONCURRENTLY).
78    - Pay attention, if I(concurrent=no), the table will be locked (ACCESS EXCLUSIVE) during the building process.
79      For more information about the lock levels see U(https://www.postgresql.org/docs/current/explicit-locking.html).
80    - If the building process was interrupted for any reason when I(cuncurrent=yes), the index becomes invalid.
81      In this case it should be dropped and created again.
82    - Mutually exclusive with I(cascade=yes).
83    type: bool
84    default: yes
85  unique:
86    description:
87    - Enable unique index.
88    - Only btree currently supports unique indexes.
89    type: bool
90    default: no
91    version_added: '0.2.0'
92  tablespace:
93    description:
94    - Set a tablespace for the index.
95    - Mutually exclusive with I(state=absent).
96    type: str
97  storage_params:
98    description:
99    - Storage parameters like fillfactor, vacuum_cleanup_index_scale_factor, etc.
100    - Mutually exclusive with I(state=absent).
101    type: list
102    elements: str
103  cascade:
104    description:
105    - Automatically drop objects that depend on the index,
106      and in turn all objects that depend on those objects.
107    - It used only with I(state=absent).
108    - Mutually exclusive with I(concurrent=yes).
109    type: bool
110    default: no
111  trust_input:
112    description:
113    - If C(no), check whether values of parameters I(idxname), I(session_role),
114      I(schema), I(table), I(columns), I(tablespace), I(storage_params),
115      I(cond) are potentially dangerous.
116    - It makes sense to use C(no) only when SQL injections via the parameters are possible.
117    type: bool
118    default: yes
119    version_added: '0.2.0'
120
121seealso:
122- module: community.postgresql.postgresql_table
123- module: community.postgresql.postgresql_tablespace
124- name: PostgreSQL indexes reference
125  description: General information about PostgreSQL indexes.
126  link: https://www.postgresql.org/docs/current/indexes.html
127- name: CREATE INDEX reference
128  description: Complete reference of the CREATE INDEX command documentation.
129  link: https://www.postgresql.org/docs/current/sql-createindex.html
130- name: ALTER INDEX reference
131  description: Complete reference of the ALTER INDEX command documentation.
132  link: https://www.postgresql.org/docs/current/sql-alterindex.html
133- name: DROP INDEX reference
134  description: Complete reference of the DROP INDEX command documentation.
135  link: https://www.postgresql.org/docs/current/sql-dropindex.html
136
137notes:
138- Supports C(check_mode).
139- The index building process can affect database performance.
140- To avoid table locks on production databases, use I(concurrent=yes) (default behavior).
141
142author:
143- Andrew Klychkov (@Andersson007)
144- Thomas O'Donnell (@andytom)
145
146extends_documentation_fragment:
147- community.postgresql.postgres
148
149'''
150
151EXAMPLES = r'''
152- name: Create btree index if not exists test_idx concurrently covering columns id and name of table products
153  community.postgresql.postgresql_idx:
154    db: acme
155    table: products
156    columns: id,name
157    name: test_idx
158
159- name: Create btree index test_idx concurrently with tablespace called ssd and storage parameter
160  community.postgresql.postgresql_idx:
161    db: acme
162    table: products
163    columns:
164    - id
165    - name
166    idxname: test_idx
167    tablespace: ssd
168    storage_params:
169    - fillfactor=90
170
171- name: Create gist index test_gist_idx concurrently on column geo_data of table map
172  community.postgresql.postgresql_idx:
173    db: somedb
174    table: map
175    idxtype: gist
176    columns: geo_data
177    idxname: test_gist_idx
178
179# Note: for the example below pg_trgm extension must be installed for gin_trgm_ops
180- name: Create gin index gin0_idx not concurrently on column comment of table test
181  community.postgresql.postgresql_idx:
182    idxname: gin0_idx
183    table: test
184    columns: comment gin_trgm_ops
185    concurrent: no
186    idxtype: gin
187
188- name: Drop btree test_idx concurrently
189  community.postgresql.postgresql_idx:
190    db: mydb
191    idxname: test_idx
192    state: absent
193
194- name: Drop test_idx cascade
195  community.postgresql.postgresql_idx:
196    db: mydb
197    idxname: test_idx
198    state: absent
199    cascade: yes
200    concurrent: no
201
202- name: Create btree index test_idx concurrently on columns id,comment where column id > 1
203  community.postgresql.postgresql_idx:
204    db: mydb
205    table: test
206    columns: id,comment
207    idxname: test_idx
208    cond: id > 1
209
210- name: Create unique btree index if not exists test_unique_idx on column name of table products
211  community.postgresql.postgresql_idx:
212    db: acme
213    table: products
214    columns: name
215    name: test_unique_idx
216    unique: yes
217    concurrent: no
218'''
219
220RETURN = r'''
221name:
222  description: Index name.
223  returned: always
224  type: str
225  sample: 'foo_idx'
226state:
227  description: Index state.
228  returned: always
229  type: str
230  sample: 'present'
231schema:
232  description: Schema where index exists.
233  returned: always
234  type: str
235  sample: 'public'
236tablespace:
237  description: Tablespace where index exists.
238  returned: always
239  type: str
240  sample: 'ssd'
241query:
242  description: Query that was tried to be executed.
243  returned: always
244  type: str
245  sample: 'CREATE INDEX CONCURRENTLY foo_idx ON test_table USING BTREE (id)'
246storage_params:
247  description: Index storage parameters.
248  returned: always
249  type: list
250  sample: [ "fillfactor=90" ]
251valid:
252  description: Index validity.
253  returned: always
254  type: bool
255  sample: true
256'''
257
258try:
259    from psycopg2.extras import DictCursor
260except ImportError:
261    # psycopg2 is checked by connect_to_db()
262    # from ansible.module_utils.postgres
263    pass
264
265from ansible.module_utils.basic import AnsibleModule
266from ansible_collections.community.postgresql.plugins.module_utils.database import check_input
267from ansible_collections.community.postgresql.plugins.module_utils.postgres import (
268    connect_to_db,
269    exec_sql,
270    get_conn_params,
271    postgres_common_argument_spec,
272)
273
274
275VALID_IDX_TYPES = ('BTREE', 'HASH', 'GIST', 'SPGIST', 'GIN', 'BRIN')
276
277
278# ===========================================
279# PostgreSQL module specific support methods.
280#
281
282class Index(object):
283
284    """Class for working with PostgreSQL indexes.
285
286    TODO:
287        1. Add possibility to change ownership
288        2. Add possibility to change tablespace
289        3. Add list called executed_queries (executed_query should be left too)
290        4. Use self.module instead of passing arguments to the methods whenever possible
291
292    Args:
293        module (AnsibleModule) -- object of AnsibleModule class
294        cursor (cursor) -- cursor object of psycopg2 library
295        schema (str) -- name of the index schema
296        name (str) -- name of the index
297
298    Attrs:
299        module (AnsibleModule) -- object of AnsibleModule class
300        cursor (cursor) -- cursor object of psycopg2 library
301        schema (str) -- name of the index schema
302        name (str) -- name of the index
303        exists (bool) -- flag the index exists in the DB or not
304        info (dict) -- dict that contents information about the index
305        executed_query (str) -- executed query
306    """
307
308    def __init__(self, module, cursor, schema, name):
309        self.name = name
310        if schema:
311            self.schema = schema
312        else:
313            self.schema = 'public'
314        self.module = module
315        self.cursor = cursor
316        self.info = {
317            'name': self.name,
318            'state': 'absent',
319            'schema': '',
320            'tblname': '',
321            'tblspace': '',
322            'valid': True,
323            'storage_params': [],
324        }
325        self.exists = False
326        self.__exists_in_db()
327        self.executed_query = ''
328
329    def get_info(self):
330        """Refresh index info.
331
332        Return self.info dict.
333        """
334        self.__exists_in_db()
335        return self.info
336
337    def __exists_in_db(self):
338        """Check index existence, collect info, add it to self.info dict.
339
340        Return True if the index exists, otherwise, return False.
341        """
342        query = ("SELECT i.schemaname, i.tablename, i.tablespace, "
343                 "pi.indisvalid, c.reloptions "
344                 "FROM pg_catalog.pg_indexes AS i "
345                 "JOIN pg_catalog.pg_class AS c "
346                 "ON i.indexname = c.relname "
347                 "JOIN pg_catalog.pg_index AS pi "
348                 "ON c.oid = pi.indexrelid "
349                 "WHERE i.indexname = %(name)s")
350
351        res = exec_sql(self, query, query_params={'name': self.name}, add_to_executed=False)
352        if res:
353            self.exists = True
354            self.info = dict(
355                name=self.name,
356                state='present',
357                schema=res[0][0],
358                tblname=res[0][1],
359                tblspace=res[0][2] if res[0][2] else '',
360                valid=res[0][3],
361                storage_params=res[0][4] if res[0][4] else [],
362            )
363            return True
364
365        else:
366            self.exists = False
367            return False
368
369    def create(self, tblname, idxtype, columns, cond, tblspace,
370               storage_params, concurrent=True, unique=False):
371        """Create PostgreSQL index.
372
373        Return True if success, otherwise, return False.
374
375        Args:
376            tblname (str) -- name of a table for the index
377            idxtype (str) -- type of the index like BTREE, BRIN, etc
378            columns (str) -- string of comma-separated columns that need to be covered by index
379            tblspace (str) -- tablespace for storing the index
380            storage_params (str) -- string of comma-separated storage parameters
381
382        Kwargs:
383            concurrent (bool) -- build index in concurrent mode, default True
384        """
385        if self.exists:
386            return False
387
388        if idxtype is None:
389            idxtype = "BTREE"
390
391        query = 'CREATE'
392
393        if unique:
394            query += ' UNIQUE'
395
396        query += ' INDEX'
397
398        if concurrent:
399            query += ' CONCURRENTLY'
400
401        query += ' "%s"' % self.name
402
403        query += ' ON "%s"."%s" ' % (self.schema, tblname)
404
405        query += 'USING %s (%s)' % (idxtype, columns)
406
407        if storage_params:
408            query += ' WITH (%s)' % storage_params
409
410        if tblspace:
411            query += ' TABLESPACE "%s"' % tblspace
412
413        if cond:
414            query += ' WHERE %s' % cond
415
416        self.executed_query = query
417
418        return exec_sql(self, query, return_bool=True, add_to_executed=False)
419
420    def drop(self, cascade=False, concurrent=True):
421        """Drop PostgreSQL index.
422
423        Return True if success, otherwise, return False.
424
425        Args:
426            schema (str) -- name of the index schema
427
428        Kwargs:
429            cascade (bool) -- automatically drop objects that depend on the index,
430                default False
431            concurrent (bool) -- build index in concurrent mode, default True
432        """
433        if not self.exists:
434            return False
435
436        query = 'DROP INDEX'
437
438        if concurrent:
439            query += ' CONCURRENTLY'
440
441        query += ' "%s"."%s"' % (self.schema, self.name)
442
443        if cascade:
444            query += ' CASCADE'
445
446        self.executed_query = query
447
448        return exec_sql(self, query, return_bool=True, add_to_executed=False)
449
450
451# ===========================================
452# Module execution.
453#
454
455
456def main():
457    argument_spec = postgres_common_argument_spec()
458    argument_spec.update(
459        idxname=dict(type='str', required=True, aliases=['name']),
460        db=dict(type='str', aliases=['login_db']),
461        state=dict(type='str', default='present', choices=['absent', 'present']),
462        concurrent=dict(type='bool', default=True),
463        unique=dict(type='bool', default=False),
464        table=dict(type='str'),
465        idxtype=dict(type='str', aliases=['type']),
466        columns=dict(type='list', elements='str', aliases=['column']),
467        cond=dict(type='str'),
468        session_role=dict(type='str'),
469        tablespace=dict(type='str'),
470        storage_params=dict(type='list', elements='str'),
471        cascade=dict(type='bool', default=False),
472        schema=dict(type='str'),
473        trust_input=dict(type='bool', default=True),
474    )
475    module = AnsibleModule(
476        argument_spec=argument_spec,
477        supports_check_mode=True,
478    )
479
480    idxname = module.params["idxname"]
481    state = module.params["state"]
482    concurrent = module.params["concurrent"]
483    unique = module.params["unique"]
484    table = module.params["table"]
485    idxtype = module.params["idxtype"]
486    columns = module.params["columns"]
487    cond = module.params["cond"]
488    tablespace = module.params["tablespace"]
489    storage_params = module.params["storage_params"]
490    cascade = module.params["cascade"]
491    schema = module.params["schema"]
492    session_role = module.params["session_role"]
493    trust_input = module.params["trust_input"]
494
495    if not trust_input:
496        # Check input for potentially dangerous elements:
497        check_input(module, idxname, session_role, schema, table, columns,
498                    tablespace, storage_params, cond)
499
500    if concurrent and cascade:
501        module.fail_json(msg="Concurrent mode and cascade parameters are mutually exclusive")
502
503    if unique and (idxtype and idxtype != 'btree'):
504        module.fail_json(msg="Only btree currently supports unique indexes")
505
506    if state == 'present':
507        if not table:
508            module.fail_json(msg="Table must be specified")
509        if not columns:
510            module.fail_json(msg="At least one column must be specified")
511    else:
512        if table or columns or cond or idxtype or tablespace:
513            module.fail_json(msg="Index %s is going to be removed, so it does not "
514                                 "make sense to pass a table name, columns, conditions, "
515                                 "index type, or tablespace" % idxname)
516
517    if cascade and state != 'absent':
518        module.fail_json(msg="cascade parameter used only with state=absent")
519
520    conn_params = get_conn_params(module, module.params)
521    db_connection = connect_to_db(module, conn_params, autocommit=True)
522    cursor = db_connection.cursor(cursor_factory=DictCursor)
523
524    # Set defaults:
525    changed = False
526
527    # Do job:
528    index = Index(module, cursor, schema, idxname)
529    kw = index.get_info()
530    kw['query'] = ''
531
532    #
533    # check_mode start
534    if module.check_mode:
535        if state == 'present' and index.exists:
536            kw['changed'] = False
537            module.exit_json(**kw)
538
539        elif state == 'present' and not index.exists:
540            kw['changed'] = True
541            module.exit_json(**kw)
542
543        elif state == 'absent' and not index.exists:
544            kw['changed'] = False
545            module.exit_json(**kw)
546
547        elif state == 'absent' and index.exists:
548            kw['changed'] = True
549            module.exit_json(**kw)
550    # check_mode end
551    #
552
553    if state == "present":
554        if idxtype and idxtype.upper() not in VALID_IDX_TYPES:
555            module.fail_json(msg="Index type '%s' of %s is not in valid types" % (idxtype, idxname))
556
557        columns = ','.join(columns)
558
559        if storage_params:
560            storage_params = ','.join(storage_params)
561
562        changed = index.create(table, idxtype, columns, cond, tablespace, storage_params, concurrent, unique)
563
564        if changed:
565            kw = index.get_info()
566            kw['state'] = 'present'
567            kw['query'] = index.executed_query
568
569    else:
570        changed = index.drop(cascade, concurrent)
571
572        if changed:
573            kw['state'] = 'absent'
574            kw['query'] = index.executed_query
575
576    if not kw['valid']:
577        db_connection.rollback()
578        module.warn("Index %s is invalid! ROLLBACK" % idxname)
579
580    if not concurrent:
581        db_connection.commit()
582
583    kw['changed'] = changed
584    db_connection.close()
585    module.exit_json(**kw)
586
587
588if __name__ == '__main__':
589    main()
590