Skip to content

exclusion constraint from template table is missing in partitions #861

Description

@christiansicari

I need to create a table like the following

CREATE TABLE test_schema.ranged_table
(
    a        text      NOT NULL,
    b        text      NOT NULL,
    myrange int4range NOT NULL CHECK (NOT isempty(myrange)),
    _upper   int4 GENERATED ALWAYS AS (upper(myrange)) STORED
) PARTITION BY RANGE (upper(myrange));

and I need a primary key and a and a exclusion constraint on the fields (a,b, myrange).
Unfortunately, postgres doesn't allow creating keys and constraints on partitioned tables when expressions are used in the partition keys.

My workaround was to add those only to the template table like this:

CREATE TABLE test_schema.dummy_template
(
    LIKE test_schema.ranged_table INCLUDING ALL,
    PRIMARY KEY (a, b, myrange),
    EXCLUDE USING GIST (a WITH =, b WITH =, myrange WITH &&)
);

And finally configure partman to use this template:

-- Init partman
SELECT partman.create_parent(
               p_parent_table => 'test_schema.ranged_table',
               p_control => '_upper',
               p_control_not_null => false,
               p_template_table := 'test_schema.dummy_template',
               p_type => 'range',
               p_interval => '30',
               p_premake => 2
       );

I was expecting to see the PK and the exclusion constraints, but I can see only the first:

SELECT conrelid::regclass, conname, contype
FROM pg_constraint
WHERE contype in ('x', 'p') AND conrelid::regclass::text LIKE 'test_schema.%';

gives:

conrelid,conname,contype
test_schema.dummy_template,dummy_template_pkey,p
test_schema.dummy_template,dummy_template_a_b_myrange_excl,x
test_schema.ranged_table_p0,ranged_table_p0_pkey,p
test_schema.ranged_table_p30,ranged_table_p30_pkey,p
test_schema.ranged_table_p60,ranged_table_p60_pkey,p
test_schema.ranged_table_default,ranged_table_default_pkey,p

Instead, if I manually add the constraint on the already generated partitions, it goes through.
Am I missing something?

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions