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 1ozeyC-0000A2-7r for pgsql-general@arkaria.postgresql.org; Mon, 28 Nov 2022 14:18:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ozeyB-0007Oc-4d for pgsql-general@arkaria.postgresql.org; Mon, 28 Nov 2022 14:18:51 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ozeyA-0007OT-Pt for pgsql-general@lists.postgresql.org; Mon, 28 Nov 2022 14:18:50 +0000 Received: from mail-qt1-x834.google.com ([2607:f8b0:4864:20::834]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ozey7-0008B5-Ly for pgsql-general@lists.postgresql.org; Mon, 28 Nov 2022 14:18:49 +0000 Received: by mail-qt1-x834.google.com with SMTP id fz10so6714874qtb.3 for ; Mon, 28 Nov 2022 06:18:47 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=9cOQeMaPVT7xkogQYMsLIhR8EJSS/qzDXA0K1WtbELo=; b=b+iL/3+wFgRI3+rghTafVtiroECo/R6+DGCafw4Te3oAx0OOGMvEg8IWKvsiupsOGA dkLWyAzS0Ysf7webLmb3/S/0Vzq9UB+YQit71i9YmFko9PKEwLmceGktNQ+TRW9mbvyC WdD5rwHtQZDvaf6RF4JAczhEL8NilM8n1y/IfPd0IqlwKbARyAzafxuZEjuZJ0YFhKxs G0Sog9RxiywOBFu4RCf0hZ3Db5qs/2lrV0TLoTdiR/ltdqq+2U+CjnnU1n2P6WkJdFFx BNFJnso+iKO1Txuz8rAQQyG6Zpqp8FYGOBfjazvT/9SfKMYYTcKwAjcv3LRc7Ei3lWu0 YTzg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=9cOQeMaPVT7xkogQYMsLIhR8EJSS/qzDXA0K1WtbELo=; b=tzW/M5ZWndEgTixBZ3+j7XzmB+4BSG29pQhgLW6WVAjgysO/t7d+Z+yAIrZpQcfpBT OLYyJMSkA/kTLJcFerUjenHC8B9pEGx2JNOyflhyFRhXIEWYGV6qfj7E3qbYItTTlLac chaHgCSKX8B/EoY7+ECq+GO8Cbc4956ePYqWrp01Us4+8weAr+ISBkCqdatkka0jP3BO Q/Zm3HGZdJMEw8YlrLxebLjiGhz2eA9zNsCJNZZMcrHv0aXki5JgjoQfSBalEKIuuxyr dGbQZS7ONV69wZY7sUZ+NoxfWhExOA9Diss6TsMha00cycsZ0B+gOW9ND/2TvgpCBTya GjRw== X-Gm-Message-State: ANoB5plA3qWSdUVIk/o/QLHcrWBHZ09EVJy5jneCqm2YF2lcL+8TbhVj ohdN513PZkR39NmjfCaKDpUR3jI0V0s= X-Google-Smtp-Source: AA0mqf64Q3INbVFq7eAyvGb+8OlBvMpjjig6r/05eY8pIjb35DSHWD1RaoCqKLY4ifzeIaBhWLJ/fQ== X-Received: by 2002:ac8:747:0:b0:3a5:1ea2:2ae0 with SMTP id k7-20020ac80747000000b003a51ea22ae0mr47601812qth.288.1669645126491; Mon, 28 Nov 2022 06:18:46 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id cc16-20020a05622a411000b003a5416da03csm6950144qtb.96.2022.11.28.06.18.45 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 28 Nov 2022 06:18:45 -0800 (PST) Message-ID: Date: Mon, 28 Nov 2022 08:18:45 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: delete statement returning too many results Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <34484222-02BB-408A-8FBB-DCE0F64D96B5@k5d.de> From: Ron In-Reply-To: <34484222-02BB-408A-8FBB-DCE0F64D96B5@k5d.de> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 11/28/22 07:29, Arlo Louis O'Keeffe wrote: > Hello everyone, > > I am seeing weird behaviour of a delete statement that is returning more results than I am expecting. > > This is the query: > > DELETE FROM queue > WHERE > id IN ( > SELECT id > FROM queue > ORDER BY id > LIMIT 1 > FOR UPDATE > SKIP LOCKED > ) > RETURNING *; > > My understanding is that the limit in the sub-select should prevent this query from ever > returning more than one result. Sadly I am seeing cases where there is more than one result. > > This repository has a Java setup that pretty reliably reproduces my issue: > https://github.com/ArloL/postgres-query-error-demo > > I checked the docs for select and delete and couldn’t find any hint for cases > where the behaviour of limit might be surprising. > > Am I missing something? More than one row will be deleted if there in more than one record in "queue" for the specific value of "id" (i.e "id" is not unique). -- Angular momentum makes the world go 'round.