Re: Huge delay to finish even having all the records inserted

On 12/23/2015 06:49 AM, Alexander Franca Fernandes wrote:
> Hi,
>
> I'm inserting 14 millions records from a text file using the Postgre
> COPY command.

So how did you execute this?

In other words what is the script you used?

>
> After waiting five days I've realized that all the records seems to be
> already inserted,
>
> but the process is still running!!

From what I see it is not running:

http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

idle: The backend is waiting for a new client command.

Looks to me whatever you used to run the COPY connected and then never
disconnected and Postgres is waiting for more instructions on that
connection.

>
> I don't know if there's any internal postgresql process that impose that
> I have to wait even having the records shown in a SELECT query...
>
> I don't know if it's safe to cancel the process right now.
>
> Here's the activity query result (I've cut the columns into lines):
>
> postgres=# SELECT * from pg_stat_activity ;
>
>
> datid |
> --------+
> 136776 |
> -----------------
> datname |
> ----------+
> xyzdb |
>
> -----------------
> procpid |
> ---------+-
> 303311 |
>
> -----------------
> usesysid |
> ---------+-
> 16387 |
>
> -----------------
> usename |
> ---------+
> xyz |
>
> -----------------
>
> current_query |
> ----------------------------------+-
> <IDLE> |
>
> --------------------------------------
>
> waiting |
> --------+
> f |
>
> -----------------
> xact_start |
> -------------------------------+
> |
>
> -----------------------------------------------------------
> query_start |
> -------------------------------+
> 2015-12-18 12:01:05.553534-06 |
>
> -----------------------------------------------------------
> backend_start | client_addr | client_port
> -------------------------------+-------------+-------------
> 2015-12-18 12:01:05.547759-06 | | -1
>
>
> []s
> Alex
>

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