Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1skjTU-002Sz1-V7 for pgsql-general@arkaria.postgresql.org; Sun, 01 Sep 2024 12:14:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1skjTT-00E14V-RN for pgsql-general@arkaria.postgresql.org; Sun, 01 Sep 2024 12:14:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1skjTT-00E14N-Cx for pgsql-general@lists.postgresql.org; Sun, 01 Sep 2024 12:14:31 +0000 Received: from mail-lf1-x130.google.com ([2a00:1450:4864:20::130]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1skjTR-0008GF-2z for pgsql-general@lists.postgresql.org; Sun, 01 Sep 2024 12:14:31 +0000 Received: by mail-lf1-x130.google.com with SMTP id 2adb3069b0e04-53349ee42a9so4450397e87.3 for ; Sun, 01 Sep 2024 05:14:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1725192868; x=1725797668; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=hjYNdna2pT8O5umNAeZh0HaaHF95ZijDS/s/chiZvww=; b=bJMFOeutE+c9QTAI/OUyZKGYK7k5LFgPDXbz6uQpkLhIagp/TKJ/Q3ki3aP3n+OtS3 K5YnjdTSHN9QsDejaTNU07F0Zzfk8kRtCiknUq8LC5TCzik7N56s7wwjPBDmxUKLREGX wDLIkPB4aOL+TtucewbMWyIi5kAtVx2a7EMBaqHmhy94Ql+wTEHAYCLMoJun5nx1WCFg xQ3wl1QXQTA2HoEB4xRpqv/x2XvTSRJLTLol/AbzCl5p+fkEVLLTaUYTPuNNC0hyIMB6 V2A0ErxDRbM1YHZb+oQ3nyya8tIE97Bubsxkdl7QDXOUiRwxyerIaP8nvy1a5WwbwX8L KBzw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1725192868; x=1725797668; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=hjYNdna2pT8O5umNAeZh0HaaHF95ZijDS/s/chiZvww=; b=RWe5/Ojb6RpeXB7GQF/K8FiYBuOn9E5xbvYPKd9RX770u7Vm3dgvNgXnEI8YDVSzi+ VCmzDl0UIN2iHm8etigjbDBSUEBaM0xAVXmW5mrByOoZUK9Sf7qWu0hHL3dnVCtooMBe mg6N1PeYU1tSaNKHwBFvOdtGe95ZHj9aZ0tAVzJfzjbXXjouQZg6ZkaLuC8SzNo0Te2T 5MeTAQ3mbjhtJ3Jzf7DO4ZKcTA9I6ZblKG0lOvmGk57NX4mz9e3ftgcoVj3BxTDTXRrL vIe+JkrOI/SUswvQp1km4FR5FkKZR7qqa0UKRtHiPE/V3KQ1bqttxknaQ8+rFP5UAzNx JaLQ== X-Forwarded-Encrypted: i=1; AJvYcCUFV40QFtR4eChz0+0+mEnOLWNrC74hSVh1lP68Xb/4+7CYWX2iKDHJS7hkGXitUbytIyM8s9hUqkewykxw@lists.postgresql.org X-Gm-Message-State: AOJu0Ywu9/pfVLixJ4tZISvPChZ9EGs3oRJtRA83MDeMXnMZzyNWAevP NWIHKg+NnmtarXNdCV9UqAGLIw2hgWLAWv1TjSW7GjvvzQmKnMBsii0JJBqp93evNhV1JWCl9ME Pe9h6HjKD2htsJdwqYHEw/I2Kx8s= X-Google-Smtp-Source: AGHT+IF324T0Z3XBgK8RHSd1t/llaaHdmgBgul+aX0c8eFLPtGSx+VUneN9iCVlTgMDf0QrI2ss5jx9F7AUyYXkwIzI= X-Received: by 2002:a05:6512:159f:b0:52c:d753:2829 with SMTP id 2adb3069b0e04-53546b36b1dmr5884366e87.19.1725192867114; Sun, 01 Sep 2024 05:14:27 -0700 (PDT) MIME-Version: 1.0 References: <7fa81130-0b33-4e53-bb32-39d84f06c680@aklaver.com> In-Reply-To: From: veem v Date: Sun, 1 Sep 2024 17:44:14 +0530 Message-ID: Subject: Re: Partitioning and unique key To: "David G. Johnston" Cc: Adrian Klaver , pgsql-general Content-Type: multipart/alternative; boundary="00000000000003791806210dc4e1" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000003791806210dc4e1 Content-Type: text/plain; charset="UTF-8" On Sun, 1 Sept 2024 at 11:38, veem v wrote: > > On Sun, 1 Sept 2024 at 10:03, veem v wrote: > >> >> On Sun, 1 Sept 2024 at 09:13, David G. Johnston < >> david.g.johnston@gmail.com> wrote: >> >>> On Saturday, August 31, 2024, veem v 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 >>>> (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. >>> >>> >>>> >>>> >> 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? >> > > 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. > > 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 "" because it is part of the partition key of relation "" 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. --00000000000003791806210dc4e1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Sun, 1 Sept 2024 at 11:38, veem v = <veema0000@gmail.com> wrot= e:

On Sun, 1 Sept 2024 at 10:03, veem v <veema0000@gmail.com> wro= te:

On Sun, 1 Sept 2024 at 09:13, David G. Johnston <<= a href=3D"mailto:david.g.johnston@gmail.com" target=3D"_blank">david.g.john= ston@gmail.com> wrote:
On Saturday, August 31, 2024, veem v <veema0000@gmail.com> 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 (tra= nsaction_id,transaction_date).

<= /div>
While this might seem logical, in reality date and timestamptz ar= e different fixed-width data types and thus any attempt to change from one = to the other will involve a table rewrite.=C2=A0 Best you could do is leave= the timestamptz in place and just truncate to day so the time is always mi= dnight UTC.
=C2=A0

=C2=A0
Here=C2=A0 , 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(u= sing 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 tryi= ng to make the data type also as DATE for the transaction_timestap column.<= /div>

As in this case anyway we have to create another c= olumn to populate the date+timestamp values as we cant throw those values a= way per business need, so we will be kind of rewriting the table.So is it o= kay 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=C2=A0 to hold date+timestamp v= alue and also altering the existing transaction_timestamp column to DATE fr= om type timestamptz).
3)Then do the data fix(delete the duplicate= s) and alter the column, one partition at a time for all of the partitions = and once done , attach=C2=A0 those partitions one by one.
5)Renam= e the columns at table level.Hope this won't need any table rewrite.

Is there any downside if we go by the above appr= oach?

Or do you mean to s= ay there is no way we can modify the data type of a partition key even by d= etaching the partitions one by=C2=A0one? 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 cre= ating from scratch means creating those indexes again. So I wanted to achie= ve it by detaching partitions, doing the required change and attaching it a= gain.


I tr= ied=C2=A0 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.= =C2=A0

ERROR: =C2=A0cannot alter column "= <patition_key_column>" because it is part of the partition key o= f relation "<table_name>"

Now = I am thinking if it's really going to get too complex if we try to stic= k with the partition detach and attach strategy. As a few teammates say , h= aving a new column added with just a date type and then drop the existing F= K and PK first and then detach all the partitions, and attach the partition= s back using the new DATE column. and then recreate the PK again. Btw we ha= ve ~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.
--00000000000003791806210dc4e1--