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 1skdlK-001lVV-In for pgsql-general@arkaria.postgresql.org; Sun, 01 Sep 2024 06:08:35 +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 1skdlI-009vMP-AJ for pgsql-general@arkaria.postgresql.org; Sun, 01 Sep 2024 06:08: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 1skdlH-009vME-UC for pgsql-general@lists.postgresql.org; Sun, 01 Sep 2024 06:08:32 +0000 Received: from mail-wr1-x432.google.com ([2a00:1450:4864:20::432]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1skdlF-0005sp-OC for pgsql-general@lists.postgresql.org; Sun, 01 Sep 2024 06:08:31 +0000 Received: by mail-wr1-x432.google.com with SMTP id ffacd0b85a97d-374ba74e9b6so1231099f8f.0 for ; Sat, 31 Aug 2024 23:08:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1725170908; x=1725775708; 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=5NIxqNsqfaULa0miZcpr+rKo4/uKjotA2h/izB0YGtI=; b=LKd8lLe4StyiSbQtpCAJG4llGkO/OeroxAeD3e9lnlQoz4exl1ZOsXRNCYqR2QiZW/ yOTwvmuWjUAncddQ6j3rBQ+SUnoYksxiiCjTkijdd2N31W73zKIYopeAPMX2uSzfAR9e 8lghDL+oWyGe7MZXiRT03i5IyWa5yOM0HUy0CzIwu5LqCycpomlg1po4UAQINJrEqy24 4vBZs+fl63EvaDYcTUrKwbEri+vOmy0yohcZsJylRAnxg8NZ1m9D0rixtHZbcTMrLvUc snrB7F00VlyhuM/uSmcQ/86azMhQ7cUfsnTLIc1JudF8bowhtJ0qE28H8xi4siSZl8q/ pueA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1725170908; x=1725775708; 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=5NIxqNsqfaULa0miZcpr+rKo4/uKjotA2h/izB0YGtI=; b=MmGAC1UVhPhF5QMoDVIS/bKm+4Jw5D7xWNUffnj1h4Wr/Bm0Oe4t7yERf7g+v8ENEy puEUfbQ88bxl54eXlDaEcnHhRfWhiBXRYYPdLtNdCt9Ry+HAr5+j9JbHzZQCLRthcwSX l/AL/ReIOolappa79Mq76RqsBh1FPJ4mw7+buxR7Si2J6ZHaPIbwVW1yy/k5Kzpq0+fC NhTlDZHbm6VzYJ7Ya3Hl/DYS1nbtvrCT+T9SVmQEKzkzhABYKMvUlsujbpLx6B1gO4qT O5v53oV6IPTJ0T/GqSPS6DwgrhTaYPLi6MRrxlEPE0zpt2yfpCBxZQsh2Sld9NzUAEQK ieyA== X-Forwarded-Encrypted: i=1; AJvYcCVzJKLMjVesYKrrbTIlttMCaX4vdsYuZhY/PjAmepoMKfU37RxyBv17Usk8oL3nEbCj6cVSApyZbB1Z0klR@lists.postgresql.org X-Gm-Message-State: AOJu0Yy5Qgz8f6OAyUHP3ZeDXVBGz/PUj3DoQHPtGRbY1ed4ACrTkd50 0xcbmRWJTLVBXabeowEm0tyCV7lbw3zAzA2WVQxuD/yEgcWGKmFzYNFdGn0N96F/qxd0zKQNExI UuTx9DFOdtiP7dZfH/nGYaw7eSvQ= X-Google-Smtp-Source: AGHT+IG1LI7TVOCE0slrRmVOUycemtw7dWvnVpf4bZ5MxeIdb+vseXdyjJqX2LYJmVByBgaTpN8EhSwIbubpcdX/qCY= X-Received: by 2002:adf:e053:0:b0:368:3f6a:1dea with SMTP id ffacd0b85a97d-3749b5267fdmr9385685f8f.6.1725170907033; Sat, 31 Aug 2024 23:08: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 11:38:15 +0530 Message-ID: Subject: Re: Partitioning and unique key To: "David G. Johnston" Cc: Adrian Klaver , pgsql-general Content-Type: multipart/alternative; boundary="000000000000173a0c062108a73b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000173a0c062108a73b Content-Type: text/plain; charset="UTF-8" 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. --000000000000173a0c062108a73b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Sun, 1 Sept 2024 at 10:03, veem v <= veema0000@gmail.com> 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 <veema0000@gmail.com> wrote:


= iii)And then alter the datatype of the partition key transaction_date to DA= TE in one shot at the table level(which should be fast as its having more g= ranularity as compare to existing timestamptype, so should be catalog or di= ctionary change only), and that will remain the part of composite PK (trans= action_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.=C2=A0 Best you could do is leave t= he timestamptz in place and just truncate to day so the time is always midn= ight UTC.
=C2=A0

=C2=A0
Here=C2=A0 , if we keep the PK colu= mn as is i.e. the transaction_timestamp as timestamptz but truncate the tim= e 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 t= o make the data type also as DATE for the transaction_timestap column.

As in this case anyway we have to create another colum= n 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.
<= div>2)Do the alter using "only" key word in table level. (For add= ing new column transaction_timestamp_new=C2=A0 to hold date+timestamp value= and also altering the existing transaction_timestamp column to DATE from t= ype timestamptz).
3)Then do the data fix(delete the duplicates) a= nd 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)Rename th= e 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 t= here is no way we can modify the data type of a partition key even by detac= hing 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 i= t? I was avoiding this because we have many indexes also in it , so creatin= g from scratch means creating those indexes again. So I wanted to achieve i= t by detaching partitions, doing the required change and attaching it again= .

--000000000000173a0c062108a73b--