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 1si10p-00EN5N-1D for pgsql-general@arkaria.postgresql.org; Sun, 25 Aug 2024 00:21: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 1si10l-00A9Un-Db for pgsql-general@arkaria.postgresql.org; Sun, 25 Aug 2024 00:21:39 +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 1shCJQ-004YR1-A9 for pgsql-general@lists.postgresql.org; Thu, 22 Aug 2024 18:13:33 +0000 Received: from mail-dm6nam04on20711.outbound.protection.outlook.com ([2a01:111:f403:2409::711] helo=NAM04-DM6-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 1shCJJ-000wGX-Ho for pgsql-general@lists.postgresql.org; Thu, 22 Aug 2024 18:13:31 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=RmETuDEpvmbu3AV7mPDrnp8QDZ5x3b8JSF6gD83JOTMNESho5jn6r9bgce6B1IIuTgNiRKv2qHMXzoUjbmMUUymlQegkFRysgJFSEJZB0PQvCCsiZoPorL8gLjZ7XXcQUb8kSGO4eiJ4UWAizbQGWiyThsgWfyfoha1gIjbbpQXQFA/uvBCcemQJA9tXr+KpA/zgBx050NjBVblobVP9zutts+gFMcCb00rLA0zuhKX30gxAZ9AuLA1G1DB/MDo8r23eluWlYhfMwJsZUJaBHX0FmF1kwRuDM1/ojgTRL+EmSyG2CByNpgvXMju+dKbazNdTnHwC4f5Suvl3VTWHbw== 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=EFvrwWNzXZGbSEDqpBlBt+x1IaImm8B7df3FVfV64PM=; b=gAVjxK5y/JV4kYNnwopKqteQfUdaGKhvfjYiREwP1pGKjaT0IfgoizTXDNhFb2Amjy9ZjLmC4CMjFAE4jqiJFvItTtsM+TYUY6IZTDHkVGLfqaBxtSsnuWehHgSPpC/QHnEIkltT6MOobMIOgqZyEuRE2sVlddkW2jxyXXNFdBF6fkfQ5KelZOo1ef8zfeS6V+/q3m3zNt3qA/LgFdk3p00CEiy7Bh2+CQj0KdwfQn3PSADadSJoBgomJhdznBXB7jcEIfBlt59HUU+Z3jseH7zhDAOr3khz42CspKS3SclYRnWIwV9UwyhdF4THKodkfV91f3EMKNDNgQRjvzO4UA== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=optionmetrics.com; dmarc=pass action=none header.from=optionmetrics.com; dkim=pass header.d=optionmetrics.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=optionmetrics.com; s=selector2; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=EFvrwWNzXZGbSEDqpBlBt+x1IaImm8B7df3FVfV64PM=; b=oXJ8eER8HU+IAurLdozXs3SNum+sDG13rVrSK5wdtjIF3ykjyUznUjQXiXIB67LKI1Ufho7Hc7CQQ4+Dy04G+zwadOYU409cChrhg0DfhvmRkr0N17Q/oy3W8yzW8N6++vb+WuZOB0EkabeGqAD7nX9HiZCU77UMQVYQOzJOt0A= Received: from BYAPR01MB4374.prod.exchangelabs.com (2603:10b6:a03:a1::28) by MN0PR01MB7780.prod.exchangelabs.com (2603:10b6:208:37f::21) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7875.21; Thu, 22 Aug 2024 18:13:19 +0000 Received: from BYAPR01MB4374.prod.exchangelabs.com ([fe80::6efe:63a6:c2e9:5e50]) by BYAPR01MB4374.prod.exchangelabs.com ([fe80::6efe:63a6:c2e9:5e50%2]) with mapi id 15.20.7875.019; Thu, 22 Aug 2024 18:13:19 +0000 From: Justin Giacobbi To: "pgsql-general@lists.postgresql.org" Subject: On exclusion constraints and validity dates Thread-Topic: On exclusion constraints and validity dates Thread-Index: Adr0vPfvz4IenfQpQ/yTf66AkbQkQA== Date: Thu, 22 Aug 2024 18:13:19 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=optionmetrics.com; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: BYAPR01MB4374:EE_|MN0PR01MB7780:EE_ x-ms-office365-filtering-correlation-id: e7eb4931-4e04-419c-6dcf-08dcc2d61a19 x-ms-exchange-atpmessageproperties: SA x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|1800799024|366016|376014|38070700018; x-microsoft-antispam-message-info: =?us-ascii?Q?6zza+nXzPCbdrEkS7QoI5UYQj4QxNqzcecFzbx6V6KrKCK4+gwwVncSSr/Wb?= =?us-ascii?Q?xGaKfbBhoYpDOraYavIHfssddXQ6FS/+gjbj9jEnFcaCXBOsjqmRh/MzmXg4?= =?us-ascii?Q?v7GWLs+8j3nPyduVo1jGeoL3t+Ft89ufNUd6T2y4X7sJyKFN86l9Kfq7MyK2?= =?us-ascii?Q?MKADCPh0AxxHKWnAM2i6KSTsTK/EQc/vmzW2ymOwLaUV0mi43V6pFU88RE5B?= =?us-ascii?Q?Gp8M7n3hZHDK4amz8Fs6T043sAK52jwc5huIo6Yt5at4iOPhmv/EAsyN1h00?= =?us-ascii?Q?/vTTn1BwKNkQHLlWVhN9sL+ci/NLbAQwONSPnWN19fwKAU68Adhvze4OiopY?= =?us-ascii?Q?9kpkJKlKnQ4qw5ibPSw/wCLN8UGvvEchJQD8AAB6xxPBAek2gxO7CTm0fTRK?= =?us-ascii?Q?L3YT6ErUQcp+06/a/PRnSrWiK6ndV5KpIyrcpXeYbGeKrD9IGoiEJmiQLSXT?= =?us-ascii?Q?Ue8DCC2g+1GFrNlaHhKPg62KixTOxbi56SI673TrKp7hqP7h/s86Lc1/vYuW?= =?us-ascii?Q?rMpz/dBmHC9zeo5393pdq9wktvk5i6N51d8cXWHZBK1MkyDN2pdN24XzSgWo?= =?us-ascii?Q?WzgNkHBFJEA5PkWXA4MrnJ3Vb1F51T+aLKEIJkoJrWNG9qoNG4a1jygbYHWn?= =?us-ascii?Q?u6u1Wqtg4gHdREtssI0E+TGJfMEvvWVPhNkZwoWVNsbO2AvWoPdDXzItdEaw?= =?us-ascii?Q?dc5x7poYW0X7B0Bz4gnIG5BId9BbsKB9toByGKjUfLpzFcDpaWooj0m133I9?= =?us-ascii?Q?pjhhePaCmiT8sNxRZ/PrmeJjk2oCPIAvj6xkqOFDZv0HajQX1QwhW09Y7wuY?= =?us-ascii?Q?y873TdnKCZApH6hqP0HfIm+dpFCKBJnf4LZqn4MpT8wwVKm1h937c4CQECfc?= =?us-ascii?Q?bag/AyQlSwIXHZkRMvGsOx0ZvdGGTGeA83PMG1g9W2D9MZOMDlUHplI9eBaS?= =?us-ascii?Q?t2WYhmKsW3KkBjHSoNSXJruqeq0BzXV9lMvUdaEkNYtFQxpsQrXZYYHUhlpD?= =?us-ascii?Q?Jgm8T1YNd5Z3RJP2z/8VWN3zfhfKA1kJYq2UzwNQt87sEMKSDY6tEqUXALMs?= =?us-ascii?Q?Rw7O1o6gFGa+2me0BUC5LKq+bkMlhk3JkK9F1m6XaCElJrc7gfMqVQI/Tu/D?= =?us-ascii?Q?uQutw+0yUX2ga3mcplvSF6YjA/w6Kv52t9HMT63X0o04CeS6W+MMAFOiEyo4?= =?us-ascii?Q?R9PaS/N4fSmFl/bh8IzhBnAL8osLrvnelpM21+7ebP83y23AW8WteIJyCgPV?= =?us-ascii?Q?QbxDEQFrcjuMYUaAORUTaL4BiZe5gyd4jkJjRKI+YI6JjQcE0msIphd/PbiY?= =?us-ascii?Q?ouW0f86xOyDl7Ru7BiFnn6eNT25P3X/28/0CueV984dR/3uiEcnFvppM30we?= =?us-ascii?Q?LS3ymHuwU2EZdI/WRGjS5aTr13yMePtcG8LwV8P9Qw2nNWziQg=3D=3D?= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:BYAPR01MB4374.prod.exchangelabs.com;PTR:;CAT:NONE;SFS:(13230040)(1800799024)(366016)(376014)(38070700018);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?us-ascii?Q?LqTJUxk119Yo4AD864neAhSuUs4UfaQE3KXMA/M5LfIHo7fl/F50kvHmqbiC?= =?us-ascii?Q?UZaCcwhjlWnF3EE0b8ywpbdoIuRqhlsv7wmtf0112tawSABN7ixL84PCy52h?= =?us-ascii?Q?s0C3FsXXLCsKKpfvF40jIrqg23L1isfriuyi/LS2xNj4WDmZVDISH9FcJ0BY?= =?us-ascii?Q?8B1WeRj/0YZm5uT9k7vQbmSuUKdKkqmSaDTpRds8Yksvfmx7gFxJ8BOV/tvo?= =?us-ascii?Q?RrUYdCL+Ukps356Epqoom0pUAWaYlA5QrR/dH0Vmpi9CqJFGFFEqnSzRTEhq?= =?us-ascii?Q?ZF7DwGwkTIuOpxo9Fh91R6wAxqDEuB0pX27xs8frxiC+wbd0cIgeB7+WNy4T?= =?us-ascii?Q?aKDmr+/aIfh3lbAsfQxT9o/NrlEVFqfnZ66v5kaJFR/Uk/hDpcdFF4f9Ahju?= =?us-ascii?Q?80xsOthrBD2OyPCsygL+eCZDaYbtCYu7yWj3r+40XkQTLRUG83Ci6IeUEX+q?= =?us-ascii?Q?9Z98d9ahiMDDly4AspXJJJp6nUUIM3CDyRL5Itvz0ofgBg5dh0k+lHUklTOt?= =?us-ascii?Q?fvMoZfjsc+u6iB8eZUnE6lxlbc+V5pGSIDDpBOvQhX7sHOZATTw/ZtxQlMGU?= =?us-ascii?Q?AM5sk3s9fVMOpPbW3EJ0sqv/6tlc9Sol1OfQjRAnKJnKZdJ00VpwcqkpAW0l?= =?us-ascii?Q?IA9GYCOxShIXMZaxePDems4O0O5hAl/gHiotP3RrrXIGzad2gsfNt5IS+fqB?= =?us-ascii?Q?cAMHoeAQfCWnyn/S7IcyrPz1MSbg0yWP3fCn9aO9Ngdj+VaX7uW9xm67LRq5?= =?us-ascii?Q?w+Q/2iEGdDOzWhcAdRhrWInPwHnKcL9ctNOTV1uv4PWlcH9qdBQCJvBB9vw/?= =?us-ascii?Q?JY5Ga2riiBmnlYMAIIkHmWkfsMJYNOjI8Yh6RQyK6NR41UTakNViW1BPup8F?= =?us-ascii?Q?eHwZCfBx3FjoDRQSJdN55mzc3qwZQ4lGCqBcmmeEDhtBzF5Hu0a8pbRuiA9+?= =?us-ascii?Q?+o/zwY5aUeUq7RlfU/mhbQ3WWMPld3mT+zIZBl0gV0b2nXjO0AHSuH2fV3hi?= =?us-ascii?Q?Stzyl7Wlse6WC2l2NjILNfhqJjBp58ysEjersGluQm0jusOs03O+H/t9SJ3L?= =?us-ascii?Q?9CQmerNiNyyfFAjrBTtbNiThH38zfyPGvoPDBrCI/TLuvzYcV7YG6uX6nxVH?= =?us-ascii?Q?CTh61Ig4c1CkfuCHzHr1MIfMEB7LZP+Jla6K2e4rbMvi1IuJg7P6rIFqs62x?= =?us-ascii?Q?cSgO0MXw/nAZS6qUiEIkXy1z3VTxV37q+hWXNYNNXZg7LTET169At28XwLO/?= =?us-ascii?Q?VQgSgfxUqc7OfQ30aFF+6vy98eT6g9nUYRPn9Ri1yXu90nwerKL6//1Vi21n?= =?us-ascii?Q?m7+WypEJxP+kcmIzkuN2R0wZc9j7NiPWfYzF5W78RtKWBGLKOaSUWGs2F7z/?= =?us-ascii?Q?WvPlzkO42hitMawtAZBhKpxL4r3aAeoKa+09QwC4uou1icaqsS3tDyHVoaI6?= =?us-ascii?Q?lE8M96c+uIaskTUou0LXcTbKZBzz0r5lfToVSRZyG1yKavUgz+QiIQhXNorN?= =?us-ascii?Q?0aWnXdaH98XA4VK51xgGTLeNobqOAwTEJXy0w5G3uWhM6fRlExni83tYUOQD?= =?us-ascii?Q?zCnAfz+LO+KoaZmAHtx5O+aBlNxm7qqFD/K1eMpv?= Content-Type: multipart/alternative; boundary="_000_BYAPR01MB4374D78F63C66F0ED4178696A18F2BYAPR01MB4374prod_" MIME-Version: 1.0 X-OriginatorOrg: optionmetrics.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: BYAPR01MB4374.prod.exchangelabs.com X-MS-Exchange-CrossTenant-Network-Message-Id: e7eb4931-4e04-419c-6dcf-08dcc2d61a19 X-MS-Exchange-CrossTenant-originalarrivaltime: 22 Aug 2024 18:13:19.6340 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: dac783ea-6427-4b8a-a09b-ad9c80e23bd0 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: lxb45+NU0p+FcShLpJop3JI/YHSL9EBBGhtyd8HYKYpWEK7NNLDupkXrCpqolTGNpPaWnuxIKc12i/LbU4wkkSD95KWVhgodfxGJY29maY0= X-MS-Exchange-Transport-CrossTenantHeadersStamped: MN0PR01MB7780 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_BYAPR01MB4374D78F63C66F0ED4178696A18F2BYAPR01MB4374prod_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hello, I have an issue that on the surface seems orthogonal to existing functional= ity. I'm trying to dynamically update validity ranges as new s repla= ce old s. In a nutshell the problem looks like this: psqlprompt=3D# select * from rangetest; id | rangecol ----+----------------------------------------------------- 0 | empty 0 | ["2024-05-05 00:00:00+00","2024-05-06 00:00:00+00") 0 | ["2024-05-06 00:00:00+00","9999-03-31 00:00:00+00") 1 | ["2024-05-06 00:00:00+00",) psqlprompt=3D# insert into rangetest values (1, '["2024-06-07 00:00:00+0",)= ') on conflict on constraint rangetest_id_ran gecol_excl do update rangecol =3D concat('[', lower(rangetest.rangecol),','= , lower(excluded.ran gecol),')')::tstzrange; ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints So I'm not sure if I'm after a feature request, a workaround or contributio= n advice. Maybe someone can point me in the right direction. 1. A 'currently valid' item that becomes invalid and is replaced by a ne= w 'currently valid' item seems like such a real-world use case that there s= hould be explicit support for it. * Unfortunately, the temporal tables extensions seem too immature fo= r my needs currently. 2. Barring that an exclusion constraint arbiter would be a lovely soluti= on. 3. Barring either of those at least a 'select all conflicts' type featur= e that at least makes it easy to pair the offending rows. Currently I'm looking at working around this in the application or in a sto= red procedure/insert trigger that is essentially the same logic. Whichever = seems easier to maintain. Advice on how to submit a feature request, or maybe a better workaround tha= t I haven't discovered would be most welcome. What would be even more welco= me is someone with insight into these pieces of the program that can tell m= e if I'd be biting off more than I can chew (or violating a principle) tryi= ng to submit one of the three options above as a feature. Thank you --_000_BYAPR01MB4374D78F63C66F0ED4178696A18F2BYAPR01MB4374prod_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hello,

 

I have an issue that on the surface seems orthogonal= to existing functionality. I’m trying to dynamically update validity= ranges as new <thing>s replace old <thing>s.

 

In a nutshell the = problem looks like this:

 <= /p>

psqlprompt=3D# select * from rangetest;

id |        =             &nb= sp; rangecol

----+-----------------------------------------------= ------

  0 | empty

  0 | ["2024-05-05 00:00:00+00","= ;2024-05-06 00:00:00+00")

  0 | ["2024-05-06 00:00:00+00","= ;9999-03-31 00:00:00+00")

  1 | ["2024-05-06 00:00:00+00",)

 

psqlprompt=3D# insert into rangetest values (1, '[&q= uot;2024-06-07 00:00:00+0",)') on conflict on constraint rangetest_id_= ran

gecol_excl do update rangecol =3D concat('[', lower(= rangetest.rangecol),',', lower(excluded.ran

gecol),')')::tstzrange;

ERROR:  ON CO= NFLICT DO UPDATE not supported with exclusion constraints

 

 

So I’m not sure if I’m after a feature r= equest, a workaround or contribution advice. Maybe someone can point me in = the right direction.

  1. A ‘currently valid’ item that becomes invalid and is repl= aced by a new ‘currently valid’ item seems like such a real-wor= ld use case that there should be explicit support for it.
  2. Unfortunately, the temporal tables extensions seem too immature for m= y needs currently.
  • Barring that an exclusion constraint arbiter would be a lovely soluti= on.
  • Barring either of those at least a ‘select a= ll conflicts’ type feature that at least makes it easy to pair the of= fending rows.

    •  

    Currently I’m looking at working around this i= n the application or in a stored procedure/insert trigger that is essential= ly the same logic. Whichever seems easier to maintain.

     

    Advice on how to submit a feature request, or maybe = a better workaround that I haven’t discovered would be most welcome. = What would be even more welcome is someone with insight into these pieces o= f the program that can tell me if I’d be biting off more than I can chew (or violating a principle) trying to submi= t one of the three options above as a feature.

     

    Thank you

    --_000_BYAPR01MB4374D78F63C66F0ED4178696A18F2BYAPR01MB4374prod_--