[Bug]: ALTER PROCEDURE ... RENAME TO does not propagate to job metadata

[RobAtticus]

What type of bug is this?

Unexpected error

What subsystems and features are affected?

User-Defined Action (UDA)

What happened?

I created a job using add_job with a custom procedure. I then decided to rename the procedure, but the job metadata info does not get updated, causing the job to fail with cache lookup failed for function 0.

TimescaleDB version affected

2.17.0

PostgreSQL version used

16

What operating system did you use?

Cloud

What installation method did you use?

Other

What platform did you run on?

Timescale Cloud

Relevant log output and stack trace

No response

How can we reproduce the bug?

CREATE OR REPLACE PROCEDURE public.foo(IN job_id INT, IN config jsonb)
  LANGUAGE plpgsql
AS $procedure$
BEGIN
  RAISE NOTICE 'done';
END;
$procedure$;

SELECT add_job('public.foo', schedule_interval=>'1m'::INTERVAL);

ALTER PROCEDURE foo RENAME TO bar;

-- Check timescaledb_information.jobs, proc_name will not be renamed.

[mkindahl]

An obvious solution would be to store the procedure in the jobs table as a regproc but that does not work since pg_upgrade does not support regproc.