1394909 - Remove all signature_summary_* tables

Status: RESOLVED FIXED

(Socorro :: Database, task)

Description

[Peter Bengtsson [:peterbe]]

There are (I only peeked into stage's db) 1,913 signature_summary_* tables. 

We stopped the crontabber that fills these tables many months ago. There's also no way to access its content via the webapp in any way. 

We can now delete all their tables. And all stored procedures related to populating. 

Also, there are two points to remember additionally: The signature_summary_* tables are partitioned. For bootstrap the list of tables to partition is defined in https://github.com/mozilla-services/socorro/blob/2d92a505bb2a49a6f95bb133fcb625f45412ae4c/socorro/external/postgresql/staticdata.py#L168-L207 and there's an actual table in postgres that needs cleaning up too: 

breakpad=> select table_name from  report_partition_info where table_name like 'signature_summary_%';
           table_name
---------------------------------
 signature_summary_installations
 signature_summary_architecture
 signature_summary_graphics
 signature_summary_device
 signature_summary_flash_version
 signature_summary_os
 signature_summary_process_type
 signature_summary_products
 signature_summary_uptime
(9 rows)

Comment 1

[Peter Bengtsson [:peterbe]]

https://github.com/mozilla-services/socorro/pull/3950 has an active alembic migration under review. Let's wait for that to avoid conflicts.

Comment 2

[Peter Bengtsson [:peterbe]]

One exciting thing is the number of bytes we're going to set free...


breakpad=> select table_name, total, pg_size_pretty(total)
breakpad-> from (
breakpad(> select trim(trailing '_0123456789' from table_name) as table_name, sum(pg_relation_size(table_name)) as total
breakpad(> from information_schema.tables
breakpad(> where table_schema = 'public' and table_name like 'signature_summary%'
breakpad(> group by 1
breakpad(> ) as agg
breakpad-> order by 2 desc limit 30;
           table_name            |    total    | pg_size_pretty
---------------------------------+-------------+----------------
 signature_summary_graphics      | 12046245888 | 11 GB
 signature_summary_uptime        | 11199438848 | 10 GB
 signature_summary_flash_version |  7997546496 | 7627 MB
 signature_summary_process_type  |  7677337600 | 7322 MB
 signature_summary_architecture  |  7616561152 | 7264 MB
 signature_summary_products      |  6738747392 | 6427 MB
 signature_summary_installations |  6728278016 | 6417 MB
 signature_summary_os            |  4332830720 | 4132 MB
 signature_summary_device        |  4299464704 | 4100 MB
(9 rows)


breakpad=> select pg_size_pretty(sum(total))
breakpad-> from (
breakpad(> select trim(trailing '_0123456789' from table_name) as table_name, sum(pg_relation_size(table_name)) as total
breakpad(> from information_schema.tables
breakpad(> where table_schema = 'public' and table_name like 'signature_summary%'
breakpad(> group by 1
breakpad(> ) as agg;
 pg_size_pretty
----------------
 64 GB
(1 row)

Comment 3

[[github robot]]

Commit pushed to master at https://github.com/mozilla-services/socorro

https://github.com/mozilla-services/socorro/commit/2ba5e85e4bb215b290f81e03201a03b46d820030
fixes bug 1394909 - Remove all signature_summary_* tables (#3954)

* fixes bug 1394909 - Remove all signature_summary_* tables

* don't close the connection

* if exists silly

* typos everywhere today

* some macros are evil

* sigh

* remove temp_signature_summaries ref

Comment 4

[Peter Bengtsson [:peterbe]]

This failed to execute on -stage admin because of this error::


INFO  [alembic.runtime.migration] Running upgrade afc2f95a298b -> 07c6633fa1b6, delete signature summaries
Traceback (most recent call last):
  File "/data/socorro/socorro-virtualenv/bin/alembic", line 11, in <module>
    sys.exit(main())
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/alembic/config.py", line 471, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/alembic/config.py", line 465, in main
    self.run_cmd(cfg, options)
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/alembic/config.py", line 448, in run_cmd
    **dict((k, getattr(options, k)) for k in kwarg)
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/alembic/command.py", line 174, in upgrade
    script.run_env()
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/alembic/script/base.py", line 397, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/alembic/util/pyfiles.py", line 81, in load_python_file
    module = load_module_py(module_id, path)
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/alembic/util/compat.py", line 79, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "alembic/env.py", line 91, in <module>
    run_migrations_online()
  File "alembic/env.py", line 84, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/alembic/runtime/environment.py", line 797, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/alembic/runtime/migration.py", line 312, in run_migrations
    step.migration_fn(**kw)
  File "/data/socorro/application/alembic/versions/07c6633fa1b6_delete_signature_summaries.py", line 31, in upgrade
    op.execute('DROP TABLE IF EXISTS {}'.format(table_name))
  File "<string>", line 8, in execute
  File "<string>", line 3, in execute
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/alembic/operations/ops.py", line 1826, in execute
    return operations.invoke(op)
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/alembic/operations/base.py", line 318, in invoke
    return fn(self, operation)
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/alembic/operations/toimpl.py", line 161, in execute_sql
    execution_options=operation.execution_options
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/alembic/ddl/impl.py", line 121, in execute
    self._exec(sql, execution_options)
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/alembic/ddl/impl.py", line 118, in _exec
    return conn.execute(construct, *multiparams, **params)
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 729, in execute
    return meth(self, multiparams, params)
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 322, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 826, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 958, in _execute_context
    context)
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1159, in _handle_dbapi_exception
    exc_info
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 951, in _execute_context
    context)
  File "/data/socorro/socorro-virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 436, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.InternalError: (InternalError) cannot drop table signature_summary_os because other objects depend on it
DETAIL:  table signature_summary_os_20130603 depends on table signature_summary_os
table signature_summary_os_20130610 depends on table signature_summary_os
table signature_summary_os_20130617 depends on table signature_summary_os
table signature_summary_os_20130624 depends on table signature_summary_os
....

Comment 5

[[github robot]]

Commit pushed to master at https://github.com/mozilla-services/socorro

https://github.com/mozilla-services/socorro/commit/c185523338236456af73039a53f7fbbda879d833
fixes bug 1394909 - correct order of table deletions with partitions (#3957)