Skip to content

2.25.0.0-b183

tagged this 22 Oct 12:42
Summary:
This diff disables the  defualt partition creation in pg_partman as after creating default partition the further maintenance work of moving rows out of default partition into newly created partitions will cause this error

```
 updated partition constraint for default partition would be violated by some row
```

When `partition_data_proc` or `run_maintenance` functions  tries to move rows from default partition to a newly created child partition, it does that process in following steps

1.) Create a temporary table to hold the data that needs to be moved. This is done because child tables cannot be created in native partitioning if data that belongs to it exists in the default.

```
CREATE TEMP TABLE IF NOT EXISTS partman_temp_data_storage (LIKE %I.%I INCLUDING INDEXES) ON COMMIT DROP
```

2.)  Move rows from default table to the temp table.

```
    EXECUTE format('WITH partition_data AS (
            DELETE FROM %1$I.%2$I WHERE %3$I >= %4$s AND %3$I < %5$s RETURNING *)
        INSERT INTO partman_temp_data_storage (%6$s) SELECT %6$s FROM partition_data'
```

3.) Create the child partition via create_partition_id  function

```
PERFORM @extschema@.create_partition_id(p_parent_table, v_partition_id, p_analyze);
```

4.) After creation of the child partition, move the data from temp table to the new child partition.

```
EXECUTE format('WITH partition_data AS (
            DELETE FROM ONLY %1$I.%2$I WHERE %3$I >= %4$s AND %3$I < %5$s RETURNING *)
        INSERT INTO %1$I.%6$I (%7$s) SELECT %7$s FROM partition_data
```
The issue is occurring at step 3 while creating the new child partition. Since currently YugabyteDB does not have transactional DDL support and the plsql functions are run inside a transactional context,   `create_partition_id`  and `partition_data_proc`  are in different transactional context. When `create_partition_id`  tries to attach the newly created child partition, that transaction still finds the default partition have violating rows present and the `ALTER TABLE ATTACH` query fails stating

```
updated partition constraint for default partition would be violated by some row .
```

Hence to not get fall in this scenario, default partition creation should be disabled in pg_partman. This diff also adds the check that if default partition is created deliberately then `partition_data_proc` and `run_maintenance` will early exit without performing any operation.
Jira: DB-12352

Test Plan: Jenkins: test regex: PgPartmanTest*

Reviewers: skumar, hsunder

Reviewed By: hsunder

Subscribers: yql

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