Jira (PDB-5109) Improve delete-reports scripts for upgrades to 2019.8

[Austin Blatt (Jira)]

Title: Message Title

Austin Blatt created an issue   PuppetDB / PDB-5109 Improve delete-reports scripts for upgrades to 2019.8 Issue Type: Improvement Assignee: Unassigned Created: 2021/04/20 11:13 AM Priority: Normal Reporter: Austin Blatt Replace the old scripts in PE 2019.8 upgrade cautions with these new ones. Before 2019.3.0 BEGIN TRANSACTION; DO $$ DECLARE db_schema_version integer; 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 > 72 then raise exception 'This version of the delete-reports.sql is for versions of PuppetDB before migration 73, your database is on migration % which is too new for this version of PuppetDB.', 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; 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; 2019.3.0 through 2019.6.0 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; Add Comment   This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.396499.1618942386000.14565.1618942440213%40Atlassian.JIRA.