Description
IsCreatedConcurrently() emits CREATE INDEX CONCURRENTLY and suppresses the surrounding transaction (#1210, #1214). dotnet ef migrations script without --idempotent leaves that statement outside the transaction, and PostgreSQL accepts it.
dotnet ef migrations script --idempotent still wraps every command in NpgsqlHistoryRepository.GetBeginIfNotExistsScript, including commands marked TransactionSuppressed. That method is a DO $EF$ block, because PostgreSQL has no top-level IF. PostgreSQL rejects CREATE INDEX CONCURRENTLY inside a function.
database update is unaffected. It runs the suppressed command on its own, outside a transaction and outside DO.
Reproduction
Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3, Microsoft.EntityFrameworkCore 10.0.10, dotnet-ef 10.0.10, PostgreSQL 14.13.
modelBuilder.Entity<Blog>(b => b.HasIndex(x => x.Name).IsCreatedConcurrently());
dotnet ef migrations script --idempotent emits:
COMMIT;
DO $EF$
BEGIN
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260925155729_AddNameIndex') THEN
CREATE INDEX CONCURRENTLY "IX_Blogs_Name" ON "Blogs" ("Name");
END IF;
END $EF$;The table create is already committed. Executing the DO block on PostgreSQL 14.13, with an empty __EFMigrationsHistory, fails:
ERROR: CREATE INDEX CONCURRENTLY cannot be executed from a function
CONTEXT: SQL statement "CREATE INDEX CONCURRENTLY "IX_Blogs_Name" ON "Blogs" ("Name")"
PL/pgSQL function inline_code_block line 4 at SQL statement
The history insert comes after that statement, so it never runs. A second attempt enters the IF again and fails on CREATE TABLE with relation "Blogs" already exists.
Without --idempotent, the same migration emits a top-level statement, which succeeds:
COMMIT;
CREATE INDEX CONCURRENTLY "IX_Blogs_Name" ON "Blogs" ("Name");Expected
Transaction-suppressed commands should be emitted outside the DO $EF$ block. The history check cannot be a PL/pgSQL IF around CREATE INDEX CONCURRENTLY.
EF Core's Migrator.GenerateSqlScript applies GetBeginIfNotExistsScript to every command, including TransactionSuppressed ones. #1214 set suppressTransaction on the create-index command, which is why the script commits first. The idempotent wrapper is a separate layer and still produces SQL PostgreSQL will not run.
Description
IsCreatedConcurrently()emitsCREATE INDEX CONCURRENTLYand suppresses the surrounding transaction (#1210, #1214).dotnet ef migrations scriptwithout--idempotentleaves that statement outside the transaction, and PostgreSQL accepts it.dotnet ef migrations script --idempotentstill wraps every command inNpgsqlHistoryRepository.GetBeginIfNotExistsScript, including commands markedTransactionSuppressed. That method is aDO $EF$block, because PostgreSQL has no top-levelIF. PostgreSQL rejectsCREATE INDEX CONCURRENTLYinside a function.database updateis unaffected. It runs the suppressed command on its own, outside a transaction and outsideDO.Reproduction
Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3, Microsoft.EntityFrameworkCore 10.0.10, dotnet-ef 10.0.10, PostgreSQL 14.13.
dotnet ef migrations script --idempotentemits:The table create is already committed. Executing the
DOblock on PostgreSQL 14.13, with an empty__EFMigrationsHistory, fails:The history insert comes after that statement, so it never runs. A second attempt enters the
IFagain and fails onCREATE TABLEwithrelation "Blogs" already exists.Without
--idempotent, the same migration emits a top-level statement, which succeeds:Expected
Transaction-suppressed commands should be emitted outside the
DO $EF$block. The history check cannot be a PL/pgSQLIFaroundCREATE INDEX CONCURRENTLY.EF Core's
Migrator.GenerateSqlScriptappliesGetBeginIfNotExistsScriptto every command, includingTransactionSuppressedones. #1214 setsuppressTransactionon the create-index command, which is why the script commits first. The idempotent wrapper is a separate layer and still produces SQL PostgreSQL will not run.