# HG changeset patch # User Alain Mazy # Date 1764686654 -3600 # Node ID 135bd5b3228ce634d805a628b34a5c8ba0c13559 # Parent 3a0ba14a99f556c3512e062eca8902ca9f05f749 fix upgrade diff -r 3a0ba14a99f5 -r 135bd5b3228c PostgreSQL/Plugins/SQL/Upgrades/Rev6ToRev10.sql --- a/PostgreSQL/Plugins/SQL/Upgrades/Rev6ToRev10.sql Mon Dec 01 15:10:22 2025 +0100 +++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev6ToRev10.sql Tue Dec 02 15:44:14 2025 +0100 @@ -1,7 +1,43 @@ -- Adding a PK to these 2 table to allow pg_repack to process these tables, enabling reclaiming disk space and defragmenting the tables. -ALTER TABLE InvalidChildCounts ADD COLUMN pk BIGSERIAL PRIMARY KEY; -ALTER TABLE GlobalIntegersChanges ADD COLUMN pk BIGSERIAL PRIMARY KEY; +-- in rev 10, we need to modify the table InvalidChildCounts that is created in rev 4 but, +-- the table is actually created in PrepareDatabase.sql that will be run after the upgrade script +-- hence this check before executin the ALTER TABLE. +DO $$ +BEGIN + IF EXISTS ( + SELECT 1 + FROM information_schema.tables + WHERE table_schema = 'public' + AND table_name = 'invalidchildcounts' + ) THEN + ALTER TABLE InvalidChildCounts ADD COLUMN pk BIGSERIAL PRIMARY KEY; + END IF; +END $$; --- Adding the queues timeout -ALTER TABLE Queues ADD COLUMN reservedUntil BIGINT DEFAULT NULL; \ No newline at end of file +-- same for GlobalIntegersChanges table that is created in rev2: +DO $$ +BEGIN + IF EXISTS ( + SELECT 1 + FROM information_schema.tables + WHERE table_schema = 'public' + AND table_name = 'globalintegerschanges' + ) THEN + ALTER TABLE GlobalIntegersChanges ADD COLUMN pk BIGSERIAL PRIMARY KEY; + END IF; +END $$; + + +-- Adding the queues timeout (Queues table was created in rev5) +DO $$ +BEGIN + IF EXISTS ( + SELECT 1 + FROM information_schema.tables + WHERE table_schema = 'public' + AND table_name = 'queues' + ) THEN + ALTER TABLE Queues ADD COLUMN reservedUntil BIGINT DEFAULT NULL; + END IF; +END $$;