Обсуждение: Alter table never finishes
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
On Tue, Jul 30, 2013 at 7:50 AM, Leonardo M. Ramé <l.rame@griensu.com> wrote:
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();
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.
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 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
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;
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
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
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!)