int_to_big_int_postgres_migration.sql

-- psql
-- \timing on

-- Creating test data
CREATE TABLE largetable (id INT NOT NULL, content TEXT);

INSERT INTO largetable
    SELECT i, 'The Road Not Taken'
              'Two roads diverged in a yellow wood,'
              'And sorry I could not travel both'
              'I shall be telling this with a sigh'
              'Somewhere ages and ages hence:'
              'Two roads diverged in a wood, and I—'
              'I took the one less traveled by,'
              'And that has made all the difference.'
    FROM generate_series(1, 1000000) AS i;


CREATE SEQUENCE largetable_id_seq START 1000001;
ALTER TABLE largetable
ALTER id SET DEFAULT nextval('largetable_id_seq');

CREATE UNIQUE INDEX ON largetable(id);

ALTER TABLE largetable
ADD PRIMARY KEY USING INDEX largetable_id_IDX;

INSERT INTO largetable (content) VALUES ('Two roads diverged in a yellow wood,');
INSERT INTO largetable (content) VALUES ('And sorry I could not travel both');
INSERT INTO largetable (content) VALUES ('And be one traveler, long I stood');
INSERT INTO largetable (content) VALUES ('And looked down one as far as I could');
INSERT INTO largetable (content) VALUES ('To where it bent in the undergrowth;');
INSERT INTO largetable (content) VALUES ('Then took the other, as just as fair,');
INSERT INTO largetable (content) VALUES ('And having perhaps the better claim,');
INSERT INTO largetable (content) VALUES ('Because it was grassy and wanted wear;');
INSERT INTO largetable (content) VALUES ('Though as for that the passing there');
INSERT INTO largetable (content) VALUES ('Had worn them really about the same,');
INSERT INTO largetable (content) VALUES ('And both that morning equally lay');
INSERT INTO largetable (content) VALUES ('In leaves no step had trodden black.');
INSERT INTO largetable (content) VALUES ('Oh, I kept the first for another day!');
INSERT INTO largetable (content) VALUES ('Yet knowing how way leads on to way,');
INSERT INTO largetable (content) VALUES ('I doubted if I should ever come back.');
INSERT INTO largetable (content) VALUES ('I shall be telling this with a sigh');
INSERT INTO largetable (content) VALUES ('Somewhere ages and ages hence:');
INSERT INTO largetable (content) VALUES ('Two roads diverged in a wood, and I—');
INSERT INTO largetable (content) VALUES ('I took the one less traveled by,');
INSERT INTO largetable (content) VALUES ('And that has made all the difference.');

-- Check content
SELECT * FROM largetable ORDER BY id DESC LIMIT 20;

-- \dt+ largetable;
-- \d largetable;

ALTER TABLE largetable ALTER id TYPE BIGINT;

-- \dt+ largetable;
-- \d largetable;

-- Adding a column with a non-null default or changing the type of an existing column will require the entire table and indexes to be rewritten.
-- As an exception, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed, but any indexes on the affected columns must still be rebuilt. 
-- Requires ACCESS EXCLUSIVE lock
-- No reads, writes allowed to other transactions
-- Causes table rewrite, if not binary coercible (slow)

-- -- One possible concurrent solution
-- Add new BIGINT column
-- Write procedure to copy values to new column in batches
-- Write trigger to replicate changes from old column
-- Drop old column, rename new column
-- Make new column PK


ALTER TABLE largetable 
ADD COLUMN id_new BIGINT
NOT NULL
DEFAULT 0;

select * from largetable ORDER BY id DESC LIMIT 20;

CREATE FUNCTION largetable_trig_func()
RETURNS TRIGGER AS $$
BEGIN
    NEW.id_new := NEW.ID;
    RETURN NEW;
END $$ LANGUAGE plpgsql;


CREATE TRIGGER largetable_trig
BEFORE INSERT OR UPDATE ON largetable
FOR EACH ROW
EXECUTE FUNCTION largetable_trig_func();


CREATE PROCEDURE largetable_sync_proc() AS $$
DECLARE r RECORD;
DECLARE count BIGINT := 0;
DECLARE batchsize BIGINT := 10000;
DECLARE cur CURSOR FOR SELECT id FROM largetable;
BEGIN
    FOR r IN cur LOOP
        UPDATE largetable
        SET id_new = id
        WHERE id = r.id;
        count := count + 1;
        IF (count % batchsize = 0) THEN
            IF (count % (batchsize * 10) = 0) THEN
                RAISE NOTICE '% rows done', count;
            END IF;
        COMMIT;
        END IF;
    END LOOP;
    COMMIT;
    RETURN;
END $$ LANGUAGE plpgsql;


CALL largetable_sync_proc();


INSERT INTO largetable (content) VALUES ('That you were Romeo, you were throwing pebbles');
INSERT INTO largetable (content) VALUES ('And my daddy said: Stay away from Juliet');
INSERT INTO largetable (content) VALUES ('And I was crying on the staircase');
INSERT INTO largetable (content) VALUES ('Begging you: Please, dont go');
INSERT INTO largetable (content) VALUES ('And I said: Romeo, take me somewhere we can be alone');
INSERT INTO largetable (content) VALUES ('Ill be waiting, all theres left to do is run');
INSERT INTO largetable (content) VALUES ('Youll be the prince and Ill be the princess');
INSERT INTO largetable (content) VALUES ('Its a love story, baby, just say yes');
INSERT INTO largetable (content) VALUES ('So I sneak out to the garden to see you');
INSERT INTO largetable (content) VALUES ('We keep quiet cause were dead if they knew');
INSERT INTO largetable (content) VALUES ('So close your eyes');
INSERT INTO largetable (content) VALUES ('Escape this town for a little while, uh, oh');
INSERT INTO largetable (content) VALUES ('Cause you were Romeo, I was a scarlet letter');
INSERT INTO largetable (content) VALUES ('And my daddy said: Stay away from Juliet');
INSERT INTO largetable (content) VALUES ('But you were everything to me');
INSERT INTO largetable (content) VALUES ('I was begging you: Please, dont go');


select * from largetable ORDER BY id DESC LIMIT 35;


CREATE UNIQUE INDEX
CONCURRENTLY largetable_id_new_idx
ON largetable(id_new);



DO $$
DECLARE new_start BIGINT;
BEGIN
    SELECT max(id) + 1 FROM largetable INTO new_start;
    EXECUTE 'CREATE SEQUENCE largetable_id_bigint_seq '
            'START ' || new_start;

    ALTER TABLE largetable ALTER id_new
        SET DEFAULT nextval('largetable_id_bigint_seq');

    ALTER TABLE largetable DROP id;
    ALTER TABLE largetable RENAME id_new TO id;
    ALTER TABLE largetable ADD CONSTRAINT largetable_id_pkey
        PRIMARY KEY USING INDEX largetable_id_new_idx;

    DROP TRIGGER largetable_trig ON largetable;
    COMMIT;
END $$ LANGUAGE plpgsql;


-- \dt+ largetable;
-- \d largetable;