search

endlessm / azafea

Service to track device activations and usage metrics
Mozilla Public License 2.0
10 stars 2 forks source link

metrics: Fix the image product/personality functions #85

Closed bochecha closed 4 years ago

bochecha commented 4 years ago

The PostgreSQL autovacuum workers keep failing their ANALYZE step in production:

  2020-01-21 11:56:53 UTC::@:[22993]:ERROR:  relation "metrics_machine" does not exist at character 102
  2020-01-21 11:56:53 UTC::@:[22993]:QUERY:  SELECT (
                  SELECT reverse(split_part(reverse(image_id), '.', 1)) AS personality
                    FROM metrics_machine
                    WHERE metrics_machine.machine_id = _machine_id
                )
  2020-01-21 11:56:53 UTC::@:[22993]:CONTEXT:  PL/pgSQL function public.get_image_personality(text) line 3 at RETURN
          automatic analyze of table "azafea.public.metrics_request_v2"

This is because they operate in a different context where they apparently don't assume the "public" tablespace to be the default.

This migration recreates the functions (recreating the corresponding indexes) to fix that, by explicitly specifying that the tables belong to the public tablespace.

This migration also declares the functions as parallel safe, since they are and this can help the PostgreSQL query optimizer.

bochecha commented 4 years ago

Tested with @adarnimrod and this indeed fixes the autovacuum issue.