liquibase run query by parts – Postgresql

IMO, in case of PostgreSQL the idea of "splitting original query into parts" within single connection/transaction does not make sense at all (I do not see any performance benefits there), moreover it is not clear what does the statement "sometimes transaction fails due connection lost or some" mean – if you are experiencing infrastructure issues you are supposed to resolve them via configuring/tuning infrastructure.

The liquibase way of performing such migrations is following:

I. define SQL migration as:

do
$$
    begin
        raise exception 'please perform following update manually';
    end
$$;

II. operator is supposed to perform following steps:

• run liquibase update and get error
• perform manual update
• run liquibase mark-next-changeset-ran
• run liquibase update again

BTW, if we are talking about theoretical possibilities of getting performance benefits I would think about something like:

do
$$
    declare
        connections text[];
        tile        record;
        connection  text;
    begin
        begin
            -- split id range into 10 tiles
            for tile in (with tiles as (select ntile(10) over (order by id) as part, id
                                        from tbl)
                         select part, min(id) as rmin, max(id) as rmax
                         from tiles
                         group by part
                         order by part)
                LOOP
                    connection = 'q' || tile.part;
                    connections = array_append(connections, connection);
                    PERFORM dblink_connect(connection, 'dbname=' || current_database());
                    PERFORM dblink_send_query(connection,
                                              'update tbl set column1 =''some'' WHERE id >=''' ||
                                              tile.rmin
                                                  || ''' AND id <= ''' || tile.rmax || '''');
                END LOOP;

            foreach connection in ARRAY connections
                loop
                    PERFORM * FROM dblink_get_result(connection) as t1(f1 text);
                end loop;

            PERFORM dblink_disconnect(c)
            from (select unnest(dblink_get_connections()) as c) conn
            where c = ANY (connections);

        end;

    exception
        when others then
            PERFORM dblink_disconnect(c)
            from (select unnest(dblink_get_connections()) as c) conn
            where c = ANY (connections);
            raise;

    end;
$$;