REFRESH MATERIALIZED VIEW CONCURRENTLY Runs Fine Inside a Transaction Block

REFRESH MATERIALIZED VIEW CONCURRENTLY runs fine inside BEGIN and COMMIT. It does not throw cannot run inside a transaction block. That error exists in Postgres, and it is real, but it belongs to a different set of CONCURRENTLY operations: CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, REINDEX CONCURRENTLY, and ALTER TABLE ... DETACH PARTITION CONCURRENTLY. Refreshing a materialized view concurrently is not one of them.

If you landed here because you are staring at that exact error message with a REFRESH MATERIALIZED VIEW CONCURRENTLY statement above it in your migration, the two are unrelated. Something else in the same transaction is throwing it, most likely one of the four statements above, or you are about to add transaction: false to a migration that never needed it. Either way, the rest of this page is the source-verified detail behind that answer, tested against a real Postgres instance, not inferred from the docs alone.

Why this looks like the CREATE INDEX CONCURRENTLY problem

The confusion is reasonable. Every Postgres statement that carries a CONCURRENTLY keyword shares a family resemblance: it trades a strong, blocking lock for a weaker one, in exchange for extra internal complexity. If you have already hit the transaction-block error once with CREATE INDEX CONCURRENTLY, it is a fair guess that REFRESH MATERIALIZED VIEW CONCURRENTLY behaves the same way.

It does not, and the gap between them is not a matter of interpretation. Postgres implements the transaction-block restriction as an explicit function call, PreventInTransactionBlock(), defined in src/backend/access/transam/xact.c, and it is the source of the literal error text: "%s cannot run inside a transaction block". Grepping the Postgres 17 source for every caller of that function outside the replication protocol turns up this complete list:

utility.c:      PreventInTransactionBlock(isTopLevel, "COMMIT PREPARED");
utility.c:      PreventInTransactionBlock(isTopLevel, "ROLLBACK PREPARED");
utility.c:      PreventInTransactionBlock(isTopLevel, "CREATE TABLESPACE");
utility.c:      PreventInTransactionBlock(isTopLevel, "DROP TABLESPACE");
utility.c:      PreventInTransactionBlock(isTopLevel, "CREATE DATABASE");
utility.c:      PreventInTransactionBlock(isTopLevel, "DROP DATABASE");
utility.c:      PreventInTransactionBlock(isTopLevel, "ALTER SYSTEM");
utility.c:      PreventInTransactionBlock(isTopLevel, "ALTER TABLE ... DETACH CONCURRENTLY");
utility.c:      PreventInTransactionBlock(isTopLevel, "CREATE INDEX CONCURRENTLY");
utility.c:      PreventInTransactionBlock(isTopLevel, "DROP INDEX CONCURRENTLY");
indexcmds.c:    PreventInTransactionBlock(isTopLevel, "REINDEX CONCURRENTLY");
indexcmds.c:    PreventInTransactionBlock(isTopLevel, "REINDEX SCHEMA"/"REINDEX SYSTEM"/"REINDEX DATABASE");
indexcmds.c:    PreventInTransactionBlock(isTopLevel, "REINDEX TABLE"/"REINDEX INDEX");
vacuum.c:       PreventInTransactionBlock(isTopLevel, stmttype);   -- VACUUM, ANALYZE
cluster.c:      PreventInTransactionBlock(isTopLevel, "CLUSTER");
dbcommands.c:   PreventInTransactionBlock(isTopLevel, "ALTER DATABASE SET TABLESPACE");
discard.c:      PreventInTransactionBlock(isTopLevel, "DISCARD ALL");

A handful of additional call sites exist in subscriptioncmds.c and walsender.c, covering logical replication commands such as CREATE SUBSCRIPTION ... WITH (create_slot = true) and the streaming replication protocol, none of which show up in an ordinary schema migration file.

src/backend/commands/matview.c, the file that implements REFRESH MATERIALIZED VIEW, is not in that list. It has zero calls to PreventInTransactionBlock, in every currently maintained branch (checked against REL_14_STABLE through REL_18_STABLE). Nothing in the materialized view refresh path ever asks Postgres to reject an explicit transaction.

Proof, not just an absence of a function call

Reading source is good evidence. Running it is better. This is the actual output from a Postgres 17.9 session:

postgres=# BEGIN;
BEGIN
postgres=*# REFRESH MATERIALIZED VIEW CONCURRENTLY pf_mv;
REFRESH MATERIALIZED VIEW
postgres=*# COMMIT;
COMMIT

No error, no notice, nothing unusual. For comparison, the statement that actually triggers the message, run in the same session:

