Alter table never finishes

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.

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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;

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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!)