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 1rpBOb-005UP9-Q6 for pgsql-bugs@arkaria.postgresql.org; Tue, 26 Mar 2024 18:19:38 +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 1rpBOa-002pEN-RE for pgsql-bugs@arkaria.postgresql.org; Tue, 26 Mar 2024 18:19:36 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rpBOZ-002p7M-0r for pgsql-bugs@lists.postgresql.org; Tue, 26 Mar 2024 18:19:36 +0000 Received: from fhigh1-smtp.messagingengine.com ([103.168.172.152]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rpBOR-006crf-3t for pgsql-bugs@lists.postgresql.org; Tue, 26 Mar 2024 18:19:34 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailfhigh.nyi.internal (Postfix) with ESMTP id 291FB1140102; Tue, 26 Mar 2024 14:19:25 -0400 (EDT) Received: from imap49 ([10.202.2.99]) by compute2.internal (MEProxy); Tue, 26 Mar 2024 14:19:25 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=eulerto.com; h= cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm3; t=1711477165; x=1711563565; bh=yVqjPIaWlj qB43QCRIaQgBAEdfv/gcJEjlckWeZ4nlw=; b=AQLaXXet7leKsErjkdTrzgrYn+ JOlOz5XceOVqhEDW+hNNxDxInV8SCtcN2s04GFharsNMyIOVYanEjfHR9jtDX80N dHXeiFoOK3vvl/C/3eo3MbLsh9mVszoVOe+6Ou1x8DLIEhtxKHsu3w2+FjHbk2NA FpL76ifYzkLIhUQN5DkNtVhs3B9WUiA+PixdD5FuQ4CfY01bsHt3ZTTtnPaUyrOb LTI2MR3W9k2A6MpdRgiOW1dCNIfZzKr88b8b+bB9YfitPrWimbcxrnHSK0ma/u6t 1K8xPpc136nAWYnr7ESCNdCRJWuYtOIubojDYEAYeHaKabemOjvGALDHNagA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm2; t=1711477165; x=1711563565; bh=yVqjPIaWljqB43QCRIaQgBAEdfv/ gcJEjlckWeZ4nlw=; b=GeilL9oYQoT/0o1sXJiNJ4a4gYikDgv5kOuDzyS8JOCU D9g7ZJ65lI0D6InX1oi6RJkf+mdSvVVV3nxiJQLD/IHBlO8N9hEShs+WpYHb/nBX +5QEsmzhud6SjAyQLO8LrzP02NPmfqcdqtLkU93uVw39xvBk/wUwee81Kop3P0tp ZNnqy1ZfO3J2PXvQOB7I01kn8xHWygsILR4agYkV1pqhSGyutJOAgZ4UOtkV9mi5 WSZ+xg/fLUwGr1wekjg6WhRtXvpdglYWwFTFGxUfwHXfswAMvLyRRY+jtdOD469V 7aj8WJZX0Grn3/lHv931cDJK0nGucxEHbLIJW+VfYw== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledruddufedgudduudcutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecunecujfgurhepofgfggfkjghffffhvffutgesrg dtreerreertdenucfhrhhomhepfdfguhhlvghrucfvrghvvghirhgrfdcuoegvuhhlvghr segvuhhlvghrthhordgtohhmqeenucggtffrrghtthgvrhhnpeekgeefhefhudeftdefje ekveevvdfhjeejgffhgeeitdejfedttdetfeeljeelgeenucffohhmrghinhepghhithhh uhgsrdgtohhmpdhpohhsthhgrhgvshhqlhdrohhrghdpvghnthgvrhhprhhishgvuggsrd gtohhmnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomhep vghulhgvrhesvghulhgvrhhtohdrtghomh X-ME-Proxy: Feedback-ID: i0c21471d:Fastmail Received: by mailuser.nyi.internal (Postfix, from userid 501) id B27A015A0092; Tue, 26 Mar 2024 14:19:24 -0400 (EDT) X-Mailer: MessagingEngine.com Webmail Interface User-Agent: Cyrus-JMAP/3.11.0-alpha0-328-gc998c829b7-fm-20240325.002-gc998c829 MIME-Version: 1.0 Message-Id: <0370353a-56a3-4fb7-aa12-46422d05b2b5@app.fastmail.com> In-Reply-To: <18402-23452305346476d6@postgresql.org> References: <18402-23452305346476d6@postgresql.org> Date: Tue, 26 Mar 2024 15:18:49 -0300 From: "Euler Taveira" To: gparc@online.fr, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition Content-Type: multipart/alternative; boundary=a6370c2f942b4973b93a1ec9bd1ff491 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --a6370c2f942b4973b93a1ec9bd1ff491 Content-Type: text/plain On Wed, Mar 20, 2024, at 11:29 AM, PG Bug reporting form wrote: > CREATE TABLE master_2026 (LIKE master INCLUDING DEFAULTS INCLUDING > CONSTRAINTS); > ALTER TABLE master_2026 ADD CONSTRAINT master_2026_ck CHECK (millesime = > '2026'); > DO > $do$ > BEGIN > INSERT INTO master_2026 VALUES (generate_series(1,100000), '2026'); > END > $do$; > CREATE UNIQUE INDEX CONCURRENTLY master_2026_pkey ON master_2026 (id, > millesime); > ALTER TABLE master ATTACH PARTITION master_2026 FOR VALUES IN ('2026'); You don't add a primary key or unique constraint here. Hence, the ALTER TABLE .. ATTACH PARTITION doesn't consider the master_2026_pkey as a candidate for primary key index. Add the following command and your index will be used. ALTER TABLE master_2026 ADD CONSTRAINT master_2026_pk PRIMARY KEY USING INDEX master_2026_pkey; See [1]. Documentation [2] says UNIQUE and PRIMARY KEY constraints from the parent table will be created in the partition, if they don't already exist. [1] https://github.com/postgres/postgres/blob/REL_16_STABLE/src/backend/commands/tablecmds.c#L18165-L18185 [2] https://www.postgresql.org/docs/16/sql-altertable.html -- Euler Taveira EDB https://www.enterprisedb.com/ --a6370c2f942b4973b93a1ec9bd1ff491 Content-Type: text/html Content-Transfer-Encoding: quoted-printable
On Wed, Mar 20,= 2024, at 11:29 AM, PG Bug reporting form wrote:
CREATE TABLE master_2026 (LIKE mas= ter INCLUDING DEFAULTS INCLUDING
CONSTRAINTS);
ALTER TABLE master_2026 ADD CONSTRAINT master_2026_ck CHECK (millesim= e =3D
'2026');
DO 
$do$
BEGIN
      INSERT I= NTO master_2026 VALUES (generate_series(1,100000), '2026');
END
$do$;
CREATE UNIQUE INDEX CONCURRENTLY= master_2026_pkey ON master_2026 (id,
millesime);
ALTER TABLE master ATTACH PARTITION master_2026 FOR VALUES IN ('20= 26');

You don't add a primary = key or unique constraint here. Hence, the ALTER TABLE ..
A= TTACH PARTITION doesn't consider the master_2026_pkey as a candidate for=
primary key index. Add the following command and your ind= ex will be used.

ALTER TABLE master_2026
        ADD CONSTRAINT ma= ster_2026_pk PRIMARY KEY USING INDEX master_2026_pkey;
See [1]. Documentation [2] says

UNIQUE and PRIMARY KEY constraints from the parent table will be create= d in the
partition, if they don't already exist.
=




--
Euler Taveira

--a6370c2f942b4973b93a1ec9bd1ff491--