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).
