Re: Orphaned relations after crash/sigkill during CREATE TABLE

On 8/20/20 1:37 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:
> > So from [1] you are using CREATE TABLE AS. 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. Have not tested but I'm going to assume
> > if you kill the above the problem would not happen or would be fixable
> > by DELETE FROM some_table/TRUNCATE some_table;
>
> I was able to implement this, which creates the table quickly in a first
> transaction and populates it in a second transaction.
>
> However we were still seeing orphaned files on crash, and I believe I
> tracked it down to subsequent CREATE INDEX statements also creating
> these orphaned files (if they are running during a crash).

If the crashes are still being caused by the OOM killer then it looks to
me you need a more capable cloud instance.

Can you partition the tables to break the work into smaller units?

>
> Is that issue known as well?  I don't believe I can use the same trick
> to sidestep that one...
>
> -Jason

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