Comments (7)
I was able to reproduce this issue with the latest dbmate 2.7.0-main, against PostgreSQL 10.21:
root@c2a4e68d4451:/src# cat testdata/db/migrations/455_test_transaction_concurrently.sql
-- migrate:up transaction:false
drop index concurrently my_index;
create index concurrently my_index on my_table (
some_column
);
-- migrate:down
root@c2a4e68d4451:/src# dist/dbmate -u $POSTGRES_TEST_URL -d testdata/db/migrations up
Applying: 455_test_transaction_concurrently.sql
Error: pq: DROP INDEX CONCURRENTLY cannot be executed from a function or multi-command string
Searching for that particular error turns up lib/pq
issue #820, and our own #126 that references it, where @amacneil wrote:
It sounds like this is a limitation of the
lib/pq
driver, and I'm not aware of any other golang drivers for postgresql. Therefore, I will close this with the workaround of putting each concurrent index creation in a separate migration file.
And this issue came up again in #182.
It looks like this is ultimately an issue with how lib/pq
and Postgres handle transactions and support for multi-command queries, and isn't something that can be fixed from dbmate's end.
The workaround is to create separate migration files for each CREATE INDEX CONCURRENTLY
command, which isn't great but it's simple and it works.
Perhaps the migrations option section of the README could document this, including the various error string responses above so that people searching may be able to find the information?
This is certainly a problem that has occurred repeatedly over the years, so I think there should be value in mentioning it clearly in the documentation.
from dbmate.
does moving to pgx can solve this ?
Or using some kind of (not perfect ) regex ( ;[end of line]) to separate statements and run them one by one?
I think it can support a lot of use cases
from dbmate.
Taking a look at this, I've created similar migration:
-- migrate:up transaction:false
drop index concurrently idx;
create index concurrently idx on test (id);
I get a slightly different error which actually states it can't work within a transaction:
% dbmate up
Applying: 20230831142614_error.sql
Error: pq: DROP INDEX CONCURRENTLY cannot run inside a transaction block
Details:
- Version: 2.6.0
- Database: PostgreSQL 15.3
- System: MacOS 13.4.1
Not got my test enviornment setup back up yet, I'll investigate more soon.
from dbmate.
Duplicate of #126.
from dbmate.
does moving to pgx can solve this ?
Or using some kind of (not perfect ) regex ( ;[end of line]) to separate statements and run them one by one?
I think it can support a lot of use cases
Moving to pgx is unlikely to solve this.
The author of pgx, @jackc, created their own SQL migration tool named tern. And, they encountered this very same issue, because the problem is not one that can be safely resolved at the driver level, in pgx
.
Regular expressions alone cannot be used to create a fully correct SQL parser that could safely split any arbitrary SQL statement, although strictly limiting the splitting to ;\n
might be a little safer, but still not 100% safe:
insert into t (data) values (
'this is a;
long string'
);
You can see what @jackc did in tern
, implementing their own minimal SQL parser in order to split statements, in this commit, b23e02f6. If you examine the tests they wrote, you can begin to see the complexity of possible cases that even a "simple" implementation must handle correctly.
from dbmate.
@dossy , I believe it can solve a lot of use cases for transaction:false, which is better then the current state.
The Author states he want to keep things simple, supporting just basic statements could be good enough. Also, in Goose they use a custom annotation for complex statements. This also can be done on every statement in the transaction:false case.
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION histories_partition_creation( DATE, DATE )
returns void AS $$
DECLARE
create_query text;
BEGIN
FOR create_query IN SELECT
'CREATE TABLE IF NOT EXISTS histories_'
|| TO_CHAR( d, 'YYYY_MM' )
|| ' ( CHECK( created_at >= timestamp '''
|| TO_CHAR( d, 'YYYY-MM-DD 00:00:00' )
|| ''' AND created_at < timestamp '''
|| TO_CHAR( d + INTERVAL '1 month', 'YYYY-MM-DD 00:00:00' )
|| ''' ) ) inherits ( histories );'
FROM generate_series( $1, $2, '1 month' ) AS d
LOOP
EXECUTE create_query;
END LOOP; -- LOOP END
END; -- FUNCTION END
$$
language plpgsql;
-- +goose StatementEnd
For me its blocking this simple transaction:false migration:
set statement_timeout = '60min';
CREATE INDEX Concurrently IF NOT EXISTS...
If a few lines of code can add 70% statement support dont you think its a good addition? And of course not supporting multiline statements or harder statements in this mode will be added to the readme
from dbmate.
If a few lines of code can add 70% statement support dont you think its a good addition? And of course not supporting multiline statements or harder statements in this mode will be added to the readme
My personal preference is to not add anything that will break what currently is known to work, even if it's a really good addition. Personally, I value stability and reliability over feature completeness. I understand others feel differently; that's why there are so many varieties of products that do similar things.
If someone can provide an implementation of a few lines of code that will not break every user's current migrations, with tests to prove such, we could all review it together and evaluate whether it is a good addition or not.
from dbmate.
Related Issues (20)
- Dbmate Unable to Create SchemaMigrations Table HOT 6
- Postgres Schemas with special characters have DBMate create migration tables then cannot find it again
- ClickHouse Cloud https connection doesn't work HOT 2
- The migration lock is not released after terminating the migration HOT 2
- Comments (on columns) that include line breaks are never created. HOT 1
- dbmate requires each migration to define a down block with '-- migrate:down' when it defined HOT 5
- No Zookeeper configuration in server config
- Support all DSN variants for clickhouse DATABASE_URL HOT 8
- Wait for Postgres to be ready HOT 3
- $PGHOST & $PGPORT are not respected. HOT 2
- Add a section to the README documenting how environment variables are used by dbmate
- Table aliases don't work with ClickHouse HOT 2
- Add `--wait-interval` and `$DBMATE_WAIT_INTERVAL` to make wait interval configurable at runtime HOT 7
- The latest release is missing all the binaries HOT 2
- Could not open extension control file "/share/extension/http.control" HOT 2
- Embedded FS on Windows HOT 2
- `make docker-all` timeout when run locally. HOT 1
- DBMate always delets my database anytime I run a migration HOT 1
- Library methods not using context
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from dbmate.