MIME-Version: 1.0
References: <CAB+=1TViAodXqYbgB+xiXL=6AShPacH5G9PB2hbcd+gHR_Nb9w@mail.gmail.com>
 <7fa81130-0b33-4e53-bb32-39d84f06c680@aklaver.com>
In-Reply-To: <7fa81130-0b33-4e53-bb32-39d84f06c680@aklaver.com>
From: veem v <veema0000@gmail.com>
Date: Sun, 1 Sep 2024 08:36:19 +0530
Message-ID: <CAB+=1TVZhPdiCtf0W1CUHSnHEH0ALG=aWszvnS56y+V1+de7Hw@mail.gmail.com>
Subject: Re: Partitioning and unique key
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000007894c20621061c5c"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TVZhPdiCtf0W1CUHSnHEH0ALG%3DaWszvnS56y%2BV1%2Bde7Hw%40mail.gmail.com>
Precedence: bulk

--0000000000007894c20621061c5c
Content-Type: text/plain; charset="UTF-8"

On Sun, 1 Sept 2024 at 03:58, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

>
> The model is at odds with itself and untenable. If the tables hold
> multiple rows for a given transaction_id  then you cannot have a
> PK/Unique constraint on that column. Seems there is a decided lack of
> any planning. The only way I can see this happening is consolidating all
> the duplicate transaction_id rows into a single row for each
> transaction_id. That then leads to the question of how to do that and
> retain the 'correct' information from the selection of rows for each
> transaction_id.
>
>
Yes we had messed up the data for now and have multiple records for each
transaction_id persisted and thus we need to fix the data. But more than
that , as I stated , I wanted to understand first
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?

2) Additionally we were thinking if above is technically not possible, then
the maximum granularity which we can have for each transaction_id will be a
day, so the partition key transaction_timestmp can be truncated to have
only date component but no time component. So the primary key will
be (transaction_id, transaction_date). But we also don't want to lose the
time component and persist the existing data of transaction_timestmp (which
will have a time component in it, in a separate column).

And in above case , for fixing the existing data in least disruptive way,
as we have currently duplicate transaction_id inserted into the table
already because of the composite primary key(transaction_id,
transaction_timestmp).Can we simply
 i)rename the existing column transaction_timestmp to transaction_date and
then add new column transaction_timestmp using the values of existing
column partition by partition.
ii)And then delete the duplicate data using query something as below , each
partition by partition.
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).
iv) Repeat this step for all child partition tables and then for the parent
partition tables.

Will this technique be the most efficient way of fixing this mess?

WITH ranked_records AS (
            SELECT column1_id, column2_timestamptz,
                   ROW_NUMBER() OVER (PARTITION BY column1_id,
date_trunc('day', column2_timestamptz)
                                      ORDER BY column2_timestamptz DESC) AS
rn
            FROM partition_name
        )
        DELETE FROM partition_name T1
        WHERE EXISTS (
            SELECT 1
            FROM ranked_records T2
            WHERE T1.column1_id = T2.column1_id
              AND T1.column2_timestamptz = T2.column2_timestamptz
              AND T2.rn > 1
        )

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Sun, 1 Sept 2024 at 03:58, Adrian Klav=
er &lt;<a href=3D"mailto:adrian.klaver@aklaver.com">adrian.klaver@aklaver.c=
om</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margi=
n:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex=
"><br>
The model is at odds with itself and untenable. If the tables hold <br>
multiple rows for a given transaction_id=C2=A0 then you cannot have a <br>
PK/Unique constraint on that column. Seems there is a decided lack of <br>
any planning. The only way I can see this happening is consolidating all <b=
r>
the duplicate transaction_id rows into a single row for each <br>
transaction_id. That then leads to the question of how to do that and <br>
retain the &#39;correct&#39; information from the selection of rows for eac=
h <br>
transaction_id.<br><br></blockquote><div><br></div><div>Yes we had messed u=
p the data for now and have multiple records for each transaction_id persis=
ted and thus we need to fix the data. But more than that , as I stated , I =
wanted to understand first=C2=A0</div><div>1) if it&#39;s technically possi=
ble to have a unique key on only the transaction_id column having the parti=
tion key on the transaction_timestamp, because the table is going to be que=
ried/purged based on the transaction_timestamp?</div><div><br></div><div>2)=
 Additionally we were thinking if above is technically not possible, then t=
he maximum granularity which we can have for each transaction_id will be a =
day, so the partition key transaction_timestmp can be truncated to have onl=
y date component but no time component. So the primary key will be=C2=A0(tr=
ansaction_id, transaction_date). But we also don&#39;t want to lose the tim=
e component=C2=A0and persist the existing data of transaction_timestmp (whi=
ch will have a time component in it, in a separate column).</div><div><br><=
/div><div>And in above case , for fixing the existing data in least disrupt=
ive way, as we have currently duplicate transaction_id inserted into the ta=
ble already because of the composite=C2=A0primary key(transaction_id, trans=
action_timestmp).Can we simply</div><div>=C2=A0i)rename the existing column=
 transaction_timestmp to transaction_date and then add new column transacti=
on_timestmp=C2=A0using the values of existing column partition by partition=
.=C2=A0</div><div>ii)And then delete the duplicate data using query somethi=
ng as below , each partition by partition.=C2=A0</div><div>iii)And then alt=
er the datatype of the partition key transaction_date to DATE in one shot a=
t the table level(which should be fast as its having more granularity as co=
mpare to existing timestamptype, so should be catalog or dictionary change =
only), and that will remain the part of composite PK (transaction_id,transa=
ction_date).</div><div>iv) Repeat this step for all child partition tables =
and then for the parent partition tables.=C2=A0</div><div><br></div><div>Wi=
ll this technique be the most efficient way of fixing this mess?=C2=A0</div=
><div><br></div><div>WITH ranked_records AS (<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 SELECT column1_id, column2_timestamptz,<br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0ROW_NUMBER() OVER (P=
ARTITION BY column1_id, date_trunc(&#39;day&#39;, column2_timestamptz)<br>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ORDER BY column=
2_timestamptz DESC) AS rn<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM=
 partition_name<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 )<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 DELETE FROM partition_name T1<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE E=
XISTS (<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT 1<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM ranked_records T2<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 WHERE T1.column1_id =3D T2.column1_id<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 AND T1.column2_timestamptz =3D T2=
.column2_timestamptz<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 AN=
D T2.rn &gt; 1<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 )<br></div><div><br></div></d=
iv></div>

--0000000000007894c20621061c5c--