855423 - Refactor all stored procedure matviews so crontabber doesn't get stuck

Status: RESOLVED FIXED

(Socorro :: Database, task)

Description

[Peter Bengtsson [:peterbe]]

Following a longer discussion we had on IRC, if a manual backfill has been done on the nightly matview and it tries to re-try it will be stuck forever! Basically, the problem is that some (I only know about the nightly matview job at the moment) stored procedures use `RAISE EXCEPTION` instead of `RAISE NOTICE` if you feed it a repeated input. (note: crontabber won't feed a repeated input but manual plus self-healing backfill will)

Either we...

A) audit and refactor all matviews stored procs called from crontabber to replace `RAISE EXCEPTION` with `RAISE NOTICE`

B) add a `try:... exept InternalError:...` piece to the matviews.py code. So would be easy but it's sub-optimal since it might hide more important problems. 

So, ideally plan A if Selena thinks it's doable/appropriate.

Comment 1

[Peter Bengtsson [:peterbe]]

We might want to update the crontabber matviews.py jobs so that it logs the outcome rather than just running them. We can pipe this outcome to logging.

Comment 3

[Peter Bengtsson [:peterbe]]

Note to self: See https://bugzilla.mozilla.org/show_bug.cgi?id=794264#c2

Comment 5

[Selena Deckelmann :selenamarie :selena]

List of stored procedures to refactor: 

socorro/external/postgresql/raw_sql/procs/001_update_reports_clean.sql
socorro/external/postgresql/raw_sql/procs/add_column_if_not_exists.sql
socorro/external/postgresql/raw_sql/procs/add_new_product.sql
socorro/external/postgresql/raw_sql/procs/add_new_release.sql
socorro/external/postgresql/raw_sql/procs/backfill_matviews.sql
socorro/external/postgresql/raw_sql/procs/crontabber_nodelete.sql
socorro/external/postgresql/raw_sql/procs/drop_old_partitions.sql
socorro/external/postgresql/raw_sql/procs/edit_featured_versions.sql
socorro/external/postgresql/raw_sql/procs/edit_product_info.sql
socorro/external/postgresql/raw_sql/procs/update_adu.sql
socorro/external/postgresql/raw_sql/procs/update_build_adu.sql
socorro/external/postgresql/raw_sql/procs/update_correlations.sql
socorro/external/postgresql/raw_sql/procs/update_crashes_by_user_build.sql
socorro/external/postgresql/raw_sql/procs/update_crashes_by_user.sql
socorro/external/postgresql/raw_sql/procs/update_daily_crashes.sql
socorro/external/postgresql/raw_sql/procs/update_explosiveness.sql
socorro/external/postgresql/raw_sql/procs/update_hang_report.sql
socorro/external/postgresql/raw_sql/procs/update_home_page_graph_build.sql
socorro/external/postgresql/raw_sql/procs/update_home_page_graph.sql
socorro/external/postgresql/raw_sql/procs/update_nightly_builds.sql
socorro/external/postgresql/raw_sql/procs/update_os_versions.sql
socorro/external/postgresql/raw_sql/procs/update_rank_compare.sql
socorro/external/postgresql/raw_sql/procs/update_signatures.sql
socorro/external/postgresql/raw_sql/procs/update_tcbs_build.sql
socorro/external/postgresql/raw_sql/procs/update_tcbs.sql
socorro/external/postgresql/raw_sql/procs/validate_lookup.sql

Comment 6

[[github robot]]

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

https://github.com/mozilla/socorro/commit/53fcbaf24d349639cc9841a816742091df945521
bug 855423 Change all RAISE ERROR -> NOTICE in stored procs

* Add alembic migration (yay!)
* Fix a test that no longer needs to expect an exception
* Add OR REPLACE to CREATE FUNCTION

https://github.com/mozilla/socorro/commit/4a34bf574ddfa578e941e7edc8cf56eb1b617b8a
Merge pull request #1200 from selenamarie/bug855423-refactor-raise

bug 855423 Change all RAISE ERROR -> NOTICE in stored procs

Comment 7

[Selena Deckelmann :selenamarie :selena]

This change shouldn't require a downtime, but may require some conflicting locks for hourly crontabber jobs.  Also requires a change to the makefile to deploy the alembic directory.

Comment 8

[Lonnen :lonnen]

didn't actually get into 44. shipping out with 45