Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kcREv-0004cv-P3 for pgsql-bugs@arkaria.postgresql.org; Tue, 10 Nov 2020 10:51:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kcREu-0000hl-LL for pgsql-bugs@arkaria.postgresql.org; Tue, 10 Nov 2020 10:51:04 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kcR7b-0006Fz-1u for pgsql-bugs@lists.postgresql.org; Tue, 10 Nov 2020 10:43:31 +0000 Received: from mail-pl1-x62d.google.com ([2607:f8b0:4864:20::62d]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kcR7Y-0004dZ-8d for pgsql-bugs@lists.postgresql.org; Tue, 10 Nov 2020 10:43:30 +0000 Received: by mail-pl1-x62d.google.com with SMTP id t6so6271028plq.11 for ; Tue, 10 Nov 2020 02:43:28 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=date:from:to:cc:subject:references:mime-version:message-id; bh=R3Q1OJbS+3WTTAXPf8OYB939cLqykgIoy2Cn+QejKEk=; b=YN9M22ZWz5ZOQRGZJ4+tnFVNZFhPduRHvEkIFZi/kw0tAThfP7uGOvHi+CgLH/mMvz JKW+kDHQm3VCiuHjvpnSCRF6ACWZS3qPvrOkiD3HbgcKM6o1/X39L2a+LNjfsokX0FEQ 2oE3tqScrwPME6VZO+syvcf12CWlz1PeWRM1LttRhhr/uG2hBq4eDW+AJA7JQtDSMOOm imgwux1S0kEc5YIjlvlKC78K2I4ov1T6Zm9Nb4YMvF25NW73kKQsty2gPjFq6qdr2fWu AQFKZoL8p+mPQlwzrWRz7MQ7OGoVmms3pMy4kDI90IT9ttFY2QZIomyKzVCUGNNB6QLb D26Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:references:mime-version :message-id; bh=R3Q1OJbS+3WTTAXPf8OYB939cLqykgIoy2Cn+QejKEk=; b=ZKhdpj/yw0yE0wQqZGUio5LeYjdM2x6a8+NHd+YHP2Qweyp5Kk90ZQCt21plR4+bGJ pV/on5eA5sUUJZuIcOlzZ8VRbLFtapcO0DTmMvm80eEiMLh5wjTYqRqc03ouCsoUfWhn yKbiYcsSwOa6jrMYOXN6sqBZ5EVSooOdeZ3m1lfPCKYX4cOhCVXQexODfCCx8api7IPl s8MzGafyDsDBCiTk0ir5cXx7GprzcTEM8FVgkEPJPbLFg/ZbA8g+BUvUftnNn1OeoW25 C+HJLpxF1Qoybxfps916Wi9xWaOC1qPuBALOo9qv0P1N6gpsL1aGbzrq5dp64y9oUbmP aNZA== X-Gm-Message-State: AOAM533/E1pJB/kIArhf92d1ZOLMqjkK8XHmhAPiF1bJ+sxME1f32TM2 n5DnQlsNPHDUQ/ovw/vUE2s= X-Google-Smtp-Source: ABdhPJxuvHJRqiVyvR133TVYGF6dPCfqT+aHJB/SR9dXmCGSgNsPLSWssXpgNRJOSMrV7+tUXtETLQ== X-Received: by 2002:a17:902:c14b:b029:d6:ab18:108d with SMTP id 11-20020a170902c14bb02900d6ab18108dmr17015834plj.20.1605005006282; Tue, 10 Nov 2020 02:43:26 -0800 (PST) Received: from DESKTOP-1SRNIOQ ([8.210.32.134]) by smtp.gmail.com with ESMTPSA id fh22sm2708269pjb.45.2020.11.10.02.43.24 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Tue, 10 Nov 2020 02:43:25 -0800 (PST) Date: Tue, 10 Nov 2020 18:43:24 +0800 From: "zhongxuchen@gmail.com" To: "Andres Freund" , "Tom Lane" Cc: "Vik Fearing" , pgsql-bugs Subject: Re: Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint References: <16706-08837c0cdaf4ce57@postgresql.org>, <1974489.1604935011@sss.pgh.pa.us>, , <1996438.1604952901@sss.pgh.pa.us>, <20201110042524.voeknzrrke7hschl@alap3.anarazel.de> X-Priority: 3 X-GUID: 18A1BAB7-B7B0-4EE2-8A2F-1FD1D19C8FC0 X-Has-Attach: no X-Mailer: Foxmail 7.2.18.111[cn] Mime-Version: 1.0 Message-ID: <2020111018432320226511@gmail.com> Content-Type: multipart/alternative; boundary="----=_001_NextPart658807845060_=----" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. ------=_001_NextPart658807845060_=---- Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: base64 SEkhDQogICAgIFdpbGwgdGhpcyBwcm9ibGVtIHByb3ZpZGUgc3VwcG9ydD8NCiAgICAgUGVyc29u YWxseSwgaWYgdGhpcyBwcm9ibGVtIGlzIG5vdCBzb2x2ZWQsIGl0IG1lYW5zIHRoYXQgIm9uIGNv bmZsaWN0ICgpIGRvIHVwZGF0ZSIgaXMgbWVhbmluZ2xlc3MhDQogICAgIA0KICAgICDpnZ7luLjl uIzmnJtwb3N0Z3Jlc3Fs5Y+v5Lul6Kej5Yaz5q2k6Zeu6aKY77yM6K6pcG9zdGdyZXNxbOabtOWK oOWujOe+ju+8geS5n+iuqeS4quS6uueahOW8gOa6kG9ybemhueebruabtOWKoOWujOe+ju+8iGh0 dHBzOi8vZ2l0aHViLmNvbS9zYWdmcmFtZS9zYWdhY2l0eS1zcWx0b3nvvIkNCiAgICAg6Z2e5bi4 5oSf6LCi5ZCE5L2N55qE6L6b6Ium5ZKM5LuY5Ye677yBDQogICAgIA0KDQoNCg0Kemhvbmd4dWNo ZW5AZ21haWwuY29tDQogDQpGcm9tOiBBbmRyZXMgRnJldW5kDQpEYXRlOiAyMDIwLTExLTEwIDEy OjI1DQpUbzogVG9tIExhbmUNCkNDOiBWaWsgRmVhcmluZzsgemhvbmd4dWNoZW47IHBnc3FsLWJ1 Z3MNClN1YmplY3Q6IFJlOiBCVUcgIzE2NzA2OiBpbnNlcnQgaW50byBvbiBjb25mbGljdChwaykg ZG8gdXBkYXRlIGVycm9yIHZpb2xhdGVzIG5vdC1udWxsIGNvbnN0cmFpbnQNCkhpLA0KIA0KT24g MjAyMC0xMS0wOSAxNToxNTowMSAtMDUwMCwgVG9tIExhbmUgd3JvdGU6DQo+IFZpayBGZWFyaW5n IDx2aWtAcG9zdGdyZXNmcmllbmRzLm9yZz4gd3JpdGVzOg0KPiA+IEkgdGhpbmsgdGhlIGlzc3Vl IGlzIHRoZSBub3QgbnVsbCBjb25zdHJhaW50IGlzIGV2YWx1YXRlZCBiZWZvcmUgdGhlIE9ODQo+ ID4gQ09ORkxJQ1QgYW5kIHNvIHRoZXJlIGlzIG5vIGNoYW5jZSB0byAiaGVhbCIgdGhlIHJvdyBi eSBjb2FsZXNjaW5nIHRoZQ0KPiA+IG9sZCBhbmQgbmV3IHZhbHVlcy4NCj4gDQo+IEFoLiAgV2Vs bCwgT04gQ09ORkxJQ1QgaXMgZm9yIHJlc29sdmluZyBkdXBsaWNhdGUta2V5IGVycm9yczsNCj4g aXQncyBub3QgYSBnZXQtb3V0LW9mLWphaWwtZnJlZSBjYXJkIGZvciBldmVyeSBzb3J0IG9mIGVy cm9yLg0KPiANCj4gSSB0aGluayB0aGUgcmlnaHQgd2F5IHRvIGhhbmRsZSB0aGlzIGV4YW1wbGUg d291bGQgYmUgd2l0aCBhDQo+IGJlZm9yZS11cGRhdGUgdHJpZ2dlciwgd2hpY2ggSUlSQyBjYW4g bW9kaWZ5IHRoZSByb3cgYmVmb3JlDQo+IHdlIGFwcGx5IGFueSB0YWJsZSBjb25zdHJhaW50IGNo ZWNrcy4NCiANClNvdW5kcyBsaWtlIHRoZSB0by1iZS11cHNlcnRlZCByb3cgY291bGQgbmV2ZXIg YmUgaW5zZXJ0ZWQsIGJ1dCBvbmx5DQp1cGRhdGVkLiBJbiB3aGljaCBjYXNlIGFuIFVQREFURSBz b3VuZHMgbW9yZSBhcHByb3ByaWF0ZSB0aGFuIGFuIElOU0VSVA0KLi4uIE9OIENPTkZMSUNUIFNF VCAuLi4gLSBhbSBJIG1pc3Npbmcgc29tZXRoaW5nPw0KIA0KR3JlZXRpbmdzLA0KIA0KQW5kcmVz IEZyZXVuZA0K ------=_001_NextPart658807845060_=---- Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable = =0A
HI!
     Will t= his problem provide support?
     Personally, if = this problem is not solved, it means that "on conflict () do update" is me= aningless!
     
     = =E9=9D=9E=E5=B8=B8=E5=B8=8C=E6=9C=9Bpostgresql=E5=8F=AF=E4=BB=A5=E8=A7=A3= =E5=86=B3=E6=AD=A4=E9=97=AE=E9=A2=98=EF=BC=8C=E8=AE=A9postgresql=E6=9B=B4= =E5=8A=A0=E5=AE=8C=E7=BE=8E=EF=BC=81=E4=B9=9F=E8=AE=A9=E4=B8=AA=E4=BA=BA= =E7=9A=84=E5=BC=80=E6=BA=90orm=E9=A1=B9=E7=9B=AE=E6=9B=B4=E5=8A=A0=E5=AE= =8C=E7=BE=8E=EF=BC=88https://github.com/sagframe/sagacity-sqltoy=EF=BC=89<= /div>
     =E9=9D=9E=E5=B8=B8=E6=84=9F=E8=B0=A2=E5=90= =84=E4=BD=8D=E7=9A=84=E8=BE=9B=E8=8B=A6=E5=92=8C=E4=BB=98=E5=87=BA=EF=BC= =81
     
=0A


=0A
 
From: = ;Andres Freund
Date= : 2020-11-10 12:25
Subject: Re: BUG #16706: inse= rt into on conflict(pk) do update error violates not-null constraint
=
Hi,
=0A
 
=0A
On 2020-11-09 1= 5:15:01 -0500, Tom Lane wrote:
=0A
> Vik Fearing <vik@postg= resfriends.org> writes:
=0A
> > I think the issue is the= not null constraint is evaluated before the ON
=0A
> > CON= FLICT and so there is no chance to "heal" the row by coalescing the
= =0A
> > old and new values.
=0A
>
=0A
>= Ah.  Well, ON CONFLICT is for resolving duplicate-key errors;
= =0A
> it's not a get-out-of-jail-free card for every sort of error.=
=0A
>
=0A
> I think the right way to handle this= example would be with a
=0A
> before-update trigger, which II= RC can modify the row before
=0A
> we apply any table constrai= nt checks.
=0A
 
=0A
Sounds like the to-be-upserted= row could never be inserted, but only
=0A
updated. In which case= an UPDATE sounds more appropriate than an INSERT
=0A
... ON CONF= LICT SET ... - am I missing something?
=0A
 
=0A
Gr= eetings,
=0A
 
=0A
Andres Freund
=0A
=0A ------=_001_NextPart658807845060_=------