postgres=# BEGIN;
BEGIN
postgres=*# CREATE INDEX CONCURRENTLY idx_pf_val ON pf_src(val);
ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

The plain, non-CONCURRENTLY form of refresh is unrestricted too. Neither variant of REFRESH MATERIALIZED VIEW has ever been on the autocommit-only list.

Why the refresh path does not need its own transaction

CREATE INDEX CONCURRENTLY needs multiple transactions because of how it guarantees correctness without a long lock. The Postgres documentation on building indexes concurrently spells out the mechanism: “In a concurrent index build, the index is actually entered as an ‘invalid’ index into the system catalogs in one transaction, then two table scans occur in two more transactions.” Each of those transaction boundaries lets Postgres wait out other in-flight transactions before proceeding to the next phase. That sequencing is incompatible with already being inside a transaction you do not control, because the command cannot commit and reopen a transaction that belongs to your migration.

REFRESH MATERIALIZED VIEW CONCURRENTLY solves a different problem with a different mechanism, and it never needs to commit anything mid-flight. Per the source in matview.c, the concurrent path builds a transient table holding the fresh query result, then runs a full outer join between that transient table and the existing materialized view, using the view’s required unique index as the join key, and applies the differences as ordinary DELETE and INSERT statements against the view’s storage. Building a temporary table and issuing DELETE/INSERT are both things a single transaction already does every day. There is no multi-phase wait-for-other-transactions choreography to protect, so there is nothing forcing the statement out of your transaction.

The lock it actually takes

The two forms of REFRESH MATERIALIZED VIEW do not take the same lock, and the difference is the entire reason CONCURRENTLY exists. Per the explicit locking documentation:

Acquired by REFRESH MATERIALIZED VIEW CONCURRENTLY. [EXCLUSIVE] … This mode allows only concurrent ACCESS SHARE locks, i.e., only reads from the table can proceed in parallel with a transaction holding this lock mode.

Acquired by the DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, and REFRESH MATERIALIZED VIEW (without CONCURRENTLY) commands. [ACCESS EXCLUSIVE] … This mode guarantees that the holder is the only transaction accessing the table in any way.

Form	Lock mode	Blocks reads	Blocks writes	Blocks other DDL
REFRESH MATERIALIZED VIEW	ACCESS EXCLUSIVE	Yes	Yes	Yes
REFRESH MATERIALIZED VIEW CONCURRENTLY	EXCLUSIVE	No	Yes	Yes

EXCLUSIVE conflicts with everything except ACCESS SHARE, so SELECT against the view keeps working while a concurrent refresh runs. INSERT, UPDATE, and DELETE are not relevant here since nothing writes directly to a materialized view, but any other DDL or maintenance operation touching it (another refresh, a REINDEX, an ALTER MATERIALIZED VIEW) queues up behind the lock, same as it would behind any other conflicting mode. That is a real cost worth planning around, it is just a different, smaller cost than blocking reads entirely.

Two ways CONCURRENTLY can still fail, neither one about transactions

CONCURRENTLY does have real preconditions, they are just not transaction-related. Both produce clear, specific errors, verified directly:

No qualifying unique index:

postgres=# REFRESH MATERIALIZED VIEW CONCURRENTLY pf_mv_nounique;
ERROR:  cannot refresh materialized view "public.pf_mv_nounique" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.

The docs are specific about what qualifies: “at least one UNIQUE index on the materialized view which uses only column names and includes all rows; that is, it must not be an expression index or include a WHERE clause.”

The view has never been populated:

postgres=# REFRESH MATERIALIZED VIEW CONCURRENTLY pf_mv_nodata;
ERROR:  CONCURRENTLY cannot be used when the materialized view is not populated

This hits anyone who created the view with CREATE MATERIALIZED VIEW ... WITH NO DATA. The first refresh after that has to be the plain form; CONCURRENTLY only works once there is existing data to diff against. A view created without WITH NO DATA (the default) is already populated at creation time, so this does not affect the common case.

One more constraint from the same docs page, worth knowing even though it rarely bites: “Even with this option only one REFRESH at a time may run against any one materialized view.” A second concurrent refresh against the same view waits for the first to finish rather than running in parallel with it.

The part that actually matters for migrations: it rolls back cleanly

This is the detail that makes the transaction-block question worth asking in the first place, and it comes out in pgfence’s favor. Because REFRESH MATERIALIZED VIEW CONCURRENTLY does all of its work as ordinary DML inside your transaction, a ROLLBACK undoes it completely, the same as it would undo an UPDATE. Verified directly:

