Will Kahn-Greene [:willkg] ET needinfo? me Assignee
	Description • 7 years ago

We've got a bunch of stored procedures in socorro/external/postgres/raw_sql/procs/ that aren't used anywhere. Given that we don't use them, we should remove them which will make research into problems easier (fewer false positives when grepping amongst other things).

This bug covers creating a list of stored procedures that we can delete today, then writing up a PR to delete them.

	Will Kahn-Greene [:willkg] ET needinfo? me Assignee
	Comment 1 • 7 years ago

Grabbing this to work on now since it's hampering other work I want to do.

	Will Kahn-Greene [:willkg] ET needinfo? me Assignee
	Comment 2 • 7 years ago

Here's a list of stored procedures that don't look used:

* update_exploitability.sql
  backfill_exploitability.sql

  These are used only by backfill_matviews.

* update_tcbs.sql
  update_tcbs_build.sql
  backfill_tcbs.sql
  backfill_tcbs_build.sql
  
  These are used only by backfill_matviews.

Pretty sure there are some others, but I need to untangle a few things to make it clearer.


Questions:

1. Is backfill_matviews really only called by setupdb_app? Could it be called by something else somehow? A cron job? Deploys?

2. Pretty sure Adrian rewrote TCBS to use Elasticsearch and then removed the Postgres implementation in bug #1343540. Does that mean that the tcbs-related stored procedures are orphaned?

	Will Kahn-Greene [:willkg] ET needinfo? me Assignee
	Comment 3 • 7 years ago

I verified the tcbs stored procs can go. They're related to the TCBSCronApp which was removed in 5fe3da91018f53bb70099467e6d3e58b8fef3ce4.

Related to that commit, we can remove all of the following:

* update_crashes_by_user.sql
  update_crashes_by_user_build.sql

* update_home_page_graph.sql
  update_home_page_graph_build.sql

* update_exploitability.sql
  backfill_exploitability.sql

* backfill_correlations.sql
  update_correlations_addon.sql
  update_correlations_core.sql


We'll also need to update backfill_matviews.sql regarding what we removed.

	Will Kahn-Greene [:willkg] ET needinfo? me Assignee
	Comment 5 • 7 years ago

I found some more to remove that aren't used anywhere as far as I can tell:

* aurora_or_nightly.sql
* check_partitions.sql
* content_count_state.sql
* crash_madu.sql
* daily_crash_code.sql
* update_daily_crashes.sql
* backfill_daily_crashes.sql
* crontabber_nodelete.sql
* crontabber_timestamp.sql
* edit_featured_versions.sql
* find_weekly_partition.sql
* get_product_version_ids.sql
* initcap.sql
* last_record.sql
* log_priorityjobs.sql
* pacific2ts.sql
* plugin_count_state.sql
* socorro_db_data_refresh.sql
* transform_rules_insert_order.sql
* transform_rules_update_order.sql
* tstz_between.sql
* update_correlations_module.sql
* update_final_betas.sql
* update_reports_clean_cron.sql
* update_socorro_db_version.sql
* validate_lookup.sql
* watch_report_processing.sql

I'll do these in the next pass.

	[github robot]
	Comment 6 • 7 years ago

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

https://github.com/mozilla-services/socorro/commit/84ab23c863e55627725f40c7efad71c8c5459618
fixes bug 1424027 - remove unused stored procedures

https://github.com/mozilla-services/socorro/commit/1fdd752208eb71ef07c18271f8573716b0b118a1
Merge pull request #4248 from willkg/1424027-dead-procs

bug 1424027 - remove unused stored procedures

	Will Kahn-Greene [:willkg] ET needinfo? me Assignee
	Comment 7 • 6 years ago

Reopening this and tweaking the summary so I can keep removing stuff under this bug.

	Will Kahn-Greene [:willkg] ET needinfo? me Assignee
	Comment 8 • 6 years ago

The next pass covers stored procedures that aren't used anyhwere. Some of them
are utility functions used by the DBAs to do postgres work. We don't do that
sort of thing anymore, so those should go. The rest of them were used by things
that aren't around any longer.

* crontabber_nodelete.sql
  crontabber_timestamp.sql
  find_weekly_partition.sql
  get_product_version_ids.sql
  last_record.sql
  log_priorityjobs.sql
  pacific2ts.sql
  plugin_count_state.sql
  socorro_db_data_refresh.sql
  transform_rules_insert_order.sql
  transform_rules_update_order.sql
  update_reports_clean_cron.sql
  update_socorro_db_version.sql
  validate_lookup.sql
  watch_report_processing.sql

  DBA utility functions

* aurora_or_nightly.sql -- I can't find anywhere this was used ever

* check_partitions.sql -- utility function

* content_count_state.sql -- pretty sure this is a utility function

* crash_madu.sql -- used by update_gccrashes which is no more

* tstz_between.sql -- used by update_gccrashes which is no more

* daily_crash_code.sql
  update_daily_crashes.sql
  backfill_daily_crashes.sql

  We don't use this anymore.

* edit_featured_versions.sql -- used by ReleasesFeatured model which is gone

* initcap.sql

  This is actually part of postgres, so we can remove our stored procedure file.

* update_correlations_module.sql -- used by one of the Correlations apps, but we don't use that anymore

* update_final_betas.sql -- utility function maybe?

* add_column_if_not_exists
  add_old_release
  create_table_if_not_exists
  utc_day_near

  utility functions

* backfill_all_dups
  backfill_named_table
  backfill_reports_duplicates
  backfill_weekly_report_partitions
  backfill_signature_counts

  unused backfill procedures


To figure this out, I'm using two things:

1. grepping the codebase for the function defined in the stored procedure .sql file
2. doing "git log -Sutc_day_near" (using utc_day_near as an example) and looking at the history of commits that touched code that used that function to figure out where it came from and what it did

After this batch, everything is used by something else, so there's no more low-hanging fruit.

	[github robot]
	Comment 10 • 6 years ago

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

https://github.com/mozilla-services/socorro/commit/88a4261a4e1f6604908f14cbdebd02c40ee83ba7
bug 1424027 - remove more stored procedures (#4252)

This removes a batch of stored procedures that fall into a few groups:

1. utility functions for DBAs--we don't do this sort of thing anymore
2. orphaned stored procedures used by things we don't have anymore
3. stored procedures that are mysterious and want their origins and purpose
   to remain in the dark shadows

	Will Kahn-Greene [:willkg] ET needinfo? me Assignee
	Comment 11 • 6 years ago

I did two passes of stored procedures that aren't used by anything. I think that's it for this bug, so I'm marking this as FIXED.