| Lists: | pgsql-general |
|---|
| From: | Chris Barnes <compuguruchrisbarnes(at)hotmail(dot)com> |
|---|---|
| To: | Postgres General Postgres General <pgsql-general(at)postgresql(dot)org> |
| Subject: | Postgres wont drop foriegn keys on tables. |
| Date: | 2010-09-23 15:01:28 |
| Message-ID: | BLU149-W409ADC3693D8FFD8214627D4610@phx.gbl |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
I am having an odd problem that I have seen before. It usually clears itself after I restart postgres.
I am attempting to drop an foreign key on a table and it sits for hours and doesn't drop or put anything into the log.
Killing the alter puts an error in, but it doesn't time out and it cause the system to lock at some point.
What can I do to troubleshoot this?
Chris
| From: | Chris Barnes <compuguruchrisbarnes(at)hotmail(dot)com> |
|---|---|
| To: | Postgres General Postgres General <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Postgres wont drop foriegn keys on tables. |
| Date: | 2010-09-23 15:03:52 |
| Message-ID: | BLU149-W543A930A622C819562C251D4610@phx.gbl |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
Sorry,
I am running the following.
[postgres(at)pgprd01 pgcheck]$ psql --version
psql (PostgreSQL) 8.4.2
contains support for command-line editing
[postgres(at)pgprd01 pgcheck]$ uname -a
Linux system.name.com 2.6.18-164.el5 #1 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
From: compuguruchrisbarnes(at)hotmail(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Postgres wont drop foriegn keys on tables.
Date: Thu, 23 Sep 2010 11:01:28 -0400
I am having an odd problem that I have seen before. It usually clears itself after I restart postgres.
I am attempting to drop an foreign key on a table and it sits for hours and doesn't drop or put anything into the log.
Killing the alter puts an error in, but it doesn't time out and it cause the system to lock at some point.
What can I do to troubleshoot this?
Chris
| From: | Vick Khera <vivek(at)khera(dot)org> |
|---|---|
| To: | Postgres General Postgres General <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Postgres wont drop foriegn keys on tables. |
| Date: | 2010-09-23 15:28:05 |
| Message-ID: | AANLkTinaf=XJFReQDpqFk1Tb9bt4MhcNdiSVngGNnbi7@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
On Thu, Sep 23, 2010 at 11:01 AM, Chris Barnes
<compuguruchrisbarnes(at)hotmail(dot)com> wrote:
> I am attempting to drop an foreign key on a table and it sits for hours and
> doesn't drop or put anything into the log.
>
Does the pg_stat_activity view show that it is waiting for a lock? It
will need to lock both the origin and destination tables of the FK to
clear the triggers.
| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Chris Barnes <compuguruchrisbarnes(at)hotmail(dot)com> |
| Cc: | Postgres General Postgres General <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Postgres wont drop foriegn keys on tables. |
| Date: | 2010-09-23 15:29:47 |
| Message-ID: | 2292.1285255787@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
Chris Barnes <compuguruchrisbarnes(at)hotmail(dot)com> writes:
> I am attempting to drop an foreign key on a table and it sits for hours and doesn't drop or put anything into the log.
Dropping a foreign key is fast in itself, but it requires exclusive lock
on both the referencing and referenced tables. Look to see what is
blocking the lock request. pg_locks and pg_stat_activity views are
your friends.
regards, tom lane