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 1tAxkR-001AMI-W3 for pgsql-general@arkaria.postgresql.org; Tue, 12 Nov 2024 20:44:27 +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 1tAxkP-009FnL-Co for pgsql-general@arkaria.postgresql.org; Tue, 12 Nov 2024 20:44:25 +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 1tAxkP-009FnB-1n for pgsql-general@lists.postgresql.org; Tue, 12 Nov 2024 20:44:25 +0000 Received: from mail-wm1-x32f.google.com ([2a00:1450:4864:20::32f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tAxkN-001Xnn-5j for pgsql-general@lists.postgresql.org; Tue, 12 Nov 2024 20:44:24 +0000 Received: by mail-wm1-x32f.google.com with SMTP id 5b1f17b1804b1-4315eac969aso224985e9.1 for ; Tue, 12 Nov 2024 12:44:23 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=bowt-ie.20230601.gappssmtp.com; s=20230601; t=1731444262; x=1732049062; darn=lists.postgresql.org; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=yxoyIqjC6rbsG2cUqAJuSfEuA/eH3geLzzHPpHAsAwk=; b=W7h9B+L94cRKDPy2SQbfZ20lusQOD3wD9O4Gfyf4WlJPRw7zsEtv8culZ2Msv5GX8F 2tth1ogzqj2uE5sjuIOOQPSyPUNGjQQw72FzTNreHt4TwFejH/KGdFTNdW4Jq5ft6nrj RfUGSO8FFlWgftgDQhMG66oXFehikgKo4QedKDon5PKJr/VOdf8G6HY2TnkjMwX1UgM8 Sf2yYL40EjbRnxXdgh24rFyQOxZmB7JhB+8guxssj7cz5CO1TvQ/QX4Q1Getmt/+QyiA A2k6dP9hUA8tj7fQjNXEPkrkYkyB7EDVKh5yvspWbjLXkkV4Q+ZMQyYwKHNRCsoFbA39 ScZw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731444262; x=1732049062; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=yxoyIqjC6rbsG2cUqAJuSfEuA/eH3geLzzHPpHAsAwk=; b=sZiBJHBWuA45OY7QpTBRMhXFZsSSsL9OlJIRrsoZu3f8KsDBxaDRbJDxiNMe+uK55+ Xuhk5yXAniUvkHhYNIxFFJFI6yO3REVe0osi1ik/pi62DwZVClDNhR+Ba/UbMBpmJsfq ABy9+VLKO0K6sFR3+5gnayGc8OI1AtffjiX+XHXVC+dvTDWweK18C2CJbP4IjdCQuIp9 Z0N83PPX/qwEdQsszGV6S2N76UdBTpMuqXcHfx/rzK/GoWai1DG9+Nx0kJ3NOzbB9TD3 J9x9qhqcBivrSWX9pTIRu9Ou9DchlAiK2/NfCNlCkjFZmJG9FvU5WZ7CwYjOVjhteMS1 ZigQ== X-Gm-Message-State: AOJu0YzffL2ie19QOzq4iAI8wzDwkyM8kND+Y0msL+F/iGuLvBP2C0Ey b7kngFh7wqu1Q60Tlhe0tVb7Zwa2mfJPln5jdAYonTFL+Tm58GqaGOcclQW2mXnam2/JnSmPrF3 6A3y8slcNwg08u3C6iIKUmLvl8yNIaYgoVY5EqQ== X-Google-Smtp-Source: AGHT+IE8IUa/yGvLsou+u+21NGxs8BnSgfmjT4CBstoRPfTyOlBJIiabcWALaT9c7yfbnJ+8SknRvlVgy2IOa3qtJcg= X-Received: by 2002:a05:600c:19d1:b0:431:55af:a220 with SMTP id 5b1f17b1804b1-432b74c0d04mr151287045e9.12.1731444261754; Tue, 12 Nov 2024 12:44:21 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Peter Geoghegan Date: Tue, 12 Nov 2024 15:43:55 -0500 Message-ID: Subject: Re: Duplicate key error To: yudhi s Cc: pgsql-general Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Tue, Nov 12, 2024 at 3:38=E2=80=AFPM yudhi s wrote: > Can you share your thoughts on how exactly this merge query can possibly = cause the duplicate key error? MERGE doesn't take any special precautions to avoid such unique violations. ON CONFLICT does. It really is that simple. Your app will be prone to the same sorts of errors (at READ COMMITTED isolation level) if you do inserts conditioned on the absence of an existing/conflict row. You'd have to retry the statement to plug the race condition, which is how Postgres users did upserts prior to the introduction of ON CONFLICT DO UPDATE. --=20 Peter Geoghegan