This is an automated email from the ASF dual-hosted git repository.
djwang pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/main by this push:
new f9797991146 Fix COPY TO returning 0 rows during concurrent reorganize
f9797991146 is described below
commit f97979911465650b6626eeffa49af9429a11d2c6
Author: Hao Wu <[email protected]>
AuthorDate: Wed Feb 25 13:18:50 2026 +0000
Fix COPY TO returning 0 rows during concurrent reorganize
When ALTER TABLE ... SET WITH (reorganize=true) runs concurrently with
COPY TO, COPY may return 0 rows instead of all rows. The root cause is
a snapshot/lock ordering problem: PortalRunUtility() pushes the active
snapshot before calling DoCopy(), so the snapshot predates any
concurrent reorganize that had not yet committed. After COPY TO blocks
on AccessExclusiveLock and the reorganize commits, the stale snapshot
cannot see the new physical files (xmin = reorganize_xid is invisible)
while the old physical files have already been removed, yielding 0 rows.
Three code paths are fixed:
1. Relation-based COPY TO (copy.c, DoCopy):
After table_openrv() acquires AccessShareLock — which blocks until
any concurrent reorganize commits — pop and re-push the active
snapshot so it reflects all committed data at lock-grant time.
2. Query-based COPY TO, RLS COPY TO, and CTAS (copyto.c, BeginCopy):
After pg_analyze_and_rewrite() -> AcquireRewriteLocks() acquires
all direct relation locks, refresh the snapshot. This covers
COPY (SELECT ...) TO, COPY on RLS-protected tables (internally
rewritten to a query), and CREATE TABLE AS SELECT.
3. Partitioned table COPY TO (copy.c, DoCopy):
Before entering BeginCopy, call find_all_inheritors() to eagerly
acquire AccessShareLock on all child partitions. Child partition
locks are normally acquired later in ExecutorStart -> ExecInitAppend,
after PushCopiedSnapshot has already embedded a stale snapshot.
Locking all children upfront ensures the snapshot refresh in fixes
1 and 2 covers all concurrent child-partition reorganize commits.
In REPEATABLE READ or SERIALIZABLE isolation, GetTransactionSnapshot()
returns the same transaction-level snapshot, so the Pop/Push is a
harmless no-op.
Tests added:
- src/test/isolation2/sql/copy_to_concurrent_reorganize.sql
Tests 2.1-2.5 for relation-based, query-based, partitioned, RLS,
and CTAS paths across heap, AO row, and AO column storage.
- contrib/pax_storage/src/test/isolation2/sql/pax/
copy_to_concurrent_reorganize.sql
Same coverage for PAX columnar storage.
See: Issue#1545 <https://github.com/apache/cloudberry/issues/1545>
---
.../expected/pax/copy_to_concurrent_reorganize.out | 289 +++++++
.../src/test/isolation2/isolation2_schedule | 1 +
.../sql/pax/copy_to_concurrent_reorganize.sql | 170 ++++
src/backend/commands/copy.c | 81 ++
src/backend/commands/copyto.c | 37 +
.../expected/copy_to_concurrent_reorganize.out | 918 +++++++++++++++++++++
src/test/isolation2/isolation2_schedule | 1 +
.../sql/copy_to_concurrent_reorganize.sql | 561 +++++++++++++
8 files changed, 2058 insertions(+)
diff --git
a/contrib/pax_storage/src/test/isolation2/expected/pax/copy_to_concurrent_reorganize.out
b/contrib/pax_storage/src/test/isolation2/expected/pax/copy_to_concurrent_reorganize.out
new file mode 100644
index 00000000000..b4beed7d035
--- /dev/null
+++
b/contrib/pax_storage/src/test/isolation2/expected/pax/copy_to_concurrent_reorganize.out
@@ -0,0 +1,289 @@
+-- Test: PAX table — relation-based COPY TO concurrent with ALTER TABLE SET
WITH (reorganize=true)
+-- Issue: https://github.com/apache/cloudberry/issues/1545
+-- Same as test 2.1 in the main isolation2 suite but for PAX storage.
+
+CREATE TABLE copy_reorg_pax_test (a INT, b INT) DISTRIBUTED BY (a);
+CREATE
+INSERT INTO copy_reorg_pax_test SELECT i, i FROM generate_series(1, 1000) i;
+INSERT 1000
+
+-- Record original row count
+SELECT count(*) FROM copy_reorg_pax_test;
+ count
+-------
+ 1000
+(1 row)
+
+-- Session 1: Begin reorganize (holds AccessExclusiveLock)
+1: BEGIN;
+BEGIN
+1: ALTER TABLE copy_reorg_pax_test SET WITH (reorganize=true);
+ALTER
+
+-- Session 2: relation-based COPY TO should block on AccessShareLock
+2&: COPY copy_reorg_pax_test TO '/tmp/copy_reorg_pax_test.csv'; <waiting ...>
+
+-- Confirm Session 2 is waiting for the lock
+1: SELECT count(*) > 0 FROM pg_stat_activity WHERE query LIKE 'COPY
copy_reorg_pax_test%' AND wait_event_type = 'Lock';
+ ?column?
+----------
+ t
+(1 row)
+
+-- Session 1: Commit reorganize, releasing AccessExclusiveLock
+1: COMMIT;
+COMMIT
+
+-- Session 2: Should return 1000 rows (fixed), not 0 rows (broken)
+2<: <... completed>
+COPY 1000
+
+-- Verify the output file contains all rows
+CREATE TABLE copy_reorg_pax_verify (a INT, b INT) DISTRIBUTED BY (a);
+CREATE
+COPY copy_reorg_pax_verify FROM '/tmp/copy_reorg_pax_test.csv';
+COPY 1000
+SELECT count(*) FROM copy_reorg_pax_verify;
+ count
+-------
+ 1000
+(1 row)
+
+-- Cleanup
+DROP TABLE copy_reorg_pax_verify;
+DROP
+DROP TABLE copy_reorg_pax_test;
+DROP
+
+-- ============================================================
+-- Test 2.2c: PAX — query-based COPY TO + concurrent reorganize
+-- Fixed: BeginCopy() refreshes snapshot after AcquireRewriteLocks().
+-- ============================================================
+
+CREATE TABLE copy_query_reorg_pax_test (a INT, b INT) DISTRIBUTED BY (a);
+CREATE
+INSERT INTO copy_query_reorg_pax_test SELECT i, i FROM generate_series(1,
1000) i;
+INSERT 1000
+
+SELECT count(*) FROM copy_query_reorg_pax_test;
+ count
+-------
+ 1000
+(1 row)
+
+1: BEGIN;
+BEGIN
+1: ALTER TABLE copy_query_reorg_pax_test SET WITH (reorganize=true);
+ALTER
+
+2&: COPY (SELECT * FROM copy_query_reorg_pax_test) TO
'/tmp/copy_query_reorg_pax_test.csv'; <waiting ...>
+
+1: SELECT count(*) > 0 FROM pg_stat_activity WHERE query LIKE 'COPY
(SELECT%copy_query_reorg_pax_test%' AND wait_event_type = 'Lock';
+ ?column?
+----------
+ t
+(1 row)
+
+1: COMMIT;
+COMMIT
+2<: <... completed>
+COPY 1000
+
+CREATE TABLE copy_query_reorg_pax_verify (a INT, b INT) DISTRIBUTED BY (a);
+CREATE
+COPY copy_query_reorg_pax_verify FROM '/tmp/copy_query_reorg_pax_test.csv';
+COPY 1000
+SELECT count(*) FROM copy_query_reorg_pax_verify;
+ count
+-------
+ 1000
+(1 row)
+
+DROP TABLE copy_query_reorg_pax_verify;
+DROP
+DROP TABLE copy_query_reorg_pax_test;
+DROP
+
+-- ============================================================
+-- Test 2.3c: PAX — partitioned table COPY TO + child partition concurrent
reorganize
+-- Fixed: DoCopy() calls find_all_inheritors() to lock all child partitions
first.
+-- ============================================================
+
+CREATE TABLE copy_part_parent_pax (a INT, b INT) PARTITION BY RANGE (a)
DISTRIBUTED BY (a);
+CREATE
+CREATE TABLE copy_part_child1_pax PARTITION OF copy_part_parent_pax FOR VALUES
FROM (1) TO (501);
+CREATE
+CREATE TABLE copy_part_child2_pax PARTITION OF copy_part_parent_pax FOR VALUES
FROM (501) TO (1001);
+CREATE
+INSERT INTO copy_part_parent_pax SELECT i, i FROM generate_series(1, 1000) i;
+INSERT 1000
+
+SELECT count(*) FROM copy_part_parent_pax;
+ count
+-------
+ 1000
+(1 row)
+
+1: BEGIN;
+BEGIN
+1: ALTER TABLE copy_part_child1_pax SET WITH (reorganize=true);
+ALTER
+
+2&: COPY copy_part_parent_pax TO '/tmp/copy_part_parent_pax.csv'; <waiting
...>
+
+1: SELECT count(*) > 0 FROM pg_stat_activity WHERE query LIKE 'COPY
copy_part_parent_pax%' AND wait_event_type = 'Lock';
+ ?column?
+----------
+ t
+(1 row)
+
+1: COMMIT;
+COMMIT
+2<: <... completed>
+COPY 1000
+
+CREATE TABLE copy_part_pax_verify (a INT, b INT) DISTRIBUTED BY (a);
+CREATE
+COPY copy_part_pax_verify FROM '/tmp/copy_part_parent_pax.csv';
+COPY 1000
+SELECT count(*) FROM copy_part_pax_verify;
+ count
+-------
+ 1000
+(1 row)
+
+DROP TABLE copy_part_pax_verify;
+DROP
+DROP TABLE copy_part_parent_pax;
+DROP
+
+-- ============================================================
+-- Test 2.4c: PAX — RLS table COPY TO + policy-referenced table concurrent
reorganize
+-- Fixed: same as 2.2c — BeginCopy() refreshes snapshot after
AcquireRewriteLocks().
+-- ============================================================
+
+CREATE TABLE copy_rls_pax_lookup (cat INT) DISTRIBUTED BY (cat);
+CREATE
+INSERT INTO copy_rls_pax_lookup SELECT i FROM generate_series(1, 2) i;
+INSERT 2
+
+CREATE TABLE copy_rls_pax_main (a INT, category INT) DISTRIBUTED BY (a);
+CREATE
+INSERT INTO copy_rls_pax_main SELECT i, (i % 5) + 1 FROM generate_series(1,
1000) i;
+INSERT 1000
+
+ALTER TABLE copy_rls_pax_main ENABLE ROW LEVEL SECURITY;
+ALTER
+CREATE POLICY p_rls_pax ON copy_rls_pax_main USING (category IN (SELECT cat
from copy_rls_pax_lookup));
+CREATE
+
+CREATE ROLE copy_rls_pax_testuser;
+CREATE
+GRANT pg_write_server_files TO copy_rls_pax_testuser;
+GRANT
+GRANT ALL ON copy_rls_pax_main TO copy_rls_pax_testuser;
+GRANT
+GRANT ALL ON copy_rls_pax_lookup TO copy_rls_pax_testuser;
+GRANT
+
+SELECT count(*) FROM copy_rls_pax_main;
+ count
+-------
+ 1000
+(1 row)
+
+2: SET ROLE copy_rls_pax_testuser; COPY copy_rls_pax_main TO
'/tmp/copy_rls_pax_main.csv';
+SET 400
+
+1: BEGIN;
+BEGIN
+1: ALTER TABLE copy_rls_pax_lookup SET WITH (reorganize=true);
+ALTER
+
+2&: SET ROLE copy_rls_pax_testuser; COPY copy_rls_pax_main TO
'/tmp/copy_rls_pax_main.csv'; <waiting ...>
+
+1: SELECT count(*) > 0 FROM pg_stat_activity WHERE query LIKE '%COPY
copy_rls_pax_main%' AND wait_event_type = 'Lock';
+ ?column?
+----------
+ t
+(1 row)
+
+1: COMMIT;
+COMMIT
+2<: <... completed>
+SET 400
+
+-- Reset session 2's role to avoid leaking to subsequent tests
+2: RESET ROLE;
+RESET
+
+RESET ROLE;
+RESET
+CREATE TABLE copy_rls_pax_verify (a INT, category INT) DISTRIBUTED BY (a);
+CREATE
+COPY copy_rls_pax_verify FROM '/tmp/copy_rls_pax_main.csv';
+COPY 400
+SELECT count(*) FROM copy_rls_pax_verify;
+ count
+-------
+ 400
+(1 row)
+
+DROP TABLE copy_rls_pax_verify;
+DROP
+DROP POLICY p_rls_pax ON copy_rls_pax_main;
+DROP
+DROP TABLE copy_rls_pax_main;
+DROP
+DROP TABLE copy_rls_pax_lookup;
+DROP
+DROP ROLE copy_rls_pax_testuser;
+DROP
+
+-- ============================================================
+-- Test 2.5c: PAX — CTAS + concurrent reorganize
+-- Fixed as a side effect via BeginCopy() snapshot refresh.
+-- ============================================================
+
+CREATE TABLE ctas_reorg_pax_src (a INT, b INT) DISTRIBUTED BY (a);
+CREATE
+INSERT INTO ctas_reorg_pax_src SELECT i, i FROM generate_series(1, 1000) i;
+INSERT 1000
+
+SELECT count(*) FROM ctas_reorg_pax_src;
+ count
+-------
+ 1000
+(1 row)
+
+1: BEGIN;
+BEGIN
+1: ALTER TABLE ctas_reorg_pax_src SET WITH (reorganize=true);
+ALTER
+
+2&: CREATE TABLE ctas_reorg_pax_dst AS SELECT * FROM ctas_reorg_pax_src
DISTRIBUTED BY (a); <waiting ...>
+
+1: SELECT count(*) > 0 FROM pg_stat_activity WHERE query LIKE 'CREATE TABLE
ctas_reorg_pax_dst%' AND wait_event_type = 'Lock';
+ ?column?
+----------
+ t
+(1 row)
+
+1: COMMIT;
+COMMIT
+2<: <... completed>
+CREATE 1000
+
+SELECT count(*) FROM ctas_reorg_pax_dst;
+ count
+-------
+ 1000
+(1 row)
+
+DROP TABLE ctas_reorg_pax_dst;
+DROP
+DROP TABLE ctas_reorg_pax_src;
+DROP
+
+-- NOTE: Test 2.6c (PAX variant of change distribution key + query-based COPY
TO)
+-- removed for the same reason as test 2.6 (server crash, pre-existing bug).
diff --git a/contrib/pax_storage/src/test/isolation2/isolation2_schedule
b/contrib/pax_storage/src/test/isolation2/isolation2_schedule
index 72fa06f5204..fa163aa96b6 100644
--- a/contrib/pax_storage/src/test/isolation2/isolation2_schedule
+++ b/contrib/pax_storage/src/test/isolation2/isolation2_schedule
@@ -157,6 +157,7 @@ test: pax/vacuum_while_vacuum
# test: uao/bad_buffer_on_temp_ao_row
test: reorganize_after_ao_vacuum_skip_drop truncate_after_ao_vacuum_skip_drop
mark_all_aoseg_await_drop
+test: pax/copy_to_concurrent_reorganize
# below test(s) inject faults so each of them need to be in a separate group
test: segwalrep/master_wal_switch
diff --git
a/contrib/pax_storage/src/test/isolation2/sql/pax/copy_to_concurrent_reorganize.sql
b/contrib/pax_storage/src/test/isolation2/sql/pax/copy_to_concurrent_reorganize.sql
new file mode 100644
index 00000000000..05ef25852e9
--- /dev/null
+++
b/contrib/pax_storage/src/test/isolation2/sql/pax/copy_to_concurrent_reorganize.sql
@@ -0,0 +1,170 @@
+-- Test: PAX table — relation-based COPY TO concurrent with ALTER TABLE SET
WITH (reorganize=true)
+-- Issue: https://github.com/apache/cloudberry/issues/1545
+-- Same as test 2.1 in the main isolation2 suite but for PAX storage.
+
+CREATE TABLE copy_reorg_pax_test (a INT, b INT) DISTRIBUTED BY (a);
+INSERT INTO copy_reorg_pax_test SELECT i, i FROM generate_series(1, 1000) i;
+
+-- Record original row count
+SELECT count(*) FROM copy_reorg_pax_test;
+
+-- Session 1: Begin reorganize (holds AccessExclusiveLock)
+1: BEGIN;
+1: ALTER TABLE copy_reorg_pax_test SET WITH (reorganize=true);
+
+-- Session 2: relation-based COPY TO should block on AccessShareLock
+2&: COPY copy_reorg_pax_test TO '/tmp/copy_reorg_pax_test.csv';
+
+-- Confirm Session 2 is waiting for the lock
+1: SELECT count(*) > 0 FROM pg_stat_activity
+ WHERE query LIKE 'COPY copy_reorg_pax_test%' AND wait_event_type = 'Lock';
+
+-- Session 1: Commit reorganize, releasing AccessExclusiveLock
+1: COMMIT;
+
+-- Session 2: Should return 1000 rows (fixed), not 0 rows (broken)
+2<:
+
+-- Verify the output file contains all rows
+CREATE TABLE copy_reorg_pax_verify (a INT, b INT) DISTRIBUTED BY (a);
+COPY copy_reorg_pax_verify FROM '/tmp/copy_reorg_pax_test.csv';
+SELECT count(*) FROM copy_reorg_pax_verify;
+
+-- Cleanup
+DROP TABLE copy_reorg_pax_verify;
+DROP TABLE copy_reorg_pax_test;
+
+-- ============================================================
+-- Test 2.2c: PAX — query-based COPY TO + concurrent reorganize
+-- Fixed: BeginCopy() refreshes snapshot after AcquireRewriteLocks().
+-- ============================================================
+
+CREATE TABLE copy_query_reorg_pax_test (a INT, b INT) DISTRIBUTED BY (a);
+INSERT INTO copy_query_reorg_pax_test SELECT i, i FROM generate_series(1,
1000) i;
+
+SELECT count(*) FROM copy_query_reorg_pax_test;
+
+1: BEGIN;
+1: ALTER TABLE copy_query_reorg_pax_test SET WITH (reorganize=true);
+
+2&: COPY (SELECT * FROM copy_query_reorg_pax_test) TO
'/tmp/copy_query_reorg_pax_test.csv';
+
+1: SELECT count(*) > 0 FROM pg_stat_activity
+ WHERE query LIKE 'COPY (SELECT%copy_query_reorg_pax_test%' AND
wait_event_type = 'Lock';
+
+1: COMMIT;
+2<:
+
+CREATE TABLE copy_query_reorg_pax_verify (a INT, b INT) DISTRIBUTED BY (a);
+COPY copy_query_reorg_pax_verify FROM '/tmp/copy_query_reorg_pax_test.csv';
+SELECT count(*) FROM copy_query_reorg_pax_verify;
+
+DROP TABLE copy_query_reorg_pax_verify;
+DROP TABLE copy_query_reorg_pax_test;
+
+-- ============================================================
+-- Test 2.3c: PAX — partitioned table COPY TO + child partition concurrent
reorganize
+-- Fixed: DoCopy() calls find_all_inheritors() to lock all child partitions
first.
+-- ============================================================
+
+CREATE TABLE copy_part_parent_pax (a INT, b INT) PARTITION BY RANGE (a)
DISTRIBUTED BY (a);
+CREATE TABLE copy_part_child1_pax PARTITION OF copy_part_parent_pax FOR VALUES
FROM (1) TO (501);
+CREATE TABLE copy_part_child2_pax PARTITION OF copy_part_parent_pax FOR VALUES
FROM (501) TO (1001);
+INSERT INTO copy_part_parent_pax SELECT i, i FROM generate_series(1, 1000) i;
+
+SELECT count(*) FROM copy_part_parent_pax;
+
+1: BEGIN;
+1: ALTER TABLE copy_part_child1_pax SET WITH (reorganize=true);
+
+2&: COPY copy_part_parent_pax TO '/tmp/copy_part_parent_pax.csv';
+
+1: SELECT count(*) > 0 FROM pg_stat_activity
+ WHERE query LIKE 'COPY copy_part_parent_pax%' AND wait_event_type = 'Lock';
+
+1: COMMIT;
+2<:
+
+CREATE TABLE copy_part_pax_verify (a INT, b INT) DISTRIBUTED BY (a);
+COPY copy_part_pax_verify FROM '/tmp/copy_part_parent_pax.csv';
+SELECT count(*) FROM copy_part_pax_verify;
+
+DROP TABLE copy_part_pax_verify;
+DROP TABLE copy_part_parent_pax;
+
+-- ============================================================
+-- Test 2.4c: PAX — RLS table COPY TO + policy-referenced table concurrent
reorganize
+-- Fixed: same as 2.2c — BeginCopy() refreshes snapshot after
AcquireRewriteLocks().
+-- ============================================================
+
+CREATE TABLE copy_rls_pax_lookup (cat INT) DISTRIBUTED BY (cat);
+INSERT INTO copy_rls_pax_lookup SELECT i FROM generate_series(1, 2) i;
+
+CREATE TABLE copy_rls_pax_main (a INT, category INT) DISTRIBUTED BY (a);
+INSERT INTO copy_rls_pax_main SELECT i, (i % 5) + 1 FROM generate_series(1,
1000) i;
+
+ALTER TABLE copy_rls_pax_main ENABLE ROW LEVEL SECURITY;
+CREATE POLICY p_rls_pax ON copy_rls_pax_main USING (category IN (SELECT cat
from copy_rls_pax_lookup));
+
+CREATE ROLE copy_rls_pax_testuser;
+GRANT pg_write_server_files TO copy_rls_pax_testuser;
+GRANT ALL ON copy_rls_pax_main TO copy_rls_pax_testuser;
+GRANT ALL ON copy_rls_pax_lookup TO copy_rls_pax_testuser;
+
+SELECT count(*) FROM copy_rls_pax_main;
+
+2: SET ROLE copy_rls_pax_testuser; COPY copy_rls_pax_main TO
'/tmp/copy_rls_pax_main.csv';
+
+1: BEGIN;
+1: ALTER TABLE copy_rls_pax_lookup SET WITH (reorganize=true);
+
+2&: SET ROLE copy_rls_pax_testuser; COPY copy_rls_pax_main TO
'/tmp/copy_rls_pax_main.csv';
+
+1: SELECT count(*) > 0 FROM pg_stat_activity
+ WHERE query LIKE '%COPY copy_rls_pax_main%' AND wait_event_type = 'Lock';
+
+1: COMMIT;
+2<:
+
+-- Reset session 2's role to avoid leaking to subsequent tests
+2: RESET ROLE;
+
+RESET ROLE;
+CREATE TABLE copy_rls_pax_verify (a INT, category INT) DISTRIBUTED BY (a);
+COPY copy_rls_pax_verify FROM '/tmp/copy_rls_pax_main.csv';
+SELECT count(*) FROM copy_rls_pax_verify;
+
+DROP TABLE copy_rls_pax_verify;
+DROP POLICY p_rls_pax ON copy_rls_pax_main;
+DROP TABLE copy_rls_pax_main;
+DROP TABLE copy_rls_pax_lookup;
+DROP ROLE copy_rls_pax_testuser;
+
+-- ============================================================
+-- Test 2.5c: PAX — CTAS + concurrent reorganize
+-- Fixed as a side effect via BeginCopy() snapshot refresh.
+-- ============================================================
+
+CREATE TABLE ctas_reorg_pax_src (a INT, b INT) DISTRIBUTED BY (a);
+INSERT INTO ctas_reorg_pax_src SELECT i, i FROM generate_series(1, 1000) i;
+
+SELECT count(*) FROM ctas_reorg_pax_src;
+
+1: BEGIN;
+1: ALTER TABLE ctas_reorg_pax_src SET WITH (reorganize=true);
+
+2&: CREATE TABLE ctas_reorg_pax_dst AS SELECT * FROM ctas_reorg_pax_src
DISTRIBUTED BY (a);
+
+1: SELECT count(*) > 0 FROM pg_stat_activity
+ WHERE query LIKE 'CREATE TABLE ctas_reorg_pax_dst%' AND wait_event_type =
'Lock';
+
+1: COMMIT;
+2<:
+
+SELECT count(*) FROM ctas_reorg_pax_dst;
+
+DROP TABLE ctas_reorg_pax_dst;
+DROP TABLE ctas_reorg_pax_src;
+
+-- NOTE: Test 2.6c (PAX variant of change distribution key + query-based COPY
TO)
+-- removed for the same reason as test 2.6 (server crash, pre-existing bug).
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 4ccd3798067..c9d2ac4f968 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -58,6 +58,7 @@
#include "catalog/catalog.h"
#include "catalog/gp_matview_aux.h"
#include "catalog/namespace.h"
+#include "catalog/pg_inherits.h"
#include "catalog/pg_extprotocol.h"
#include "cdb/cdbappendonlyam.h"
#include "cdb/cdbaocsam.h"
@@ -136,6 +137,37 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
{
/* Open and lock the relation, using the appropriate lock type.
*/
rel = table_openrv(stmt->relation, lockmode);
+
+ /*
+ * For COPY TO, refresh the active snapshot after acquiring the
lock.
+ *
+ * The snapshot was originally pushed by PortalRunUtility()
before
+ * DoCopy() was called, which means it was taken before we
acquired
+ * the lock on the relation. If we had to wait for a
conflicting lock
+ * (e.g., AccessExclusiveLock held by a concurrent ALTER TABLE
...
+ * SET WITH (reorganize=true)), the snapshot may predate the
+ * concurrent transaction's commit. After the lock is granted,
scanning
+ * with such a stale snapshot would miss all tuples written by
the
+ * concurrent transaction, resulting in COPY returning zero
rows.
+ *
+ * This mirrors the approach used by exec_simple_query() for
SELECT
+ * statements, which pops the parse/analyze snapshot and takes
a fresh
+ * one in PortalStart() after locks have been acquired (see the
comment
+ * at postgres.c:1859-1867). It is also consistent with how
VACUUM and
+ * CLUSTER manage their own snapshots internally.
+ *
+ * In REPEATABLE READ or SERIALIZABLE mode,
GetTransactionSnapshot()
+ * returns the same transaction-level snapshot regardless,
making this
+ * a harmless no-op.
+ *
+ * We only do this for COPY TO (!is_from) because COPY FROM
inserts
+ * data and does not scan existing tuples with a snapshot.
+ */
+ if (!is_from && ActiveSnapshotSet())
+ {
+ PopActiveSnapshot();
+ PushActiveSnapshot(GetTransactionSnapshot());
+ }
}
/*
@@ -272,6 +304,55 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
errmsg("COPY FROM not
supported with row-level security"),
errhint("Use INSERT statements
instead.")));
+ /*
+ * For partitioned table COPY TO: eagerly acquire
AccessShareLock
+ * on all child partitions before refreshing the
snapshot.
+ *
+ * When COPY is performed on a partitioned table, the
parent
+ * relation's AccessShareLock is acquired above (via
table_openrv)
+ * and Method A already refreshed the snapshot.
However, the
+ * parent's AccessShareLock does NOT conflict with an
+ * AccessExclusiveLock held on a child partition by a
concurrent
+ * reorganize. As a result, Method A's snapshot may
still predate
+ * the child's reorganize commit.
+ *
+ * Child partition locks are acquired later, deep inside
+ * ExecutorStart() via ExecInitAppend(), by which time
the snapshot
+ * has already been embedded in the QueryDesc via
+ * PushCopiedSnapshot() in BeginCopy(). Even a second
snapshot
+ * refresh in BeginCopy() (after AcquireRewriteLocks)
would not
+ * help, because AcquireRewriteLocks only locks the
parent (child
+ * partitions are not in the initial range table of
+ * "SELECT * FROM parent").
+ *
+ * The fix: call find_all_inheritors() with
AccessShareLock to
+ * acquire locks on every child partition NOW, before
building the
+ * query. If a child partition's reorganize holds
+ * AccessExclusiveLock, this call blocks until that
transaction
+ * commits. Once it returns, all child-level
reorganize operations
+ * have committed, and a fresh snapshot taken here will
see all
+ * reorganized child data.
+ *
+ * find_all_inheritors() acquires locks that persist to
end of
+ * transaction. The executor will re-acquire them
during scan
+ * initialization, which is a lock-manager no-op.
+ */
+ if (!is_from && rel->rd_rel->relkind ==
RELKIND_PARTITIONED_TABLE)
+ {
+ List *part_oids;
+
+ part_oids =
find_all_inheritors(RelationGetRelid(rel),
+
AccessShareLock, NULL);
+ list_free(part_oids);
+
+ /* Refresh snapshot: all child partition locks
now held */
+ if (ActiveSnapshotSet())
+ {
+ PopActiveSnapshot();
+
PushActiveSnapshot(GetTransactionSnapshot());
+ }
+ }
+
/*
* Build target list
*
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 871a973235e..88e61305250 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -1198,6 +1198,43 @@ BeginCopy(ParseState *pstate,
Assert(query->utilityStmt == NULL);
+ /*
+ * Refresh the active snapshot after pg_analyze_and_rewrite()
has
+ * acquired all necessary relation locks via
AcquireRewriteLocks().
+ *
+ * The snapshot in use was pushed by PortalRunUtility() before
DoCopy()
+ * was called -- before any table locks were acquired. If
+ * AcquireRewriteLocks() had to wait for a conflicting
+ * AccessExclusiveLock (e.g., held by a concurrent ALTER TABLE
...
+ * SET WITH (reorganize=true)), the lock wait is now over and
the
+ * reorganize transaction has committed. The snapshot taken
before the
+ * wait does not reflect that commit: after reorganize
completes,
+ * swap_relation_files() has replaced the physical storage, so
old
+ * tuples no longer exist and the new tuples have xmin =
reorganize_xid
+ * which is not yet visible in the pre-wait snapshot. Scanning
with
+ * the stale snapshot returns 0 rows -- a violation of
transaction
+ * atomicity (the reader must see either all old rows or all
new rows).
+ *
+ * By refreshing the snapshot here -- after all locks are
acquired --
+ * we guarantee that the query will see the committed
post-reorganize
+ * data.
+ *
+ * This applies to:
+ * - Pure query-based COPY TO: COPY (SELECT ...) TO
+ * - RLS table COPY TO: converted to query-based in DoCopy();
the
+ * RLS policy references an external lookup table whose
lock is
+ * acquired by AcquireRewriteLocks().
+ *
+ * In REPEATABLE READ or SERIALIZABLE isolation,
+ * GetTransactionSnapshot() returns the same transaction-level
+ * snapshot, making this a harmless no-op.
+ */
+ if (ActiveSnapshotSet())
+ {
+ PopActiveSnapshot();
+ PushActiveSnapshot(GetTransactionSnapshot());
+ }
+
/*
* Similarly the grammar doesn't enforce the presence of a
RETURNING
* clause, but this is required here.
diff --git a/src/test/isolation2/expected/copy_to_concurrent_reorganize.out
b/src/test/isolation2/expected/copy_to_concurrent_reorganize.out
new file mode 100644
index 00000000000..0a7dfd38801
--- /dev/null
+++ b/src/test/isolation2/expected/copy_to_concurrent_reorganize.out
@@ -0,0 +1,918 @@
+-- Test: COPY TO concurrent with ALTER TABLE SET WITH (reorganize=true)
+-- Issue: https://github.com/apache/cloudberry/issues/1545
+--
+-- Tests 2.1: Core fix (relation-based COPY TO)
+-- Tests 2.2-2.5: Extended fixes for query-based, partitioned, RLS, and CTAS
paths
+
+-- ============================================================
+-- Test 2.1: relation-based COPY TO + concurrent reorganize
+-- Reproduces issue #1545: COPY TO should return correct row count
+-- after waiting for reorganize to release AccessExclusiveLock.
+-- ============================================================
+
+CREATE TABLE copy_reorg_test (a INT, b INT) DISTRIBUTED BY (a);
+CREATE
+INSERT INTO copy_reorg_test SELECT i, i FROM generate_series(1, 1000) i;
+INSERT 1000
+
+-- Record original row count
+SELECT count(*) FROM copy_reorg_test;
+ count
+-------
+ 1000
+(1 row)
+
+-- Session 1: Begin reorganize (holds AccessExclusiveLock)
+1: BEGIN;
+BEGIN
+1: ALTER TABLE copy_reorg_test SET WITH (reorganize=true);
+ALTER
+
+-- Session 2: relation-based COPY TO should block on AccessShareLock
+-- At this point PortalRunUtility has already acquired a snapshot (before
reorganize commits),
+-- then DoCopy tries to acquire the lock and blocks.
+2&: COPY copy_reorg_test TO '/tmp/copy_reorg_test.csv'; <waiting ...>
+
+-- Confirm Session 2 is waiting for the lock
+1: SELECT count(*) > 0 FROM pg_stat_activity WHERE query LIKE 'COPY
copy_reorg_test%' AND wait_event_type = 'Lock';
+ ?column?
+----------
+ t
+(1 row)
+
+-- Session 1: Commit reorganize, releasing AccessExclusiveLock
+1: COMMIT;
+COMMIT
+
+-- Session 2: Should return 1000 rows (fixed), not 0 rows (broken)
+2<: <... completed>
+COPY 1000
+
+-- Verify the output file contains all rows
+CREATE TABLE copy_reorg_verify (a INT, b INT) DISTRIBUTED BY (a);
+CREATE
+COPY copy_reorg_verify FROM '/tmp/copy_reorg_test.csv';
+COPY 1000
+SELECT count(*) FROM copy_reorg_verify;
+ count
+-------
+ 1000
+(1 row)
+
+-- Cleanup
+DROP TABLE copy_reorg_verify;
+DROP
+DROP TABLE copy_reorg_test;
+DROP
+
+-- ============================================================
+-- Test 2.2: query-based COPY TO + concurrent reorganize
+-- Fixed: BeginCopy() refreshes snapshot after pg_analyze_and_rewrite()
+-- acquires all relation locks via AcquireRewriteLocks().
+-- ============================================================
+
+CREATE TABLE copy_query_reorg_test (a INT, b INT) DISTRIBUTED BY (a);
+CREATE
+INSERT INTO copy_query_reorg_test SELECT i, i FROM generate_series(1, 1000) i;
+INSERT 1000
+
+SELECT count(*) FROM copy_query_reorg_test;
+ count
+-------
+ 1000
+(1 row)
+
+-- Session 1: reorganize holds AccessExclusiveLock
+1: BEGIN;
+BEGIN
+1: ALTER TABLE copy_query_reorg_test SET WITH (reorganize=true);
+ALTER
+
+-- Session 2: query-based COPY TO blocks (lock acquired in
pg_analyze_and_rewrite -> AcquireRewriteLocks)
+2&: COPY (SELECT * FROM copy_query_reorg_test) TO
'/tmp/copy_query_reorg_test.csv'; <waiting ...>
+
+-- Confirm Session 2 is blocked
+1: SELECT count(*) > 0 FROM pg_stat_activity WHERE query LIKE 'COPY
(SELECT%copy_query_reorg_test%' AND wait_event_type = 'Lock';
+ ?column?
+----------
+ t
+(1 row)
+
+-- Session 1: Commit
+1: COMMIT;
+COMMIT
+
+-- Session 2: Complete
+2<: <... completed>
+COPY 1000
+
+-- Verify the output file contains all rows
+CREATE TABLE copy_query_reorg_verify (a INT, b INT) DISTRIBUTED BY (a);
+CREATE
+COPY copy_query_reorg_verify FROM '/tmp/copy_query_reorg_test.csv';
+COPY 1000
+SELECT count(*) FROM copy_query_reorg_verify;
+ count
+-------
+ 1000
+(1 row)
+
+-- Cleanup
+DROP TABLE copy_query_reorg_verify;
+DROP
+DROP TABLE copy_query_reorg_test;
+DROP
+
+-- ============================================================
+-- Test 2.3: partitioned table COPY TO + child partition concurrent reorganize
+-- Fixed: DoCopy() calls find_all_inheritors() to eagerly lock all child
+-- partitions before refreshing the snapshot, ensuring the snapshot sees all
+-- child reorganize commits before the query is built.
+-- ============================================================
+
+CREATE TABLE copy_part_parent (a INT, b INT) PARTITION BY RANGE (a)
DISTRIBUTED BY (a);
+CREATE
+CREATE TABLE copy_part_child1 PARTITION OF copy_part_parent FOR VALUES FROM
(1) TO (501);
+CREATE
+CREATE TABLE copy_part_child2 PARTITION OF copy_part_parent FOR VALUES FROM
(501) TO (1001);
+CREATE
+INSERT INTO copy_part_parent SELECT i, i FROM generate_series(1, 1000) i;
+INSERT 1000
+
+SELECT count(*) FROM copy_part_parent;
+ count
+-------
+ 1000
+(1 row)
+
+-- Session 1: reorganize the child partition
+1: BEGIN;
+BEGIN
+1: ALTER TABLE copy_part_child1 SET WITH (reorganize=true);
+ALTER
+
+-- Session 2: COPY parent TO (internally converted to query-based, child lock
acquired in analyze phase)
+2&: COPY copy_part_parent TO '/tmp/copy_part_parent.csv'; <waiting ...>
+
+-- Confirm Session 2 is blocked
+1: SELECT count(*) > 0 FROM pg_stat_activity WHERE query LIKE 'COPY
copy_part_parent%' AND wait_event_type = 'Lock';
+ ?column?
+----------
+ t
+(1 row)
+
+-- Session 1: Commit
+1: COMMIT;
+COMMIT
+
+-- Session 2: Complete
+2<: <... completed>
+COPY 1000
+
+-- Verify the output file contains all rows
+CREATE TABLE copy_part_verify (a INT, b INT) DISTRIBUTED BY (a);
+CREATE
+COPY copy_part_verify FROM '/tmp/copy_part_parent.csv';
+COPY 1000
+SELECT count(*) FROM copy_part_verify;
+ count
+-------
+ 1000
+(1 row)
+
+-- Cleanup
+DROP TABLE copy_part_verify;
+DROP
+DROP TABLE copy_part_parent;
+DROP
+
+-- ============================================================
+-- Test 2.4: RLS table COPY TO + policy-referenced table concurrent reorganize
+-- Fixed: same as 2.2 — BeginCopy() refreshes snapshot after
AcquireRewriteLocks()
+-- which also acquires the lock on the RLS policy's lookup table.
+-- ============================================================
+
+CREATE TABLE copy_rls_lookup (cat INT) DISTRIBUTED BY (cat);
+CREATE
+INSERT INTO copy_rls_lookup SELECT i FROM generate_series(1, 2) i;
+INSERT 2
+
+CREATE TABLE copy_rls_main (a INT, category INT) DISTRIBUTED BY (a);
+CREATE
+INSERT INTO copy_rls_main SELECT i, (i % 5) + 1 FROM generate_series(1, 1000)
i;
+INSERT 1000
+
+ALTER TABLE copy_rls_main ENABLE ROW LEVEL SECURITY;
+ALTER
+CREATE POLICY p_rls ON copy_rls_main USING (category IN (SELECT cat FROM
copy_rls_lookup));
+CREATE
+
+-- Create non-superuser to trigger RLS (needs pg_write_server_files to COPY TO
file)
+CREATE ROLE copy_rls_testuser;
+CREATE
+GRANT pg_write_server_files TO copy_rls_testuser;
+GRANT
+GRANT ALL ON copy_rls_main TO copy_rls_testuser;
+GRANT
+GRANT ALL ON copy_rls_lookup TO copy_rls_testuser;
+GRANT
+
+SELECT count(*) FROM copy_rls_main;
+ count
+-------
+ 1000
+(1 row)
+
+-- Baseline: verify RLS filters correctly (should return 400 rows: categories
1 and 2 only)
+2: SET ROLE copy_rls_testuser; COPY copy_rls_main TO '/tmp/copy_rls_main.csv';
+SET 400
+
+-- Session 1: reorganize the lookup table
+1: BEGIN;
+BEGIN
+1: ALTER TABLE copy_rls_lookup SET WITH (reorganize=true);
+ALTER
+
+-- Session 2: COPY TO as non-superuser (RLS active, internally converted to
query-based)
+2&: SET ROLE copy_rls_testuser; COPY copy_rls_main TO
'/tmp/copy_rls_main.csv'; <waiting ...>
+
+-- Confirm Session 2 is blocked
+1: SELECT count(*) > 0 FROM pg_stat_activity WHERE query LIKE '%COPY
copy_rls_main%' AND wait_event_type = 'Lock';
+ ?column?
+----------
+ t
+(1 row)
+
+-- Session 1: Commit
+1: COMMIT;
+COMMIT
+
+-- Session 2: Complete
+2<: <... completed>
+SET 400
+
+-- Reset session 2's role to avoid leaking to subsequent tests
+2: RESET ROLE;
+RESET
+
+-- Verify: should match baseline count (400 rows filtered by RLS)
+RESET ROLE;
+RESET
+CREATE TABLE copy_rls_verify (a INT, category INT) DISTRIBUTED BY (a);
+CREATE
+COPY copy_rls_verify FROM '/tmp/copy_rls_main.csv';
+COPY 400
+SELECT count(*) FROM copy_rls_verify;
+ count
+-------
+ 400
+(1 row)
+
+-- Cleanup
+DROP TABLE copy_rls_verify;
+DROP
+DROP POLICY p_rls ON copy_rls_main;
+DROP
+DROP TABLE copy_rls_main;
+DROP
+DROP TABLE copy_rls_lookup;
+DROP
+DROP ROLE copy_rls_testuser;
+DROP
+
+-- ============================================================
+-- Test 2.5: CTAS + concurrent reorganize
+-- Fixed as a side effect: CTAS goes through pg_analyze_and_rewrite() +
+-- AcquireRewriteLocks(), so the snapshot refresh in BeginCopy() also fixes it.
+-- ============================================================
+
+CREATE TABLE ctas_reorg_src (a INT, b INT) DISTRIBUTED BY (a);
+CREATE
+INSERT INTO ctas_reorg_src SELECT i, i FROM generate_series(1, 1000) i;
+INSERT 1000
+
+SELECT count(*) FROM ctas_reorg_src;
+ count
+-------
+ 1000
+(1 row)
+
+-- Session 1: reorganize
+1: BEGIN;
+BEGIN
+1: ALTER TABLE ctas_reorg_src SET WITH (reorganize=true);
+ALTER
+
+-- Session 2: CTAS should block (lock acquired in executor or analyze phase)
+2&: CREATE TABLE ctas_reorg_dst AS SELECT * FROM ctas_reorg_src DISTRIBUTED BY
(a); <waiting ...>
+
+-- Confirm Session 2 is blocked
+1: SELECT count(*) > 0 FROM pg_stat_activity WHERE query LIKE 'CREATE TABLE
ctas_reorg_dst%' AND wait_event_type = 'Lock';
+ ?column?
+----------
+ t
+(1 row)
+
+-- Session 1: Commit
+1: COMMIT;
+COMMIT
+
+-- Session 2: Complete
+2<: <... completed>
+CREATE 1000
+
+-- Verify row count after CTAS completes
+SELECT count(*) FROM ctas_reorg_dst;
+ count
+-------
+ 1000
+(1 row)
+
+-- Cleanup
+DROP TABLE ctas_reorg_dst;
+DROP
+DROP TABLE ctas_reorg_src;
+DROP
+
+-- NOTE: Test 2.6 (change distribution key + query-based COPY TO) removed
because
+-- ALTER TABLE SET DISTRIBUTED BY + concurrent query-based COPY TO causes a
server
+-- crash (pre-existing Cloudberry bug, not related to this fix).
+
+-- ============================================================
+-- Test 2.1a: AO row table — relation-based COPY TO + concurrent reorganize
+-- Same as 2.1 but using append-optimized row-oriented table.
+-- ============================================================
+
+CREATE TABLE copy_reorg_ao_row_test (a INT, b INT) USING ao_row DISTRIBUTED BY
(a);
+CREATE
+INSERT INTO copy_reorg_ao_row_test SELECT i, i FROM generate_series(1, 1000) i;
+INSERT 1000
+
+-- Record original row count
+SELECT count(*) FROM copy_reorg_ao_row_test;
+ count
+-------
+ 1000
+(1 row)
+
+-- Session 1: Begin reorganize (holds AccessExclusiveLock)
+1: BEGIN;
+BEGIN
+1: ALTER TABLE copy_reorg_ao_row_test SET WITH (reorganize=true);
+ALTER
+
+-- Session 2: relation-based COPY TO should block on AccessShareLock
+2&: COPY copy_reorg_ao_row_test TO '/tmp/copy_reorg_ao_row_test.csv';
<waiting ...>
+
+-- Confirm Session 2 is waiting for the lock
+1: SELECT count(*) > 0 FROM pg_stat_activity WHERE query LIKE 'COPY
copy_reorg_ao_row_test%' AND wait_event_type = 'Lock';
+ ?column?
+----------
+ t
+(1 row)
+
+-- Session 1: Commit reorganize, releasing AccessExclusiveLock
+1: COMMIT;
+COMMIT
+
+-- Session 2: Should return 1000 rows (fixed), not 0 rows (broken)
+2<: <... completed>
+COPY 1000
+
+-- Verify the output file contains all rows
+CREATE TABLE copy_reorg_ao_row_verify (a INT, b INT) USING ao_row DISTRIBUTED
BY (a);
+CREATE
+COPY copy_reorg_ao_row_verify FROM '/tmp/copy_reorg_ao_row_test.csv';
+COPY 1000
+SELECT count(*) FROM copy_reorg_ao_row_verify;
+ count
+-------
+ 1000
+(1 row)
+
+-- Cleanup
+DROP TABLE copy_reorg_ao_row_verify;
+DROP
+DROP TABLE copy_reorg_ao_row_test;
+DROP
+
+-- ============================================================
+-- Test 2.1b: AO column table — relation-based COPY TO + concurrent reorganize
+-- Same as 2.1 but using append-optimized column-oriented table.
+-- ============================================================
+
+CREATE TABLE copy_reorg_ao_col_test (a INT, b INT) USING ao_column DISTRIBUTED
BY (a);
+CREATE
+INSERT INTO copy_reorg_ao_col_test SELECT i, i FROM generate_series(1, 1000) i;
+INSERT 1000
+
+-- Record original row count
+SELECT count(*) FROM copy_reorg_ao_col_test;
+ count
+-------
+ 1000
+(1 row)
+
+-- Session 1: Begin reorganize (holds AccessExclusiveLock)
+1: BEGIN;
+BEGIN
+1: ALTER TABLE copy_reorg_ao_col_test SET WITH (reorganize=true);
+ALTER
+
+-- Session 2: relation-based COPY TO should block on AccessShareLock
+2&: COPY copy_reorg_ao_col_test TO '/tmp/copy_reorg_ao_col_test.csv';
<waiting ...>
+
+-- Confirm Session 2 is waiting for the lock
+1: SELECT count(*) > 0 FROM pg_stat_activity WHERE query LIKE 'COPY
copy_reorg_ao_col_test%' AND wait_event_type = 'Lock';
+ ?column?
+----------
+ t
+(1 row)
+
+-- Session 1: Commit reorganize, releasing AccessExclusiveLock
+1: COMMIT;
+COMMIT
+
+-- Session 2: Should return 1000 rows (fixed), not 0 rows (broken)
+2<: <... completed>
+COPY 1000
+
+-- Verify the output file contains all rows
+CREATE TABLE copy_reorg_ao_col_verify (a INT, b INT) USING ao_column
DISTRIBUTED BY (a);
+CREATE
+COPY copy_reorg_ao_col_verify FROM '/tmp/copy_reorg_ao_col_test.csv';
+COPY 1000
+SELECT count(*) FROM copy_reorg_ao_col_verify;
+ count
+-------
+ 1000
+(1 row)
+
+-- Cleanup
+DROP TABLE copy_reorg_ao_col_verify;
+DROP
+DROP TABLE copy_reorg_ao_col_test;
+DROP
+
+-- ============================================================
+-- Test 2.2a: AO row — query-based COPY TO + concurrent reorganize
+-- Fixed: BeginCopy() refreshes snapshot after AcquireRewriteLocks().
+-- ============================================================
+
+CREATE TABLE copy_query_reorg_ao_row_test (a INT, b INT) USING ao_row
DISTRIBUTED BY (a);
+CREATE
+INSERT INTO copy_query_reorg_ao_row_test SELECT i, i FROM generate_series(1,
1000) i;
+INSERT 1000
+
+SELECT count(*) FROM copy_query_reorg_ao_row_test;
+ count
+-------
+ 1000
+(1 row)
+
+1: BEGIN;
+BEGIN
+1: ALTER TABLE copy_query_reorg_ao_row_test SET WITH (reorganize=true);
+ALTER
+
+2&: COPY (SELECT * FROM copy_query_reorg_ao_row_test) TO
'/tmp/copy_query_reorg_ao_row_test.csv'; <waiting ...>
+
+1: SELECT count(*) > 0 FROM pg_stat_activity WHERE query LIKE 'COPY
(SELECT%copy_query_reorg_ao_row_test%' AND wait_event_type = 'Lock';
+ ?column?
+----------
+ t
+(1 row)
+
+1: COMMIT;
+COMMIT
+2<: <... completed>
+COPY 1000
+
+CREATE TABLE copy_query_reorg_ao_row_verify (a INT, b INT) USING ao_row
DISTRIBUTED BY (a);
+CREATE
+COPY copy_query_reorg_ao_row_verify FROM
'/tmp/copy_query_reorg_ao_row_test.csv';
+COPY 1000
+SELECT count(*) FROM copy_query_reorg_ao_row_verify;
+ count
+-------
+ 1000
+(1 row)
+
+DROP TABLE copy_query_reorg_ao_row_verify;
+DROP
+DROP TABLE copy_query_reorg_ao_row_test;
+DROP
+
+-- ============================================================
+-- Test 2.2b: AO column — query-based COPY TO + concurrent reorganize
+-- Fixed: BeginCopy() refreshes snapshot after AcquireRewriteLocks().
+-- ============================================================
+
+CREATE TABLE copy_query_reorg_ao_col_test (a INT, b INT) USING ao_column
DISTRIBUTED BY (a);
+CREATE
+INSERT INTO copy_query_reorg_ao_col_test SELECT i, i FROM generate_series(1,
1000) i;
+INSERT 1000
+
+SELECT count(*) FROM copy_query_reorg_ao_col_test;
+ count
+-------
+ 1000
+(1 row)
+
+1: BEGIN;
+BEGIN
+1: ALTER TABLE copy_query_reorg_ao_col_test SET WITH (reorganize=true);
+ALTER
+
+2&: COPY (SELECT * FROM copy_query_reorg_ao_col_test) TO
'/tmp/copy_query_reorg_ao_col_test.csv'; <waiting ...>
+
+1: SELECT count(*) > 0 FROM pg_stat_activity WHERE query LIKE 'COPY
(SELECT%copy_query_reorg_ao_col_test%' AND wait_event_type = 'Lock';
+ ?column?
+----------
+ t
+(1 row)
+
+1: COMMIT;
+COMMIT
+2<: <... completed>
+COPY 1000
+
+CREATE TABLE copy_query_reorg_ao_col_verify (a INT, b INT) USING ao_column
DISTRIBUTED BY (a);
+CREATE
+COPY copy_query_reorg_ao_col_verify FROM
'/tmp/copy_query_reorg_ao_col_test.csv';
+COPY 1000
+SELECT count(*) FROM copy_query_reorg_ao_col_verify;
+ count
+-------
+ 1000
+(1 row)
+
+DROP TABLE copy_query_reorg_ao_col_verify;
+DROP
+DROP TABLE copy_query_reorg_ao_col_test;
+DROP
+
+-- ============================================================
+-- Test 2.3a: AO row — partitioned table COPY TO + child partition concurrent
reorganize
+-- Fixed: DoCopy() calls find_all_inheritors() to lock all child partitions
first.
+-- ============================================================
+
+CREATE TABLE copy_part_parent_ao_row (a INT, b INT) PARTITION BY RANGE (a)
DISTRIBUTED BY (a);
+CREATE
+CREATE TABLE copy_part_child1_ao_row PARTITION OF copy_part_parent_ao_row FOR
VALUES FROM (1) TO (501) USING ao_row;
+CREATE
+CREATE TABLE copy_part_child2_ao_row PARTITION OF copy_part_parent_ao_row FOR
VALUES FROM (501) TO (1001) USING ao_row;
+CREATE
+INSERT INTO copy_part_parent_ao_row SELECT i, i FROM generate_series(1, 1000)
i;
+INSERT 1000
+
+SELECT count(*) FROM copy_part_parent_ao_row;
+ count
+-------
+ 1000
+(1 row)
+
+1: BEGIN;
+BEGIN
+1: ALTER TABLE copy_part_child1_ao_row SET WITH (reorganize=true);
+ALTER
+
+2&: COPY copy_part_parent_ao_row TO '/tmp/copy_part_parent_ao_row.csv';
<waiting ...>
+
+1: SELECT count(*) > 0 FROM pg_stat_activity WHERE query LIKE 'COPY
copy_part_parent_ao_row%' AND wait_event_type = 'Lock';
+ ?column?
+----------
+ t
+(1 row)
+
+1: COMMIT;
+COMMIT
+2<: <... completed>
+COPY 1000
+
+CREATE TABLE copy_part_ao_row_verify (a INT, b INT) USING ao_row DISTRIBUTED
BY (a);
+CREATE
+COPY copy_part_ao_row_verify FROM '/tmp/copy_part_parent_ao_row.csv';
+COPY 1000
+SELECT count(*) FROM copy_part_ao_row_verify;
+ count
+-------
+ 1000
+(1 row)
+
+DROP TABLE copy_part_ao_row_verify;
+DROP
+DROP TABLE copy_part_parent_ao_row;
+DROP
+
+-- ============================================================
+-- Test 2.3b: AO column — partitioned table COPY TO + child partition
concurrent reorganize
+-- Fixed: DoCopy() calls find_all_inheritors() to lock all child partitions
first.
+-- ============================================================
+
+CREATE TABLE copy_part_parent_ao_col (a INT, b INT) PARTITION BY RANGE (a)
DISTRIBUTED BY (a);
+CREATE
+CREATE TABLE copy_part_child1_ao_col PARTITION OF copy_part_parent_ao_col FOR
VALUES FROM (1) TO (501) USING ao_column;
+CREATE
+CREATE TABLE copy_part_child2_ao_col PARTITION OF copy_part_parent_ao_col FOR
VALUES FROM (501) TO (1001) USING ao_column;
+CREATE
+INSERT INTO copy_part_parent_ao_col SELECT i, i FROM generate_series(1, 1000)
i;
+INSERT 1000
+
+SELECT count(*) FROM copy_part_parent_ao_col;
+ count
+-------
+ 1000
+(1 row)
+
+1: BEGIN;
+BEGIN
+1: ALTER TABLE copy_part_child1_ao_col SET WITH (reorganize=true);
+ALTER
+
+2&: COPY copy_part_parent_ao_col TO '/tmp/copy_part_parent_ao_col.csv';
<waiting ...>
+
+1: SELECT count(*) > 0 FROM pg_stat_activity WHERE query LIKE 'COPY
copy_part_parent_ao_col%' AND wait_event_type = 'Lock';
+ ?column?
+----------
+ t
+(1 row)
+
+1: COMMIT;
+COMMIT
+2<: <... completed>
+COPY 1000
+
+CREATE TABLE copy_part_ao_col_verify (a INT, b INT) USING ao_column
DISTRIBUTED BY (a);
+CREATE
+COPY copy_part_ao_col_verify FROM '/tmp/copy_part_parent_ao_col.csv';
+COPY 1000
+SELECT count(*) FROM copy_part_ao_col_verify;
+ count
+-------
+ 1000
+(1 row)
+
+DROP TABLE copy_part_ao_col_verify;
+DROP
+DROP TABLE copy_part_parent_ao_col;
+DROP
+
+-- ============================================================
+-- Test 2.4a: AO row — RLS table COPY TO + policy-referenced table concurrent
reorganize
+-- Fixed: same as 2.4 — BeginCopy() refreshes snapshot after
AcquireRewriteLocks().
+-- ============================================================
+
+CREATE TABLE copy_rls_ao_row_lookup (cat INT) USING ao_row DISTRIBUTED BY
(cat);
+CREATE
+INSERT INTO copy_rls_ao_row_lookup SELECT i FROM generate_series(1, 2) i;
+INSERT 2
+
+CREATE TABLE copy_rls_ao_row_main (a INT, category INT) USING ao_row
DISTRIBUTED BY (a);
+CREATE
+INSERT INTO copy_rls_ao_row_main SELECT i, (i % 5) + 1 FROM generate_series(1,
1000) i;
+INSERT 1000
+
+ALTER TABLE copy_rls_ao_row_main ENABLE ROW LEVEL SECURITY;
+ALTER
+CREATE POLICY p_rls_ao_row ON copy_rls_ao_row_main USING (category IN (SELECT
cat FROM copy_rls_ao_row_lookup));
+CREATE
+
+CREATE ROLE copy_rls_ao_row_testuser;
+CREATE
+GRANT pg_write_server_files TO copy_rls_ao_row_testuser;
+GRANT
+GRANT ALL ON copy_rls_ao_row_main TO copy_rls_ao_row_testuser;
+GRANT
+GRANT ALL ON copy_rls_ao_row_lookup TO copy_rls_ao_row_testuser;
+GRANT
+
+SELECT count(*) FROM copy_rls_ao_row_main;
+ count
+-------
+ 1000
+(1 row)
+
+-- Baseline: verify RLS filters correctly (should return 400 rows: categories
1 and 2 only)
+2: SET ROLE copy_rls_ao_row_testuser; COPY copy_rls_ao_row_main TO
'/tmp/copy_rls_ao_row_main.csv';
+SET 400
+
+1: BEGIN;
+BEGIN
+1: ALTER TABLE copy_rls_ao_row_lookup SET WITH (reorganize=true);
+ALTER
+
+2&: SET ROLE copy_rls_ao_row_testuser; COPY copy_rls_ao_row_main TO
'/tmp/copy_rls_ao_row_main.csv'; <waiting ...>
+
+1: SELECT count(*) > 0 FROM pg_stat_activity WHERE query LIKE '%COPY
copy_rls_ao_row_main%' AND wait_event_type = 'Lock';
+ ?column?
+----------
+ t
+(1 row)
+
+1: COMMIT;
+COMMIT
+2<: <... completed>
+SET 400
+
+2: RESET ROLE;
+RESET
+
+RESET ROLE;
+RESET
+CREATE TABLE copy_rls_ao_row_verify (a INT, category INT) USING ao_row
DISTRIBUTED BY (a);
+CREATE
+COPY copy_rls_ao_row_verify FROM '/tmp/copy_rls_ao_row_main.csv';
+COPY 400
+SELECT count(*) FROM copy_rls_ao_row_verify;
+ count
+-------
+ 400
+(1 row)
+
+DROP TABLE copy_rls_ao_row_verify;
+DROP
+DROP POLICY p_rls_ao_row ON copy_rls_ao_row_main;
+DROP
+DROP TABLE copy_rls_ao_row_main;
+DROP
+DROP TABLE copy_rls_ao_row_lookup;
+DROP
+DROP ROLE copy_rls_ao_row_testuser;
+DROP
+
+-- ============================================================
+-- Test 2.4b: AO column — RLS table COPY TO + policy-referenced table
concurrent reorganize
+-- Fixed: same as 2.4 — BeginCopy() refreshes snapshot after
AcquireRewriteLocks().
+-- ============================================================
+
+CREATE TABLE copy_rls_ao_col_lookup (cat INT) USING ao_column DISTRIBUTED BY
(cat);
+CREATE
+INSERT INTO copy_rls_ao_col_lookup SELECT i FROM generate_series(1, 2) i;
+INSERT 2
+
+CREATE TABLE copy_rls_ao_col_main (a INT, category INT) USING ao_column
DISTRIBUTED BY (a);
+CREATE
+INSERT INTO copy_rls_ao_col_main SELECT i, (i % 5) + 1 FROM generate_series(1,
1000) i;
+INSERT 1000
+
+ALTER TABLE copy_rls_ao_col_main ENABLE ROW LEVEL SECURITY;
+ALTER
+CREATE POLICY p_rls_ao_col ON copy_rls_ao_col_main USING (category IN (SELECT
cat FROM copy_rls_ao_col_lookup));
+CREATE
+
+CREATE ROLE copy_rls_ao_col_testuser;
+CREATE
+GRANT pg_write_server_files TO copy_rls_ao_col_testuser;
+GRANT
+GRANT ALL ON copy_rls_ao_col_main TO copy_rls_ao_col_testuser;
+GRANT
+GRANT ALL ON copy_rls_ao_col_lookup TO copy_rls_ao_col_testuser;
+GRANT
+
+SELECT count(*) FROM copy_rls_ao_col_main;
+ count
+-------
+ 1000
+(1 row)
+
+-- Baseline: verify RLS filters correctly (should return 400 rows: categories
1 and 2 only)
+2: SET ROLE copy_rls_ao_col_testuser; COPY copy_rls_ao_col_main TO
'/tmp/copy_rls_ao_col_main.csv';
+SET 400
+
+1: BEGIN;
+BEGIN
+1: ALTER TABLE copy_rls_ao_col_lookup SET WITH (reorganize=true);
+ALTER
+
+2&: SET ROLE copy_rls_ao_col_testuser; COPY copy_rls_ao_col_main TO
'/tmp/copy_rls_ao_col_main.csv'; <waiting ...>
+
+1: SELECT count(*) > 0 FROM pg_stat_activity WHERE query LIKE '%COPY
copy_rls_ao_col_main%' AND wait_event_type = 'Lock';
+ ?column?
+----------
+ t
+(1 row)
+
+1: COMMIT;
+COMMIT
+2<: <... completed>
+SET 400
+
+2: RESET ROLE;
+RESET
+
+RESET ROLE;
+RESET
+CREATE TABLE copy_rls_ao_col_verify (a INT, category INT) USING ao_column
DISTRIBUTED BY (a);
+CREATE
+COPY copy_rls_ao_col_verify FROM '/tmp/copy_rls_ao_col_main.csv';
+COPY 400
+SELECT count(*) FROM copy_rls_ao_col_verify;
+ count
+-------
+ 400
+(1 row)
+
+DROP TABLE copy_rls_ao_col_verify;
+DROP
+DROP POLICY p_rls_ao_col ON copy_rls_ao_col_main;
+DROP
+DROP TABLE copy_rls_ao_col_main;
+DROP
+DROP TABLE copy_rls_ao_col_lookup;
+DROP
+DROP ROLE copy_rls_ao_col_testuser;
+DROP
+
+-- ============================================================
+-- Test 2.5a: AO row — CTAS + concurrent reorganize
+-- Fixed as a side effect via BeginCopy() snapshot refresh.
+-- ============================================================
+
+CREATE TABLE ctas_reorg_ao_row_src (a INT, b INT) USING ao_row DISTRIBUTED BY
(a);
+CREATE
+INSERT INTO ctas_reorg_ao_row_src SELECT i, i FROM generate_series(1, 1000) i;
+INSERT 1000
+
+SELECT count(*) FROM ctas_reorg_ao_row_src;
+ count
+-------
+ 1000
+(1 row)
+
+1: BEGIN;
+BEGIN
+1: ALTER TABLE ctas_reorg_ao_row_src SET WITH (reorganize=true);
+ALTER
+
+2&: CREATE TABLE ctas_reorg_ao_row_dst AS SELECT * FROM ctas_reorg_ao_row_src
DISTRIBUTED BY (a); <waiting ...>
+
+1: SELECT count(*) > 0 FROM pg_stat_activity WHERE query LIKE 'CREATE TABLE
ctas_reorg_ao_row_dst%' AND wait_event_type = 'Lock';
+ ?column?
+----------
+ t
+(1 row)
+
+1: COMMIT;
+COMMIT
+2<: <... completed>
+CREATE 1000
+
+SELECT count(*) FROM ctas_reorg_ao_row_dst;
+ count
+-------
+ 1000
+(1 row)
+
+DROP TABLE ctas_reorg_ao_row_dst;
+DROP
+DROP TABLE ctas_reorg_ao_row_src;
+DROP
+
+-- ============================================================
+-- Test 2.5b: AO column — CTAS + concurrent reorganize
+-- Fixed as a side effect via BeginCopy() snapshot refresh.
+-- ============================================================
+
+CREATE TABLE ctas_reorg_ao_col_src (a INT, b INT) USING ao_column DISTRIBUTED
BY (a);
+CREATE
+INSERT INTO ctas_reorg_ao_col_src SELECT i, i FROM generate_series(1, 1000) i;
+INSERT 1000
+
+SELECT count(*) FROM ctas_reorg_ao_col_src;
+ count
+-------
+ 1000
+(1 row)
+
+1: BEGIN;
+BEGIN
+1: ALTER TABLE ctas_reorg_ao_col_src SET WITH (reorganize=true);
+ALTER
+
+2&: CREATE TABLE ctas_reorg_ao_col_dst AS SELECT * FROM ctas_reorg_ao_col_src
DISTRIBUTED BY (a); <waiting ...>
+
+1: SELECT count(*) > 0 FROM pg_stat_activity WHERE query LIKE 'CREATE TABLE
ctas_reorg_ao_col_dst%' AND wait_event_type = 'Lock';
+ ?column?
+----------
+ t
+(1 row)
+
+1: COMMIT;
+COMMIT
+2<: <... completed>
+CREATE 1000
+
+SELECT count(*) FROM ctas_reorg_ao_col_dst;
+ count
+-------
+ 1000
+(1 row)
+
+DROP TABLE ctas_reorg_ao_col_dst;
+DROP
+DROP TABLE ctas_reorg_ao_col_src;
+DROP
+
+-- NOTE: Tests 2.6a/2.6b (AO variants of change distribution key + query-based
COPY TO)
+-- removed for the same reason as test 2.6 (server crash, pre-existing bug).
diff --git a/src/test/isolation2/isolation2_schedule
b/src/test/isolation2/isolation2_schedule
index d9d33ad76e4..4a0f9dc6925 100644
--- a/src/test/isolation2/isolation2_schedule
+++ b/src/test/isolation2/isolation2_schedule
@@ -152,6 +152,7 @@ test: uao/fast_analyze_row
test: uao/create_index_allows_readonly_row
test: reorganize_after_ao_vacuum_skip_drop truncate_after_ao_vacuum_skip_drop
mark_all_aoseg_await_drop
+test: copy_to_concurrent_reorganize
# below test(s) inject faults so each of them need to be in a separate group
test: segwalrep/master_wal_switch
diff --git a/src/test/isolation2/sql/copy_to_concurrent_reorganize.sql
b/src/test/isolation2/sql/copy_to_concurrent_reorganize.sql
new file mode 100644
index 00000000000..3473193d142
--- /dev/null
+++ b/src/test/isolation2/sql/copy_to_concurrent_reorganize.sql
@@ -0,0 +1,561 @@
+-- Test: COPY TO concurrent with ALTER TABLE SET WITH (reorganize=true)
+-- Issue: https://github.com/apache/cloudberry/issues/1545
+--
+-- Tests 2.1: Core fix (relation-based COPY TO)
+-- Tests 2.2-2.5: Extended fixes for query-based, partitioned, RLS, and CTAS
paths
+
+-- ============================================================
+-- Test 2.1: relation-based COPY TO + concurrent reorganize
+-- Reproduces issue #1545: COPY TO should return correct row count
+-- after waiting for reorganize to release AccessExclusiveLock.
+-- ============================================================
+
+CREATE TABLE copy_reorg_test (a INT, b INT) DISTRIBUTED BY (a);
+INSERT INTO copy_reorg_test SELECT i, i FROM generate_series(1, 1000) i;
+
+-- Record original row count
+SELECT count(*) FROM copy_reorg_test;
+
+-- Session 1: Begin reorganize (holds AccessExclusiveLock)
+1: BEGIN;
+1: ALTER TABLE copy_reorg_test SET WITH (reorganize=true);
+
+-- Session 2: relation-based COPY TO should block on AccessShareLock
+-- At this point PortalRunUtility has already acquired a snapshot (before
reorganize commits),
+-- then DoCopy tries to acquire the lock and blocks.
+2&: COPY copy_reorg_test TO '/tmp/copy_reorg_test.csv';
+
+-- Confirm Session 2 is waiting for the lock
+1: SELECT count(*) > 0 FROM pg_stat_activity
+ WHERE query LIKE 'COPY copy_reorg_test%' AND wait_event_type = 'Lock';
+
+-- Session 1: Commit reorganize, releasing AccessExclusiveLock
+1: COMMIT;
+
+-- Session 2: Should return 1000 rows (fixed), not 0 rows (broken)
+2<:
+
+-- Verify the output file contains all rows
+CREATE TABLE copy_reorg_verify (a INT, b INT) DISTRIBUTED BY (a);
+COPY copy_reorg_verify FROM '/tmp/copy_reorg_test.csv';
+SELECT count(*) FROM copy_reorg_verify;
+
+-- Cleanup
+DROP TABLE copy_reorg_verify;
+DROP TABLE copy_reorg_test;
+
+-- ============================================================
+-- Test 2.2: query-based COPY TO + concurrent reorganize
+-- Fixed: BeginCopy() refreshes snapshot after pg_analyze_and_rewrite()
+-- acquires all relation locks via AcquireRewriteLocks().
+-- ============================================================
+
+CREATE TABLE copy_query_reorg_test (a INT, b INT) DISTRIBUTED BY (a);
+INSERT INTO copy_query_reorg_test SELECT i, i FROM generate_series(1, 1000) i;
+
+SELECT count(*) FROM copy_query_reorg_test;
+
+-- Session 1: reorganize holds AccessExclusiveLock
+1: BEGIN;
+1: ALTER TABLE copy_query_reorg_test SET WITH (reorganize=true);
+
+-- Session 2: query-based COPY TO blocks (lock acquired in
pg_analyze_and_rewrite -> AcquireRewriteLocks)
+2&: COPY (SELECT * FROM copy_query_reorg_test) TO
'/tmp/copy_query_reorg_test.csv';
+
+-- Confirm Session 2 is blocked
+1: SELECT count(*) > 0 FROM pg_stat_activity
+ WHERE query LIKE 'COPY (SELECT%copy_query_reorg_test%' AND wait_event_type
= 'Lock';
+
+-- Session 1: Commit
+1: COMMIT;
+
+-- Session 2: Complete
+2<:
+
+-- Verify the output file contains all rows
+CREATE TABLE copy_query_reorg_verify (a INT, b INT) DISTRIBUTED BY (a);
+COPY copy_query_reorg_verify FROM '/tmp/copy_query_reorg_test.csv';
+SELECT count(*) FROM copy_query_reorg_verify;
+
+-- Cleanup
+DROP TABLE copy_query_reorg_verify;
+DROP TABLE copy_query_reorg_test;
+
+-- ============================================================
+-- Test 2.3: partitioned table COPY TO + child partition concurrent reorganize
+-- Fixed: DoCopy() calls find_all_inheritors() to eagerly lock all child
+-- partitions before refreshing the snapshot, ensuring the snapshot sees all
+-- child reorganize commits before the query is built.
+-- ============================================================
+
+CREATE TABLE copy_part_parent (a INT, b INT) PARTITION BY RANGE (a)
DISTRIBUTED BY (a);
+CREATE TABLE copy_part_child1 PARTITION OF copy_part_parent FOR VALUES FROM
(1) TO (501);
+CREATE TABLE copy_part_child2 PARTITION OF copy_part_parent FOR VALUES FROM
(501) TO (1001);
+INSERT INTO copy_part_parent SELECT i, i FROM generate_series(1, 1000) i;
+
+SELECT count(*) FROM copy_part_parent;
+
+-- Session 1: reorganize the child partition
+1: BEGIN;
+1: ALTER TABLE copy_part_child1 SET WITH (reorganize=true);
+
+-- Session 2: COPY parent TO (internally converted to query-based, child lock
acquired in analyze phase)
+2&: COPY copy_part_parent TO '/tmp/copy_part_parent.csv';
+
+-- Confirm Session 2 is blocked
+1: SELECT count(*) > 0 FROM pg_stat_activity
+ WHERE query LIKE 'COPY copy_part_parent%' AND wait_event_type = 'Lock';
+
+-- Session 1: Commit
+1: COMMIT;
+
+-- Session 2: Complete
+2<:
+
+-- Verify the output file contains all rows
+CREATE TABLE copy_part_verify (a INT, b INT) DISTRIBUTED BY (a);
+COPY copy_part_verify FROM '/tmp/copy_part_parent.csv';
+SELECT count(*) FROM copy_part_verify;
+
+-- Cleanup
+DROP TABLE copy_part_verify;
+DROP TABLE copy_part_parent;
+
+-- ============================================================
+-- Test 2.4: RLS table COPY TO + policy-referenced table concurrent reorganize
+-- Fixed: same as 2.2 — BeginCopy() refreshes snapshot after
AcquireRewriteLocks()
+-- which also acquires the lock on the RLS policy's lookup table.
+-- ============================================================
+
+CREATE TABLE copy_rls_lookup (cat INT) DISTRIBUTED BY (cat);
+INSERT INTO copy_rls_lookup SELECT i FROM generate_series(1, 2) i;
+
+CREATE TABLE copy_rls_main (a INT, category INT) DISTRIBUTED BY (a);
+INSERT INTO copy_rls_main SELECT i, (i % 5) + 1 FROM generate_series(1, 1000)
i;
+
+ALTER TABLE copy_rls_main ENABLE ROW LEVEL SECURITY;
+CREATE POLICY p_rls ON copy_rls_main USING (category IN (SELECT cat FROM
copy_rls_lookup));
+
+-- Create non-superuser to trigger RLS (needs pg_write_server_files to COPY TO
file)
+CREATE ROLE copy_rls_testuser;
+GRANT pg_write_server_files TO copy_rls_testuser;
+GRANT ALL ON copy_rls_main TO copy_rls_testuser;
+GRANT ALL ON copy_rls_lookup TO copy_rls_testuser;
+
+SELECT count(*) FROM copy_rls_main;
+
+-- Baseline: verify RLS filters correctly (should return 400 rows: categories
1 and 2 only)
+2: SET ROLE copy_rls_testuser; COPY copy_rls_main TO '/tmp/copy_rls_main.csv';
+
+-- Session 1: reorganize the lookup table
+1: BEGIN;
+1: ALTER TABLE copy_rls_lookup SET WITH (reorganize=true);
+
+-- Session 2: COPY TO as non-superuser (RLS active, internally converted to
query-based)
+2&: SET ROLE copy_rls_testuser; COPY copy_rls_main TO '/tmp/copy_rls_main.csv';
+
+-- Confirm Session 2 is blocked
+1: SELECT count(*) > 0 FROM pg_stat_activity
+ WHERE query LIKE '%COPY copy_rls_main%' AND wait_event_type = 'Lock';
+
+-- Session 1: Commit
+1: COMMIT;
+
+-- Session 2: Complete
+2<:
+
+-- Reset session 2's role to avoid leaking to subsequent tests
+2: RESET ROLE;
+
+-- Verify: should match baseline count (400 rows filtered by RLS)
+RESET ROLE;
+CREATE TABLE copy_rls_verify (a INT, category INT) DISTRIBUTED BY (a);
+COPY copy_rls_verify FROM '/tmp/copy_rls_main.csv';
+SELECT count(*) FROM copy_rls_verify;
+
+-- Cleanup
+DROP TABLE copy_rls_verify;
+DROP POLICY p_rls ON copy_rls_main;
+DROP TABLE copy_rls_main;
+DROP TABLE copy_rls_lookup;
+DROP ROLE copy_rls_testuser;
+
+-- ============================================================
+-- Test 2.5: CTAS + concurrent reorganize
+-- Fixed as a side effect: CTAS goes through pg_analyze_and_rewrite() +
+-- AcquireRewriteLocks(), so the snapshot refresh in BeginCopy() also fixes it.
+-- ============================================================
+
+CREATE TABLE ctas_reorg_src (a INT, b INT) DISTRIBUTED BY (a);
+INSERT INTO ctas_reorg_src SELECT i, i FROM generate_series(1, 1000) i;
+
+SELECT count(*) FROM ctas_reorg_src;
+
+-- Session 1: reorganize
+1: BEGIN;
+1: ALTER TABLE ctas_reorg_src SET WITH (reorganize=true);
+
+-- Session 2: CTAS should block (lock acquired in executor or analyze phase)
+2&: CREATE TABLE ctas_reorg_dst AS SELECT * FROM ctas_reorg_src DISTRIBUTED BY
(a);
+
+-- Confirm Session 2 is blocked
+1: SELECT count(*) > 0 FROM pg_stat_activity
+ WHERE query LIKE 'CREATE TABLE ctas_reorg_dst%' AND wait_event_type =
'Lock';
+
+-- Session 1: Commit
+1: COMMIT;
+
+-- Session 2: Complete
+2<:
+
+-- Verify row count after CTAS completes
+SELECT count(*) FROM ctas_reorg_dst;
+
+-- Cleanup
+DROP TABLE ctas_reorg_dst;
+DROP TABLE ctas_reorg_src;
+
+-- NOTE: Test 2.6 (change distribution key + query-based COPY TO) removed
because
+-- ALTER TABLE SET DISTRIBUTED BY + concurrent query-based COPY TO causes a
server
+-- crash (pre-existing Cloudberry bug, not related to this fix).
+
+-- ============================================================
+-- Test 2.1a: AO row table — relation-based COPY TO + concurrent reorganize
+-- Same as 2.1 but using append-optimized row-oriented table.
+-- ============================================================
+
+CREATE TABLE copy_reorg_ao_row_test (a INT, b INT) USING ao_row DISTRIBUTED BY
(a);
+INSERT INTO copy_reorg_ao_row_test SELECT i, i FROM generate_series(1, 1000) i;
+
+-- Record original row count
+SELECT count(*) FROM copy_reorg_ao_row_test;
+
+-- Session 1: Begin reorganize (holds AccessExclusiveLock)
+1: BEGIN;
+1: ALTER TABLE copy_reorg_ao_row_test SET WITH (reorganize=true);
+
+-- Session 2: relation-based COPY TO should block on AccessShareLock
+2&: COPY copy_reorg_ao_row_test TO '/tmp/copy_reorg_ao_row_test.csv';
+
+-- Confirm Session 2 is waiting for the lock
+1: SELECT count(*) > 0 FROM pg_stat_activity
+ WHERE query LIKE 'COPY copy_reorg_ao_row_test%' AND wait_event_type =
'Lock';
+
+-- Session 1: Commit reorganize, releasing AccessExclusiveLock
+1: COMMIT;
+
+-- Session 2: Should return 1000 rows (fixed), not 0 rows (broken)
+2<:
+
+-- Verify the output file contains all rows
+CREATE TABLE copy_reorg_ao_row_verify (a INT, b INT) USING ao_row DISTRIBUTED
BY (a);
+COPY copy_reorg_ao_row_verify FROM '/tmp/copy_reorg_ao_row_test.csv';
+SELECT count(*) FROM copy_reorg_ao_row_verify;
+
+-- Cleanup
+DROP TABLE copy_reorg_ao_row_verify;
+DROP TABLE copy_reorg_ao_row_test;
+
+-- ============================================================
+-- Test 2.1b: AO column table — relation-based COPY TO + concurrent reorganize
+-- Same as 2.1 but using append-optimized column-oriented table.
+-- ============================================================
+
+CREATE TABLE copy_reorg_ao_col_test (a INT, b INT) USING ao_column DISTRIBUTED
BY (a);
+INSERT INTO copy_reorg_ao_col_test SELECT i, i FROM generate_series(1, 1000) i;
+
+-- Record original row count
+SELECT count(*) FROM copy_reorg_ao_col_test;
+
+-- Session 1: Begin reorganize (holds AccessExclusiveLock)
+1: BEGIN;
+1: ALTER TABLE copy_reorg_ao_col_test SET WITH (reorganize=true);
+
+-- Session 2: relation-based COPY TO should block on AccessShareLock
+2&: COPY copy_reorg_ao_col_test TO '/tmp/copy_reorg_ao_col_test.csv';
+
+-- Confirm Session 2 is waiting for the lock
+1: SELECT count(*) > 0 FROM pg_stat_activity
+ WHERE query LIKE 'COPY copy_reorg_ao_col_test%' AND wait_event_type =
'Lock';
+
+-- Session 1: Commit reorganize, releasing AccessExclusiveLock
+1: COMMIT;
+
+-- Session 2: Should return 1000 rows (fixed), not 0 rows (broken)
+2<:
+
+-- Verify the output file contains all rows
+CREATE TABLE copy_reorg_ao_col_verify (a INT, b INT) USING ao_column
DISTRIBUTED BY (a);
+COPY copy_reorg_ao_col_verify FROM '/tmp/copy_reorg_ao_col_test.csv';
+SELECT count(*) FROM copy_reorg_ao_col_verify;
+
+-- Cleanup
+DROP TABLE copy_reorg_ao_col_verify;
+DROP TABLE copy_reorg_ao_col_test;
+
+-- ============================================================
+-- Test 2.2a: AO row — query-based COPY TO + concurrent reorganize
+-- Fixed: BeginCopy() refreshes snapshot after AcquireRewriteLocks().
+-- ============================================================
+
+CREATE TABLE copy_query_reorg_ao_row_test (a INT, b INT) USING ao_row
DISTRIBUTED BY (a);
+INSERT INTO copy_query_reorg_ao_row_test SELECT i, i FROM generate_series(1,
1000) i;
+
+SELECT count(*) FROM copy_query_reorg_ao_row_test;
+
+1: BEGIN;
+1: ALTER TABLE copy_query_reorg_ao_row_test SET WITH (reorganize=true);
+
+2&: COPY (SELECT * FROM copy_query_reorg_ao_row_test) TO
'/tmp/copy_query_reorg_ao_row_test.csv';
+
+1: SELECT count(*) > 0 FROM pg_stat_activity
+ WHERE query LIKE 'COPY (SELECT%copy_query_reorg_ao_row_test%' AND
wait_event_type = 'Lock';
+
+1: COMMIT;
+2<:
+
+CREATE TABLE copy_query_reorg_ao_row_verify (a INT, b INT) USING ao_row
DISTRIBUTED BY (a);
+COPY copy_query_reorg_ao_row_verify FROM
'/tmp/copy_query_reorg_ao_row_test.csv';
+SELECT count(*) FROM copy_query_reorg_ao_row_verify;
+
+DROP TABLE copy_query_reorg_ao_row_verify;
+DROP TABLE copy_query_reorg_ao_row_test;
+
+-- ============================================================
+-- Test 2.2b: AO column — query-based COPY TO + concurrent reorganize
+-- Fixed: BeginCopy() refreshes snapshot after AcquireRewriteLocks().
+-- ============================================================
+
+CREATE TABLE copy_query_reorg_ao_col_test (a INT, b INT) USING ao_column
DISTRIBUTED BY (a);
+INSERT INTO copy_query_reorg_ao_col_test SELECT i, i FROM generate_series(1,
1000) i;
+
+SELECT count(*) FROM copy_query_reorg_ao_col_test;
+
+1: BEGIN;
+1: ALTER TABLE copy_query_reorg_ao_col_test SET WITH (reorganize=true);
+
+2&: COPY (SELECT * FROM copy_query_reorg_ao_col_test) TO
'/tmp/copy_query_reorg_ao_col_test.csv';
+
+1: SELECT count(*) > 0 FROM pg_stat_activity
+ WHERE query LIKE 'COPY (SELECT%copy_query_reorg_ao_col_test%' AND
wait_event_type = 'Lock';
+
+1: COMMIT;
+2<:
+
+CREATE TABLE copy_query_reorg_ao_col_verify (a INT, b INT) USING ao_column
DISTRIBUTED BY (a);
+COPY copy_query_reorg_ao_col_verify FROM
'/tmp/copy_query_reorg_ao_col_test.csv';
+SELECT count(*) FROM copy_query_reorg_ao_col_verify;
+
+DROP TABLE copy_query_reorg_ao_col_verify;
+DROP TABLE copy_query_reorg_ao_col_test;
+
+-- ============================================================
+-- Test 2.3a: AO row — partitioned table COPY TO + child partition concurrent
reorganize
+-- Fixed: DoCopy() calls find_all_inheritors() to lock all child partitions
first.
+-- ============================================================
+
+CREATE TABLE copy_part_parent_ao_row (a INT, b INT) PARTITION BY RANGE (a)
DISTRIBUTED BY (a);
+CREATE TABLE copy_part_child1_ao_row PARTITION OF copy_part_parent_ao_row FOR
VALUES FROM (1) TO (501) USING ao_row;
+CREATE TABLE copy_part_child2_ao_row PARTITION OF copy_part_parent_ao_row FOR
VALUES FROM (501) TO (1001) USING ao_row;
+INSERT INTO copy_part_parent_ao_row SELECT i, i FROM generate_series(1, 1000)
i;
+
+SELECT count(*) FROM copy_part_parent_ao_row;
+
+1: BEGIN;
+1: ALTER TABLE copy_part_child1_ao_row SET WITH (reorganize=true);
+
+2&: COPY copy_part_parent_ao_row TO '/tmp/copy_part_parent_ao_row.csv';
+
+1: SELECT count(*) > 0 FROM pg_stat_activity
+ WHERE query LIKE 'COPY copy_part_parent_ao_row%' AND wait_event_type =
'Lock';
+
+1: COMMIT;
+2<:
+
+CREATE TABLE copy_part_ao_row_verify (a INT, b INT) USING ao_row DISTRIBUTED
BY (a);
+COPY copy_part_ao_row_verify FROM '/tmp/copy_part_parent_ao_row.csv';
+SELECT count(*) FROM copy_part_ao_row_verify;
+
+DROP TABLE copy_part_ao_row_verify;
+DROP TABLE copy_part_parent_ao_row;
+
+-- ============================================================
+-- Test 2.3b: AO column — partitioned table COPY TO + child partition
concurrent reorganize
+-- Fixed: DoCopy() calls find_all_inheritors() to lock all child partitions
first.
+-- ============================================================
+
+CREATE TABLE copy_part_parent_ao_col (a INT, b INT) PARTITION BY RANGE (a)
DISTRIBUTED BY (a);
+CREATE TABLE copy_part_child1_ao_col PARTITION OF copy_part_parent_ao_col FOR
VALUES FROM (1) TO (501) USING ao_column;
+CREATE TABLE copy_part_child2_ao_col PARTITION OF copy_part_parent_ao_col FOR
VALUES FROM (501) TO (1001) USING ao_column;
+INSERT INTO copy_part_parent_ao_col SELECT i, i FROM generate_series(1, 1000)
i;
+
+SELECT count(*) FROM copy_part_parent_ao_col;
+
+1: BEGIN;
+1: ALTER TABLE copy_part_child1_ao_col SET WITH (reorganize=true);
+
+2&: COPY copy_part_parent_ao_col TO '/tmp/copy_part_parent_ao_col.csv';
+
+1: SELECT count(*) > 0 FROM pg_stat_activity
+ WHERE query LIKE 'COPY copy_part_parent_ao_col%' AND wait_event_type =
'Lock';
+
+1: COMMIT;
+2<:
+
+CREATE TABLE copy_part_ao_col_verify (a INT, b INT) USING ao_column
DISTRIBUTED BY (a);
+COPY copy_part_ao_col_verify FROM '/tmp/copy_part_parent_ao_col.csv';
+SELECT count(*) FROM copy_part_ao_col_verify;
+
+DROP TABLE copy_part_ao_col_verify;
+DROP TABLE copy_part_parent_ao_col;
+
+-- ============================================================
+-- Test 2.4a: AO row — RLS table COPY TO + policy-referenced table concurrent
reorganize
+-- Fixed: same as 2.4 — BeginCopy() refreshes snapshot after
AcquireRewriteLocks().
+-- ============================================================
+
+CREATE TABLE copy_rls_ao_row_lookup (cat INT) USING ao_row DISTRIBUTED BY
(cat);
+INSERT INTO copy_rls_ao_row_lookup SELECT i FROM generate_series(1, 2) i;
+
+CREATE TABLE copy_rls_ao_row_main (a INT, category INT) USING ao_row
DISTRIBUTED BY (a);
+INSERT INTO copy_rls_ao_row_main SELECT i, (i % 5) + 1 FROM generate_series(1,
1000) i;
+
+ALTER TABLE copy_rls_ao_row_main ENABLE ROW LEVEL SECURITY;
+CREATE POLICY p_rls_ao_row ON copy_rls_ao_row_main USING (category IN (SELECT
cat FROM copy_rls_ao_row_lookup));
+
+CREATE ROLE copy_rls_ao_row_testuser;
+GRANT pg_write_server_files TO copy_rls_ao_row_testuser;
+GRANT ALL ON copy_rls_ao_row_main TO copy_rls_ao_row_testuser;
+GRANT ALL ON copy_rls_ao_row_lookup TO copy_rls_ao_row_testuser;
+
+SELECT count(*) FROM copy_rls_ao_row_main;
+
+-- Baseline: verify RLS filters correctly (should return 400 rows: categories
1 and 2 only)
+2: SET ROLE copy_rls_ao_row_testuser; COPY copy_rls_ao_row_main TO
'/tmp/copy_rls_ao_row_main.csv';
+
+1: BEGIN;
+1: ALTER TABLE copy_rls_ao_row_lookup SET WITH (reorganize=true);
+
+2&: SET ROLE copy_rls_ao_row_testuser; COPY copy_rls_ao_row_main TO
'/tmp/copy_rls_ao_row_main.csv';
+
+1: SELECT count(*) > 0 FROM pg_stat_activity
+ WHERE query LIKE '%COPY copy_rls_ao_row_main%' AND wait_event_type = 'Lock';
+
+1: COMMIT;
+2<:
+
+2: RESET ROLE;
+
+RESET ROLE;
+CREATE TABLE copy_rls_ao_row_verify (a INT, category INT) USING ao_row
DISTRIBUTED BY (a);
+COPY copy_rls_ao_row_verify FROM '/tmp/copy_rls_ao_row_main.csv';
+SELECT count(*) FROM copy_rls_ao_row_verify;
+
+DROP TABLE copy_rls_ao_row_verify;
+DROP POLICY p_rls_ao_row ON copy_rls_ao_row_main;
+DROP TABLE copy_rls_ao_row_main;
+DROP TABLE copy_rls_ao_row_lookup;
+DROP ROLE copy_rls_ao_row_testuser;
+
+-- ============================================================
+-- Test 2.4b: AO column — RLS table COPY TO + policy-referenced table
concurrent reorganize
+-- Fixed: same as 2.4 — BeginCopy() refreshes snapshot after
AcquireRewriteLocks().
+-- ============================================================
+
+CREATE TABLE copy_rls_ao_col_lookup (cat INT) USING ao_column DISTRIBUTED BY
(cat);
+INSERT INTO copy_rls_ao_col_lookup SELECT i FROM generate_series(1, 2) i;
+
+CREATE TABLE copy_rls_ao_col_main (a INT, category INT) USING ao_column
DISTRIBUTED BY (a);
+INSERT INTO copy_rls_ao_col_main SELECT i, (i % 5) + 1 FROM generate_series(1,
1000) i;
+
+ALTER TABLE copy_rls_ao_col_main ENABLE ROW LEVEL SECURITY;
+CREATE POLICY p_rls_ao_col ON copy_rls_ao_col_main USING (category IN (SELECT
cat FROM copy_rls_ao_col_lookup));
+
+CREATE ROLE copy_rls_ao_col_testuser;
+GRANT pg_write_server_files TO copy_rls_ao_col_testuser;
+GRANT ALL ON copy_rls_ao_col_main TO copy_rls_ao_col_testuser;
+GRANT ALL ON copy_rls_ao_col_lookup TO copy_rls_ao_col_testuser;
+
+SELECT count(*) FROM copy_rls_ao_col_main;
+
+-- Baseline: verify RLS filters correctly (should return 400 rows: categories
1 and 2 only)
+2: SET ROLE copy_rls_ao_col_testuser; COPY copy_rls_ao_col_main TO
'/tmp/copy_rls_ao_col_main.csv';
+
+1: BEGIN;
+1: ALTER TABLE copy_rls_ao_col_lookup SET WITH (reorganize=true);
+
+2&: SET ROLE copy_rls_ao_col_testuser; COPY copy_rls_ao_col_main TO
'/tmp/copy_rls_ao_col_main.csv';
+
+1: SELECT count(*) > 0 FROM pg_stat_activity
+ WHERE query LIKE '%COPY copy_rls_ao_col_main%' AND wait_event_type = 'Lock';
+
+1: COMMIT;
+2<:
+
+2: RESET ROLE;
+
+RESET ROLE;
+CREATE TABLE copy_rls_ao_col_verify (a INT, category INT) USING ao_column
DISTRIBUTED BY (a);
+COPY copy_rls_ao_col_verify FROM '/tmp/copy_rls_ao_col_main.csv';
+SELECT count(*) FROM copy_rls_ao_col_verify;
+
+DROP TABLE copy_rls_ao_col_verify;
+DROP POLICY p_rls_ao_col ON copy_rls_ao_col_main;
+DROP TABLE copy_rls_ao_col_main;
+DROP TABLE copy_rls_ao_col_lookup;
+DROP ROLE copy_rls_ao_col_testuser;
+
+-- ============================================================
+-- Test 2.5a: AO row — CTAS + concurrent reorganize
+-- Fixed as a side effect via BeginCopy() snapshot refresh.
+-- ============================================================
+
+CREATE TABLE ctas_reorg_ao_row_src (a INT, b INT) USING ao_row DISTRIBUTED BY
(a);
+INSERT INTO ctas_reorg_ao_row_src SELECT i, i FROM generate_series(1, 1000) i;
+
+SELECT count(*) FROM ctas_reorg_ao_row_src;
+
+1: BEGIN;
+1: ALTER TABLE ctas_reorg_ao_row_src SET WITH (reorganize=true);
+
+2&: CREATE TABLE ctas_reorg_ao_row_dst AS SELECT * FROM ctas_reorg_ao_row_src
DISTRIBUTED BY (a);
+
+1: SELECT count(*) > 0 FROM pg_stat_activity
+ WHERE query LIKE 'CREATE TABLE ctas_reorg_ao_row_dst%' AND wait_event_type
= 'Lock';
+
+1: COMMIT;
+2<:
+
+SELECT count(*) FROM ctas_reorg_ao_row_dst;
+
+DROP TABLE ctas_reorg_ao_row_dst;
+DROP TABLE ctas_reorg_ao_row_src;
+
+-- ============================================================
+-- Test 2.5b: AO column — CTAS + concurrent reorganize
+-- Fixed as a side effect via BeginCopy() snapshot refresh.
+-- ============================================================
+
+CREATE TABLE ctas_reorg_ao_col_src (a INT, b INT) USING ao_column DISTRIBUTED
BY (a);
+INSERT INTO ctas_reorg_ao_col_src SELECT i, i FROM generate_series(1, 1000) i;
+
+SELECT count(*) FROM ctas_reorg_ao_col_src;
+
+1: BEGIN;
+1: ALTER TABLE ctas_reorg_ao_col_src SET WITH (reorganize=true);
+
+2&: CREATE TABLE ctas_reorg_ao_col_dst AS SELECT * FROM ctas_reorg_ao_col_src
DISTRIBUTED BY (a);
+
+1: SELECT count(*) > 0 FROM pg_stat_activity
+ WHERE query LIKE 'CREATE TABLE ctas_reorg_ao_col_dst%' AND wait_event_type
= 'Lock';
+
+1: COMMIT;
+2<:
+
+SELECT count(*) FROM ctas_reorg_ao_col_dst;
+
+DROP TABLE ctas_reorg_ao_col_dst;
+DROP TABLE ctas_reorg_ao_col_src;
+
+-- NOTE: Tests 2.6a/2.6b (AO variants of change distribution key + query-based
COPY TO)
+-- removed for the same reason as test 2.6 (server crash, pre-existing bug).
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
