Unable to completely drop pgagent schema

From: Dean Franken <d(dot)franken(at)federation(dot)edu(dot)au>
To: "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Subject: Unable to completely drop pgagent schema
Date: 2017-08-04 02:55:02
Message-ID: 28F76C61D001574AB393AC23F54BA614EB5E70@MEMPHIS.uob.ballarat.edu.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

If the pgagent is installed, it is not possible to fully drop the pgagent schema once job tables are populated.
After dropping the schema or drop cascading the pgagent extension, if performing a pg_dumpall the original pgagent.* table data is left intact. The schema and tables are not visible in any of the catalogues but are present in the pg_dumpall output.
This prevents restoring data in the pgagent schema from a backup.

Tested with pgagent/xenial,now 3.4.1-2 amd64 + postgresql/xenial,xenial,now 9.5+173
and pgagent/xenial-pgdg,now 3.4.1-3.pgdg16.04+1 amd64 + postgresql-9.6/xenial-pgdg,now 9.6.3-1.pgdg16.04+1 amd64

Jobs created with pgAdmin3 1.22 and pgAdmin 3 LTS 1.23.0b

Reproduced with;
CREATE EXTENSION pgagent;
-- add a job with an arbitrary step and schedule
DROP SCHEMA pgagent CASCADE;
-- pg_dumpall and examine the output

Dumpall Example output;
--
-- Data for Name: pga_job; Type: TABLE DATA; Schema: pgagent; Owner: root
--

COPY pga_job (jobid, jobjclid, jobname, jobdesc, jobhostagent, jobenabled, jobcreated, jobchanged, jobagentid, jobnextrun, joblastrun) FROM stdin;
1 1 test job 1 t 2017-08-04 10:21:59.436306+10 2017-08-04 10:21:59.436306+10 \N 2017-08-07 00:00:00+10 \N
\.

Restore attempt output;
CREATE EXTENSION pgagent;
SET search_path = pgagent, pg_catalog;
INSERT INTO pga_job (jobid, jobjclid, jobname, jobdesc, jobhostagent, jobenabled, jobcreated, jobchanged, jobagentid, jobnextrun, joblastrun) VALUES (1, 1, 'test job 1', '', '', true, '2017-08-04 10:21:59.436306+10', '2017-08-04 10:21:59.436306+10', NULL, '2017-08-07 00:00:00+10', NULL);
ERROR: duplicate key value violates unique constraint "pga_job_pkey"
DETAIL: Key (jobid)=(1) already exists.

Workaround;
-- This seems to allow restore (tested in 9.6.3, once pgagent schema was manually created drop then inserts aren't failing anymore)
CREATE SCHEMA pgagent;
CREATE EXTENSION pgagent;
SET search_path = pgagent, pg_catalog;
INSERT INTO pga_job (jobid, jobjclid, jobname, jobdesc, jobhostagent, jobenabled, jobcreated, jobchanged, jobagentid, jobnextrun, joblastrun) VALUES (1, 1, 'test job 1', '', '', true, '2017-08-04 10:21:59.436306+10', '2017-08-04 10:21:59.436306+10', NULL, '2017-08-07 00:00:00+10', NULL);

Browse pgsql-bugs by date

  From Date Subject
Next Message Augustine, Jobin 2017-08-04 05:53:43 Replication to Postgres 10 on Windows is broken
Previous Message Peter Geoghegan 2017-08-03 19:19:24 Re: Crash report for some ICU-52 (debian8) COLLATE and work_mem values