Subject: Re: Partitioning and unique key

From: "David G. Johnston" <[email-masked]>
Date: Sat, 31 Aug 2024 20:43:06 -0700

On Saturday, August 31, 2024, veem v <[email-masked]> 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.

---

From: veem v <[email-masked]>
Date: Sun, 1 Sep 2024 10:03:21 +0530

> timestamptz in place and just truncate to day so the time is always
Here  , if we keep the PK column as is i.e. the transaction_timestamp as
timestamptz but truncate the time component , in that case again in future
if someone tries to insert(using insert on conflict) data into the table
with time component , it will get consumed and will not be restricted by
the PK constraint. So I was trying to make the data type also as DATE for
the transaction_timestap column.

As in this case anyway we have to create another column to populate the
date+timestamp values as we cant throw those values away per business need,
so we will be kind of rewriting the table.So is it okay if if we will

1) Detach all the partitions.
2)Do the alter using "only" key word in table level. (For adding new column
transaction_timestamp_new  to hold date+timestamp value and also altering
the existing transaction_timestamp column to DATE from type timestamptz).
3)Then do the data fix(delete the duplicates) and alter the column, one
partition at a time for all of the partitions and once done , attach  those
partitions one by one.
5)Rename the columns at table level.Hope this won't need any table rewrite.

Is there any downside if we go by the above approach?

---

From: veem v <[email-masked]>
Date: Sun, 1 Sep 2024 11:38:15 +0530

On Sun, 1 Sept 2024 at 10:03, veem v <[email-masked]> wrote:

>
> On Sun, 1 Sept 2024 at 09:13, David G. Johnston <
> [email-masked]> wrote:
>
> 2)Do the alter using "only" key word in table level. (For adding new
> column transaction_timestamp_new  to hold date+timestamp value and also
> altering the existing transaction_timestamp column to DATE from type
> timestamptz).

Or do you mean to say there is no way we can modify the data type of a
partition key even by detaching the partitions one by one? And thus we may
have only way left is to create the table from scratch with partitions and
populate the data to it? I was avoiding this because we have many indexes
also in it , so creating from scratch means creating those indexes again.
So I wanted to achieve it by detaching partitions, doing the required
change and attaching it again.

---

From: veem v <[email-masked]>
Date: Sun, 1 Sep 2024 17:44:14 +0530

On Sun, 1 Sept 2024 at 11:38, veem v <[email-masked]> wrote:

>>>> 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
I tried  by detaching the partitions, but still then it's not allowing me
to alter the DAT TYPE of the partition key and throwing error as below.

ERROR:  cannot alter column "<patition_key_column>" because it is part of
the partition key of relation "<table_name>"

Now I am thinking if it's really going to get too complex if we try to
stick with the partition detach and attach strategy. As a few teammates say
, having a new column added with just a date type and then drop the
existing FK and PK first and then detach all the partitions, and attach the
partitions back using the new DATE column. and then recreate the PK again.
Btw we have ~5 partition tables with parent child relationship on which
this fix has to be applied. So I'm still wondering the best way possible
for fixing this issue.