[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: How to copy rows into same table efficiently
From:       Michael Lewis <mlewis () entrata ! com>
Date:       2021-10-26 23:32:24
Message-ID: CAHOFxGq-LfgWQuftFtvBGQPjWeOW_rq1FjzK6vjHfF_i73fvLw () mail ! gmail ! com
[Download RAW message or body]

On Tue, Oct 26, 2021 at 1:07 AM Arun Suresh <arun.suresh.303@gmail.com>
wrote:

> Current approach taken is to build a query like below:
> INSERT INTO mytable (col1, col2, col3, col4) SELECT col1, 'XYZ', col3,
> col4 FROM mytable WHERE col2 = 'ABCD'
>
> Is there a better way to do this?
> There could be other tables with foreign key reference, would a simple
> ordering of the copy based on table relationship suffice?
>

The other concern I would have is having a rolling view of the data in
default read committed mode. If you copy data from a primary table (with
other tables having fkey to that table coming later in the process), then
you may need to use some created_on < script_start_time, else when you try
to insert dependent rows where the record in the primary table did not
exist yet when you started your process and your rows in dependent table
want to reference that primary table row... error.

Or, use REPEATABLE READ, but WAL buildup may be a concern.

[Attachment #3 (text/html)]

<div dir="ltr"><div dir="ltr">On Tue, Oct 26, 2021 at 1:07 AM Arun Suresh &lt;<a \
href="mailto:arun.suresh.303@gmail.com">arun.suresh.303@gmail.com</a>&gt; \
wrote:<br></div><div class="gmail_quote"><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div>Current approach taken is to \
build a query like below:<br></div>INSERT INTO mytable (col1, col2, col3, col4) \
SELECT col1, &#39;XYZ&#39;, col3, col4 FROM mytable WHERE col2 = \
&#39;ABCD&#39;<br><br>Is there a better way to do this?<div>There could be other \
tables with foreign key reference, would a simple ordering of the copy based on table \
relationship suffice?<br></div></div></blockquote><div><br></div><div>The other \
concern I would have is having a rolling view of the data in default read committed \
mode. If you copy data from a primary table (with other tables having fkey to that \
table coming later in the process), then you may need to use some created_on &lt; \
script_start_time, else when you try to insert dependent rows where the record in the \
primary table did not exist yet when you started your process and your rows in \
dependent table want to reference that primary table row... \
error.</div><div><br></div><div>Or, use REPEATABLE READ, but WAL buildup may be a \
concern.</div></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]