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.98.2) (envelope-from ) id 1xLYa5-00000001Zkf-04sf for pgsql-bugs@arkaria.postgresql.org; Tue, 27 Oct 2026 04:14:37 +0000 Received: from localhost ([127.0.0.1]:33414 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xLYa4-000000094x7-2Czw for pgsql-bugs@arkaria.postgresql.org; Tue, 27 Oct 2026 04:14:36 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:58934) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xLYa4-000000094wz-1Cxx for pgsql-bugs@lists.postgresql.org; Tue, 27 Oct 2026 04:14:36 +0000 Received: from mail-pj1-x102a.google.com ([2607:f8b0:4864:20::102a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1xLYa2-000000017z0-2vjg for pgsql-bugs@lists.postgresql.org; Tue, 27 Oct 2026 04:14:35 +0000 Received: by mail-pj1-x102a.google.com with SMTP id 98e67ed59e1d1-3ad787e0593so1578221a91.3 for ; Mon, 26 Oct 2026 21:14:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1793074474; x=1793679274; darn=lists.postgresql.org; h=content-transfer-encoding:content-type:in-reply-to:from :content-language:references:to:subject:user-agent:mime-version:date :message-id:from:to:cc:subject:date:message-id:reply-to:content-type; bh=Bv+IPgXSSAxhiZq7pw45MG7eBI0fGuHpMPpj9Tnqnnw=; b=b8lOtow/C+FM07VRV+tY2xcPv3A0JfLvHF4IjYpJkJE8wBzb15nk+l5WeR71n2FJpV rYJy7ExWwc0qVeX289Ux+wNOUfAKSFfW89UGC39h6ZPN4leNGrXbW0FTjuPvAONz7tni rr7C3DqfFVJ39YIrx06aDNYeniRAPxIaeKS1TmuWBJrJcmpTZ7qKNYQ7YGElayqeFfCS M+5/hJ566Mjt06fwpsAr8c3zyMELfgUdWvDkZPol31EpX8+5s2oGPTY9f53OQqsTHUaj 6z3xh1fK8x38GK52Hy4hcYGj+IiJ24IxfEayYqExzfV1srennFyfE6c7XbmIqBhgOOUJ FxoA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20260707; t=1793074474; x=1793679274; h=content-transfer-encoding:content-type:in-reply-to:from :content-language:references:to:subject:user-agent:mime-version:date :message-id:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to:content-type; bh=Bv+IPgXSSAxhiZq7pw45MG7eBI0fGuHpMPpj9Tnqnnw=; b=bq0XKq5xicJ1O3LsfHRbfvpuqkuhlGSHpKZK/6tTACQ2qNpJxsudzHMdHv18kvvZwM DeB5qu18qNrIsKl6BfE4Zs7Amanmn8WpuoDclhmelOwFjYUa0Qw1MbR7AVBl0cogaC6W i6d2TgxqQuHscwQFVvalYhU5K5YuF8EJMP6DDt9j0O1NR+BSYEA2URd/POKmQuLp4/Uz +mtn2kfVU3b6abDIgkJy9bB4cw3Aq9QV5gwjgzoJE69D9cjM8qlYjrHvbAqiyNfVCLDd xER9IVkFFumPYaSUZbzKl82Es4qSJlR1mu0Zqm6E8qoBqJ93k+2cCAv2b0iKCcUmyDmn D/zw== X-Forwarded-Encrypted: i=1; AKwUvBxS48dU6Ht4BqsvH5GgbI0hybb7gxUcvLbV3qfks5ESjFAkDtsIbd/qwCLtGaXvaLS8xHtTJqxCdz+M@lists.postgresql.org X-Gm-Message-State: AFq9FYIrRb/JAXYJo/S4svu5vXWfuAcgD7KUfqC6b9phlo73Y51u1b2R kpGIq4E50Xwq22+t/YbK+2iDSO16KZxOg5FZsUXL8sKR1VOkF1lJx4Zh X-Gm-Gg: AYBFou2QliokYvznWTo5LgkmZXH0rScwonYYRYOsmAljzAmB/FLmoxJaZenoolZoXCI Z86hCbJfN3AP6GFS6wv2Jo4aWB79wgo8fv1vwCr3RC2rcivGC7oOfxX2fYmyIFNwXtvbFDBEUVA W0iL5MRBpzulFXNlcp8bF6UjCHGqO6I5RP5Pm1eWZhOjrcArDHmDtKhwDUtRvNmVLnN4O8Q+cOT nNWE/iidTgRqO6zLCAFyBreXVxiBj+WTCbzmvNR+62fgZqMzWs3hJ4YXJwTBjWSulMzmGybN/hX Udc0oLLf1CKN3zqw0T9q+gSF5L6jhoJno2DVt7U5kxg9benaHxfKCnnNlbXIuU9rcjRRmTGHY6P BDedaQtHN8Va8jzUrfdDuc83ptkOXTv7hOwXbsAH68U4LQnaZnJOGdg6sy9+L+dRaReJkghdODK ty9PmN76cohng9c4ct3mwEnu0OUD9D/HBXyuiMhuKXUownE0bp7MkLDErNqgPNEVnfWkExgIWvt jNZXcqJJhFNHongxk0wlThiXeFB23KGGzIHtLdoiukClHaU0hD43JD7YSrBQtpIHT/4dlltWfRV WyUqQ0W74sbfclsFiqvgzoUpue6J/ZwVNZGwabVdq1ivVOxes68PZXOMACMj0LVqCt7OqxI6lPd ExkQmyMNrKyg= X-Received: by 2002:a17:90b:2745:b0:3ac:ea3b:f28b with SMTP id 98e67ed59e1d1-3adeafaeb57mr6799758a91.24.1793074473758; Mon, 26 Oct 2026 21:14:33 -0700 (PDT) Received: from [192.168.1.19] (58-190-153-33f1.hyg1.eonet.ne.jp. [58.190.153.33]) by smtp.gmail.com with ESMTPSA id 98e67ed59e1d1-3ae401bf352sm1849086a91.0.2026.10.26.21.14.32 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 26 Oct 2026 21:14:33 -0700 (PDT) Message-ID: <637f15f1-847e-4587-8aa1-7148f5e99699@gmail.com> Date: Tue, 27 Oct 2026 13:14:31 +0900 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: BUG #19777: PERIOD foreign key rejects an empty referencing range even though the referenced period covers it To: 1482694023@qq.com, pgsql-bugs@lists.postgresql.org, PG Bug reporting form References: <19777-e1090cf88c970b8b@postgresql.org> Content-Language: en-US From: Takeshi Ideriha In-Reply-To: <19777-e1090cf88c970b8b@postgresql.org> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, On 2026/10/20 18:14, PG Bug reporting form wrote: > > > Expected result: the insert should succeed if an empty referencing period is > considered covered by the referenced period, consistent with range > containment and the documented PERIOD foreign-key coverage rule. Please > confirm whether empty referencing periods are intended to satisfy PERIOD > foreign keys. In my opinion, this result is expected because empty is treated differently by range type and by WITHOUT OVERLAPS constraint. > SELECT range_agg(p) @> 'empty'::int4range AS parent_covers_empty > FROM period_parent_minimal > WHERE k = '[1,2)'::int4range; > > The SELECT returns true. Then run: This is considered to be the same as "select '[0,10)'::int4range @> 'empty';", which returns true since 'empty' range is contained in every range [1]. [1] https://www.postgresql.org/docs/18/functions-range.html -- Every range contains the empty range. -- > INSERT INTO period_child_minimal > VALUES ('[1,2)'::int4range, 'empty'::int4range); > > Actual result: rejected with SQLSTATE 23503 (foreign key violation). However, the INSERT fails. The point is a WITHOUT OVERLAPS column does not permit empty range [2]. The referenced table does not permit empty range so referencing table also cannot accept it. [2] https://www.postgresql.org/docs/18/sql-createtable.html -- The `WITHOUT OVERLAPS` column must have a range or multirange type. Empty ranges/multiranges are not permitted. -- Regards, Takeshi Ideriha