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 1suPoP-006Xni-N2 for pgsql-general@arkaria.postgresql.org; Sat, 28 Sep 2024 05:16:10 +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 1suPoO-002tl9-HI for pgsql-general@arkaria.postgresql.org; Sat, 28 Sep 2024 05:16:08 +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 1suPoO-002tl0-6E for pgsql-general@lists.postgresql.org; Sat, 28 Sep 2024 05:16:08 +0000 Received: from mail-oa1-x2c.google.com ([2001:4860:4864:20::2c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1suPoH-001Sxi-A8 for pgsql-general@postgresql.org; Sat, 28 Sep 2024 05:16:07 +0000 Received: by mail-oa1-x2c.google.com with SMTP id 586e51a60fabf-2870bd5e1f7so885205fac.3 for ; Fri, 27 Sep 2024 22:16:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1727500560; x=1728105360; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=zIX5KcGzXWPyAiQmnMyss/ABtDz+XxILPnOfrY1Xv7s=; b=Zu2MH7ORXR+mdX+olm1FTUtI+M1bloDQBgQFOf6PFxowj5qaTULJD0wjacT6IJwaeR IdMUBl/NMmX2wS2lK1PSL0vHdVOvUqNJ4kzKCaCaaVOHAOoZDyUZqf65FxZl4RQqQiYV GEBISTcJkr6q9cE1X75vuMKTbPO2PgUP4Qoyv0JZQoNk1K/njzGaNMCQr/GOPJKllwF1 v+jusPSoIKo9aldOyUOQgBX/usVG50AzBjhUpZE/17zkzG7i76OlvlxGl24LI1BP0NWR p2/S1KGJ2bVWVxU2uv+Ahmgzj8uHnGtkRYdpogHBebkpa077tlHSzYvoPEhH56qWBSP6 q9Rg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1727500560; x=1728105360; h=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=zIX5KcGzXWPyAiQmnMyss/ABtDz+XxILPnOfrY1Xv7s=; b=fIgIubW34HePpCBC9I89klp4olsdQvb6GRfmysBZrvjjYLuIoPD9GGH1vLV8lfX+Wf WmilRpFnsHiFxsfSJkrebNqzCwpLB6dZhEDXRP0RXDnjjIVV3olO6+q4Wke2ayh7yCnQ sGPOfPBtFNItQJyqIqDWdhn4vTQtGaEBeQswDT0D15ogUBrh6eiDlLLi6MNymK8Sl+w2 r430PafXNf8YjglSZT7A5YpfjY01Ptp2pr4b2arMtcBG4XdizBdvByngjbtPmyWdngd5 i9XjdjyKI0a5GAij4bcjADCGoCAule9mxiXk3eFrjBNNs/VsBoZjZoI56SQo6ELKcbxD v9vw== X-Gm-Message-State: AOJu0YwIlHktrekOFzVkDS/5Mg8yVNTYs8l8+V2KWiiHWwHb0IeO3y+l uKqHAM9EX+aZjGvnIbuRgf5ZMg87eHnT6PmDm/rh7QqXSYXeiilsYTiObpCEJhCgbIkkz0JWpil Yd3Hv02/MNzQE3v3hhHujj8eFXPG+xw== X-Google-Smtp-Source: AGHT+IHcThUorxm4Cevyl/IPyJLBZHm701MM6eH5/5ldNlmKOwR1WdMazDj6s3GrTLOzi3nEyh8o8HEf6Qc73prUGyA= X-Received: by 2002:a05:6870:e6d4:b0:277:d360:8971 with SMTP id 586e51a60fabf-28710c2489emr3798159fac.43.1727500560563; Fri, 27 Sep 2024 22:16:00 -0700 (PDT) MIME-Version: 1.0 References: <771900.1727499306@sss.pgh.pa.us> In-Reply-To: <771900.1727499306@sss.pgh.pa.us> From: Ron Johnson Date: Sat, 28 Sep 2024 01:15:49 -0400 Message-ID: Subject: Re: Regarding use of single column as primary key on partitioned table To: pgsql-general Content-Type: multipart/alternative; boundary="00000000000043050906232711a6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000043050906232711a6 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sat, Sep 28, 2024 at 12:55=E2=80=AFAM Tom Lane wrote= : > Ron Johnson writes: > > On Sat, Sep 28, 2024 at 12:39=E2=80=AFAM David G. Johnston < > > david.g.johnston@gmail.com> wrote: > >> On Friday, September 27, 2024, Durgamahesh Manne < > >>> Can't we use primary key on singal column(betid) on partitioned table > >>> rather than using composite key (placedon,betid)? > > >> No. It would be misleading to allow such a thing because a unique ind= ex > >> can only span a single partition. > > > That is IMO a serious (and probably unfixable, given how PG stores > tables) > > flaw in PG's partitioning design. > > You can call it a flaw if you want, but it's an intentional design > limitation. The only way to relax it would be to invent global > indexes (that is, single indexes covering the entire partitioning > tree), which would basically throw away every advantage of making > a partitioned structure in the first place. We've had this discussion before. As a DBA, I don't think that requiring that global indices be dropped before, and recreated after, ATTACH or DETACH is onerous, but you do. (I did that every six months for 15 years on a legacy system which supports global indices. One other feature that it had was the ability to create multiple indices on the same table, at the same time, in different transactions, by setting the isolation level to SHARED DATA DEFINITION, which was specifically and only for that situation.) If that's what you want, don't partition your table. > Or accept partitioning by the synthetic PK (which is often -- though not always -- a reasonable approximation of timestamp). --=20 Death to , and butter sauce. Don't boil me, I'm still alive. crustacean! --00000000000043050906232711a6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Sat, Sep 28, 2024 at 12:55=E2=80=AFAM = Tom Lane <tgl@sss.pgh.pa.us>= wrote:
Ron Johnson <ronljohnsonjr@gmail.com> writes:
> On Sat, Sep 28, 2024 at 12:39=E2=80=AFAM David G. Johnston <
> david.= g.johnston@gmail.com> wrote:
>> On Friday, September 27, 2024, Durgamahesh Manne <
>>> Can't we use primary key on singal column(betid) on partit= ioned table
>>> rather than using composite key (placedon,betid)?

>> No.=C2=A0 It would be misleading to allow such a thing because a u= nique index
>> can only span a single partition.

> That is IMO a serious (and probably unfixable, given how PG stores tab= les)
> flaw in PG's partitioning design.

You can call it a flaw if you want, but it's an intentional design
limitation.=C2=A0 The only way to relax it would be to invent global
indexes (that is, single indexes covering the entire partitioning
tree), which would basically throw away every advantage of making
a partitioned structure in the first place.=C2=A0

We've had this discussion before.=C2=A0 As a DBA, I don't th= ink that requiring that global indices be dropped before, and recreated aft= er, ATTACH or DETACH is onerous, but you do.

(I di= d that every six months for 15 years on a legacy system which supports glob= al indices.=C2=A0 One other feature that it had was the ability to create m= ultiple indices on the same table, at the same time, in different transacti= ons, by setting the isolation level to SHARED DATA DEFINITION, which was sp= ecifically and only for that situation.)

If that's what you=C2=A0want, don&= #39;t partition your table.

Or accept p= artitioning by the synthetic PK (which is often -- though not always -- a r= easonable approximation of timestamp).

--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> cru= stacean!
--00000000000043050906232711a6--