pg.ddx.io pgsql-in-general@postgresql.org mailing list archive
help / color / mirror / Atom feedRegarding use of single column as primary key on partitioned table
6+ messages / 5 participants
[nested] [flat]
* Regarding use of single column as primary key on partitioned table
@ 2024-09-28 04:25 Durgamahesh Manne <maheshpostgres9@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Durgamahesh Manne @ 2024-09-28 04:25 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org; pgsql-in-general
Hi Team
test=> CREATE TABLE public.bet (
betid int4 NOT NULL,
externalbetid text NULL,
externalsystem text NULL,
placedon timestamptz NULL,
createdon timestamptz NULL
) partition by list (placedon) ;
CREATE TABLE
test=> alter table public.bet add primary key (betid);
ERROR: unique constraint on partitioned table must include all
partitioning columns
DETAIL: PRIMARY KEY constraint on table "bet" lacks column "placedon"
which is part of the partition key.
test=>
Can't we use primary key on singal column(betid) on partitioned table
rather than using composite key (placedon,betid)?
Regards,
Durga Mahesh
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Regarding use of single column as primary key on partitioned table
@ 2024-09-28 04:39 David G. Johnston <david.g.johnston@gmail.com>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2024-09-28 04:39 UTC (permalink / raw)
To: Durgamahesh Manne <maheshpostgres9@gmail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>; pgsql-in-general
On Friday, September 27, 2024, Durgamahesh Manne <maheshpostgres9@gmail.com>
wrote:
>
> ERROR: unique constraint on partitioned table must include all
> partitioning columns
> DETAIL: PRIMARY KEY constraint on table "bet" lacks column "placedon"
> which is part of the partition key.
> test=>
>
> 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 index
can only span a single partition.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Regarding use of single column as primary key on partitioned table
@ 2024-09-28 04:39 Christophe Pettus <xof@thebuild.com>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Christophe Pettus @ 2024-09-28 04:39 UTC (permalink / raw)
To: Durgamahesh Manne <maheshpostgres9@gmail.com>; +Cc: pgsql-general@lists.postgresql.org; pgsql-in-general
> On Sep 27, 2024, at 21:25, Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:
>
> Can't we use primary key on singal column(betid) on partitioned table rather than using composite key (placedon,betid)?
No. Any unique constraint on a partitioned table must include the partition key, including a primary key constraint.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Regarding use of single column as primary key on partitioned table
@ 2024-09-28 04:49 Ron Johnson <ronljohnsonjr@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ron Johnson @ 2024-09-28 04:49 UTC (permalink / raw)
To: pgsql-general <pgsql-general@postgresql.org>
On Sat, Sep 28, 2024 at 12:39 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Friday, September 27, 2024, Durgamahesh Manne <
> maheshpostgres9@gmail.com> wrote:
>
>>
>> ERROR: unique constraint on partitioned table must include all
>> partitioning columns
>> DETAIL: PRIMARY KEY constraint on table "bet" lacks column "placedon"
>> which is part of the partition key.
>> test=>
>>
>> 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 index
> 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.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> crustacean!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Regarding use of single column as primary key on partitioned table
@ 2024-09-28 04:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2024-09-28 04:55 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-general <pgsql-general@postgresql.org>
Ron Johnson <ronljohnsonjr@gmail.com> writes:
> On Sat, Sep 28, 2024 at 12:39 AM 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 index
>> 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. If that's what you
want, don't partition your table.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Regarding use of single column as primary key on partitioned table
@ 2024-09-28 05:15 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Ron Johnson @ 2024-09-28 05:15 UTC (permalink / raw)
To: pgsql-general <pgsql-general@postgresql.org>
On Sat, Sep 28, 2024 at 12:55 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ron Johnson <ronljohnsonjr@gmail.com> writes:
> > On Sat, Sep 28, 2024 at 12:39 AM 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 index
> >> 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).
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> crustacean!
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-09-28 05:15 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-28 04:25 Regarding use of single column as primary key on partitioned table Durgamahesh Manne <maheshpostgres9@gmail.com>
2024-09-28 04:39 ` David G. Johnston <david.g.johnston@gmail.com>
2024-09-28 04:49 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-09-28 04:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-09-28 05:15 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-09-28 04:39 ` Christophe Pettus <xof@thebuild.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox