Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tJEpJ-001ggh-Tz for pgsql-general@arkaria.postgresql.org; Thu, 05 Dec 2024 16:35:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tJEpH-007UwI-DK for pgsql-general@arkaria.postgresql.org; Thu, 05 Dec 2024 16:35:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tIxQI-002n9G-4X for pgsql-general@lists.postgresql.org; Wed, 04 Dec 2024 22:00:43 +0000 Received: from mail-ed1-x532.google.com ([2a00:1450:4864:20::532]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tIxQC-0014kQ-H3 for pgsql-general@postgresql.org; Wed, 04 Dec 2024 22:00:42 +0000 Received: by mail-ed1-x532.google.com with SMTP id 4fb4d7f45d1cf-5d0b922a637so302651a12.1 for ; Wed, 04 Dec 2024 14:00:36 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1733349635; x=1733954435; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=nuL78dylQzFhuJlXTxXPnNxqIb9tfuJUa4hKUASqOJQ=; b=QsG3WhjYURGGK0gPlwgqmqk9P7k1DYUVCEk/92/Fu1wz3X2YoTG9Gnt19Vcrox5bEC w3LXK5YQEMJ1GO7qfZzozUlXrzWBO4U803t1GnytiJ3XVZccC8qYtwFvqi/+5QgarSDi ZtLIdbp7sSKJyB1RuPRmjDAxr6dwe0smulxlzlEvg05/AMimkaw0TSZ+tZllaNAlogSv FhX8Ic0p+1AIgbkMETHGUrIvRXkSoPb0XcDEbk4oS/yuQNe+H2ajxJa1clok/iGu4RuB NyfRwycnYzwvQNySf0ZwLLu87+a7zw7FOYUqg+032fNq/yDS8UKilnKurFU+wWq7ywgN zW6A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1733349635; x=1733954435; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=nuL78dylQzFhuJlXTxXPnNxqIb9tfuJUa4hKUASqOJQ=; b=ff/jNTOaU+jCq4ii96i/JM6PIsZCdOzcoMj9BinnwtXRq7cex8g/FvBNOachxrE2Wi p7WAjuKtcqVD4H4H+T14EHFDeDQQ+00s0FrjzRhSPg6h/HSrjic18LIe9RF3RNMemBH9 sxxKEkvdZ7Pj4A4gLIDfoFyS6PYR8WRHDBfi48MjlsDpg43KueD8QdQbIL8DMv8Md1oa 9b9JQepg5IS0zEQSf0J/+FtdlVjMVKq9a7c+Q1adZVRbAruRW79z3HDhwhslvzNmzvXB tBIE3KLpd9aq00Sa6LIRZ38k9crc6J+AZxeVIayJhG599hKuxoLIRle53R0qR3RATo3p oWQg== X-Gm-Message-State: AOJu0YxgqQPig9tObez0u7LtSLEiXPYSa7d+k4vfB6JOmAhzoKyHu6WP jW/RBKWkKwHDhc1UMOP9CzyPXgkdcpq7cE+uB5eg84NZq3Sph7LIXwad04/oRirAYhyNGu5j/Wq K3NuAM9Mh9kL2xcfruvg7deGKGjg= X-Gm-Gg: ASbGncuYQhUiMTed2slhZvBNVsf4jsOpQTUyFUzxy22AKvLpbVjTYKXJxJTExsNz2K2 W6ZhKs1bowxsZwkGyCubwaMV5EVG2xS0Vg9DKaNV3Np0nbA8wQkT0G51In7U= X-Google-Smtp-Source: AGHT+IEspxQ4mFtFscZumVmAgYqFe+q21BehIZgFA/d/sJitaclJUP+xu6PqzCGDgRLPi6yQsCdtfZpVDJjlXFY3Wx0= X-Received: by 2002:a05:6402:510e:b0:5d0:224b:d54e with SMTP id 4fb4d7f45d1cf-5d10cb9a4aemr7750583a12.33.1733349634663; Wed, 04 Dec 2024 14:00:34 -0800 (PST) MIME-Version: 1.0 References: <928c165be24ee307c2fbaa29a2c6360eb8a00e02.camel@cybertec.at> In-Reply-To: <928c165be24ee307c2fbaa29a2c6360eb8a00e02.camel@cybertec.at> From: Bolaji Wahab Date: Wed, 4 Dec 2024 23:00:23 +0100 Message-ID: Subject: Re: Clarification of behaviour when dropping partitions To: Laurenz Albe Cc: pgsql-general@postgresql.org Content-Type: multipart/alternative; boundary="0000000000003eed3b062878e948" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003eed3b062878e948 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Yes, this is what I have done. But the whole point of declaring the foreign key constraint on the partitioned table is to have it automatically created on subsequent/future partitions. On Wed, Dec 4, 2024 at 6:20=E2=80=AFPM Laurenz Albe wrote: > On Wed, 2024-12-04 at 14:22 +0100, Bolaji Wahab wrote: > > I have these two partitioned tables, with referential integrity. The > tables > > are structured in such a way that we have 1 to 1 mapping between their > > partitions. This is achieved with a foreign key. > > > > CREATE TABLE parent ( > > partition_date date NOT NULL, > > id uuid NOT NULL, > > external_transaction_id uuid NOT NULL, > > > > CONSTRAINT parent_pkey > > PRIMARY KEY (id, partition_date), > > > > CONSTRAINT parent_external_transaction_id_key > > UNIQUE (external_transaction_id, partition_date) > > ) PARTITION BY RANGE (partition_date); > > > > CREATE TABLE parent_2024_12_01 > > PARTITION OF public.parent > > FOR VALUES FROM ('2024-12-01') TO ('2024-12-02'); > > > > CREATE TABLE parent_2024_12_02 > > PARTITION OF public.parent > > FOR VALUES FROM ('2024-12-02') TO ('2024-12-03'); > > > > [...] > > > > CREATE TABLE child ( > > partition_date date NOT NULL, > > transaction_id uuid NOT NULL, > > key text NOT NULL, > > value text NOT NULL, > > > > CONSTRAINT child_pkey > > PRIMARY KEY (transaction_id, key, partition_date), > > > > CONSTRAINT child_transaction_id_fkey > > FOREIGN KEY (transaction_id, partition_date) > > REFERENCES parent (id, partition_date) > > ) PARTITION BY RANGE (partition_date); > > > > CREATE TABLE child_2024_12_01 > > PARTITION OF child > > FOR VALUES FROM ('2024-12-01') TO ('2024-12-02'); > > > > CREATE TABLE child_2024_12_02 > > PARTITION OF public.child > > FOR VALUES FROM ('2024-12-02') TO ('2024-12-03'); > > I recommend that you don't create the foreign key constraint between the > partitioned tables, but between the individual partitions. > > That will make detaching and dropping partitions easier, and you will hav= e > the same integrity guarantees. > > Yours, > Laurenz Albe > --0000000000003eed3b062878e948 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Yes, this is what I have done.
But the whole point of = declaring the foreign key constraint on the partitioned table is to have it= automatically created on subsequent/future partitions.

On Wed, Dec 4, 2024 at 6:20=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2024-12-04= at 14:22 +0100, Bolaji Wahab wrote:
> I have these two partitioned tables, with=C2=A0referential integrity. = The tables
> are structured=C2=A0in such=C2=A0a way that we have 1 to 1 mapping bet= ween their
> partitions. This is achieved with a foreign key.
>
> CREATE TABLE parent (
> =C2=A0 =C2=A0 partition_date date NOT NULL,
> =C2=A0 =C2=A0 id uuid NOT NULL,
> =C2=A0 =C2=A0 external_transaction_id uuid NOT NULL,
>
> =C2=A0 =C2=A0 CONSTRAINT parent_pkey
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 PRIMARY KEY (id, partition_date),
>
> =C2=A0 =C2=A0 CONSTRAINT parent_external_transaction_id_key
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 UNIQUE (external_transaction_id, partition= _date)
> ) PARTITION BY RANGE (partition_date);
>
> CREATE TABLE parent_2024_12_01
> =C2=A0 =C2=A0 PARTITION OF public.parent
> =C2=A0 =C2=A0 FOR VALUES FROM ('2024-12-01') TO ('2024-12-= 02');
>
> CREATE TABLE parent_2024_12_02
> =C2=A0 =C2=A0 PARTITION OF public.parent
> =C2=A0 =C2=A0 FOR VALUES FROM ('2024-12-02') TO ('2024-12-= 03');
>
> [...]
>
> CREATE TABLE child (
> =C2=A0 =C2=A0 partition_date date NOT NULL,
> =C2=A0 =C2=A0 transaction_id uuid NOT NULL,
> =C2=A0 =C2=A0 key =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0text NOT NU= LL,
> =C2=A0 =C2=A0 value =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0text NOT NULL, >
> =C2=A0 =C2=A0 CONSTRAINT child_pkey
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 PRIMARY KEY (transaction_id, key, partitio= n_date),
>
> =C2=A0 =C2=A0 CONSTRAINT child_transaction_id_fkey
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 FOREIGN KEY (transaction_id, partition_dat= e)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 REFERENCES parent (id, partition_date)
> ) PARTITION BY RANGE (partition_date);
>
> CREATE TABLE child_2024_12_01
> =C2=A0 =C2=A0 PARTITION OF child
> =C2=A0 =C2=A0 FOR VALUES FROM ('2024-12-01') TO ('2024-12-= 02');
>
> CREATE TABLE child_2024_12_02
> =C2=A0 =C2=A0 PARTITION OF public.child
> =C2=A0 =C2=A0 FOR VALUES FROM ('2024-12-02') TO ('2024-12-= 03');

I recommend that you don't create the foreign key constraint between th= e
partitioned tables, but between the individual partitions.

That will make detaching and dropping partitions easier, and you will have<= br> the same integrity guarantees.

Yours,
Laurenz Albe
--0000000000003eed3b062878e948--