partition existing table non blocking

gistfile1.txt

https://www.enterprisedb.com/blog/partitioning-large-table-without-long-running-lock

create table orig_table
( id int generated always as identity not null,
  data float default random()
);

create index orig_data_index on orig_table(data);
create index orig_id_index on orig_table(id);

insert into orig_table (id)
overriding system value select nextval('orig_table_id_seq')
from generate_series(1,100000);
create table part_table
(like orig_table including all)
partition by range(data);

create table part_table_p1
partition of part_table
for values from (minvalue) to (0.25);

create table part_table_p2
partition of part_table
for values from (0.25) to (0.5);

create table part_table_p3
partition of part_table
for values from (0.5) to (0.75);

create table part_table_p4
partition of part_table
for values from (0.75) to (maxvalue);

create or replace function part_v_trigger()
returns trigger
language plpgsql
as
$TRIG$

begin
    IF TG_OP = 'INSERT'
    THEN
        INSERT INTO part_table overriding system value VALUES(nextval('orig_table_id_seq'), NEW.data);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE'
    THEN
        DELETE FROM part_table WHERE id = OLD.id;
        DELETE FROM old_orig_table WHERE id = OLD.id;
        RETURN OLD;
    ELSE -- UPDATE
        DELETE FROM old_orig_table WHERE id = OLD.id;
        IF FOUND
        THEN
            INSERT INTO part_table overriding system value select OLD.id, NEW.data;
        ELSE
            UPDATE part_table SET data = NEW.data
                WHERE id = OLD.id;
        END IF;
        RETURN NEW;
    END IF;
end

$TRIG$;

BEGIN;

    ALTER TABLE orig_table RENAME TO old_orig_table;

    ALTER TABLE old_orig_table SET(
      autovacuum_enabled = false, toast.autovacuum_enabled = false
   );

    CREATE VIEW orig_table AS
    SELECT id, data FROM old_orig_table
    UNION ALL
    SELECT id, data FROM part_table
    ;

    CREATE TRIGGER orig_table_part_trigger
    INSTEAD OF INSERT OR UPDATE OR DELETE on orig_table
    FOR EACH ROW
    EXECUTE FUNCTION part_v_trigger();

COMMIT;



BEGIN;
    DROP VIEW orig_table CASCADE;
    DROP FUNCTION part_v_trigger();

/*
ERROR:  cannot change ownership of identity sequence
DETAIL:  Sequence "orig_table_id_seq" is linked to table "old_orig_table".
    this fails for identity columns
    */
    -- ALTER SEQUENCE orig_table_id_seq OWNED BY part_table.id;
    
    select setval('part_table_id_seq', nextval('orig_table_id_seq'));
    ALTER TABLE part_table RENAME TO orig_table;
COMMIT;

-- after all old data copied, drop the orig table

BEGIN;
    DROP TABLE old_orig_table;
COMMIT;

postgres=# \ds
                List of relations
 Schema |       Name        |   Type   |  Owner
--------+-------------------+----------+----------
 public | orig_table_id_seq | sequence | postgres
 public | part_table_id_seq | sequence | postgres
(2 rows)

postgres=# select nextval('orig_table_id_seq');
 nextval
---------
  100002
(1 row)

postgres=# select nextval('part_table_id_seq');
 nextval
---------
       1
(1 row)

postgres=# select setval('part_table_id_seq', nextval('orig_table_id_seq'));
 setval
--------
 100003
(1 row)

postgres=# select nextval('orig_table_id_seq');
 nextval
---------
  100004
(1 row)


---------------
insert into orig_table(col2) select 100;
\d orig_table
insert into orig_table(data) select 100;
select * from part_table where data = 100;
select * from old_orig_table limit 4;
update orig_table set data = 101 where id = 1;
select * from part_table where data = 101;