pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Dominique Devienne <ddevienne@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: COPY RETURNING?
Date: Wed, 19 Apr 2023 08:41:18 -0700
Message-ID: <7978c79d-fa51-0ece-fdfd-3973e9f6c849@aklaver.com> (raw)
In-Reply-To: <CAFCRh-9nKHCG3OQQ=_Rbm3CW+NqBVZZM5AX0hh8SraixF7xTuA@mail.gmail.com>
References: <CAFCRh-9nKHCG3OQQ=_Rbm3CW+NqBVZZM5AX0hh8SraixF7xTuA@mail.gmail.com>

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






view thread (2+ messages)

Message-ID: <7978c79d-fa51-0ece-fdfd-3973e9f6c849@aklaver.com>
Permalink:  ../7978c79d-fa51-0ece-fdfd-3973e9f6c849@aklaver.com/
Also on:    postgresql.org/message-id/7978c79d-fa51-0ece-fdfd-3973e9f6c849@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, ddevienne@gmail.com
  Subject: Re: COPY RETURNING?
  In-Reply-To: <7978c79d-fa51-0ece-fdfd-3973e9f6c849@aklaver.com>
  References: <CAFCRh-9nKHCG3OQQ=_Rbm3CW+NqBVZZM5AX0hh8SraixF7xTuA@mail.gmail.com> <7978c79d-fa51-0ece-fdfd-3973e9f6c849@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox