Issue

I’m trying to use an Edge Function to insert rows into a table named weather_logs. The function will be called from an AFTER INSERT trigger on locations. What’s causing the issue is that weather_logs has a column location_id which is a foreign key on locations.

Haven’t had any success, I always get an error like the following where 47 is the id of the row inserted into locations:

{
  "code": "23503",
  "details": "Key (location_id)=(47) is not present in table "locations".",
  "hint": null,
  "message": "insert or update on table "weather_logs" violates foreign key constraint "weather_logs_location_id_fkey""
}

This is the upsert call I’m trying to make in my edge function:

// Upsert weather log records into the weather_logs table
const { error: upsertError } = await supabase
  .from("weather_logs")
  .upsert(weatherLogRecords, { onConflict: "location_id,date" });

I’ve tried several different options…

Altering the table to defer the Constraint Check

ALTER TABLE weather_logs
  ALTER CONSTRAINT weather_logs_location_id_fkey
  DEFERRABLE INITIALLY DEFERRED;

Using a CONSTRAINT TRIGGER

CREATE CONSTRAINT TRIGGER after_insert_location
AFTER INSERT ON locations
DEFERRABLE
FOR EACH ROW
EXECUTE FUNCTION trigger_backfill_weather_logs();

Using AFTER COMMIT event trigger

CREATE EVENT TRIGGER trigger_after_commit
ON COMMIT
EXECUTE FUNCTION after_commit_weather_logs();