Thread: Alter table never finishes

Alter table never finishes

From

Leonardo M. Ramé

Date:

30 July 2013, 17:50:15

Hi, I need to do an alter table on a small table (~300 records), but it
never ends. It may be because there are clients using that table.

How can I force disconnect all clients to let me alter that table?.

Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292

Re: Alter table never finishes

From

bricklen

Date:

30 July 2013, 18:17:58

On Tue, Jul 30, 2013 at 7:50 AM, Leonardo M. Ramé <l.rame@griensu.com> wrote:

Hi, I need to do an alter table on a small table (~300 records), but it
never ends. It may be because there are clients using that table.

How can I force disconnect all clients to let me alter that table?.

If you are using PostgreSQL 9.2+, you can use this:
select pg_cancel_backend(pid) from pg_stat_activity where pid <> pg_backend_pid();

If you are using earlier version, replace "pid" with "procpid".

That query will cancel all queries other than the session issuing the pg_cancel_backend() calls.

Re: Alter table never finishes

From

Giuseppe Broccolo

Date:

30 July 2013, 18:54:32

How can I force disconnect all clients to let me alter that table?.

Regards,

There are two ways: the first is based on pg_terminate_backend() function and 'pg_stat_activity' catalog to kill idle processes.
So in a psql session type (tried on PostgreSQL 8.4):

==# SELECT procpid, (SELECT pg_terminate_backend(procid)) AS killed from pg_stat_activity WHERE current_query LIKE '<IDLE>';

A more heavy handed approach then should be used on terminal, forcing kill of idle processes using their pid:

:$ for x in `ps -ef | grep -e "postgres.*idle" | awk '{print $2}'`; do kill -9 $x; done

Hope it can help.

Giuseppe.

-- 
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it

Re: Alter table never finishes

From

Leonardo M. Ramé

Date:

30 July 2013, 20:08:13

On 2013-07-30 17:56:16 +0200, Giuseppe Broccolo wrote:
> >How can I force disconnect all clients to let me alter that table?.
> >
> >Regards,
>
> There are two ways: the first|is based on pg_terminate_backend()
> function and 'pg_stat_activity' catalog |||to kill idle processes.
> So in a psql session type (tried on PostgreSQL 8.4):
>
> ==# SELECT procpid, (SELECT pg_terminate_backend(procid)) AS killed
> from pg_stat_activity WHERE current_query LIKE '<IDLE>';
>
> A more heavy handed approach then should be used on terminal,
> forcing kill of idle processes using their pid:
>
> :$ for x in `ps -ef | grep -e "postgres.*idle" | awk '{print $2}'`;
> do kill -9 $x; done
>
> Hope it can help.
>
> Giuseppe.
>
> --
> Giuseppe Broccolo - 2ndQuadrant Italy
> PostgreSQL Training, Services and Support
> giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it
>

Thanks to both, Giuseppe and Bricklen. As I have 9.2 I've used:

select pg_cancel_backend(pid) from pg_stat_activity where pid <> pg_backend_pid();

And it returned this:

pg_cancel_backend
-------------------
 t
 t
(2 rows)

But when I execute my update table command, it still never ends...Any
hint?.

--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292

Re: Alter table never finishes

From

bricklen

Date:

30 July 2013, 20:26:47

On Tue, Jul 30, 2013 at 10:07 AM, Leonardo M. Ramé <l.rame@griensu.com> wrote:

select pg_cancel_backend(pid) from pg_stat_activity where pid <> pg_backend_pid();

And it returned this:

pg_cancel_backend
-------------------
t
t
(2 rows)

But when I execute my update table command, it still never ends...Any
hint?.

Sounds like locking issues. In another session -- other than the one you are trying to run your update, what does the following query show?

SELECT
    waiting.locktype           AS waiting_locktype,
    waiting.relation::regclass AS waiting_table,
    waiting_stm.query          AS waiting_query,
    waiting.mode               AS waiting_mode,
    waiting.pid                AS waiting_pid,
    other.locktype             AS other_locktype,
    other.relation::regclass   AS other_table,
    other_stm.query            AS other_query,
    other.mode                 AS other_mode,
    other.pid                  AS other_pid,
    other.granted              AS other_granted
