golang-migrate / golang-migrate/migrate

Allow something like "-- +goose StatementBegin" in addition to ";" for multi-statement splitter

Open
#1,237 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Go
Stars
18.9k
Forks
1.6k
Avg merge
3d 3h
Merged PRs (30d)
4

Description

**Is your feature request related to a problem? Please describe.**
I have special type of migrations, where I need to migrate large amounts of data after some DDL is changed. Because these tables are large, these scripts executed much faster when processed by parts, and also to prevent this huge work from being lost because of database timeout - I try to commit after every step.

Example

```sql
--/
create or replace procedure fill_volume_metadata(in STEP int default 100000)
as $$
declare
MAX_META_ID numeric(19);
begin
select coalesce(max(ID), 0) into MAX_META_ID from datevalues;
RAISE NOTICE 'MAX datevalues: % %', MAX_META_ID, E'\n';

FOR IDX IN 1..MAX_META_ID BY STEP LOOP
RAISE NOTICE 'datevalues: %...% %', IDX, IDX + STEP - 1, E'\n';

perform insert_volume_metadata(
DataValue
) from (
select
v.DataValue
from datevalues v
where v.ID >= IDX and v.ID < (IDX + STEP)
) v;

COMMIT;
END LOOP;
RAISE NOTICE 'datevalues finished: % %', MAX_META_ID, E'\n';
end;
$$ LANGUAGE plpgsql;
--//

CALL fill_volume_metadata();

drop procedure if exists fill_volume_metadata(in STEP int);
```

The above script works when executed with raw `psql`, but with `migrate` it throws transaction error, because multi statement is disabled and this is executed in single transaction. Currently I just execute these migrations with `psql`, and then perform `migrate force`.

**Describe the solution you'd like**

https://github.com/pressly/goose has nice solution for multistatement mode. In addition to `;` it recognizes blocks

```
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION histories_partition_creation( DATE, DATE )
...
END; -- FUNCTION END
$$
language plpgsql;
-- +goose StatementEnd
```

If something like this will be supported, I could wrap my statements with these comments, and enable single statement mode.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.