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 1soJXH-00AD4G-HB for pgsql-general@arkaria.postgresql.org; Wed, 11 Sep 2024 09:21:17 +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 1soJXG-00DAn2-SE for pgsql-general@arkaria.postgresql.org; Wed, 11 Sep 2024 09:21:14 +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 1so9Gz-003S0H-A6 for pgsql-general@lists.postgresql.org; Tue, 10 Sep 2024 22:23:45 +0000 Received: from mail-me3aus01on2072c.outbound.protection.outlook.com ([2a01:111:f403:201d::72c] helo=AUS01-ME3-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1so9Gv-000X2P-7Z for pgsql-general@postgresql.org; Tue, 10 Sep 2024 22:23:43 +0000 ARC-Seal: i=2; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=pass; b=W01dGfURcJ5Dh89IUYxLkRmIzPDzw9JWKuL9tcrnZ13tvTs5hNTa8CHVGUkc/GVeFfZQsz+Nxjzz6ij/SmHXh9U/kjXk90/h1/DlRrEOZ3eKG7bSI+QTPcx27zRnEonHg3UI+3JbU/5AvEX01ZHpBe8xiV8zeyXDU/gbZaJOftqvJe29sUVRC7Mro2ev4DjcjgltXnvRMeio7RLNhxmCogdleIDK1DYCPFFN0jnLNlWpRq9lDiRMzlHy4Shi7hUu+ebZmyld+4IWXJo3N2Iv6NsliO/6t3Bu8oDiLWq25OpSZNz9cFa0QJdwXGMQy+DULBuq56h402TiCVFrgSt8qQ== ARC-Message-Signature: i=2; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=NeV1oibgK4JIOFaldWcPMdsiuQDWIT6Q0OhHlCvgzb4=; b=BT6YMb6El6Hkn6qJLLz94fss9wIQF2Kmrbr7VZlktiO8wLS8mZce518xWl4ao4Mz++Y2K+H0XQz41mxwf85H1N5MDGNaeODpeGJrAmDsh27L/bJxhrsdMnPr0AkDebPV34qBrVzLNqp6S8bY0OMIVZxudkizKqzcyj/j0ZXbBpUUjqVv7sq8givNYGX06O4G1w1dgORkpcQ8Lmz8OOSt0cXuvuQb40AFPXItJmvHbxV7cg9TbQj8HE2vHV4IxD0JtPyEt7FBdCglbIHPGxQMuDm/dFoRcHJFSYFz26gci/O3efEHzvRkckQmjviuSIHI6o+RHkG8OQoUSGlNIA8RxQ== ARC-Authentication-Results: i=2; mx.microsoft.com 1; spf=pass (sender ip is 104.47.71.241) smtp.rcpttodomain=postgresql.org smtp.mailfrom=niwa.co.nz; dmarc=pass (p=none sp=none pct=100) action=none header.from=niwa.co.nz; dkim=pass (signature was verified) header.d=niwa.co.nz; arc=pass (0 oda=1 ltdi=1 spf=[1,1,smtp.mailfrom=niwa.co.nz] dkim=[1,1,header.d=niwa.co.nz] dmarc=[1,1,header.from=niwa.co.nz]) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=niwa.co.nz; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=NeV1oibgK4JIOFaldWcPMdsiuQDWIT6Q0OhHlCvgzb4=; b=sLwdLFMVOxhwNyqCTCCpbKkk9eXkK/cNPT7sN5FeH1Ie/wvFNrtCLzC7b+qdijEjmqieTMkRRMJQUYIBBaM/8WH2KzSUKcnKUteLApHw2UoYCP4uiqA8XUXacwTgHEk5gV8XEC2cGrowK2scAX7QEveCMtzbFTfD84JnhI97TAY= Received: from SY5P300CA0018.AUSP300.PROD.OUTLOOK.COM (2603:10c6:10:1ff::6) by SY7P300MB1451.AUSP300.PROD.OUTLOOK.COM (2603:10c6:10:2c5::21) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7962.17; Tue, 10 Sep 2024 22:23:32 +0000 Received: from SY1PEPF000066C5.ausprd01.prod.outlook.com (2603:10c6:10:1ff:cafe::1f) by SY5P300CA0018.outlook.office365.com (2603:10c6:10:1ff::6) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7939.25 via Frontend Transport; Tue, 10 Sep 2024 22:23:32 +0000 X-MS-Exchange-Authentication-Results: spf=pass (sender IP is 104.47.71.241) smtp.mailfrom=niwa.co.nz; dkim=pass (signature was verified) header.d=niwa.co.nz;dmarc=pass action=none header.from=niwa.co.nz; Received-SPF: Pass (protection.outlook.com: domain of niwa.co.nz designates 104.47.71.241 as permitted sender) receiver=protection.outlook.com; client-ip=104.47.71.241; helo=AUS01-ME3-obe.outbound.protection.outlook.com; pr=C Received: from wellmail.dmz.niwa.co.nz (202.36.29.34) by SY1PEPF000066C5.mail.protection.outlook.com (10.167.241.55) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7918.13 via Frontend Transport; Tue, 10 Sep 2024 22:23:31 +0000 Received: from AUS01-ME3-obe.outbound.protection.outlook.com (mail-me3aus01lp2241.outbound.protection.outlook.com [104.47.71.241]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client CN "mail.protection.outlook.com", Issuer "DigiCert Cloud Services CA-1" (not verified)) by wellmail.dmz.niwa.co.nz (Postfix) with ESMTPS id 4D66C609D61; Wed, 11 Sep 2024 10:23:31 +1200 (NZST) ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=iVjch7K99NXR5nk7tlJonBJrA703PcCcU/Cv0yFZ3o07dkNDo9JwBUSLuGdwJpRIFl1c0DUpdLzNALGFDFjFtUcq6OGVeuzQD0AW/om6+nihTAvJYr4DNMFlFBukffJpyJiayrSbXH2QWnXtVRx5AWxWziQcPnk3a7tx52y+EZbzrJFobTM7UWULTzm+uE48P4nlr7ibejdKYB0PNJiVzTaUj1lmFNcm9tJFYnGgiuwPIoInaBEUiVDHogKBHrgNxqY0H4nSuQ7ns0kXeWZ+IETWSPTRkVf81nOPE4lnVcytVbP1k5PS38T4BvOEWdylJgJdWjIvK/YyCtH1fYWSOQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=NeV1oibgK4JIOFaldWcPMdsiuQDWIT6Q0OhHlCvgzb4=; b=lRNwbx714jZmU/fd4flHueZimYwdv36k6HXXbKyrQs86ngPE53KfNUg+VnV0SWEuzDcNk3RwXI/BrSThovegVtE21NxCEuqr/wkNlkNnBPgRXsXayErpEwQISBBC2+qwdZBBerm2SB5R05zIv9hCsYoQicN9tJYZQstwTxUTWVxdc5+pNaksuuTTl47H23wwBRsOod0sgB4ye571aTrlijAgLyaVP5rTubmlJMA7th/3kqaNTEZDUxbW/6iVAAgR8cskrsEKygVe+MpIAf5HVptfLSBNijGtfMjJjwFoZTlYxawsfy55q/7xiNdoZKNJU/lhSHV4XutRh5xZA99/Kw== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=niwa.co.nz; dmarc=pass action=none header.from=niwa.co.nz; dkim=pass header.d=niwa.co.nz; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=niwa.co.nz; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=NeV1oibgK4JIOFaldWcPMdsiuQDWIT6Q0OhHlCvgzb4=; b=sLwdLFMVOxhwNyqCTCCpbKkk9eXkK/cNPT7sN5FeH1Ie/wvFNrtCLzC7b+qdijEjmqieTMkRRMJQUYIBBaM/8WH2KzSUKcnKUteLApHw2UoYCP4uiqA8XUXacwTgHEk5gV8XEC2cGrowK2scAX7QEveCMtzbFTfD84JnhI97TAY= Received: from SY7P300MB0761.AUSP300.PROD.OUTLOOK.COM (2603:10c6:10:28c::10) by SY2PPF39C22CC0F.AUSP300.PROD.OUTLOOK.COM (2603:10c6:18::390) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7962.17; Tue, 10 Sep 2024 22:23:28 +0000 Received: from SY7P300MB0761.AUSP300.PROD.OUTLOOK.COM ([fe80::da5c:b8f6:3849:775c]) by SY7P300MB0761.AUSP300.PROD.OUTLOOK.COM ([fe80::da5c:b8f6:3849:775c%3]) with mapi id 15.20.7939.022; Tue, 10 Sep 2024 22:23:28 +0000 From: Brent Wood To: Christophe Pettus , pgsql-general Subject: Re: Overlapping values (?) in multi-column partitioned tables Thread-Topic: Overlapping values (?) in multi-column partitioned tables Thread-Index: AQHbA87URpQNROMIn0WhN7MPuO/Fx7JRlst9 Date: Tue, 10 Sep 2024 22:23:28 +0000 Message-ID: References: <2FAC43EB-7E04-476A-BEBE-39CDBCA0EDCC@thebuild.com> In-Reply-To: <2FAC43EB-7E04-476A-BEBE-39CDBCA0EDCC@thebuild.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: Authentication-Results-Original: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=niwa.co.nz; x-ms-traffictypediagnostic: SY7P300MB0761:EE_|SY2PPF39C22CC0F:EE_|SY1PEPF000066C5:EE_|SY7P300MB1451:EE_ X-MS-Office365-Filtering-Correlation-Id: bf7c4164-f448-4963-1570-08dcd1e7340b x-niwa-seemail: Out X-MS-Exchange-SenderADCheck: 1 X-MS-Exchange-AntiSpam-Relay: 0 X-Microsoft-Antispam-Untrusted: BCL:0;ARA:13230040|366016|376014|69100299015|1800799024|38070700018; X-Microsoft-Antispam-Message-Info-Original: =?us-ascii?Q?s0Gk91M2l5UNNz1CQ3NsTALwXZIwjVLtu4QmKGXY4XSbg9lBBW6RJHE36Oks?= =?us-ascii?Q?eJ0aI1UiyIsNLiCiXsMiM21O9l59kylFbNx04rNAdPQKwYVR3Gq0CFBjPdps?= =?us-ascii?Q?cJew44i1DYAWu4lmpkPFS4zb2jGRajBVaVD4FSZSTNNcI5kEBV8JEFyDkIOc?= =?us-ascii?Q?m3R6ztnt4VCS0zw7/dnDq2jFL3dw94vSqKc8xHv3w+A0smZLShPZPZrrrKAq?= =?us-ascii?Q?sm6Jc0YrO/FxYfug+f2UuYXK3aTU30Qpx2We0AtHj6kucSe80gTY+A1OzJTF?= =?us-ascii?Q?WOqiilPd+Cqt3Val/fn8ldyb2T2w4ZD/ujsn5GBDM4kNx07BD1H8hCY+dbWt?= =?us-ascii?Q?mw4XyBkAfJgcSxq49kDDUUo8CMcRG610xSidzbowezhIIP394VFxxi+5pyfK?= =?us-ascii?Q?CDDdtgQrMgfA87BwtH4CJp15f9ioQwRbG1mJ92L1UZccXpdgFQnCaNdBeA6c?= =?us-ascii?Q?D+1mseLvTxOesR3J9UIWqhnR+Eyt+truSgIRHakkoVbH+SEg2Nl16ZPdGLga?= =?us-ascii?Q?8DnrT+Ij3rbbvmLJk4OEymexajhZMsRbmmPElIIZQVSeWZ8ZTloLmQcVzqfC?= =?us-ascii?Q?A5UAvc7iUNJzh5a3oAUmI1Hx/MW/OdCPewgJJao49ok//nJdVrEUjyfP03Di?= =?us-ascii?Q?sYM0U4NYHAwRjPoATSckdQ9iaeeUAzPbS1uIKyMT1LN8RXrGg7HO7G1GZVks?= =?us-ascii?Q?kFXGOmjGovVruXnN+TT/z+j3wuWuzle1snfrQ9UGYtATr1ksEjKfreA7Q9H2?= =?us-ascii?Q?ZgLdMo07kuiYDnk+basQRR1saxWjwZ/wfxfdCC9N0G6N3mJ61NhWNKqE1Rt2?= =?us-ascii?Q?UUcE6uhs4iV5/XamXdG5N43nDSKJxA7TgGjH0Us+aKRRuVrUkF+cppg5umwp?= =?us-ascii?Q?QHsVIw8HnBtldr8k8Xj31kjaTD5wQ6GkDz2Fu2vJNDT5lbW0gs6kHU9DfHkm?= =?us-ascii?Q?o/l+l1ixyoS4s0xpT2orJoLEUYDtJp7friw8RgvqSTaI3OGKeqLw52a13tbk?= =?us-ascii?Q?dmCFMTXeaAF4ZQcmz90YEWYyGQhM7rg87nukm6/laIxoIj8bYn5j41f4Srgf?= =?us-ascii?Q?+R/oe5CC/h+E/wd1Tc2z+ad8XCmPt+2N3TGEokz0HfFVP0+eB++XpPU/TdyX?= =?us-ascii?Q?iBhi+moNa/MCN1YW0NBFrMTbMENDb6IyUzd9+BkvDZqUgHLhOqSMxQ/n5ia8?= =?us-ascii?Q?/B+NjbdXWvoDy74KgGJNr0EvxWc2Qgd5XJIqIipwGFAgV+cSTPqyqtS2E9Um?= =?us-ascii?Q?PppQTXiE+jABLGQGj1g6YovKzm7n7YCPZqnbebm/YP+oNEt4GG3qidbM6qTm?= =?us-ascii?Q?H63tGSjNJ0N3gI9nShVEEZXtQdsrCg5n1bSMe2WryQiQ6jeRp9dQSkcj3X4W?= =?us-ascii?Q?28asfE8iQdZS0m01JU9pxJUx78o89a5uAYihI2WKH9Y57LUXyw=3D=3D?= X-Forefront-Antispam-Report-Untrusted: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:SY7P300MB0761.AUSP300.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFS:(13230040)(366016)(376014)(69100299015)(1800799024)(38070700018);DIR:OUT;SFP:1102; Content-Type: multipart/alternative; boundary="_000_SY7P300MB07614A220ADFE92147763474A19A2SY7P300MB0761AUSP_" MIME-Version: 1.0 X-MS-Exchange-Transport-CrossTenantHeadersStamped: SY2PPF39C22CC0F X-EOPAttributedMessage: 0 X-MS-Exchange-SkipListedInternetSender: ip=[104.47.71.241];domain=AUS01-ME3-obe.outbound.protection.outlook.com X-MS-Exchange-ExternalOriginalInternetSender: ip=[104.47.71.241];domain=AUS01-ME3-obe.outbound.protection.outlook.com X-MS-Exchange-Transport-CrossTenantHeadersStripped: SY1PEPF000066C5.ausprd01.prod.outlook.com X-MS-PublicTrafficType: Email X-MS-Office365-Filtering-Correlation-Id-Prvs: a9ad124e-83a6-4be0-f000-08dcd1e73229 X-Microsoft-Antispam: BCL:0;ARA:13230040|1032899013|82310400026|376014|1800799024|35042699022|69100299015|36860700013; X-Microsoft-Antispam-Message-Info: 6tE4IVzGJQO/X3EHWAn46gjvFXMudIWP31KJb2LGFjSKetGQiLO/Jv+5xQ1BGntVE1n8bRgymmOGh3M1BrfSVxXdxDN4GiXEBDcsb7ZvlMUUopWb68lgcGeZuXo8/x8ocl8+HX9OlZhCigEdRQZRUn5baXArtj1I2DeQOMdrvQmO9zZ/3DQZ/o3fZZ0YZan69/6J5GJBImH0wKQdtNmeEEzJZOFNNFC7b3bXpwjt59Xf0s5BC0RLYq5XTc7G3YPAL3+fnvU/3HhkzWI0tzVUsQ7OfTY0BUw7TQFQC7yhAkgGGL8nPZpBAUwJEc7zn9qNfp90LaX4L9R73g6sy7OyzVzNFFpKV76G0P0qttOJwfk3ispk1z3UYd+r2mvaExxfjPq3x8PHVQYl/ya6sGaQbR/qmSssGM4RNOrW/+TglFrzvfaBNLrwfGYqQwbcY68D0MPO1AD2KD3M0iYijPD0ReIlB28UW/piHOQsU8HxZ/oSXdRriiCGC31CV+dAN8y3Ubrx4e6bKByvLP6ACRUhLx4GTm6JzRvpZEoikI6+eKJcbExIGgy4vydCLvGvnTOc90FQJgC69ITjdOZExv1Ag/AIyFbtNGcQF7YX4zPVS4z6sLnMiHUgqXrvg+wcX4YeSdSuUa0crXUq3frhsETO1NmbZ1Jx31gW1loFXyeWEBKiqgJ2PGCYivmm2CRoSqEFFx3kNXe00CPW+KCCUMX955pYEQjqi9dKwWJxJx7HqErdmWQCWtRSX/x4w+B+aB4h4DI21Peif+KngaRExaN9UoG/dzySLpml6NxyjZq5vzLUFgK92H1ILAWDX1yZMEVmqc1HD4DsyrNI4pQDDVLk0g== X-Forefront-Antispam-Report: CIP:202.36.29.34;CTRY:AU;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:AUS01-ME3-obe.outbound.protection.outlook.com;PTR:mail-me3aus01lp2241.outbound.protection.outlook.com;CAT:NONE;SFS:(13230040)(1032899013)(82310400026)(376014)(1800799024)(35042699022)(69100299015)(36860700013);DIR:OUT;SFP:1102; X-OriginatorOrg: niwa.co.nz X-MS-Exchange-CrossTenant-OriginalArrivalTime: 10 Sep 2024 22:23:31.9466 (UTC) X-MS-Exchange-CrossTenant-Network-Message-Id: bf7c4164-f448-4963-1570-08dcd1e7340b X-MS-Exchange-CrossTenant-Id: 41caed73-6a0c-468a-ba49-9ff6aafd1c77 X-MS-Exchange-CrossTenant-OriginalAttributedTenantConnectingIp: TenantId=41caed73-6a0c-468a-ba49-9ff6aafd1c77;Ip=[202.36.29.34];Helo=[wellmail.dmz.niwa.co.nz] X-MS-Exchange-CrossTenant-AuthSource: SY1PEPF000066C5.ausprd01.prod.outlook.com X-MS-Exchange-CrossTenant-AuthAs: Anonymous X-MS-Exchange-CrossTenant-FromEntityHeader: HybridOnPrem X-MS-Exchange-Transport-CrossTenantHeadersStamped: SY7P300MB1451 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_SY7P300MB07614A220ADFE92147763474A19A2SY7P300MB0761AUSP_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm not sure of the less than vs less than or equals in this one, so it may= be my ignorance... but which partition gets records with a date of '24-02-= 01", it seems that without a more explicit definition there is ambiguity on= dates at the partition limits when those dates are common to multiple part= itions? To avoid this shouldn't the definition be: > xof=3D# create table t (pk bigint not null, ts timestamp not null) partit= ion by range (ts, pk); > CREATE TABLE > xof=3D# create table t1 partition of t for values from ('2024-01-01'::tim= estamp, minvalue) to ('2024-01-31'::timestamp, maxvalue); > CREATE TABLE > xof=3D# create table t2 partition of t for values from ('2024-02-01'::tim= estamp, minvalue) to ('2024-02-29'::timestamp, maxvalue); Brent Wood Principal Technician, Fisheries NIWA DDI: +64 (4) 3860529 ________________________________ From: Christophe Pettus Sent: Wednesday, September 11, 2024 10:13 To: pgsql-general Subject: Overlapping values (?) in multi-column partitioned tables Hi, I am clearly not understanding something. Consider: > xof=3D# create table t (pk bigint not null, ts timestamp not null) partit= ion by range (ts, pk); > CREATE TABLE > xof=3D# create table t1 partition of t for values from ('2024-01-01'::tim= estamp, minvalue) to ('2024-02-01'::timestamp, maxvalue); > CREATE TABLE > xof=3D# create table t2 partition of t for values from ('2024-02-01'::tim= estamp, minvalue) to ('2024-03-01'::timestamp, maxvalue); > ERROR: partition "t2" would overlap partition "t1" > LINE 1: ...on of t for values from ('2024-02-01'::timestamp, minvalue) ..= . > ^ > xof=3D# In what way do those partitions overlap? In other words, there does not ap= pear to be a value of (ts, pk) having '2024-01-01'::timestamp <=3D ts < '20= 24-03-01'::timestamp for any pk where it would be ambiguous which partition= that row would go into. (I'm imagining it on a cartesian plane, and there= isn't any overlap between the rectangles those partition boundaries define= .) I'm obviously missing something, but... what? Thank you! [https://www.niwa.co.nz/static/niwa-2018-horizontal-180.png] Brent Wood Principal Technician - GIS and Spatial Data Management Programme Leader - Environmental Information Delivery +64-4-386-0529 National Institute of Water & Atmospheric Research Ltd (NIWA) 301 Evans Bay Parade Hataitai Wellington New Zealand Connect with NIWA: niwa.co.nz Facebook LinkedIn Twitte= r Instagram YouTube To ensure compliance with legal requirements and to maintain cyber security= standards, NIWA's IT systems are subject to ongoing monitoring, activity l= ogging and auditing. This monitoring and auditing service may be provided b= y third parties. Such third parties can access information transmitted to, = processed by and stored on NIWA's IT systems. Note: This email is intended solely for the use of the addressee and may co= ntain information that is confidential or subject to legal professional pri= vilege. If you receive this email in error please immediately notify the se= nder and delete the email. --_000_SY7P300MB07614A220ADFE92147763474A19A2SY7P300MB0761AUSP_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I'm not sure of the less than vs less than or equals in this one, so it may= be my ignorance... but which partition gets records with a date of '24-02-= 01", it seems that without a more explicit definition there is ambigui= ty on dates at the partition limits when those dates are common to multiple partitions?

To avoid this shouldn't the definition be:

> xof=3D# create table t (pk bigint not null, ts timestamp not null) par= tition by range (ts, pk);
> CREATE TABLE
> xof=3D# create table t1 partition of t for values from ('2024-01-01'::= timestamp, minvalue) to ('2024-01-31'::timestamp, maxvalue);
> CREATE TABLE
> xof=3D# create table t2 partition of t for values from ('2024-02-01'::= timestamp, minvalue) to ('2024-02-29'::timestamp, maxvalue);

 

Brent Wood

Principal Technician, Fisheries
NIWA
DDI:  +64 (4) 3860529

Hi,

I am clearly not understanding something.  Consider:

> xof=3D# create table t (pk bigint not null, ts timestamp not null) par= tition by range (ts, pk);
> CREATE TABLE
> xof=3D# create table t1 partition of t for values from ('2024-01-01'::= timestamp, minvalue) to ('2024-02-01'::timestamp, maxvalue);
> CREATE TABLE
> xof=3D# create table t2 partition of t for values from ('2024-02-01'::= timestamp, minvalue) to ('2024-03-01'::timestamp, maxvalue);
> ERROR:  partition "t2" would overlap partition "t1= "
> LINE 1: ...on of t for values from ('2024-02-01'::timestamp, minvalue)= ...
>            = ;            &n= bsp;            = ;            &n= bsp;            ^ > xof=3D#

In what way do those partitions overlap?  In other words, there does n= ot appear to be a value of (ts, pk) having '2024-01-01'::timestamp <=3D = ts < '2024-03-01'::timestamp for any pk where it would be ambiguous whic= h partition that row would go into.  (I'm imagining it on a cartesian plane, and there isn't any overlap between the rectangle= s those partition boundaries define.)

I'm obviously missing something, but... what?  Thank you!

Brent Wood
Principal Technician - GIS and Spatial Data Managem= ent
Programme Leader - Environmental Information Delive= ry
+64-4-386-0529

National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade Hataitai Wellington New Zealand
Connect with NIWA: niwa.co.nz= Facebook LinkedIn= Twitter Instagram YouTube
To ensure compliance with legal requirement= s and to maintain cyber security standards, NIWA's IT systems are subject t= o ongoing monitoring, activity logging and auditing. This monitoring and au= diting service may be provided by third parties. Such third parties can access information transmitted to, process= ed by and stored on NIWA's IT systems.
Note: This email is intended solely for the use of the addressee and may co= ntain information that is confidential or subject to legal professional pri= vilege. If you receive this email in error please immediately notify the se= nder and delete the email. --_000_SY7P300MB07614A220ADFE92147763474A19A2SY7P300MB0761AUSP_--