Resolution for "function partman.add_parent(...) does not exist" Error in YugabyteDB – Yugabyte

Environment

Yugabyte Anywhere: N/A
YugabyteDB Version: - All Versions supporting pg_partman

Issue

When attempting to register an existing partitioned table with pg_partman in YugabyteDB, the operation fails with the following error:

ERROR: function partman.add_parent(p_parent_table => unknown, p_control => unknown, p_type => unknown, p_interval => unknown, p_premake => integer) does not exist 

LINE 1: SELECT partman.add_parent( ^ 

HINT: No function matches the given name and argument types. You might need to add explicit type casts. 

SQL state: 42883 Character: 8

This error occurs because the partman.add_parent() function is not available in the build of pg_partman that ships with YugabyteDB.

Resolution

Overview

The pg_partman extension in YugabyteDB supports managing partitioned tables, but the specific function add_parent() (often used in other PostgreSQL environments or older versions of partman to add existing tables) is not available.

The supported method to register a native partitioned table with pg_partman in YugabyteDB is to use the partman.create_parent() function. This function can handle both creating new partitioned tables and registering existing ones, provided the arguments match the existing table's structure.

To resolve this issue, you must update your SQL commands to use partman.create_parent() instead of partman.add_parent(). Additionally, ensure that any scheduled maintenance jobs via pg_cron use the correct maintenance function partman.run_maintenance().

Steps

1. Verify the Error

Confirm that the error matches the missing function signature for partman.add_parent:

ERROR: function partman.add_parent(...) does not exist

2. Use partman.create_parent()

Replace the call to add_parent with create_parent. Ensure you use the same arguments that describe your partitioning scheme.

Example (Invalid):

SELECT partman.add_parent(

    p_parent_table => 'cpc_dbo.investment_transaction',

    p_control      => 'transaction_date',

    p_type         => 'native',

    p_interval     => 'monthly',

    p_premake      => 3

);

Example (Correct):

SELECT partman.create_parent(

    p_parent_table => 'cpc_dbo.investment_transaction',

    p_control      => 'transaction_date',

    p_type         => 'native',

    p_interval     => 'monthly',

    p_premake      => 3

);

3. Verify and Configure Maintenance

After registering the table, you may want to update the retention policy and run maintenance immediately to create any missing partitions.

-- Update retention policy (example)

UPDATE partman.part_config

   SET retention = '36 months'

 WHERE parent_table = 'cpc_dbo.investment_transaction';

-- Run maintenance immediately

SELECT partman.run_maintenance();

4. Schedule Maintenance with pg_cron

If you are using pg_cron to automate partition maintenance, ensure you are calling the correct function partman.run_maintenance(). Do not use partman_maintenance() as it is not a standard function.

Example:

SELECT cron.schedule(

    job_name   => 'partition_maintenance_job',

    schedule   => '00 01 * * *',

    command    => $$SELECT partman.run_maintenance();$$

);

Additional Information

SUPPORT-891