800372 - Explanation of backfill errors: explosiveness failure on tables not existing

Status: RESOLVED INVALID

(Socorro :: Database, task)

Description

[Selena Deckelmann :selenamarie :selena]

Josh -- could you comment on why the errors below are occurring? I'm tied up with giving a talk until noon today.

-selena

CONTEXT:  SQL statement "SELECT backfill_reports_clean( first_rc, last_rc )"
PL/pgSQL function "backfill_matviews" line 51 at PERFORM
psql:run_backfill.sql:3: INFO:  rank_compare
psql:run_backfill.sql:3: INFO:  explosiveness (slow)
psql:run_backfill.sql:3: NOTICE:  table "crash_madu" does not exist, skipping
CONTEXT:  SQL statement "DROP TABLE IF EXISTS crash_madu"
PL/pgSQL function "backfill_explosiveness" line 4 at SQL statement
SQL statement "SELECT backfill_explosiveness(thisday)"
PL/pgSQL function "backfill_matviews" line 86 at PERFORM
psql:run_backfill.sql:3: NOTICE:  table "xtab_mult" does not exist, skipping
CONTEXT:  SQL statement "DROP TABLE IF EXISTS xtab_mult"
PL/pgSQL function "backfill_explosiveness" line 5 at SQL statement
SQL statement "SELECT backfill_explosiveness(thisday)"
PL/pgSQL function "backfill_matviews" line 86 at PERFORM
psql:run_backfill.sql:3: NOTICE:  table "crash_xtab" does not exist, skipping
CONTEXT:  SQL statement "DROP TABLE IF EXISTS crash_xtab"
PL/pgSQL function "backfill_explosiveness" line 6 at SQL statement
SQL statement "SELECT backfill_explosiveness(thisday)"
PL/pgSQL function "backfill_matviews" line 86 at PERFORM
psql:run_backfill.sql:3: NOTICE:  table "explosive_oneday" does not exist, skipping
CONTEXT:  SQL statement "DROP TABLE IF EXISTS explosive_oneday"
PL/pgSQL function "backfill_explosiveness" line 7 at SQL statement
SQL statement "SELECT backfill_explosiveness(thisday)"
PL/pgSQL function "backfill_matviews" line 86 at PERFORM
psql:run_backfill.sql:3: NOTICE:  table "explosive_threeday" does not exist, skipping
CONTEXT:  SQL statement "DROP TABLE IF EXISTS explosive_threeday"
PL/pgSQL function "backfill_explosiveness" line 8 at SQL statement
SQL statement "SELECT backfill_explosiveness(thisday)"
PL/pgSQL function "backfill_matviews" line 86 at PERFORM
psql:run_backfill.sql:3: INFO:  correlations
 backfill_matviews
-------------------
 t

Comment 1

[[:jberkus] Josh Berkus]

Selena,

So that's some CYA in the backfill script.  Most of the temp tables in the various Matview functions are created "ON COMMIT DROP" and not explicitly dropped, so the backfill script needs to be sure to drop them if you're doing more than one day in a row.  So there's a bunch of DROP IF EXISTS.

This won't be necessary when you move backfill to an external script which commits after each day of work.

Comment 2

[[:jberkus] Josh Berkus]

Also, those aren't errors, those are INFO statements.