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 1sHDSG-004k7M-V4 for pgsql-bugs@arkaria.postgresql.org; Wed, 12 Jun 2024 02:11:18 +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 1sHDSD-00C8KZ-Bj for pgsql-bugs@arkaria.postgresql.org; Wed, 12 Jun 2024 02:11: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 1sHDSB-00C8KR-TX for pgsql-bugs@lists.postgresql.org; Wed, 12 Jun 2024 02:11:13 +0000 Received: from mail-tycjpn01olkn2096.outbound.protection.outlook.com ([40.92.99.96] helo=JPN01-TYC-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 1sHDS6-000suj-2V for pgsql-bugs@lists.postgresql.org; Wed, 12 Jun 2024 02:11:10 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=dkp7XUSazyG4R/Z5Cw+N0K2hzM/JuQ/HpvnoBEuFqdf3GJ42wcBsJ6paaV4+a1Ja9hfUKfBQLDANfrpZ+8HNKvj7bzJ7rELseiyBV1niI70NfxzLdHUKSlPXo4XqCcnqZ3sxUcsSP27F6l0hbm+V9y7ch2hwwDUSg9yyY2Sg8MwXR45feJfnRcRth15jQ/r+YSg+mwbNKs6xGpwGOk9/BVwKwl0xgSdYUN4u1XrKQAG7SB0yKMOwTraa6ncsVQQouQcrRFpTsSRpeRKnVQs4VMlxUf7226oKXFWHd7v7zaagJR2yv+wL3ymLponRXNOCIx05pA01M6kG1QEQPWEeDQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; 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=Olxqsigt8AkuQA9e08eRb7Krs7KCGx0AxN3lvjK8KIA=; b=VAoR18z+GWfGyFOnU5+4z1msT5ZeyMYFEqwssx4o9mSpxyP0xzlDf1DqRf11nh8o67B5r4qNMMSmo5ns40W0DVxsHfQJ5dkmDXQLxjldZNVVnXZV9hXzrfGQ1vENl2O3wa3v/0V9tN9SAXl8IglLQU/SFQ7JkaaDyXs+abvClSjBzfiQSubnfBVBLkDWYPscsGPVXOzc+7r+AEKxByuxZ1PNX2rXzN7PW96pu/ITPOPNsRU9wAKI2CqH+BMmfXGD70GTeacl73sd9G3gaTrwm2WwPVTF5TsEixINIInNltv0Shgl3EPwuWdNTQNBCnQ48iPfM3Qx/xI2MFuSprWupQ== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=none; dmarc=none; dkim=none; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=outlook.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=Olxqsigt8AkuQA9e08eRb7Krs7KCGx0AxN3lvjK8KIA=; b=iJwUeONxjkPqZZfrY+s4rBFzl2VmbBPjtly4ZwXKFMPVNbWMnAb5woNvrhu+VpDM0FTQmCPRCU9cGXJzVZu5AsUcxGguAzORLQ+le69Q8l+ZU0/bj3Q7dBj4l/QPvKVxlE3C4qtaj2apVYAEWkSZCtTQB/qtJgiQIYaK2jgJMz6U7q6Z5j5VwnfjPInojmIlGQjA3j1AMXf6+0XvhP8HwjLoH3mcVnbrnDdutxEg6JSqyXpP1HyX8/1u7hC1P90iH9ob1byfoxUUXhx45tOSngx0N54wz3fJTa8NolTcUDXIY3tqeswUiPU8NfkjfPlMPwjWOXmHLvNExfz7F2+iFw== Received: from TYCP286MB2861.JPNP286.PROD.OUTLOOK.COM (2603:1096:400:304::9) by OS9P286MB3978.JPNP286.PROD.OUTLOOK.COM (2603:1096:604:2d5::13) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7633.38; Wed, 12 Jun 2024 02:11:01 +0000 Received: from TYCP286MB2861.JPNP286.PROD.OUTLOOK.COM ([fe80::9d81:f3f7:adce:fa1d]) by TYCP286MB2861.JPNP286.PROD.OUTLOOK.COM ([fe80::9d81:f3f7:adce:fa1d%6]) with mapi id 15.20.7633.037; Wed, 12 Jun 2024 02:11:01 +0000 From: =?gb2312?B?zfUguqM=?= To: Tom Lane CC: Peter Geoghegan , "pgsql-bugs@lists.postgresql.org" Subject: =?gb2312?B?u9i4tDogQlVHICMxODUwMjogVXBzZXJ0IG9uIHZpZXcgcmV0dXJucyA0MlAx?= =?gb2312?Q?0_error_when_condition_is_an_expression?= Thread-Topic: BUG #18502: Upsert on view returns 42P10 error when condition is an expression Thread-Index: AQHau94KYpafYo/M7EOomKswWI6xG7HDC2CAgABYEK0= Date: Wed, 12 Jun 2024 02:11:01 +0000 Message-ID: References: <18502-545b53f5b81e54e0@postgresql.org> <1153168.1718139271@sss.pgh.pa.us> In-Reply-To: <1153168.1718139271@sss.pgh.pa.us> Accept-Language: zh-CN, en-US Content-Language: zh-CN X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: x-ms-exchange-messagesentrepresentingtype: 1 x-tmn: [sV2sLkZMZykG2mY6AgGxqODwpLLAqaCW] x-ms-publictraffictype: Email x-ms-traffictypediagnostic: TYCP286MB2861:EE_|OS9P286MB3978:EE_ x-ms-office365-filtering-correlation-id: 04da80ad-b8d8-40ca-68f0-08dc8a84e7fe x-microsoft-antispam: BCL:0;ARA:14566002|461199020|3412199017|102099026|3430499026|440099020; x-microsoft-antispam-message-info: LGdD6/SUC5Fg8VuJf0PK+2HklpHUYYerGeJ++aoXVAazL9jdP4miGX9PkIEzi2zOF5sfQrcppQLXX7WTn4kzoA25SfRPLgwFZ+fmBJpWv2GgDi2lIsLTUjd8r6bCYp6BFLoOvmD3Gl9gJHzAlN7rmoaMXC2Y6EBVUlNSHqmAgY21Xxf4bmyvvQH8zQ77MZYm1/mt4ltleuNXQap5/i7gF1hxI65UhUEAhFqu1e0NXGQD8fihHh17zzLiKsRAv+NYPd6hmnSO+fRd0FNewTMqPc9DaFnA40fo/tHv3a56/huUG0RhL+nLF3T6e6FNDRErwqlieJUUoPWyxKuMURA5HGu5KopN9/Oz68cfdlQLuqnjjdDXZJeFHx0L7I8XBzIN62Tp4NyJvk1VBlKrYUg2njS03ueW6nLPv0L95my4NHA3Ag12TV29WkWO2BYcky6p9x6/POjOvogmoz2bw3hD7wiQ2lxqA5AdIQBb45sCCBzmbIWcBzyqbSHjf+zwsZOzlda6lxv+mbEt38P6pUOBps77P8texxuOtvgpGb3fGa/PqEeeQXuY507BIXeVlCYw x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?gb2312?B?WC9mU29sUjNHMGh4SkxUK3NWdlU2K3YvQ1JsMTdEZ3Q3dlI1V0R5YkVJTGJh?= =?gb2312?B?aHpaaWQyUXNlMUFpTERxcXpON1FnTWFJNGtFa0VpdDJmMTRUL2swKytjai9G?= =?gb2312?B?M1FTVVpvMWt1aW1waDVmbmQwaktsb2F6TkdaMzNrRG1RN1d0Q2w5Sy9kdExN?= =?gb2312?B?clhaYU92OHBDOGpSVUFjTVFoVVkzbmFBb1d6M1l2c3U5ZFkwaHREMGxtNTJo?= =?gb2312?B?aTJZdUwva3BPRzJmdFQ2UTNrQVhKSlA2Uit5RVhEZVBPanp6L3U2WVZvaWlQ?= =?gb2312?B?UjF4NkVjcmQxcjRFallSc29FSEsvMk44S3F2QlNYQVFnZG4zMHlPYTNFZmVW?= =?gb2312?B?KzZaeVkzQkxoVHpYQVlMNk54QTZQQ2pCMEpHRkJscXNLd0ZZWUtCU09CdDNP?= =?gb2312?B?ak5UMStmTDFIQytZWkhFbGRKc2NTUzNack9ENndZclpvOTFOd1RCOWFxYkYv?= =?gb2312?B?V0ZYYkNkMERYKzFTVkpEYkdVcXlpeEFzekJ2WTVRTlU1c2V5Uk96TjMwL0Q3?= =?gb2312?B?WkdDdEEzOUlXYVRmZCtTREtrTmI3YUJKVCtuQ3NzanUvWjdIWWUzaHM2TkMr?= =?gb2312?B?dC8zVTNmcmZqRS85RFAxTm1icmxOSHMxTWl2K0xtNVZ0MjdOUnR5dXRPWEdY?= =?gb2312?B?TDZuQ3MrL3ZnU3dpdGtibTVvcDZvMXhTK0ZuWXR2VytlSUlGN2d5eVVBZElS?= =?gb2312?B?YVNJRVZtWGZPd1dqMnIrcnFUSkFPd0RlWHRPbUZabnAzN21vL2t0dm9yQjlm?= =?gb2312?B?alFmVDk1ZUlCRlhWelUwT0s2a2N5UHdhQ1BaS2k4aWlkV0hJMGhLTGUzVFRj?= =?gb2312?B?bWVUb2dxQ2laWnBUTTVNQTY2cHdlL0xnSEdVNkZuTDhPYUU3T1R2RGlEc0d4?= =?gb2312?B?UUs1bFVSMkx1MXB0elhiQ1FNOTFoTzJldU9jdElId0lzanpQTnBTWHovalFa?= =?gb2312?B?VHF0QVRZU0JRaXo4TDdEVDhaK0xiTzluZi9XU1RjeXJTa0JyYW14NU9NazVJ?= =?gb2312?B?UHFpOUpCR3NxTGFXdC9ybFlXa1RnTzAxOFNBWHFEQ3dMM2ZwNzBHdDRSakdY?= =?gb2312?B?dHF1M1NxYVVaSnV6WTdQTXVDTnlVR0F1bDBsLzZMWUtDN1ZxWU5ETW5SaHhQ?= =?gb2312?B?ZGRNUW1Da0xxaVlZVEViVVFiUlJXVFBwRFZiOUYwbElwY3JYb21YV2ErMHAv?= =?gb2312?B?Q05CdUptcE5TbGNUVWJIazM5cnBsKzJiNzcwVFpJT2g5Qzd0Zzc4WTAzU3RH?= =?gb2312?B?em4xbUNJMVcrY2l0SVhnQWl4SVZMYmdEQTNqK0hDVmd1N0U2a3NyazJsU2FG?= =?gb2312?B?MnV3ZWFXYy9walhyYzI3T2s5Tm1Mb2dnTVhSOVVrSm1lWTdqeUxOajdXS3Nj?= =?gb2312?B?TjJFRjNOaFdDU2NaRlRUU2J0QXVNSEp1VXRhdy9ySDVudExYYmVaeENPR2c5?= =?gb2312?B?RExxVWIzaWtjaGkwNlFYNXNlbHc0S3ZVZXNpelZrUXczN2c0dTBJVzBlTnJP?= =?gb2312?B?bGZvRjFRU3BkRjFScEdTWUZKQ2oxMXQrdU84c2oxaHhoaVR3cHBORWhDdEE4?= =?gb2312?B?YzRyUWRMWkZ6dWZnUnZOaHRuOFRRRWY0TmhYVjRDaFNOM2FtU0JvWDMrOExr?= =?gb2312?Q?nDb1tUGHdVlR4PbF45dx2+Bus8w6VU3innHGoXQZ1rSE=3D?= Content-Type: multipart/alternative; boundary="_000_TYCP286MB28619F2053FA2AFB7BDC6ED8EAC02TYCP286MB2861JPNP_" MIME-Version: 1.0 X-OriginatorOrg: outlook.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: TYCP286MB2861.JPNP286.PROD.OUTLOOK.COM X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-CrossTenant-Network-Message-Id: 04da80ad-b8d8-40ca-68f0-08dc8a84e7fe X-MS-Exchange-CrossTenant-originalarrivaltime: 12 Jun 2024 02:11:01.1775 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-Transport-CrossTenantHeadersStamped: OS9P286MB3978 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_TYCP286MB28619F2053FA2AFB7BDC6ED8EAC02TYCP286MB2861JPNP_ Content-Type: text/plain; charset="gb2312" Content-Transfer-Encoding: base64 VGhhbmtzIGZvciB0aGUgYW5hbHlzaXMuIE1heSBJIGFzayBob3cgbG9uZyB3aWxsIGl0IGNvc3Qg bm9ybWFsbHkgZm9yIHN1Y2ggYnVnIGZpeCBiZWluZyByZWxlYXNlZCBzaW5jZSBJJ20gbmV3IHRv IHRoZSBjb21tdW5pdHk/IEFuZCB3aWxsIGl0IGJlIGJhY2sgcG9ydCB0byBQRyAxMj8NCg0KSGFp IFdhbmcNCl9fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fDQq3orz+yMs6IFRvbSBMYW5l IDx0Z2xAc3NzLnBnaC5wYS51cz4NCreiy83KsbzkOiAyMDI0xOo21MIxMsjVIDQ6NTQNCsrVvP7I yzogbWljaGFlbC53YW5naGFpLmFAb3V0bG9vay5jb20gPG1pY2hhZWwud2FuZ2hhaS5hQG91dGxv b2suY29tPg0Ks63LzTogUGV0ZXIgR2VvZ2hlZ2FuIDxwZ0Bib3d0LmllPjsgcGdzcWwtYnVnc0Bs aXN0cy5wb3N0Z3Jlc3FsLm9yZyA8cGdzcWwtYnVnc0BsaXN0cy5wb3N0Z3Jlc3FsLm9yZz4NCtb3 zOI6IFJlOiBCVUcgIzE4NTAyOiBVcHNlcnQgb24gdmlldyByZXR1cm5zIDQyUDEwIGVycm9yIHdo ZW4gY29uZGl0aW9uIGlzIGFuIGV4cHJlc3Npb24NCg0KUEcgQnVnIHJlcG9ydGluZyBmb3JtIDxu b3JlcGx5QHBvc3RncmVzcWwub3JnPiB3cml0ZXM6DQo+IEkgaGF2ZSBhIHRhYmxlLCBpbmRleCBh bmQgdmlldyBsaWtlIGZvbGxvd2luZzoNCj4gYGBgDQo+IENSRUFURSBUQUJMRSBteV90YWJsZQ0K PiAoDQo+ICAgICBpZCAgIHV1aWQgcHJpbWFyeSBrZXksDQo+ICAgICBkYXRhIGpzb25iIG5vdCBu dWxsDQo+ICk7DQo+IENSRUFURSBVTklRVUUgSU5ERVggT04gbXlfdGFibGUgKChkYXRhIC0+PiAn a2V5JykpOw0KPiBDUkVBVEUgVklFVyBteV92aWV3IEFTIFNFTEVDVCAqIEZST00gbXlfdGFibGU7 DQo+IGBgYA0KPiBUaGUgdXBzZXJ0IG9uIHZpZXcgcmV0dXJucyA0MlAxMCBlcnJvciB3aGVuIEkg ZXhlY3V0ZSB0aGUgZm9sbG93aW5nIFNRTA0KPiBgYGANCj4gSU5TRVJUIElOVE8gbXlfdmlldyAo aWQsIGRhdGEpDQo+IFZBTFVFUyAoJzk5MGNjNzVjLTJlNjAtNGMwZC04YmVjLTlhYzk3NmRjMDNi Yyc6OnV1aWQsDQo+ICAgICAgICAgJ3sNCj4gICAgICAgICAgICJrZXkiOiAidmFsdWUiDQo+ICAg ICAgICAgfSc6Ompzb25iKQ0KPiBPTiBDT05GTElDVCAoKGRhdGEgLT4+ICdrZXknKSkNCj4gICAg IERPIE5PVEhJTkc7DQo+IGBgYA0KDQpGb3IgdGhlIGFyY2hpdmVzJyBzYWtlOiB0aGUgZXJyb3Ig YmVpbmcgY29tcGxhaW5lZCBvZiBpcw0KDQpFUlJPUjogIHRoZXJlIGlzIG5vIHVuaXF1ZSBvciBl eGNsdXNpb24gY29uc3RyYWludCBtYXRjaGluZyB0aGUgT04gQ09ORkxJQ1Qgc3BlY2lmaWNhdGlv bg0KDQpUaGFua3MgZm9yIHRoZSByZXBvcnQuICBUaGUgZmF1bHQgbGllcyB3aXRoIGluZmVyX2Fy Yml0ZXJfaW5kZXhlcygpLA0Kd2hpY2ggYXNzdW1lcyB0aGF0IGl0IGNhbiBtYXRjaCB0aGUgb3V0 cHV0IG9mDQpSZWxhdGlvbkdldEluZGV4RXhwcmVzc2lvbnMgb3IgUmVsYXRpb25HZXRJbmRleFBy ZWRpY2F0ZQ0KZGlyZWN0bHkgdG8gdGhlIHF1ZXJ5IHdpdGhvdXQgYWRqdXN0aW5nIHZhcm5vcy4g IFRoYXQgd29ya3MNCm1vc3Qgb2YgdGhlIHRpbWUsIGJlY2F1c2UgdGhlIElOU0VSVCB0YXJnZXQg dHlwaWNhbGx5IGhhcw0KdmFybm8gMSwgbWF0Y2hpbmcgdGhlIHdheSB0aGVzZSB0cmVlcyBhcmUg c3RvcmVkIGluIHRoZSBjYXRhbG9ncy4NCkJ1dCBub3Qgc28gbXVjaCB3aGVuIHdlJ3ZlIGZsYXR0 ZW5lZCBhbiB1cGRhdGFibGUgdmlldzsNCnNvIHRoZSBtYXRjaCBmYWlscyBldmVuIHdoZW4gaXQg c2hvdWxkIHN1Y2NlZWQuDQoNClRoZSBhdHRhY2hlZCBzZWVtcyB0byBiZSBlbm91Z2ggdG8gZml4 IGl0Lg0KDQogICAgICAgICAgICAgICAgICAgICAgICByZWdhcmRzLCB0b20gbGFuZQ0KDQo= --_000_TYCP286MB28619F2053FA2AFB7BDC6ED8EAC02TYCP286MB2861JPNP_ Content-Type: text/html; charset="gb2312" Content-Transfer-Encoding: quoted-printable
Thanks for the analysis. May I ask how long will it cost normally for such = bug fix being released since I'm new to the community? And will it be back = port to PG 12?

Hai Wang
=B7=A2=BC=FE=C8=CB: Tom Lan= e <tgl@sss.pgh.pa.us>
=B7=A2=CB=CD=CA=B1=BC=E4: 2024=C4=EA6=D4=C212=C8=D5 4:54
=CA=D5=BC=FE=C8=CB: michael.wanghai.a@outlook.com <michael.wangha= i.a@outlook.com>
=B3=AD=CB=CD: Peter Geoghegan <pg@bowt.ie>; pgsql-bugs@lists.p= ostgresql.org <pgsql-bugs@lists.postgresql.org>
=D6=F7=CC=E2: Re: BUG #18502: Upsert on view returns 42P10 error whe= n condition is an expression
 
PG Bug reporting form <noreply@postgresql.org&g= t; writes:
> I have a table, index and view like following:
> ```
> CREATE TABLE my_table
> (
>     id   uuid primary key,
>     data jsonb not null
> );
> CREATE UNIQUE INDEX ON my_table ((data ->> 'key'));
> CREATE VIEW my_view AS SELECT * FROM my_table;
> ```
> The upsert on view returns 42P10 error when I execute the following SQ= L
> ```
> INSERT INTO my_view (id, data)
> VALUES ('990cc75c-2e60-4c0d-8bec-9ac976dc03bc'::uuid,
>         '{
>           "key&= quot;: "value"
>         }'::jsonb)
> ON CONFLICT ((data ->> 'key'))
>     DO NOTHING;
> ```

For the archives' sake: the error being complained of is

ERROR:  there is no unique or exclusion constraint matching the ON CON= FLICT specification

Thanks for the report.  The fault lies with infer_arbiter_indexes(), which assumes that it can match the output of
RelationGetIndexExpressions or RelationGetIndexPredicate
directly to the query without adjusting varnos.  That works
most of the time, because the INSERT target typically has
varno 1, matching the way these trees are stored in the catalogs.
But not so much when we've flattened an updatable view;
so the match fails even when it should succeed.

The attached seems to be enough to fix it.

            &nb= sp;           regards, to= m lane

--_000_TYCP286MB28619F2053FA2AFB7BDC6ED8EAC02TYCP286MB2861JPNP_--