Re: Orphaned relations after crash/sigkill during CREATE TABLE

On 8/18/20 1:19 PM, Jason Myers wrote:
> > On Tue, Aug 18, 2020 at 3:49 PM Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> > Have you tried with:
> > BEGIN;
> > CREATE TABLE some_table SELECT some_data FROM other_table LIMIT 1 WITH
> > NO DATA;
> > COMMIT;
> >
> > The above gets you the table structure, but no data.
> >
> > BEGIN;
> > INSERT into some_table SELECT * FROM other_table;
> >COMMIT;
> >
> > The above populates the table
>
> Thanks -- we were indeed creating and populating the new table all in a
> single transaction.
>
> I'll see if we can split this into two transactions so that the table
> structure is committed quickly.  I think you're right that this would
> mostly sidestep the issue.

Assuming the table you are pulling from is fairly static, it could also
allow you to regulate the amount of data you transfer at any one time
into the new table.

>
> -Jason
>
> p.s. Apologies if this is formatted wrong, this is my first mailing list
> post.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com