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