Re: Alter table set logged hanging after writing out all WAL

On Tue, Feb 6, 2018 at 9:48 PM, Jeremy Finzel <finzelj(at)gmail(dot)com> wrote:

> On Tue, Feb 6, 2018 at 6:02 PM Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
> wrote:
>
>> On Tue, Feb 06, 2018 at 01:36:04AM -0600, Jeremy Finzel wrote:
>> > Here is the basic structure - is the gist index significant?:
>> >
>> > CREATE UNLOGGED TABLE foo (
>> > as_of_date daterange NOT NULL,
>> > customer_id integer,
>> > bunch_of_fields_here);
>> >
>> > ALTER TABLE ONLY foo
>> > ADD CONSTRAINT foo_as_of_date_excl EXCLUDE USING gist (customer_id
>> WITH
>> > =, as_of_date WITH &&);
>> >
>> > CREATE UNIQUE INDEX foo_idx1 ON foo USING btree (customer_id) WHERE
>> > (upper(as_of_date) = 'infinity'::date);
>> >
>> > CREATE INDEX foo_idx2 ON foo USING btree (customer_id,
>> lower(as_of_date))
>> > WHERE (upper(as_of_date) = 'infinity'::date);
>> >
>> > CREATE UNIQUE INDEX foo_idx3 ON foo USING btree (customer_id,
>> > lower(as_of_date));
>>
>> I am not sure, but I would think about something related to gist here
>> when heavy insertions are done on it... I cannot put my finger on the
>> thread though.
>>
>> > This is all I see - please help me if there's a better command I can
>> > run:
>>
>> If the process is still running, can you attach gdb to it and then run
>> the command bt? You may need to install debugging symbols to make the
>> trace readable.
>> --
>> Michael
>
>
> I am trying a few other scenarios to see if I can reproduce. I was able to
> set to logged a copy of the table with no indexes. I am now attempting same
> with only the gist index. If I can reproduce it on a non production server
> I will try gdb.
>
> Thank you much for the follow up.
>
> Jeremy
>

I was able to get it to finish by just waiting awhile. To give you an
idea, the table with no indexes was set logged in 7 minutes. With the gist
index, it took 3 hours but finally finished. It is only writing WAL for
about the first 30 minutes, then it apparently is not writing any more WAL
but takes very long to finish.

Thanks,
Jeremy