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 1u5hRS-004cri-S6 for pgsql-bugs@arkaria.postgresql.org; Fri, 18 Apr 2025 08:51:23 +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 1u5hRQ-00A4Y6-Gb for pgsql-bugs@arkaria.postgresql.org; Fri, 18 Apr 2025 08:51:21 +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 1u5UW8-00FaRP-NJ for pgsql-bugs@lists.postgresql.org; Thu, 17 Apr 2025 19:03:21 +0000 Received: from mail-pj1-x102d.google.com ([2607:f8b0:4864:20::102d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u5UW5-000cGX-24 for pgsql-bugs@lists.postgresql.org; Thu, 17 Apr 2025 19:03:20 +0000 Received: by mail-pj1-x102d.google.com with SMTP id 98e67ed59e1d1-30828fc17adso1156089a91.1 for ; Thu, 17 Apr 2025 12:03:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=logansw-com.20230601.gappssmtp.com; s=20230601; t=1744916596; x=1745521396; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=/rcx8BRF73+1MNJ9ykPBgIPNwsQwiYnMimCyjuXmF/c=; b=xuvKTIAQJ2/tekAQwK63Ys4uvUeXkxZ/b10OEiRvqEkGMXj3BsciuYoJCiRBwVVogk UPneMwH0nHPuz1/0r7nM1+6zSd77KCutjG3qMORpNDwILyeV54VY4IH3fosW8VUcDuTT WH331QVdEkTCKX76c8Thtky9ZLnSIzx15MqblxD2eF12oBp0LO/rWWAGzKQ9eG5/nM/C ohsgC6xpdMbhZGidw9kMkxIqNlLu0LeLA8yrZS3HXb1W8kI4+KamzUHAY16Yqx8fL5M6 L9R1sp/EIu9rQLxX55OUDzOavCjKWHgFXooGNszguMureuvIch9uaH49n++gWyx6LroH hiGg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1744916596; x=1745521396; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=/rcx8BRF73+1MNJ9ykPBgIPNwsQwiYnMimCyjuXmF/c=; b=R5cpia4s4leLY62juyjz2MJCmkRXOtWfwOoph2ER4VQfgb61TNrbfFGK5HlhtJEgNJ AmEVNPYVPI1iqd8mfQSc4Olq/W3OstznNGBi85+umGp+HQitdj2i/AvoVCoAwYR0/AkZ qJ3CTtLWrdG2OJ5ioN902qcyKPN8dCn7/zsXg95W0cbLOANw/cwWY2vUSRl9VOVvRT2w PrOA8/mnrO2iAlW8auKjdU47/2Tqn8WjkpomZP6LIQc1kLq24cMIWEF+LdAp1ueUH/DK WEPzPXlUZEAddXYGFfjl432hrTQI3Pp8P3D3sxZDJpPRYazJaky9WJ/Jmtvl35+PnQVT vQ+g== X-Forwarded-Encrypted: i=1; AJvYcCWX4ZN1/nZk10cKPGs6JRt04f6pEec6fptBoI5osMTD0VqqnYO3dPBbv27m27ME8hdDBLsYuDT9XJfX@lists.postgresql.org X-Gm-Message-State: AOJu0Yw1N+T8/ugoojGCFArGBY2CQdTJVzDeuPBdvrGQm97hxYATrzLi fwjcXzv0p8U4fCFqbIy0mUc1LONF0AYN4Hz+gY/9I94tzLZ4g3kIBdTD0TkcrpRTUuS+n3n4CuE r X-Gm-Gg: ASbGncvVjjO73feUEFmwrfWpsN5FTSxedhKKsnRpC0/cdyKJrA8G9QnnnoKYFodPge3 XjHX7jaJAT4Dny2TOs1N0YQ2uwdNZTm2rukmN9YC87EydG/5CJQC+zsqfMUcRN9qKk5pf3DN0hU HOoIbaG5U98K5WlzPDpFbjt/KPut6PShj3Ys15cqQE5UxdtDyaDMOvyQRHlD1bpviMFU19NdfLZ ygzOJ7rDXGNbJD8hRhZg9T5mmKwJI0XvOCjjz2kcHlcViEdz0Tshz9ChmRp+HyFOCEmp4/hWZGX kVSRLZlCV9/dxGfCv/TieVDqEGkq0AkCPQtSvATrYCli840foVhWgLZVP4yFjA7vyCoRRaCutw= = X-Google-Smtp-Source: AGHT+IFKjYnITiWNfPlao+LzLHFO1AvaBOSVJNwM7YRPWcoG73ULeaxsY049g7XGd51D5d0Md4My6Q== X-Received: by 2002:a17:90b:4ed0:b0:2ff:5714:6a with SMTP id 98e67ed59e1d1-3087bb698e6mr160298a91.19.1744916596419; Thu, 17 Apr 2025 12:03:16 -0700 (PDT) Received: from smtpclient.apple ([2603:3026:100:6500:d8eb:d7ce:1b2c:21f]) by smtp.gmail.com with ESMTPSA id d9443c01a7336-22c50bdda3bsm3742345ad.27.2025.04.17.12.03.15 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 17 Apr 2025 12:03:15 -0700 (PDT) From: Keith Paskett Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_E813AA16-CEC1-493D-9884-2C40403BADD7" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.400.131.1.6\)) Subject: Re: pg_restore error with partitioned table having exclude constraint Date: Thu, 17 Apr 2025 13:03:04 -0600 In-Reply-To: <202504171518.tpkk67i7pauh@alvherre.pgsql> Cc: Japin Li , pgsql-bugs@lists.postgresql.org To: =?utf-8?Q?=C3=81lvaro_Herrera?= References: <202504171518.tpkk67i7pauh@alvherre.pgsql> X-Mailer: Apple Mail (2.3826.400.131.1.6) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_E813AA16-CEC1-493D-9884-2C40403BADD7 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 I=E2=80=99m pretty sure that I saw support for exclusion constraints in = the PG17 release notes, and added them to tables that weren=E2=80=99t able to use them in PG16. They worked great. I just discovered the issue with pg_dump/pg_restore. The restored tables actually still work because the partition tables = have the proper constraint. Even new partition tables created after the pg_restore get the correct = constraint. Can I assume that the patch will get incorporated into a future release, and not that exclusion constraints on partition tables will be = disallowed? -Keith > On Apr 17, 2025, at 9:18=E2=80=AFAM, =C3=81lvaro Herrera = wrote: >=20 > On 2025-Apr-17, Japin Li wrote: >=20 >> It seems PG 16 does not support exclusion constraints on partitioned = tables. >=20 > Yeah, my recollection is that they were purposefully disallowed = (mainly > because I didn't want to research how to fully make them work when > adding local partitioned indexes), and that we needed to do more work = if > we wanted to let them through. I suspect commit 8c852ba9a4 was = mistaken > to allow that case without looking for further implications. >=20 > --=20 > =C3=81lvaro Herrera PostgreSQL Developer =E2=80=94 = https://www.EnterpriseDB.com/ > "On the other flipper, one wrong move and we're Fatal Exceptions" > (T.U.X.: Term Unit X - http://www.thelinuxreview.com/TUX/) --Apple-Mail=_E813AA16-CEC1-493D-9884-2C40403BADD7 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 I=E2=80=99m = pretty sure that I saw support for exclusion constraints in the PG17 = release notes,
and added them to tables that weren=E2=80=99t able to = use them in PG16.

They worked great. I just = discovered the issue with pg_dump/pg_restore.
The restored = tables actually still work because the partition tables have the proper = constraint.
Even new partition tables created after the = pg_restore get the correct constraint.

Can I = assume that the patch will get incorporated into a future = release,
and not that exclusion constraints on partition = tables will be = disallowed?

-Keith


On Apr 17, 2025, at 9:18=E2=80=AFA= M, =C3=81lvaro Herrera <alvherre@kurilemu.de> wrote:

On 2025-Apr-17, Japin Li = wrote:

It seems PG 16 does not support = exclusion constraints on partitioned tables.

Yeah, = my recollection is that they were purposefully disallowed = (mainly
because I didn't want to research how to fully make them work = when
adding local partitioned indexes), and that we needed to do more = work if
we wanted to let them through.  I suspect commit = 8c852ba9a4 was mistaken
to allow that case without looking for = further implications.

--
=C3=81lvaro Herrera =         PostgreSQL Developer =  =E2=80=94  https://www.EnterpriseDB.com/
"On the other = flipper, one wrong move and we're Fatal Exceptions"
(T.U.X.: Term = Unit X  - = http://www.thelinuxreview.com/TUX/)

=
= --Apple-Mail=_E813AA16-CEC1-493D-9884-2C40403BADD7--