Supabase中PLV8数据库函数日志查看与调试方法咨询

查看PLV8.elog输出的方法 #

• 调整日志级别
  Supabase默认不记录INFO级日志，需先修改PostgreSQL日志级别：
  会话级临时生效：

  SET log_min_messages = info;
  

  全局永久生效（需管理员权限）：

  ALTER SYSTEM SET log_min_messages = info;
  

  执行后重启数据库会话即可生效。

• 查看日志位置
  在Supabase控制台进入目标项目，点击左侧「数据库」→「日志」，在筛选器中选择「INFO」级别，就能看到plv8.elog(INFO, ...)的输出内容。

• 临时调试技巧
  如果暂时无法调整日志级别，可将日志级别改为WARNING/ERROR（这类日志默认会被捕获），同时把JSON对象转为字符串避免格式异常：

  plv8.elog(WARNING, JSON.stringify(request_data));
  

针对当前报错的排查 #

你遇到的Cannot read properties of undefined (reading 'id')错误，说明代码中request_data.user为undefined，大概率是传入的JSON参数缺少user字段，或JSON格式不符合预期。通过上述日志方法打印完整request_data即可快速定位问题。

你的PLV8函数代码 #

-- FUNCTION: public.handle_onboard_user(json)

-- DROP FUNCTION IF EXISTS public.handle_onboard_user(json);

CREATE OR REPLACE FUNCTION public.handle_onboard_user(
    request_data json)
    RETURNS void
    LANGUAGE 'plv8'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
try {
  plv8.elog(INFO, request_data);
  // --Extract JSON params
  var user = request_data.user; 
  var selected_skills = request_data.selected_skills;
  var selected_categories = request_data.selected_categories;
  var experiences = request_data.experiences;
  var accomplishments = request_data.accomplishments;

  var auth_user_id = user.id;

  // --Update user data onboarding
  plv8.execute('UPDATE public.users SET first_name = $1, last_name = $2, title = $3, resume = $4, degree_programme_id = $5, avatar = $6, about = $7 ,is_onboarded = true, updated_at = now() WHERE id = $8', user.first_name, user.last_name, user.title, user.resume, user.degree_programme_id, user.avatar, user.about, auth_user_id);
  plv8.elog(INFO, 'Update user info successful');

  // -- Select preference of specific user
  var result = plv8.execute('SELECT * FROM public.preferences WHERE user_id = $1', auth_user_id);

  var preference_id = result[0].id;

  // --Update skills
  for (var i = 0; i < selected_skills.length; i++) {
    var skill = selected_skills[i];

    plv8.execute('INSERT INTO public.selected_skills (emsi_id, name) VALUES ($1, $2) ON CONFLICT (emsi_id) DO NOTHING', skill.emsi_id, skill.name);

    var result = plv8.execute('SELECT id FROM public.selected_skills WHERE emsi_id = $1 LIMIT 1', skill.emsi_id);

    if (result.length == 0) {
      continue;
    }

    var skill_id = result[0].id;

    plv8.execute('INSERT INTO public.skills_preferences ' +
  '(selected_skill_id, preference_id) VALUES ($1, $2)', skill_id, preference_id);
  }
  plv8.elog(INFO, 'Insert skills successful');

  // --Insert user selected categories
  for (var i = 0; i < selected_categories.length; i++) {
    var category = selected_categories[i];

    plv8.execute('INSERT INTO public.categories_preferences (category_id, preference_id) VALUES ($1, $2)', category.id, preference_id);
  }
  plv8.elog(INFO, 'Insert selected categories successful');

  // --Insert experiences of user
  for (var i = 0; i < experiences.length; i++) {
    var experience = experiences[i];

    plv8.execute('INSERT INTO public.experiences (title, company_name, is_current, start_date, end_date, description, user_id) VALUES ($1, $2, $3, $4, $5, $6, $7)', experience.title, experience.company_name, experience.is_current, experience.start_date, experience.end_date, experience.description, auth_user_id);
  }
  plv8.elog(INFO, 'Insert user experiences success');

  // --Insert accomplishments of user
  for (var i = 0; i < accomplishments.length; i++) {
    var accomplishment = accomplishments[i];

    plv8.execute('INSERT INTO public.accomplishments (title, issuer, is_active, start_date, end_date, description, user_id) VALUES ($1, $2, $3, $4, $5, $6, $7)', accomplishment.title, accomplishment.issuer, accomplishment.is_active, accomplishment.start_date, accomplishment.end_date, accomplishment.description, auth_user_id);
  }
  plv8.elog(INFO, 'Insert user accomplishments success');

  return;

} catch (error) {

  plv8.elog(ERROR, error.message);

}
$BODY$;

ALTER FUNCTION public.handle_onboard_user(json)
    OWNER TO postgres;

GRANT EXECUTE ON FUNCTION public.handle_onboard_user(json) TO PUBLIC;

GRANT EXECUTE ON FUNCTION public.handle_onboard_user(json) TO anon;

GRANT EXECUTE ON FUNCTION public.handle_onboard_user(json) TO authenticated;

GRANT EXECUTE ON FUNCTION public.handle_onboard_user(json) TO postgres;

GRANT EXECUTE ON FUNCTION public.handle_onboard_user(json) TO service_role;

内容的提问来源于stack exchange，提问作者jmsandiegoo