public inbox for [email protected]  

help / color / mirror / Atom feed

From: David G. Johnston <[email protected]>
To: veem v <[email protected]>
Cc: Adrian Klaver <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Partitioning and unique key
Date: Sat, 31 Aug 2024 20:43:06 -0700
Message-ID: <CAKFQuwa_A7jT_M__U2GjewyeEBhARud5_=Mmd2rrF2eo34gKyA@mail.gmail.com> (raw)
In-Reply-To: <CAB+=1TVZhPdiCtf0W1CUHSnHEH0ALG=aWszvnS56y+V1+de7Hw@mail.gmail.com>
References: <CAB+=1TViAodXqYbgB+xiXL=6AShPacH5G9PB2hbcd+gHR_Nb9w@mail.gmail.com>
	<[email protected]>
	<CAB+=1TVZhPdiCtf0W1CUHSnHEH0ALG=aWszvnS56y+V1+de7Hw@mail.gmail.com>

On Saturday, August 31, 2024, veem v <[email protected]> wrote:

>
> 1) if it's technically possible to have a unique key on only the
> transaction_id column having the partition key on the
> transaction_timestamp, because the table is going to be queried/purged
> based on the transaction_timestamp?
>

There is presently no such thing as a cross-partition unique constraint.
If you define the constraint on the [partitioned] table the documentation
is perfectly clear, as are I believe the error messages, that it will
require all partitioning columns to be included - since that is what
happens in reality.  If you target the partitions directly with the unique
index or constraint no such limitation should exist.


>
> iii)And then alter the datatype of the partition key transaction_date to
> DATE in one shot at the table level(which should be fast as its having more
> granularity as compare to existing timestamptype, so should be catalog or
> dictionary change only), and that will remain the part of composite PK
> (transaction_id,transaction_date).
>

While this might seem logical, in reality date and timestamptz are
different fixed-width data types and thus any attempt to change from one to
the other will involve a table rewrite.  Best you could do is leave the
timestamptz in place and just truncate to day so the time is always
midnight UTC.


> iv) Repeat this step for all child partition tables and then for the
> parent partition tables.
>

I’d suggest trying to just build a new partitioned table that is correctly
defined. Then populate it.  Add a trigger to the existing one to keep the
new one in sync.  Then change your application code to point to the new
partitioned table.  At which point the old partitioned table can be dropped.

David J.

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Partitioning and unique key
  In-Reply-To: <CAKFQuwa_A7jT_M__U2GjewyeEBhARud5_=Mmd2rrF2eo34gKyA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox