Re: postgres 8.4, COPY, and high concurrency

On Tue, Nov 13, 2012 at 7:10 PM, Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> wrote:
> I had moved on to a different approach to importing the data which
> does not work concurrently. However, I went back and tried to
> re-create the situation and - at least a naive attempt failed. I'll
> give it a few more tries -- I was creating two tables using CREATE
> TABLE <unique name> LIKE (some other table INCLUDING <everything>).
> Then I would copy the data in, add some constraints (FK constraints
> but only within these two tables) and then finally (for each table)
> issue an ALTER TABLE <unique name> INHERIT <some other table>. To be
> clear, however, everything bogged down in the COPY stage which was
> immediately following the table creation.
>
> I'll note that my naive test showed almost no unexpected overhead at
> all, so it's clearly not representative of the problem I encountered.

I'm still unable to replicate the problem, but I can show I wasn't
crazy, either. The average time to perform one of these COPY
operations when things are working is in the 15-45 second range. I
had configured PG to log any statement that look longer than 3
seconds, so I got a bunch of those in the logs. I have since
reconfigured to log *everything*. Anyway, when things were going
badly, COPY would take anywhere from 814 seconds to just under 1400
seconds for the exact same files.

UPDATE: I have been able to replicate the issue. The parent table (the
one referenced in the LIKE portion of the CREATE TABLE statement) had
three indices.

Now that I've been able to replicate the issue, are there tests that I
can perform that would be useful to people?
I will also try to build a stand-alone test.

--
Jon