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 1pp9w1-0005sl-VQ for pgsql-general@arkaria.postgresql.org; Wed, 19 Apr 2023 15:41:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pp9w0-0001br-Rl for pgsql-general@arkaria.postgresql.org; Wed, 19 Apr 2023 15:41:28 +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 1pp9vz-0001bi-9N for pgsql-general@lists.postgresql.org; Wed, 19 Apr 2023 15:41:28 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pp9vu-003r1t-K9 for pgsql-general@lists.postgresql.org; Wed, 19 Apr 2023 15:41:26 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.nyi.internal (Postfix) with ESMTP id 1DE3A5C0118; Wed, 19 Apr 2023 11:41:20 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute5.internal (MEProxy); Wed, 19 Apr 2023 11:41:20 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to; s=fm2; t= 1681918880; x=1682005280; bh=cPhvUyRqqj0ClB1AIrg2kF9Jwrq6XI1kKHY 9lt6r6yY=; b=dqAvnvrxoLDC6Vq/caTpktdF/+eKX/0NdxPbe5/+iyF8RMIWOO+ bpVdChyDq/iXV7uoKVKVlbX08/3vU8zYH/UcQ6Hf82I8zaYpr1p+aoeuQCraV+Ow uc1Cy2AgoCodSQGMoc9rfVw4iASrlh6yvEOTAjF/AGLD7wGmOBcewoLrpe8bT2ws LPjQysCYX4tuZEb6+1jSbhb+Ih9fSPoopb2ihkrqTpkYnXT/Fa5vFgBlaBQLPAky MSzXPcyfNLd9mUj0e8YneFEobIe4UVTmE1v5FHxlLo8UzkW5HG8iqlAbs8iCdMjQ ryUWC0o3tkxnRQ3Cx5ivtFyja/pa/VmtU6A== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm3; t=1681918880; x= 1682005280; bh=cPhvUyRqqj0ClB1AIrg2kF9Jwrq6XI1kKHY9lt6r6yY=; b=Y 5nXpilfyMH6yKwGSrJYQyebLZ+1zX9MY+ULkRMdgapfqXMFmbzoMSTrfH3xoG0kW InO7I+CSXl0z6SZBzaI7kkfuN9Og96ZbEh4OIYzpG98r2oYl9UsNyxEZEVqZC4zb 6x31z+H+YB4i8anDVw9mkcGhTCWQ6TLNqaiX92+znMP8q6ePh3JoUo6UkavfnLj0 cLoOetvSyJoWNwEs+ON1/wWdOhT+dmDyykaADZHIJm7wxmeN9+LE+RFgCoJ6hqfk D5IFucwklVgxbx7QDWb75d3eo10qE14PhZPrzHQ190bOmEWInmewXDrxlOljW2vf TEsVH3OMZDz6i9fJ56GeA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrfedttddgleefucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvfhfhjggtgfesthejredttdefjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpeffudehtddugefgkeevudffheevvefhfeettdfhveei vdfgieeghfeiueeggffggeenucffohhmrghinhepphhoshhtghhrvghsqhhlrdhorhhgne cuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhi rghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 19 Apr 2023 11:41:19 -0400 (EDT) Message-ID: <7978c79d-fa51-0ece-fdfd-3973e9f6c849@aklaver.com> Date: Wed, 19 Apr 2023 08:41:18 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.10.0 Subject: Re: COPY RETURNING? To: Dominique Devienne , pgsql-general@lists.postgresql.org References: Content-Language: en-US From: Adrian Klaver 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/19/23 02:55, Dominique Devienne wrote: > Hi. > > We are switching a schema type's surrogate/primary key, from `uuid` to > `int`. > That schema has parent-child relationships enforced with foreign-keys. > Our ingestion/schema-loading code uses COPY FROM STDIN BINARY. > > Before, the SK/PK was generated client-side, as random uuid. > The ingestion code maintained client-side maps from NKs to uuids, > to be used for FK columns in child-tables COPY'd later. > > But now that the SK/PK is an integer identity column generated server-side, > thanks to an implicitly-created sequence, we need that generated per-row > `int` PK. > With a normal prepared statement, we'd use a RETURNING clause, to avoid > a separate round-trip to the server. Can the same somehow be achieved > with COPY? > > I'm afraid the answer is no, but I want to ask anyway, maybe there's a > way or work-around? 1) Correct COPY does not have RETURNING. 2) What do you need to do with that new id? 3) Off the top of my head a possible solution may be a trigger using a transition relation. https://www.postgresql.org/docs/current/sql-createtrigger.html The REFERENCING option enables collection of transition relations, which are row sets that include all of the rows inserted, deleted, or modified by the current SQL statement. This feature lets the trigger see a global view of what the statement did, not just one row at a time. This option is only allowed for an AFTER trigger that is not a constraint trigger; also, if the trigger is an UPDATE trigger, it must not specify a column_name list. OLD TABLE may only be specified once, and only for a trigger that can fire on UPDATE or DELETE; it creates a transition relation containing the before-images of all rows updated or deleted by the statement. Similarly, NEW TABLE may only be specified once, and only for a trigger that can fire on UPDATE or INSERT; it creates a transition relation containing the after-images of all rows updated or inserted by the statement. > > At the end of the COPY, we do get a ResultSet, so API-wise it was be > possible for it > to contain some rows, I guess, but I'm not sure the COPY protocol > supports returning > rows, nor what the syntax would be to have a COPY RETURNING form of COPY. > > Thanks for any insights. --DD -- Adrian Klaver adrian.klaver@aklaver.com