BEGIN TRANSACTION; |
DO $$ DECLARE |
db_schema_version integer; |
r RECORD; |
BEGIN |
if exists (select from information_schema.tables |
where table_schema = 'public' |
and table_name = 'schema_migrations') |
then |
select max(version) into db_schema_version from schema_migrations; |
case |
when db_schema_version != 73 then |
raise exception |
'This version of the delete-reports.sql is for versions of PuppetDB |
on migration 73, your database is on migration %', db_schema_version |
USING HINT = |
'You may already be upgraded, or you may need a different |
version of the script.'; |
|
else |
ALTER TABLE certnames DROP CONSTRAINT IF EXISTS certnames_reports_id_fkey; |
UPDATE certnames SET latest_report_id = NULL; |
|
FOR r IN (SELECT tablename FROM pg_tables WHERE tablename LIKE 'resource_events_%') LOOP |
EXECUTE 'DROP TABLE ' || quote_ident(r.tablename); |
END LOOP; |
|
TRUNCATE TABLE reports CASCADE; |
|
ALTER TABLE certnames |
ADD CONSTRAINT certnames_reports_id_fkey |
FOREIGN KEY (latest_report_id) REFERENCES reports(id) ON DELETE SET NULL; |
end case; |
else |
raise exception |
'Could not find puppetdb schema_migrations table'; |
end if; |
|
end $$; |
|
COMMIT TRANSACTION;
|