FROM pg_catalog.pg_locks AS waiting
JOIN pg_catalog.pg_stat_activity AS waiting_stm ON (waiting_stm.pid = waiting.pid)
JOIN pg_catalog.pg_locks AS other ON ((waiting."database" = other."database" AND waiting.relation = other.relation) OR waiting.transactionid = other.transactionid)
JOIN pg_catalog.pg_stat_activity AS other_stm ON (other_stm.pid = other.pid)
WHERE NOT waiting.granted
AND waiting.pid <> other.pid;

Re: Alter table never finishes

From

Leonardo M. Ramé

Date:

30 July 2013, 20:29:33

On 2013-07-30 10:26:39 -0700, bricklen wrote:
> On Tue, Jul 30, 2013 at 10:07 AM, Leonardo M. Ramé <l.rame@griensu.com>wrote:
>
> >
> > select pg_cancel_backend(pid) from pg_stat_activity where pid <>
> > pg_backend_pid();
> >
> > And it returned this:
> >
> > pg_cancel_backend
> > -------------------
> >  t
> >  t
> > (2 rows)
> >
> > But when I execute my update table command, it still never ends...Any
> > hint?.
> >
> >
> Sounds like locking issues. In another session -- other than the one you
> are trying to run your update, what does the following query show?
>
> SELECT
>     waiting.locktype           AS waiting_locktype,
>     waiting.relation::regclass AS waiting_table,
>     waiting_stm.query          AS waiting_query,
>     waiting.mode               AS waiting_mode,
>     waiting.pid                AS waiting_pid,
>     other.locktype             AS other_locktype,
>     other.relation::regclass   AS other_table,
>     other_stm.query            AS other_query,
>     other.mode                 AS other_mode,
>     other.pid                  AS other_pid,
>     other.granted              AS other_granted
> FROM pg_catalog.pg_locks AS waiting
> JOIN pg_catalog.pg_stat_activity AS waiting_stm ON (waiting_stm.pid =
> waiting.pid)
> JOIN pg_catalog.pg_locks AS other ON ((waiting."database" =
> other."database" AND waiting.relation  = other.relation) OR
> waiting.transactionid = other.transactionid)
> JOIN pg_catalog.pg_stat_activity AS other_stm ON (other_stm.pid = other.pid)
> WHERE NOT waiting.granted
> AND waiting.pid <> other.pid;

Sorry bricklen, I've killed all idle connections with "kill -9 <PID>",
then I was able to execute the alter table.


--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292

Re: Alter table never finishes

From

bricklen

Date:

30 July 2013, 20:34:04

On Tue, Jul 30, 2013 at 10:29 AM, Leonardo M. Ramé <l.rame@griensu.com> wrote:

> > select pg_cancel_backend(pid) from pg_stat_activity where pid <>
> > pg_backend_pid();

As Giuseppe mentioned, if you need to kill not just the queries, but the connection as well, you could use:

select pg_terminate_backend(pid) from pg_stat_activity where pid <> pg_backend_pid();

That is much safer than kill -9, which if issued against the parent postgres process will crash your cluster.

Sorry bricklen, I've killed all idle connections with "kill -9 <PID>",
then I was able to execute the alter table.

No problem.

Re: Alter table never finishes

From

Alvaro Herrera

Date:

30 July 2013, 20:35:13

Leonardo M. Ramé escribió:

> Sorry bricklen, I've killed all idle connections with "kill -9 <PID>",
> then I was able to execute the alter table.

I don't think that was such a great idea.  Once you killed the first
one, postmaster terminated all other server processes, run recovery, and
restarted service afresh.  By the time you got to the second PID, it
wouldn't have been there anyway.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Re: Alter table never finishes

From

bricklen

Date:

30 July 2013, 20:49:03

On Tue, Jul 30, 2013 at 10:34 AM, bricklen <bricklen@gmail.com> wrote:

Sorry bricklen, I've killed all idle connections with "kill -9 <PID>",
then I was able to execute the alter table.

No problem.

btw, I meant 'no need to apologize that the queries were gone', not that I recommend "kill -9" (I don't!)