FK on partitioned table when PK is not part of the partition column (AKA template)

[Asafb26]

Hi,

Im trying to partitioning my table by timestamp, while my unique id is a uuid column.

After reading your documentation, I removed all temporary FKs pointing to the table to be partitioned and used a template table to declare PKs on children while the partition key is indeed the timestamp.

I am now trying to set the FK back, but I am getting an error saying that there is no unique constraint matching the given keys for the referenced table.

What am I missing? is it even possible to use FK on partitioned table when PK is not part of the partition column?

[keithf4]

Can you please provide the schema for both tables involved in the FK reference and the contents of the part_config table for this partition set so I can try to recreate your scenario?

[Asafb26]

Sure, providing both schema and steps i took to migrate the data:

-- Rename current table and indexes

ALTER TABLE public.schema_transaction RENAME to old_schema_transaction;

ALTER INDEX schema_transaction_timestamp_fef523d9 RENAME TO old_schema_transaction_timestamp_fef523d9;
ALTER INDEX schema_transaction_identifier_20ea9d2d RENAME TO old_schema_transaction_identifier_20ea9d2d;
ALTER INDEX schema_transaction_identifier_20ea9d2d_like RENAME TO old_schema_transaction_identifier_20ea9d2d_like;
ALTER INDEX schema_transaction_classification_id_e5a2343d RENAME TO old_schema_transaction_classification_id_e5a2343d;
ALTER INDEX schema_transaction_classification_id_e5a2343d_like RENAME TO old_schema_transaction_classification_id_e5a2343d_like;
ALTER INDEX schema_transaction_contract_id_185ca565 RENAME TO old_schema_transaction_contract_id_185ca565;
ALTER INDEX schema_transaction_contract_id_185ca565_like RENAME TO old_schema_transaction_contract_id_185ca565_like;
ALTER INDEX schema_transaction_from_address_id_9c4b4703 RENAME TO old_schema_transaction_from_address_id_9c4b4703;
ALTER INDEX schema_transaction_from_address_id_9c4b4703_like RENAME TO old_schema_transaction_from_address_id_9c4b4703_like;
ALTER INDEX schema_transaction_organization_id_82c3dc0e RENAME TO old_schema_transaction_organization_id_82c3dc0e;
ALTER INDEX schema_transaction_organization_id_82c3dc0e_like RENAME TO old_schema_transaction_organization_id_82c3dc0e_like;
ALTER INDEX schema_transaction_to_address_id_d0333104 RENAME TO old_schema_transaction_to_address_id_d0333104;
ALTER INDEX schema_transaction_to_address_id_d0333104_like RENAME TO old_schema_transaction_to_address_id_d0333104_like;
ALTER INDEX schema_transaction_created_at_0fe77057 RENAME TO old_schema_transaction_created_at_0fe77057;
ALTER INDEX schema_transaction_created_by_5c43771d RENAME TO old_schema_transaction_created_by_5c43771d;
ALTER INDEX schema_transaction_created_by_5c43771d_like RENAME TO old_schema_transaction_created_by_5c43771d_like;
ALTER INDEX schema_transaction_deleted_aee3391b RENAME TO old_schema_transaction_deleted_aee3391b;
ALTER INDEX schema_tran_organiz_d01639_idx RENAME TO old_schema_tran_organiz_d01639_idx;
ALTER INDEX schema_transaction_offset_50f6e1bd RENAME TO old_schema_transaction_offset_50f6e1bd;
ALTER INDEX schema_tran_organiz_028d98_idx RENAME TO old_schema_tran_organiz_028d98_idx;
ALTER INDEX schema_tran_organiz_34cc33_idx RENAME TO old_schema_tran_organiz_34cc33_idx;
ALTER INDEX schema_tran_organiz_77da25_idx RENAME TO old_schema_tran_organiz_77da25_idx;
ALTER INDEX schema_tran_organiz_61e8df_idx RENAME TO old_schema_tran_organiz_61e8df_idx;


-- Create the partitioned table and indexes
CREATE TABLE schema_transaction (
    index bigint,
    timestamp timestamp with time zone NOT NULL,
    identifier character varying(256) NOT NULL,
    platform character varying(256) NOT NULL,
    method_id character varying(256),
    block_number integer NOT NULL,
    decoded_function_name character varying(256),
    success boolean NOT NULL,
    classification_id character varying(256) REFERENCES schema_transactionclassification(key) DEFERRABLE INITIALLY DEFERRED,
    contract_id character varying(256) REFERENCES schema_contract(key) DEFERRABLE INITIALLY DEFERRED,
    from_address_id character varying(256) REFERENCES schema_account(key) DEFERRABLE INITIALLY DEFERRED,
    organization_id character varying(256) NOT NULL REFERENCES schema_organization(id) DEFERRABLE INITIALLY DEFERRED,
    to_address_id character varying(256) REFERENCES schema_account(key) DEFERRABLE INITIALLY DEFERRED,
    additional_data jsonb NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    created_by character varying NOT NULL,
    deleted boolean NOT NULL,
    "offset" timestamp with time zone NOT NULL,
    id numeric(46,30)
)
PARTITION BY RANGE (timestamp);

CREATE INDEX schema_transaction_timestamp_fef523d9 ON schema_transaction(timestamp timestamptz_ops);
CREATE INDEX schema_transaction_identifier_20ea9d2d ON schema_transaction(identifier text_ops);
CREATE INDEX schema_transaction_identifier_20ea9d2d_like ON schema_transaction(identifier varchar_pattern_ops);
CREATE INDEX schema_transaction_classification_id_e5a2343d ON schema_transaction(classification_id text_ops);
CREATE INDEX schema_transaction_classification_id_e5a2343d_like ON schema_transaction(classification_id varchar_pattern_ops);
CREATE INDEX schema_transaction_contract_id_185ca565 ON schema_transaction(contract_id text_ops);
CREATE INDEX schema_transaction_contract_id_185ca565_like ON schema_transaction(contract_id varchar_pattern_ops);
CREATE INDEX schema_transaction_from_address_id_9c4b4703 ON schema_transaction(from_address_id text_ops);
CREATE INDEX schema_transaction_from_address_id_9c4b4703_like ON schema_transaction(from_address_id varchar_pattern_ops);
CREATE INDEX schema_transaction_organization_id_82c3dc0e ON schema_transaction(organization_id text_ops);
CREATE INDEX schema_transaction_organization_id_82c3dc0e_like ON schema_transaction(organization_id varchar_pattern_ops);
CREATE INDEX schema_transaction_to_address_id_d0333104 ON schema_transaction(to_address_id text_ops);
CREATE INDEX schema_transaction_to_address_id_d0333104_like ON schema_transaction(to_address_id varchar_pattern_ops);
CREATE INDEX schema_transaction_created_at_0fe77057 ON schema_transaction(created_at timestamptz_ops);
CREATE INDEX schema_transaction_created_by_5c43771d ON schema_transaction(created_by text_ops);
CREATE INDEX schema_transaction_created_by_5c43771d_like ON schema_transaction(created_by varchar_pattern_ops);
CREATE INDEX schema_transaction_deleted_aee3391b ON schema_transaction(deleted bool_ops);
CREATE INDEX schema_tran_organiz_d01639_idx ON schema_transaction(organization_id text_ops,"offset" timestamptz_ops,deleted bool_ops);
CREATE INDEX schema_transaction_offset_50f6e1bd ON schema_transaction("offset" timestamptz_ops);
CREATE INDEX schema_tran_organiz_028d98_idx ON schema_transaction(organization_id text_ops,timestamp timestamptz_ops,"offset" timestamptz_ops,deleted bool_ops,classification_id text_ops);
CREATE INDEX schema_tran_organiz_34cc33_idx ON schema_transaction(organization_id text_ops,timestamp timestamptz_ops,"offset" timestamptz_ops,deleted bool_ops,decoded_function_name text_ops);
CREATE INDEX schema_tran_organiz_77da25_idx ON schema_transaction(organization_id text_ops,"offset" timestamptz_ops,deleted bool_ops,platform text_ops);
CREATE INDEX schema_tran_organiz_61e8df_idx ON schema_transaction(organization_id text_ops,timestamp timestamptz_ops,"offset" timestamptz_ops,deleted bool_ops,platform text_ops);


-- Adding PK as ID while timestamp is the partition key (template table)
CREATE TABLE public.schema_transaction_template (LIKE public.schema_transaction);

ALTER TABLE public.schema_transaction_template ADD PRIMARY KEY (id);




-- Set partman partitioning
SELECT partman.create_parent(
    p_parent_table := 'public.schema_transaction'
    , p_control := 'timestamp'
    , p_interval := '1 month'
    , p_template_table:= 'public.schema_transaction_template'
    , p_premake := 3
	, p_type => 'native'
);

 -- Migrate the old data
CALL partman.partition_data_proc(
    p_parent_table := 'public.schema_transaction'
    , p_interval := '1000'
    , p_source_table := 'public.old_schema_transaction'
);

And (one of) the ref table:

-- DDL generated by Postico 2.0.5
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE TABLE schema_transactioncomment (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    identifier character varying(256) NOT NULL,
    platform character varying(256),
    file_key character varying(2048),
    comment text NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    created_at timestamp with time zone NOT NULL,
    organization_id character varying(256) NOT NULL REFERENCES schema_organization(id) DEFERRABLE INITIALLY DEFERRED,
    user_id integer NOT NULL REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED,
    transaction_id numeric(46,30) REFERENCES schema_transaction(id) DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT schema_transactioncommen_identifier_organization__334776de_uniq UNIQUE (identifier, organization_id, platform, created_at)
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX schema_transactioncomment_pkey ON schema_transactioncomment(id int8_ops);
CREATE UNIQUE INDEX schema_transactioncommen_identifier_organization__334776de_uniq ON schema_transactioncomment(identifier text_ops,organization_id text_ops,platform text_ops,created_at timestamptz_ops);
CREATE INDEX schema_transactioncomment_organization_id_96ffdfe9 ON schema_transactioncomment(organization_id text_ops);
CREATE INDEX schema_transactioncomment_organization_id_96ffdfe9_like ON schema_transactioncomment(organization_id varchar_pattern_ops);
CREATE INDEX schema_transactioncomment_user_id_44599e93 ON schema_transactioncomment(user_id int4_ops);
CREATE INDEX schema_transactioncomment_temp_tx_id_86062d81 ON schema_transactioncomment(transaction_id numeric_ops);

[keithf4]

Apologies but I cannot reproduce this fully because only part of the full schema involved in all tables isn't here. The old table you're renaming, the FK reference tables (schema_transactionclassification, schema_contract, etc). Also the commands to create the FK that are generating the error that you're seeing.

The issue is around FKs, so I really need the schema of all tables involved and the actual commands you're running to get the errors you're seeing. FKs are a tricky issue in partitioning right now and I'm still trying to figure out the "right" way to do things myself and effectively be able to report the issues upstream. Basically if you can provide an independent, reproducible use-case that shows all the steps to recreate it, then me and others will be able to better debug and see what's happening.

[Asafb26]

Hi sorry for the bad ref, created a simpler reproduceable example below:

-- Create a table to store information about people
CREATE TABLE people (
	id INT PRIMARY KEY, -- Unique identifier for each person
	name VARCHAR(50), -- Column to store the name of the person
	timestamp TIMESTAMP -- Column to store timestamps
);


-- Insert dummy data into the 'people' table
INSERT INTO people (id, name, timestamp) VALUES
    (1, 'John', NOW()),                     -- Inserting John with current timestamp
    (2, 'Alice', NOW() - INTERVAL '1 day'), -- Inserting Alice with a timestamp one day ago
    (3, 'Bob', NOW() - INTERVAL '2 days');  -- Inserting Bob with a timestamp two days ago


-- Create a table to store country information with a foreign key reference to the people table
CREATE TABLE country (
    id SERIAL PRIMARY KEY,                  -- Auto-incrementing ID column, acting as the primary key
    country_name VARCHAR(100),              -- Column to store the name of the country
    people_id INT                           -- Foreign key referencing the 'id' column of 'people' table
);


-- Add a foreign key constraint to ensure data integrity
ALTER TABLE country
ADD CONSTRAINT country_people_id_fkey FOREIGN KEY (people_id) REFERENCES people(id);


-- Insert dummy data into the 'country' table
INSERT INTO country (country_name, people_id) VALUES
    ('USA', 1),                             -- Inserting USA with a reference to person John
    ('Canada', 2),                          -- Inserting Canada with a reference to person Alice
    ('UK', 3);                              -- Inserting UK with a reference to person Bob


-- Start Migration
-- Remove the foreign key constraint
ALTER TABLE country
DROP CONSTRAINT country_people_id_fkey;


-- Rename the existing 'people' table to 'old_people'
ALTER TABLE public.people RENAME to old_people;


-- Create a new partitioned table for 'people' based on the timestamp column
CREATE TABLE people (
    id INT,                               -- Change data type to INT
    name VARCHAR(50),
    timestamp TIMESTAMP
)
PARTITION BY RANGE (timestamp);


-- Create a template table for the partitions
CREATE TABLE public.people_template (LIKE public.people);
ALTER TABLE public.people_template ADD PRIMARY KEY (id);


-- Create partitions for the 'people' table
SELECT partman.create_parent(
    p_parent_table := 'public.people',
    p_control := 'timestamp',
    p_interval := '1 month',
    p_template_table:= 'public.people_template',
    p_premake := 3,
    p_type => 'native'
);


-- Migrate data from the old 'people' table to the partitioned 'people' table
CALL partman.partition_data_proc(
    p_parent_table := 'public.people',
    p_interval := '1000',
    p_source_table := 'public.old_people'
);


VACUUM ANALYZE public.people;


-- ERROR HERE
ALTER TABLE country
ADD CONSTRAINT country_people_id_fkey FOREIGN KEY (people_id) REFERENCES people(id);

The error:

ERROR:  there is no unique constraint matching given keys for referenced table "people"

[keithf4]

Apologies for the delay on getting back to you on this. Been very busy and just want to let you know I haven't forgotten about this. FKs have been a particular thorn in partitioning so I want to take some time and better nail down where the actual problems are so I can hopefully report upstream.

[Asafb26]

Hi have any insight about it? did you manage to reproduce the error?

[keithf4]

Sorry, other work still has me pretty busy. I'll hopefully have that wrapped up in the next week or so

[SMatusik]

are there any updates on that? I'm currently struggling with the same scenario, when I cannot set up FK to partitioned table, as primary key does not exists on parent table

[baryaner]

@keithf4 any updates?