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 1skcHW-001ZLb-Cx for pgsql-general@arkaria.postgresql.org; Sun, 01 Sep 2024 04:33:43 +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 1skcHT-008le9-Pj for pgsql-general@arkaria.postgresql.org; Sun, 01 Sep 2024 04:33:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1skcHT-008le1-7t for pgsql-general@lists.postgresql.org; Sun, 01 Sep 2024 04:33:39 +0000 Received: from mail-wr1-x436.google.com ([2a00:1450:4864:20::436]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1skcHQ-0004vm-OV for pgsql-general@lists.postgresql.org; Sun, 01 Sep 2024 04:33:38 +0000 Received: by mail-wr1-x436.google.com with SMTP id ffacd0b85a97d-374ba74e9b6so1192117f8f.0 for ; Sat, 31 Aug 2024 21:33:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1725165214; x=1725770014; 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=IIRRy6tIzqgUcrPv41nci1sXDrMh5MH1OI74rFIMF7w=; b=fSwZuT03fvFDo3IK7tyTvpNtNIyzXUfGMJy1boNpGnRxSuBw4uWSXz0yF8vI2/CZhy ztFpnzKpM52X8x54D9Shkh0bMhTvC4KmsaoOb3AwMsQ0zXyeFLywNjvbpMkEgeJdzMKB HT00NU6cXF6XFMoovHrPkPYZWWE3z8SHQHxKNwnPG6vrzUZoKfDagL5frZ8xPFxymeQI kTSvFPZKc0rYa8aYBPv1kTUPOhyfLin/y2a2AQeP8b/tRdLxxWegVHtCzsOXuKfW0XyI OZoyvKVaIhsNCz/nGKh4j409wZQXTiV0QVPk+r+08U9kzi8XXexDeCQ5NCnGSl6uq1Z4 7x7g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1725165214; x=1725770014; 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=IIRRy6tIzqgUcrPv41nci1sXDrMh5MH1OI74rFIMF7w=; b=AkVmV4jmHzqOEw3B8fHUmvPIjsZeFDTKqzjW9RqoaoGi51o09Bd8bbIrXwMBwT79rq XFVvOuWQOvAT2BFSOD8ZWCxNy0Ozk5T2sQQsl8957dDpDCHnwULpDajJ1IUc2y90Requ nNTSNdKINXPBo1M+wDXa0uvhFj7GAgh/982I9i3YgW8MdAfnUHmva6b2cU9hp7JTaTlS afNkrkiSzHWo4Egbj3yI4BJLHWTMJ1UlCA0kUhSOgBu0wfJpKBvLi4h1c5jBFlFOCd+9 cTyip4wC3ZvV52LawFVUvNxLl9NuP2gGny7mBKQaH7tS+hf2IupqU/VabBjLu3uKW3Oh Whhw== X-Forwarded-Encrypted: i=1; AJvYcCUke6y1Hx5D6FcZFTfgVh6nMPoBjGXlUAoymNkbZk3cHiccD34aN0l9/8BYHho2AnrSWHBXrGHIEXh271hB@lists.postgresql.org X-Gm-Message-State: AOJu0YxNS0TqbMaVrpWSWho2X/8tL0rsYAAoWaj5/Nkyo7d3lx6YQ2Em kjL3m2pjcbQvVuqdtLSf1l6LTv+HH1XImcOHoMFxbGgg+t61qWrNnaOkO0wvfaBzWTM+lxjc9RG Z5C+aNUjRVJFRO22uvhmx48sabiI= X-Google-Smtp-Source: AGHT+IHP3v0M3hwxKNWLRvH/k1KQAkchF4+vxsM4JjPWYmBs9JVJYi8WHjY+8jU7k+MynU724hHZEXibwRQRDqDyB9Q= X-Received: by 2002:a5d:6801:0:b0:374:c69b:5a24 with SMTP id ffacd0b85a97d-374c69b5b9amr585047f8f.51.1725165213591; Sat, 31 Aug 2024 21:33:33 -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 10:03:21 +0530 Message-ID: Subject: Re: Partitioning and unique key To: "David G. Johnston" Cc: Adrian Klaver , pgsql-general Content-Type: multipart/alternative; boundary="000000000000bc30e80621075316" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000bc30e80621075316 Content-Type: text/plain; charset="UTF-8" On Sun, 1 Sept 2024 at 09:13, David G. Johnston 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? --000000000000bc30e80621075316 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Sun, 1 Sept 2024 at 09:13, David G. Jo= hnston <david.g.johnston@g= mail.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 i= n one shot at the table level(which should be fast as its having more granu= larity as compare to existing timestamptype, so should be catalog or dictio= nary change only), and that will remain the part of composite PK (transacti= on_id,transaction_date).

<= div>While this might seem logical, in reality date and timestamptz are diff= erent 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 t= imestamptz in place and just truncate to day so the time is always midnight= UTC.
=C2=A0

=C2=A0
Here=C2=A0 , if we keep the PK column a= s is i.e. the transaction_timestamp as timestamptz but truncate the time co= mponent , in that case again in future if someone tries to insert(using ins= ert on conflict) data into the table with time component , it will get cons= umed and will not be restricted by the PK constraint. So I was trying to ma= ke 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 i= f 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 value and= also altering the existing transaction_timestamp column to DATE from type = timestamptz).
3)Then do the data fix(delete the duplicates) and a= lter the column, one partition at a time for all of the partitions and once= done , attach=C2=A0 those partitions one by one.
5)Rename the co= lumns at table level.Hope this won't need any table rewrite.
<= div>
Is there any downside if we go by the above approach?
--000000000000bc30e80621075316--