Skip to content

2.29.0.0-b106

2.29.0.0-b106: [#29058] YSQL: Fix REINDEX INDEX on partitioned tables with txn ddl
@dr0pdb dr0pdb tagged this 28 Oct 11:34 
Summary:
In PG, the `REINDEX INDEX` statement for a partitioned table when executed outside of explicit txn block starts one transaction for each
partition. In the `ReindexMultipleInternal` function:
1. top level statement txn is committed
2. for each partition: new txn is started, reindex is done and then txn is committed
3. new top level txn is started at the end

In YB, before transactional DDL, we started a DDL transaction when receiving the statement and commit it post the statement execution. In other words,
one DDL transaction was shared by all partitions.

With transactional DDL, we get error while executing the following example:
```
CREATE TABLE test_partitioned (i int, j int) PARTITION BY LIST (j);
CREATE INDEX NONCONCURRENTLY ON test_partitioned (i);
CREATE TABLE test_partitioned_odd PARTITION OF test_partitioned FOR VALUES IN (1, 3, 5, 7, 9);
CREATE TABLE test_partitioned_even PARTITION OF test_partitioned FOR VALUES IN (2, 4, 6, 8);
INSERT INTO test_partitioned SELECT (2 * g), g FROM generate_series(1, 9) g;
-- Mark all partitions are invalid before REINDEX.
UPDATE pg_index SET indisvalid = false
    WHERE indexrelid = 'test_partitioned_i_idx'::regclass;
UPDATE pg_index SET indisvalid = false
    WHERE indexrelid = 'test_partitioned_odd_i_idx'::regclass;
UPDATE pg_index SET indisvalid = false
    WHERE indexrelid = 'test_partitioned_even_i_idx'::regclass;
\c
REINDEX INDEX test_partitioned_i_idx;
```

```
ERROR:  Transaction for catalog table write operation 'pg_class_relname_nsp_index' not found
```

This happens because the DDL state is only set for the top level statement txn in pg_yb_utils. The DDL state is not set in the txn started for partitions. As a result, when these transactions try to do catalog modifications, we get the error. This is the same problem we fixed in `ANALYZE` as part of D44196 / bbaf5f829069. This case was missed in that revision because I missed the case of partitioned tables.

This revision uses the same common utility functions to fix the bug in `REINDEX INDEX`.
Jira: DB-18821

Test Plan:
./yb_build.sh --java-test 'org.yb.pgsql.TestDdlTransactionBlocks'

Backport-through: 2025.2

Reviewers: patnaik.balivada, pjain, myang

Reviewed By: patnaik.balivada

Subscribers: svc_phabricator, yql

Differential Revision: https://phorge.dev.yugabyte.com/D47719
Assets 2
Loading