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.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com