How to fix metrics

At the request of some instances, we have analyzed the issues related to metrics and looked for possible solutions.

Problems

We have identified two main problems:

  • Metrics generation crashing, which cause MetricJobs to run again and again.

  • Peaks in generated metrics, sudden changes from day to day when displaying metrics.

Metrics generation crashing

We have identified only one culprit here: "orphans" records, meaning records whose related component or participatory space cannot be found in the database. This is because in a previous decidim release PartipatorySpaces could be deleted but they were not deleted properly. So any application that has deleted a participatory space in the past, will probably have unrelated records that will make some metrics calculation crash.

Peaks in generated metrics

If somehow the metrics jobs fail to execute for a period of time, big differences can appear in metrics. So first make sure that you have metrics for every day, if not generate them.

If you have metrics generated for almost everyday and still see drastic changes from day to day, take into account that changing the visibility of a component or participatory space (making them private or unpublishing them) will naturally cause big differences in generated metrics.

Finally, if you see that the differences in some days are multiples of a previous generated metric, meaning suddenly you have exactly the double or the triple of a calculated metric, it is very likely that you have duplicate generated metrics. We have only seen this problem with instances using Sidekiq, not Delayed Job. We do not know the cause of this, but it seems to be a known issue Avoiding duplicate jobs in Sidekiq.

Solutions

We cannot offer a definitive solution for duplicate metrics, other than to delete old duplicate metrics and generate them again. If this problem persists, however, consider using Delayed Job. For a given metric type (rake decidim:metrics:list) that has duplicates:

  • Option 1: Remove individually each metric record per day.

  • Option 2: Delete all metric records and recalculate them. CHANGELOG of decidim version 0.18 has an example for "participants".

For orphan records, you can do the following:

  • Back up the database.

  • Delete orphan records fromt the console (code is below).

  • Delete "comments" metrics and recalculate them following the aforementioned example.

Some queries that may help

    GROUP_BY_FIELDS= %w(
      day
      metric_type
      decidim_organization_id
      participatory_space_type
      participatory_space_id
      related_object_type
      related_object_id
      decidim_category_id).join(', ')

    def remove_duplicates
      sql= <<~EOSQL.strip
      DELETE FROM decidim_metrics WHERE decidim_metrics.id NOT IN
        (SELECT id FROM (
          SELECT DISTINCT ON (#{GROUP_BY_FIELDS}) * FROM decidim_metrics));
      EOSQL
    end

    # DELETE FROM decidim_metrics WHERE decidim_metrics.id NOT IN \n  (SELECT id FROM (\n    SELECT DISTINCT ON (day, metric_type, decidim_organization_id, participatory_space_type, participatory_space_id, related_object_type, related_object_id, decidim_category_id) * FROM decidim_metrics));
    def count_duplicates
      sql= <<~EOSQL.strip
        SELECT count(1), #{GROUP_BY_FIELDS} FROM decidim_metrics GROUP BY #{GROUP_BY_FIELDS} HAVING COUNT(1) > 1;
      EOSQL
    end

Delete orphan records

"proposals", "meetings", "accountability", "debates", "pages", "budgets", "surveys"

Proposals

Delete proposals whose component does not have a participatory space and delete components of a proposal type that do not have a participatory space

Decidim::Component.where(manifest_name: "proposals").find_each(batch_size: 100) { |c|
  if c.participatory_space.blank?
    Decidim::Proposals::Proposal.where(component: c).destroy_all
    c.destroy
  end
}

Delete proposals that do not have a component

Decidim::Proposals::Proposal.find_each(batch_size: 100) { |proposal|
  proposal.delete if proposal.component.blank?
}

Meetings

Delete meetings whose component has no participatory space and delete components of meeting type that do not have a participatory space

Decidim::Component.where(manifest_name: "meetings").find_each(batch_size: 100) { |c|
  if c.participatory_space.blank?
    Decidim::Meetings::Meeting.where(component: c).destroy_all
    c.destroy
  end
}

Delete meetings that do not have a component

Decidim::Meetings::Meeting.find_each(batch_size: 100) { |meeting|
  meeting.delete if meeting.component.blank?
}

Debates

Delete debates that its component has no participatory space and the debate components that do not have a participatory space

Decidim::Component.where(manifest_name: "debates").find_each(batch_size: 100) { |c|
  if c.participatory_space.blank?
    Decidim::Debates::Debate.where(component: c).destroy_all
    c.destroy
  end
}

Destroy debates that do not have a component

Decidim::Debates::Debate.find_each(batch_size: 100) { |debate|
  debate.delete if debate.component.blank?
}

Posts

Destroy posts whose component has no participatory space and blog components that do not have a participatory space

Decidim::Component.where(manifest_name: "blogs").find_each(batch_size: 100) { |c|
  if c.participatory_space.blank?
    Decidim::Blogs::Post.where(component: c).destroy_all
    c.destroy
  end
}

Destroy posts that do not have a component

Decidim::Blogs::Post.find_each(batch_size: 100) { |post|
  post.delete if post.component.blank?
}

Accountability

Destroy results whose component has no participatory space and components of accountability type that do not have a participatory space

Decidim::Component.where(manifest_name: "accountability").find_each(batch_size: 100) { |c|
  if c.participatory_space.blank?
    Decidim::Accountability::Result.where(component: c).destroy_all
    c.destroy
  end
}

Destroy results that do not have a component

Decidim::Accountability::Result.find_each(batch_size: 100) { |result|
  result.delete if result.component.blank?
}

Pages

Destroy page components that do not have a participatory space

Decidim::Component.where(manifest_name: "pages").find_each(batch_size: 100) { |c|
  if c.participatory_space.blank?
    c.destroy
  end
}

Budgets

Destroy projects whose component has no participatory space and budget components that do not have a participatory space

Decidim::Component.where(manifest_name: "budgets").find_each(batch_size: 100) { |c|
  if c.participatory_space.blank?
    Decidim::Budgets::Project.where(component: c).destroy_all
    c.destroy
  end
}

Destroy results that do not have a component

Decidim::Budgets::Project.find_each(batch_size: 100) { |project|
  project.delete if project.component.blank?
}

Surveys

Decidim::Component.where(manifest_name: "surveys").find_each(batch_size: 100) { |c|
  if c.participatory_space.blank?
    Decidim::Surveys::Survey.where(component: c).destroy_all
    c.destroy
  end
}

Destroy surveys that do not have a component

Decidim::Surveys::Survey.find_each(batch_size: 100) { |survey|
  survey.delete if survey.component.blank?
}

Comments

Destroy comments whose commentable root is a proposal that does not have a participatory space.

proposal_ids = Decidim::Comments::Comment.where(decidim_root_commentable_type: "Decidim::Proposals::Proposal").pluck(:decidim_root_commentable_id)

proposal_ids_without_space = Decidim::Proposals::Proposal.where(id: proposal_ids).find_all{|p| p.participatory_space.blank? }.pluck(:id)

Decidim::Comments::Comment.where(decidim_root_commentable_type: "Decidim::Proposals::Proposal", decidim_root_commentable_id: proposal_ids_without_space).destroy_all