Incident brief
COPY FREEZE with prior transaction activity
COPY ... WITH (FREEZE) was attempted while a cursor was still open in the same transaction. PostgreSQL refused because it can no longer guarantee no one else can see the pre-freeze rows.
In 10 seconds
- What
- COPY FREEZE with prior transaction activity
- What triggers it
- Start a transaction, create a new table, and open a cursor (DECLARE ... CURSOR) that is still open.
- The fix
- Run COPY ... FREEZE before opening any cursor — the table just needs to have been created or truncated in the current transaction, with no cursors or older snapshots yet.
- Proof
- Reproduced on PostgreSQL 16.14 → COPY ... WITH (FREEZE) was rejected with SQLSTATE 25000 while a cursor was still open in the same transaction. The identical COPY FREEZE succeeded once it ran before the cursor was opened.
The fix
What to do right now
The immediate, application-level response to this error.
- Run COPY ... FREEZE before opening any cursor — the table just needs to have been created or truncated in the current transaction, with no cursors or older snapshots yet.
- Or move the cursor-based work into a separate transaction that runs after the COPY commits.
BEGIN;
CREATE TABLE batch8_freeze_demo (id int);
COPY batch8_freeze_demo FROM STDIN WITH (FREEZE);
1
2
\.
DECLARE freeze_demo_cur CURSOR FOR SELECT generate_series(1,3);
FETCH 1 FROM freeze_demo_cur;
COMMIT;Diagnose
See it live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Standard triage — not specific to this error
These are canonical PostgreSQL system-catalog queries, shown as SQL to run. No sample output is attached because this is general triage, not a captured lab transcript.This SQLSTATE does not have an error-specific live snapshot yet. These are the canonical system-catalog queries you run against the affected server to see the problem in real time — standard triage, not a reproduced transcript.
What is running right now
Active backends, how long each has been running, and what it is waiting on.
SELECT pid,
state,
wait_event_type,
wait_event,
now() - query_start AS running_for,
left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
AND pid <> pg_backend_pid()
ORDER BY running_for DESC NULLS LAST;Who is blocking whom
Turn raw blocking PIDs into the actual queries on both sides of the wait.
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity AS blocking ON blocking.pid = b.pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;Locks that are still waiting
Every lock a backend has requested but not yet been granted.
SELECT l.pid,
l.locktype,
l.mode,
l.granted,
COALESCE(c.relname, l.transactionid::text) AS object
FROM pg_locks l
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE NOT l.granted
ORDER BY l.pid;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual — not paraphrased.
PostgreSQL 16 Documentation — COPY
Rows will be frozen only if the table being loaded has been created or truncated in the current subtransaction, there are no cursors open and there are no older snapshots held by this transaction.Read the full section on postgresql.org →
COPY FREEZE with an open cursor
Per the manual, COPY FREEZE requires no cursors to be open in the transaction — an open cursor, even one that has already fetched a row, counts as prior transaction activity, so PostgreSQL refuses to freeze the rows.COPY FREEZE before opening any cursor
Running COPY FREEZE first, before the cursor is opened, satisfies the requirement — the table was created in this transaction and nothing else has happened yet, so the rows are frozen and the transaction commits cleanly.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab — the commands below are exactly what was executed.
- 1Start a transaction, create a new table, and open a cursor (DECLARE ... CURSOR) that is still open.
- 2Run COPY <table> FROM STDIN WITH (FREEZE) into the newly created table.
- 3SQLSTATE 25000 is reported because a cursor is still open in the transaction.
One client, one transaction: create a table, open a cursor, then attempt COPY ... WITH (FREEZE) into that table.
-- No shared setup: each session below is its own self-contained transaction.
SELECT 'no shared schema required' AS setup_note;BEGIN;
CREATE TABLE batch8_freeze_demo (id int);
DECLARE freeze_demo_cur CURSOR FOR SELECT generate_series(1,3);
FETCH 1 FROM freeze_demo_cur;
COPY batch8_freeze_demo FROM STDIN WITH (FREEZE);
1
2
\.
ROLLBACK;BEGIN;
CREATE TABLE batch8_freeze_demo (id int);
COPY batch8_freeze_demo FROM STDIN WITH (FREEZE);
1
2
\.
DECLARE freeze_demo_cur CURSOR FOR SELECT generate_series(1,3);
FETCH 1 FROM freeze_demo_cur;
COMMIT;What PostgreSQL actually returned
setup_note
---------------------------
no shared schema required
(1 row)BEGIN
CREATE TABLE
DECLARE CURSOR
generate_series
-----------------
1
(1 row)
ERROR: cannot perform COPY FREEZE because of prior transaction activity
ROLLBACKBEGIN
CREATE TABLE
COPY 2
DECLARE CURSOR
generate_series
-----------------
1
(1 row)
COMMITThe deeper lab audit for this error
What Pro unlocks here
- The exact prevention SQL — copy-paste ready
- Raw psql output captured from the Docker lab
- A senior-DBA action list to take it further
- Live monitoring queries to catch it in production
- The deeper audit: fix-that-fails counterexample, GUC before/after, server-log evidence
Related & next steps
Follow the thread
Everything this error touches — jump straight to the sibling error, term, runbook, or parameter.
Related errors
Verification
- Last verified
- 2026-07-16 (Docker lab, PostgreSQL 16.14)
- Reviewed by
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed