MIME-Version: 1.0
In-Reply-To: <CAB+=1TVZhPdiCtf0W1CUHSnHEH0ALG=aWszvnS56y+V1+de7Hw@mail.gmail.com>
References: <CAB+=1TViAodXqYbgB+xiXL=6AShPacH5G9PB2hbcd+gHR_Nb9w@mail.gmail.com>
 <7fa81130-0b33-4e53-bb32-39d84f06c680@aklaver.com> <CAB+=1TVZhPdiCtf0W1CUHSnHEH0ALG=aWszvnS56y+V1+de7Hw@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Sat, 31 Aug 2024 20:43:06 -0700
Message-ID: <CAKFQuwa_A7jT_M__U2GjewyeEBhARud5_=Mmd2rrF2eo34gKyA@mail.gmail.com>
Subject: Re: Partitioning and unique key
To: veem v <veema0000@gmail.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000005b1adf0621069f72"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwa_A7jT_M__U2GjewyeEBhARud5_%3DMmd2rrF2eo34gKyA%40mail.gmail.com>
Precedence: bulk

--0000000000005b1adf0621069f72
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Saturday, August 31, 2024, veem v <veema0000@gmail.com> 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 mo=
re
> 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=E2=80=99d suggest trying to just build a new partitioned table that is co=
rrectly
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.

--0000000000005b1adf0621069f72
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Saturday, August 31, 2024, veem v &lt;<a href=3D"mailto:veema0000@gmail.=
com">veema0000@gmail.com</a>&gt; wrote:<br><blockquote class=3D"gmail_quote=
" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><=
div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div =
dir=3D"ltr" class=3D"gmail_attr">1) if it&#39;s technically possible to hav=
e a unique key on only the transaction_id column having the partition key o=
n the transaction_timestamp, because the table is going to be queried/purge=
d based on the transaction_timestamp?<br></div></div></div></blockquote><di=
v><br></div><div>There is presently no such thing as a cross-partition uniq=
ue constraint.=C2=A0 If you define the constraint on the [partitioned] tabl=
e 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.=C2=A0 If you target the partitions directly wi=
th the unique index or constraint no such limitation should exist.</div><di=
v>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;=
border-left:1px #ccc solid;padding-left:1ex"><div dir=3D"ltr"><div class=3D=
"gmail_quote"><div><br></div><div>iii)And then alter the datatype of the pa=
rtition key transaction_date to DATE in one shot at the table level(which s=
hould be fast as its having more granularity as compare to existing timesta=
mptype, so should be catalog or dictionary change only), and that will rema=
in the part of composite PK (transaction_id,transaction_<wbr>date).<br></di=
v></div></div></blockquote><div><br></div><div>While this might seem logica=
l, 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 rewr=
ite.=C2=A0 Best you could do is leave the timestamptz in place and just tru=
ncate to day so the time is always midnight UTC.</div><div>=C2=A0</div><blo=
ckquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #c=
cc solid;padding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_quote"><div=
>iv) Repeat this step for all child partition tables and then for the paren=
t partition tables.</div></div></div></blockquote><div><br></div><div>I=E2=
=80=99d suggest trying to just build a new partitioned table that is correc=
tly defined. Then populate it.=C2=A0 Add a trigger to the existing one to k=
eep the new one in sync.=C2=A0 Then change your application code to point t=
o the new partitioned table.=C2=A0 At which point the old partitioned table=
 can be dropped.</div><div><br></div><div>David J.</div>

--0000000000005b1adf0621069f72--