opentrials icon indicating copy to clipboard operation
opentrials copied to clipboard

"ERROR: function set_updated_at() does not exist" when restoring from last dump

Open javadba opened this issue 6 years ago • 1 comments

The last dump on the website is from April 1 2018. When attempting to restore it via

pg_restore -e -v -O -x --no-owner --clean --if-exists --host=eldamar --dbname=opentrials ~/Downloads/opentrials-api-2018-04-01.dump

It spends some minutes "working" and creating various artifacts before choking on creating a trigger referencing a non-existing function set_updated_at():

...
pg_restore: creating INDEX "public.trialrecords_trial_id_index"
pg_restore: creating INDEX "public.trials_documents_document_id_index"
pg_restore: creating TRIGGER "public.conditions_set_updated_at"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3061; 2620 87835462 TRIGGER conditions_set_updated_at database
pg_restore: [archiver (db)] could not execute query: ERROR:  function set_updated_at() does not exist
    Command was: CREATE TRIGGER conditions_set_updated_at BEFORE UPDATE ON conditions FOR EACH ROW EXECUTE PROCEDURE set_updated_at();

What needs to be done differently for this to work? I am on postgres 11.4

javadba avatar Nov 17 '19 16:11 javadba

You just need to manually create the trigger https://github.com/opentrials/opentrials/blob/0eda7e551a186e20057f4621f06c293f03945fa9/migrations/20170123121324_add_trigger_for_updated_at.js

CREATE FUNCTION set_updated_at()
  RETURNS TRIGGER
  LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

sitch avatar Sep 16 '21 17:09 sitch