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 1pmLN1-00029H-DY for pgsql-general@arkaria.postgresql.org; Tue, 11 Apr 2023 21:17:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pmLN0-0000cn-9H for pgsql-general@arkaria.postgresql.org; Tue, 11 Apr 2023 21:17:42 +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 1pmLMz-0000ST-UD for pgsql-general@lists.postgresql.org; Tue, 11 Apr 2023 21:17:41 +0000 Received: from mail-pj1-x102c.google.com ([2607:f8b0:4864:20::102c]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pmLMx-0008Dg-BF for pgsql-general@lists.postgresql.org; Tue, 11 Apr 2023 21:17:40 +0000 Received: by mail-pj1-x102c.google.com with SMTP id c3so10262148pjg.1 for ; Tue, 11 Apr 2023 14:17:39 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1681247858; x=1683839858; 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=CXiUU6Uu4hM/Umk1/Sq146Vg0lDRYm6SM5ZqyjzDfGY=; b=sMcEbssBhuSgEZDz6+LvqMx040lFzUpZoxMSmHKlafUHHQPnfLZ5/4qhujZQSg3n8O 0CbVILDN+7IJgy6noI6W2YnLXvk2dGhjv8FvsCkol0V1ha/oCv737Pb4akESdBHsBS6M H9quPXD9BvZaqJ6i7dMXsis91bBRbmITxs0LnhvlpaK3wCXN1e7tTBUGXmfkwFgQQ4lw C2BNDqu5rCvBx3S59nQkFGJQkCRU+ggCjezphMBxR9OxFUJBmfaRBnz/eqgJgEIxJBcd dGR3eglv4Sr01i3J6GBLajD3u5G53n8Ss2cBuo/eNLP0T6GmwtWQNidPe35cMmhO5uiK IPkQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1681247858; x=1683839858; 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=CXiUU6Uu4hM/Umk1/Sq146Vg0lDRYm6SM5ZqyjzDfGY=; b=qOeo7/cTiADHeVwMNoHtS3ECa13WXcbt+2IttdiHV6+ao895PRHjoO4VYABAmH8WQM iyxetGCiO6YtEpCzXTQSQZ+WxTKwSO6F4lfrE7ZTFYM8a95QkWIk2klB0XmpThaP1kwl BB4aC1XAili3wM9qTjb2o2MQFLaQC0Sz23v1E0fTleE7aYuLDBWqzILjgUgY/0Lt/pOe rGu+O8irhCzeOYGa2H4lDOaqO6QHdWXOfdFLsDu9mekr3Y9zuNNmyczfJ92CUTnIkWg2 OYmiFrTUfi1CJNEr1VzJ5cKGm6szgTlUlAKlX3wX0E8EKfME1yBZ0y3+yw+0DXKFkLRn 9w/g== X-Gm-Message-State: AAQBX9cbar+GcL+7B1OQNjrZfb/ZaDAnAgoQeHkR8345AkLhbInFY0Mf X0Yp++IQJwIlZHsv4UsHP9OGikNoljo= X-Google-Smtp-Source: AKy350anzDEhs5Omx1Cmai/vL04aPWlHZk7m/jrF4LN0+F8U5M0GjD90DvA5yru8Tspejy6nxYy1NQ== X-Received: by 2002:a05:6a20:6aa7:b0:e9:5b0a:e784 with SMTP id bi39-20020a056a206aa700b000e95b0ae784mr13249126pzb.29.1681247857778; Tue, 11 Apr 2023 14:17:37 -0700 (PDT) Received: from ?IPV6:2601:681:5a00:f790:a423:c98:f7d5:55ad? ([2601:681:5a00:f790:a423:c98:f7d5:55ad]) by smtp.gmail.com with ESMTPSA id x24-20020a62fb18000000b0062de9ef6915sm10228310pfm.216.2023.04.11.14.17.36 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 11 Apr 2023 14:17:37 -0700 (PDT) Message-ID: <6644419d-50e1-97c9-5507-b6915bec96fa@gmail.com> Date: Tue, 11 Apr 2023 15:17:36 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.9.0 Subject: Re: Guidance on INSERT RETURNING order Content-Language: en-CA To: pgsql-general@lists.postgresql.org References: <95fbd8c8-2f7a-38a1-4a41-b2ac3763d89c@aklaver.com> From: Rob Sargent In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 4/11/23 14:37, Federico wrote: > > The problem here is not having the auto increment id in a particular > order, is that there > is apparently no correlation with the position of an element in the > values clause with the > id generated. That's the reason for using the sentinel column in the > general solution in the previous message. > > The extend on the use case, SQLAlchemy has 3 objects T that have > T(data='a'), T(data='b'), T(data='c') but no > value for the id column. The objective is to insert the 3 data values, > get back the ids and correctly match them with > the correct 3 objects. > >> No. Sadly, adding that ORDER BY is just voodoo programming, because >> it applies to the result of the SELECT while promising nothing about >> the order in which INSERT/RETURNING will act on those rows. > I wasn't probably clear, it's fine if INSERT/RETURNING order is > arbitrary, what matters is that the > autoincementing values is executed in the same order as select, like > mentioned in this > previous message > https://www.postgresql.org/message-id/29386.1528813619%40sss.pgh.pa.us > > Is that not the case? > >> Re-reading that 2012 thread, the main new observation I'd make today >> is that parallel operation is a thing now, and it's not hard to foresee >> that sometime soon we'll want to parallelize INSERTs. Which'd make it >> *really* hard to promise anything about the order of RETURNING output. > I think it's fine not promising anything about the order of RETURNING, but > it would be very helpful having a way of tracking what input row > generated a particular > output row. Basically the sentinel case in the original post, > without actually having to insert the sentinel into the table. > >> I think if you want to use RETURNING with multi-row inserts, the >> thing to do is more like >> >> INSERT INTO t(data) VALUES ('a'), ('b'), ('c') RETURNING data, id >> >> and then explicitly match up the returned "data" values rather than >> presuming they appear in the same order you wrote them in in VALUES. >> Admittedly this might be problematic if some of the VALUES rows >> are identical, but how much should you care? > Well, the example is very easy, but it's hard to generalize when > inserting multiple columns > with possible complex values in them, since it would mean matching on > possibly large json values, > arrays, etc. So definitely not ideal > > Thanks, > Federico > Can your client retain a hashmap of md5,data pairings, allowing the lookup on the way back using the returned data and supplied id?