postgres=# UPDATE pf_src SET val = 'changed'||id;
UPDATE 5
postgres=# BEGIN;
BEGIN
postgres=*# REFRESH MATERIALIZED VIEW CONCURRENTLY pf_mv;
REFRESH MATERIALIZED VIEW
postgres=*# SELECT val FROM pf_mv WHERE id = 1;
   val
----------
 changed1
(1 row)

postgres=*# ROLLBACK;
ROLLBACK
postgres=# SELECT val FROM pf_mv WHERE id = 1;
  val
-------
 orig1
(1 row)

Compare that to CREATE INDEX CONCURRENTLY. A failed concurrent index build cannot roll back, by construction, because it already committed the intermediate transactions that entered the index into the catalog and ran the first table scan. That is exactly why a failed build leaves an invalid index behind that has to be dropped and rebuilt by hand. REFRESH MATERIALIZED VIEW CONCURRENTLY has no equivalent failure mode. If it fails partway through, or if anything else in the same transaction fails and the whole thing rolls back, the materialized view is exactly as it was before the transaction started.

What this means for migration tools that wrap everything in a transaction

Most migration runners wrap each migration file in an implicit transaction by default, which is what makes CREATE INDEX CONCURRENTLY need an explicit opt-out: transaction = false in TypeORM, a disabled-transaction migration config in Knex, disable_ddl_transaction! in Rails. That opt-out exists because those tools have to get out of the way of a statement that manages its own transaction boundaries internally.

REFRESH MATERIALIZED VIEW CONCURRENTLY never asks for that, and adding the opt-out anyway is not a harmless precaution, it is a downgrade. Take the transaction wrapper away and you also take away the rollback safety net demonstrated above. Inside the default transaction, a failure anywhere else in the same migration file rolls the refresh back along with everything else, atomically. Run it with the transaction disabled and that guarantee is gone: the refresh either already committed on its own or it did not, independent of whatever else in the migration succeeded or failed.

The practical rule for a migration file that includes REFRESH MATERIALIZED VIEW CONCURRENTLY:

• Leave the migration’s default transaction wrapper in place. Do not add a per-file transaction opt-out for this statement.
• Still set lock_timeout before it runs. EXCLUSIVE still blocks writes and other DDL on the view, and a concurrent refresh queued behind another long-running operation on that same view can wait as long as anything else waiting on a lock.
• Confirm the required unique index exists and the view has been populated at least once, since both are compile-time-invisible preconditions that only show up when the statement actually runs.

What pgfence does with this

pgfence’s refresh-matview rule classifies REFRESH MATERIALIZED VIEW CONCURRENTLY as LOW risk, reports the EXCLUSIVE lock and what it blocks, and stops there. It does not add this statement to the same policy check that flags CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, REINDEX CONCURRENTLY, and ALTER TABLE ... DETACH PARTITION CONCURRENTLY inside an explicit transaction, because unlike those four, this statement was never going to fail there. A linter that flagged it anyway, on the reasonable-sounding assumption that all CONCURRENTLY operations share this restriction, would be training you to add a transaction opt-out that actively makes the migration less safe.

npx @flvmnt/pgfence analyze migrations/*.sql

The non-CONCURRENTLY form still gets flagged, correctly, at HIGH risk for the ACCESS EXCLUSIVE lock it holds for the full refresh duration, with a safe rewrite that adds the required unique index and switches to CONCURRENTLY.

Review checklist

1. Seeing cannot run inside a transaction block next to a REFRESH MATERIALIZED VIEW CONCURRENTLY statement? Look at the rest of the migration. The statement actually throwing it is almost certainly CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, REINDEX CONCURRENTLY, or DETACH PARTITION CONCURRENTLY.
2. Do not add a transaction opt-out to a migration file just because it contains REFRESH MATERIALIZED VIEW CONCURRENTLY. It does not need one, and removing the wrapper removes the automatic rollback on failure.
3. Confirm a qualifying unique index (no expressions, no WHERE clause, covers all rows) exists on the view before relying on CONCURRENTLY in production.
4. If the view was created WITH NO DATA, the first refresh has to be the plain form. CONCURRENTLY only works once it is already populated.
5. Set lock_timeout regardless of which form you use. EXCLUSIVE still blocks writes and other DDL on the view, and ACCESS EXCLUSIVE blocks everything.

Related

• CREATE INDEX CONCURRENTLY in a transaction is a silent footgun
• CREATE INDEX CONCURRENTLY IF NOT EXISTS does not fix a broken index
• Postgres lock mode cheat sheet
• The lock_timeout death spiral