pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedadding foreign key constraint locks up table
15+ messages / 7 participants
[nested] [flat]
* adding foreign key constraint locks up table
@ 2010-12-28 07:08 kakarukeys <kakarukeys@gmail.com>
0 siblings, 3 replies; 15+ messages in thread
From: kakarukeys @ 2010-12-28 07:08 UTC (permalink / raw)
To: pgsql-performance
I have a table "aaa" which is not very big. It has less than 10'000
rows. However read operations on this table is very frequent.
Whenever I try to create a new table "bbb" with foreign key pointing
to "aaa". The operation locks, and reading "aaa" is not possible. The
query also never seems to finish.
ALTER TABLE "bbb" ADD CONSTRAINT "topic_id_refs_id_3942a46c6ab2c0b4"
FOREIGN KEY ("topic_id") REFERENCES "aaa" ("id") DEFERRABLE INITIALLY
DEFERRED;
The current workaround is to create any new table at off-peak hours,
e.g. midnight after restarting the db.
I would like to know if there's any proper solution of this. Is this
an issue affecting all relational databases? My db is PostgreSQL 8.3.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: adding foreign key constraint locks up table
@ 2010-12-28 13:34 Lew <noone@lewscanon.com>
parent: kakarukeys <kakarukeys@gmail.com>
2 siblings, 0 replies; 15+ messages in thread
From: Lew @ 2010-12-28 13:34 UTC (permalink / raw)
To: pgsql-performance
On 12/28/2010 02:08 AM, kakarukeys wrote:
> I have a table "aaa" which is not very big. It has less than 10'000
> rows. However read operations on this table is very frequent.
>
> Whenever I try to create a new table "bbb" with foreign key pointing
> to "aaa". The operation locks, and reading "aaa" is not possible. The
> query also never seems to finish.
How long did you wait?
> ALTER TABLE "bbb" ADD CONSTRAINT "topic_id_refs_id_3942a46c6ab2c0b4"
> FOREIGN KEY ("topic_id") REFERENCES "aaa" ("id") DEFERRABLE INITIALLY
> DEFERRED;
>
> The current workaround is to create any new table at off-peak hours,
> e.g. [sic] midnight after restarting the db.
>
> I would like to know if there's any proper solution of this. Is this
> an issue affecting all relational databases? My db is PostgreSQL 8.3.
Naturally the system has to lock the table to alter it. It also has to check
that all records already in "bbb" satisfy the new constraint.
What's the longest you've waited for ALTER TABLE to release its lock?
--
Lew
Ceci n'est pas une pipe.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: adding foreign key constraint locks up table
@ 2010-12-28 13:37 Gurjeet Singh <singh.gurjeet@gmail.com>
parent: kakarukeys <kakarukeys@gmail.com>
2 siblings, 2 replies; 15+ messages in thread
From: Gurjeet Singh @ 2010-12-28 13:37 UTC (permalink / raw)
To: kakarukeys <kakarukeys@gmail.com>; +Cc: pgsql-performance
On Tue, Dec 28, 2010 at 2:08 AM, kakarukeys <kakarukeys@gmail.com> wrote:
> I have a table "aaa" which is not very big. It has less than 10'000
> rows. However read operations on this table is very frequent.
>
> Whenever I try to create a new table "bbb" with foreign key pointing
> to "aaa". The operation locks, and reading "aaa" is not possible. The
> query also never seems to finish.
>
> ALTER TABLE "bbb" ADD CONSTRAINT "topic_id_refs_id_3942a46c6ab2c0b4"
> FOREIGN KEY ("topic_id") REFERENCES "aaa" ("id") DEFERRABLE INITIALLY
> DEFERRED;
>
> The current workaround is to create any new table at off-peak hours,
> e.g. midnight after restarting the db.
>
> I would like to know if there's any proper solution of this. Is this
> an issue affecting all relational databases? My db is PostgreSQL 8.3.
>
>
how many rows does "bbb" have? And what are the data types of column
aaa.idand bbb.topic_id?
Creating a foreign key should not lock out aaa against reads. Can you
provide the output of the following:
select relname, oid from pg_class where relname in ( 'aaa', 'bbb' );
select * from pg_locks; -- run this from a new session when you think "aaa"
is locked by foreign key creation.
Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com
singh.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: adding foreign key constraint locks up table
@ 2010-12-28 13:55 kakarukeys <kakarukeys@gmail.com>
parent: Gurjeet Singh <singh.gurjeet@gmail.com>
1 sibling, 2 replies; 15+ messages in thread
From: kakarukeys @ 2010-12-28 13:55 UTC (permalink / raw)
To: pgsql-performance
On Dec 28, 9:37 pm, singh.gurj...@gmail.com (Gurjeet Singh) wrote:
> On Tue, Dec 28, 2010 at 2:08 AM, kakarukeys <kakaruk...@gmail.com> wrote:
> > I have a table "aaa" which is not very big. It has less than 10'000
> > rows. However read operations on this table is very frequent.
>
> > Whenever I try to create a new table "bbb" with foreign key pointing
> > to "aaa". The operation locks, and reading "aaa" is not possible. The
> > query also never seems to finish.
>
> > ALTER TABLE "bbb" ADD CONSTRAINT "topic_id_refs_id_3942a46c6ab2c0b4"
> > FOREIGN KEY ("topic_id") REFERENCES "aaa" ("id") DEFERRABLE INITIALLY
> > DEFERRED;
>
> > The current workaround is to create any new table at off-peak hours,
> > e.g. midnight after restarting the db.
>
> > I would like to know if there's any proper solution of this. Is this
> > an issue affecting all relational databases? My db is PostgreSQL 8.3.
>
> how many rows does "bbb" have? And what are the data types of column
> aaa.idand bbb.topic_id?
>
> Creating a foreign key should not lock out aaa against reads. Can you
> provide the output of the following:
>
> select relname, oid from pg_class where relname in ( 'aaa', 'bbb' );
>
> select * from pg_locks; -- run this from a new session when you think "aaa"
> is locked by foreign key creation.
>
> Regards,
> --
> gurjeet.singh
> @ EnterpriseDB - The Enterprise Postgres Companyhttp://www.EnterpriseDB.com
>
> singh.gurjeet@{ gmail | yahoo }.com
> Twitter/Skype: singh_gurjeet
>
> Mail sent from my BlackLaptop device
> How long did you wait?
hours in the past.
For recent happenings, I aborted after 10 mins.
Since it's a new table's creation, 'bbb' is empty.
The 'alter table' never finished, so the lock was not released.
aaa.id, bbb.topic_id are integers (id is auto-increament key)
Thank you for the investigative queries, I shall run it on next
sighting of the problem.
I also saw this:
http://postgresql.1045698.n5.nabble.com/Update-INSERT-RULE-while-running-for-Partitioning-td2057708....
"Note that using ALTER TABLE to add a constraint as well as
using DROP TABLE or TRUNCATE to remove/recycle partitions are
DDL commands that require exclusive locks. This will block
both readers and writers to the table(s) and can also cause readers
and writers to now interfere with each other. "
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: adding foreign key constraint locks up table
@ 2010-12-28 14:43 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Gurjeet Singh <singh.gurjeet@gmail.com>
1 sibling, 1 reply; 15+ messages in thread
From: Kevin Grittner @ 2010-12-28 14:43 UTC (permalink / raw)
To: kakarukeys <kakarukeys@gmail.com>; Gurjeet Singh <singh.gurjeet@gmail.com>; +Cc: pgsql-performance
Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
> how many rows does "bbb" have? And what are the data types of
> column aaa.idand bbb.topic_id?
For that matter, is there a unique index (directly or as the result
of a constraint) on the aaa.id column (by itself)?
-Kevin
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: adding foreign key constraint locks up table
@ 2010-12-28 14:56 Gurjeet Singh <singh.gurjeet@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 15+ messages in thread
From: Gurjeet Singh @ 2010-12-28 14:56 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: kakarukeys <kakarukeys@gmail.com>; pgsql-performance
On Tue, Dec 28, 2010 at 9:43 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov
> wrote:
> Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
>
> > how many rows does "bbb" have? And what are the data types of
> > column aaa.idand bbb.topic_id?
>
> For that matter, is there a unique index (directly or as the result
> of a constraint) on the aaa.id column (by itself)?
>
>
Isn't it a requirement that the FKey referenced columns be UNIQUE or PRIMARY
KEY'd already?
Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com
singh.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: adding foreign key constraint locks up table
@ 2010-12-28 14:57 Florian Weimer <fweimer@bfk.de>
parent: kakarukeys <kakarukeys@gmail.com>
2 siblings, 1 reply; 15+ messages in thread
From: Florian Weimer @ 2010-12-28 14:57 UTC (permalink / raw)
To: kakarukeys <kakarukeys@gmail.com>; +Cc: pgsql-performance
> Whenever I try to create a new table "bbb" with foreign key pointing
> to "aaa". The operation locks, and reading "aaa" is not possible. The
> query also never seems to finish.
Do you mean that the ALTER query and subsequent queries are shown as
"waiting" in pg_stat_activity? In this case, I'm also wondering why
this is inecessary.
--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: adding foreign key constraint locks up table
@ 2010-12-28 15:01 Gurjeet Singh <singh.gurjeet@gmail.com>
parent: kakarukeys <kakarukeys@gmail.com>
1 sibling, 0 replies; 15+ messages in thread
From: Gurjeet Singh @ 2010-12-28 15:01 UTC (permalink / raw)
To: kakarukeys <kakarukeys@gmail.com>; +Cc: pgsql-performance
On Tue, Dec 28, 2010 at 8:55 AM, kakarukeys <kakarukeys@gmail.com> wrote:
>
> > How long did you wait?
> hours in the past.
> For recent happenings, I aborted after 10 mins.
>
> Since it's a new table's creation, 'bbb' is empty.
> The 'alter table' never finished, so the lock was not released.
> aaa.id, bbb.topic_id are integers (id is auto-increament key)
>
That surely is a _long_ time for an empty table's ALTER.
>
>
> I also saw this:
>
> http://postgresql.1045698.n5.nabble.com/Update-INSERT-RULE-while-running-for-Partitioning-td2057708....
>
> "Note that using ALTER TABLE to add a constraint as well as
> using DROP TABLE or TRUNCATE to remove/recycle partitions are
> DDL commands that require exclusive locks. This will block
> both readers and writers to the table(s) and can also cause readers
> and writers to now interfere with each other. "
>
>
In your case ALTER TABLE would lock bbb, but not aaa; other sessions should
still be able to read aaa.
Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com
singh.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: adding foreign key constraint locks up table
@ 2010-12-28 15:07 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Gurjeet Singh <singh.gurjeet@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Kevin Grittner @ 2010-12-28 15:07 UTC (permalink / raw)
To: Gurjeet Singh <singh.gurjeet@gmail.com>; +Cc: kakarukeys <kakarukeys@gmail.com>; pgsql-performance
Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
> Isn't it a requirement that the FKey referenced columns be UNIQUE
> or PRIMARY KEY'd already?
Ah, so it is. Never mind.
-Kevin
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: adding foreign key constraint locks up table
@ 2010-12-28 15:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Florian Weimer <fweimer@bfk.de>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2010-12-28 15:08 UTC (permalink / raw)
To: Florian Weimer <fweimer@bfk.de>; +Cc: kakarukeys <kakarukeys@gmail.com>; pgsql-performance
Florian Weimer <fweimer@bfk.de> writes:
>> Whenever I try to create a new table "bbb" with foreign key pointing
>> to "aaa". The operation locks, and reading "aaa" is not possible. The
>> query also never seems to finish.
What that sounds like to me is there's some long-running (probably idle)
open transaction that's holding AccessShare lock on aaa. The ALTER is
blocked waiting for that xact to finish and release its lock.
Everything else queues up behind the ALTER. A bit of looking in
pg_locks would find the culprit, if this theory is right.
> Do you mean that the ALTER query and subsequent queries are shown as
> "waiting" in pg_stat_activity? In this case, I'm also wondering why
> this is inecessary.
ALTER ADD FOREIGN KEY must lock both tables to add triggers to them.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: adding foreign key constraint locks up table
@ 2011-01-04 13:52 Florian Weimer <fweimer@bfk.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Florian Weimer @ 2011-01-04 13:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: kakarukeys <kakarukeys@gmail.com>; pgsql-performance
* Tom Lane:
>> Do you mean that the ALTER query and subsequent queries are shown as
>> "waiting" in pg_stat_activity? In this case, I'm also wondering why
>> this is inecessary.
>
> ALTER ADD FOREIGN KEY must lock both tables to add triggers to them.
But why is such a broad lock needed? If the table was created in the
current transaction and is empty, the contents of the foreign key
table should not matter.
--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: adding foreign key constraint locks up table
@ 2011-01-04 15:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Florian Weimer <fweimer@bfk.de>
0 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2011-01-04 15:21 UTC (permalink / raw)
To: Florian Weimer <fweimer@bfk.de>; +Cc: kakarukeys <kakarukeys@gmail.com>; pgsql-performance
Florian Weimer <fweimer@bfk.de> writes:
> * Tom Lane:
>> ALTER ADD FOREIGN KEY must lock both tables to add triggers to them.
> But why is such a broad lock needed? If the table was created in the
> current transaction and is empty, the contents of the foreign key
> table should not matter.
It's not about content, it's about having reproducible results. We
cannot commit an ADD TRIGGER operation when there are table-modifying
queries already in progress, because they might (will) fail to notice
the trigger. If you don't believe this is a problem, consider the
following sequence of events:
1. Session 1 issues "DELETE FROM pk WHERE true". It fetches the table
definition, sees there are no triggers, and begins to execute the
DELETE. Now it goes to sleep for awhile.
2. Session 2 issues ALTER TABLE fk ADD FOREIGN KEY pk. If it doesn't
take a lock on pk that would exclude the concurrent DELETE, it can fall
through and commit before session 1 makes any more progress.
3. Session 2 inserts some rows in fk. They are valid since the matching
rows in pk are valid (and not yet even marked for deletion).
4. Session 1 wakes up and finishes its DELETE. Not knowing there is any
committed trigger on pk, it performs no FK checking.
Now you have rows in fk that violate the foreign key constraint.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: adding foreign key constraint locks up table
@ 2011-01-05 07:09 kakarukeys <kakarukeys@gmail.com>
parent: kakarukeys <kakarukeys@gmail.com>
1 sibling, 1 reply; 15+ messages in thread
From: kakarukeys @ 2011-01-05 07:09 UTC (permalink / raw)
To: pgsql-performance
On Dec 28 2010, 9:55 pm, kakarukeys <kakaruk...@gmail.com> wrote:
> On Dec 28, 9:37 pm, singh.gurj...@gmail.com (Gurjeet Singh) wrote:
>
>
>
> > On Tue, Dec 28, 2010 at 2:08 AM, kakarukeys <kakaruk...@gmail.com> wrote:
> > > I have a table "aaa" which is not very big. It has less than 10'000
> > > rows. However read operations on this table is very frequent.
>
> > > Whenever I try to create a new table "bbb" with foreign key pointing
> > > to "aaa". The operation locks, and reading "aaa" is not possible. The
> > > query also never seems to finish.
>
> > > ALTER TABLE "bbb" ADD CONSTRAINT "topic_id_refs_id_3942a46c6ab2c0b4"
> > > FOREIGN KEY ("topic_id") REFERENCES "aaa" ("id") DEFERRABLE INITIALLY
> > > DEFERRED;
>
> > > The current workaround is to create any new table at off-peak hours,
> > > e.g. midnight after restarting the db.
>
> > > I would like to know if there's any proper solution of this. Is this
> > > an issue affecting all relational databases? My db is PostgreSQL 8.3.
>
> > how many rows does "bbb" have? And what are the data types of column
> > aaa.idand bbb.topic_id?
>
> > Creating a foreign key should not lock out aaa against reads. Can you
> > provide the output of the following:
>
> > select relname, oid from pg_class where relname in ( 'aaa', 'bbb' );
>
> > select * from pg_locks; -- run this from a new session when you think "aaa"
> > is locked by foreign key creation.
>
> > Regards,
> > --
> > gurjeet.singh
> > @ EnterpriseDB - The Enterprise Postgres Companyhttp://www.EnterpriseDB.com
>
> > singh.gurjeet@{ gmail | yahoo }.com
> > Twitter/Skype: singh_gurjeet
>
> > Mail sent from my BlackLaptop device
> > How long did you wait?
>
> hours in the past.
> For recent happenings, I aborted after 10 mins.
>
> Since it's a new table's creation, 'bbb' is empty.
> The 'alter table' never finished, so the lock was not released.
> aaa.id, bbb.topic_id are integers (id is auto-increament key)
>
> Thank you for the investigative queries, I shall run it on next
> sighting of the problem.
>
> I also saw this:http://postgresql.1045698.n5.nabble.com/Update-INSERT-RULE-while-runn...
>
> "Note that using ALTER TABLE to add a constraint as well as
> using DROP TABLE or TRUNCATE to remove/recycle partitions are
> DDL commands that require exclusive locks. This will block
> both readers and writers to the table(s) and can also cause readers
> and writers to now interfere with each other. "
As requested, here are some output of the investigative queries, run
when the problem occurred. I could see some locks there, but I don't
know why the alter table add constraint takes so long of time.
libero=# select relname, oid from pg_class where relname in
( 'monitor_monitortopic', 'domain_banning' );
relname | oid
----------------------+-------
monitor_monitortopic | 43879
(1 row)
libero=# select * from pg_stat_activity where current_query ~ '^ALTER
TABLE';
datid | datname | procpid | usesysid | usename
|
current_query
| waiting | xact_start |
query_start | backend_start | client_addr |
client_port
-------+---------+---------+----------+---------
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+---------+-------------------------------
+-------------------------------+-------------------------------
+-------------+-------------
41788 | libero | 4544 | 16384 | jamiq | ALTER TABLE
"domain_banning" ADD CONSTRAINT "topic_id_refs_id_32761795e066407b"
FOREIGN KEY ("topic_id") REFERENCES "monitor_monitortopic" ("id")
DEFERRABLE INITIALLY DEFERRED; | t | 2011-01-05
06:31:58.726905+00 | 2011-01-05 06:32:01.507688+00 | 2011-01-05
06:31:44.966489+00 | 127.0.0.1 | 60833
(1 row)
libero=# select * from pg_locks where pid=4544;
locktype | database | relation | page | tuple | virtualxid |
transactionid | classid | objid | objsubid | virtualtransaction | pid
| mode | granted
---------------+----------+----------+------+-------+------------
+---------------+---------+-------+----------+--------------------
+------+---------------------+---------
virtualxid | | | | | 40/1295227
| | | | | 40/1295227 |
4544 | ExclusiveLock | t
relation | 41788 | 5815059 | | |
| | | | | 40/1295227 |
4544 | AccessExclusiveLock | t
object | 0 | | | |
| | 1260 | 16384 | 0 | 40/1295227 |
4544 | AccessShareLock | t
relation | 41788 | 43879 | | |
| | | | | 40/1295227 |
4544 | AccessExclusiveLock | f
relation | 41788 | 5815063 | | |
| | | | | 40/1295227 |
4544 | AccessExclusiveLock | t
relation | 41788 | 5815055 | | |
| | | | | 40/1295227 |
4544 | AccessShareLock | t
relation | 41788 | 5815055 | | |
| | | | | 40/1295227 |
4544 | ShareLock | t
relation | 41788 | 5815055 | | |
| | | | | 40/1295227 |
4544 | AccessExclusiveLock | t
relation | 41788 | 5815053 | | |
| | | | | 40/1295227 |
4544 | AccessShareLock | t
relation | 41788 | 5815053 | | |
| | | | | 40/1295227 |
4544 | AccessExclusiveLock | t
transactionid | | | | | |
1340234445 | | | | 40/1295227 | 4544 |
ExclusiveLock | t
(11 rows)
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: adding foreign key constraint locks up table
@ 2011-01-09 03:34 Robert Haas <robertmhaas@gmail.com>
parent: kakarukeys <kakarukeys@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Robert Haas @ 2011-01-09 03:34 UTC (permalink / raw)
To: kakarukeys <kakarukeys@gmail.com>; +Cc: pgsql-performance
On Wed, Jan 5, 2011 at 2:09 AM, kakarukeys <kakarukeys@gmail.com> wrote:
> As requested, here are some output of the investigative queries, run
> when the problem occurred. I could see some locks there, but I don't
> know why the alter table add constraint takes so long of time.
It's pretty clear from the output you posted that it's waiting for a
lock, but you didn't include the full contents of pg_stat_activity and
pg_locks, so we can't see who has the lock it's waiting for. Tom's
guess upthread is a good bet, though.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: adding foreign key constraint locks up table
@ 2011-01-13 15:33 kakarukeys <kakarukeys@gmail.com>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: kakarukeys @ 2011-01-13 15:33 UTC (permalink / raw)
To: pgsql-performance
On Jan 9, 11:34 am, robertmh...@gmail.com (Robert Haas) wrote:
> On Wed, Jan 5, 2011 at 2:09 AM, kakarukeys <kakaruk...@gmail.com> wrote:
> > As requested, here are some output of the investigative queries, run
> > when the problem occurred. I could see some locks there, but I don't
> > know why the alter table addconstrainttakes so long of time.
>
> It's pretty clear from the output you posted that it's waiting for a
> lock, but you didn't include the full contents of pg_stat_activity and
> pg_locks, so we can't see who has the lock it's waiting for. Tom's
> guess upthread is a good bet, though.
>
> --
> Robert Haas
> EnterpriseDB:http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> --
> Sent via pgsql-performance mailing list (pgsql-performa...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-performance
Yes. Lately, I have learned quite abit of pgsql process to interpret
the log. There was always an AccessShareLock granted on
monitor_monitortopic by some process idle in transaction. This blocks
AccessExclusiveLock that the alter table statement tried to acquire.
The correct solution will be to have that transaction rolled back and
the lock released (or simply kill the process) before running alter
table.
Thank you all for the help.
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2011-01-13 15:33 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-28 07:08 adding foreign key constraint locks up table kakarukeys <kakarukeys@gmail.com>
2010-12-28 13:34 ` Lew <noone@lewscanon.com>
2010-12-28 13:37 ` Gurjeet Singh <singh.gurjeet@gmail.com>
2010-12-28 13:55 ` kakarukeys <kakarukeys@gmail.com>
2010-12-28 15:01 ` Gurjeet Singh <singh.gurjeet@gmail.com>
2011-01-05 07:09 ` kakarukeys <kakarukeys@gmail.com>
2011-01-09 03:34 ` Robert Haas <robertmhaas@gmail.com>
2011-01-13 15:33 ` kakarukeys <kakarukeys@gmail.com>
2010-12-28 14:43 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-12-28 14:56 ` Gurjeet Singh <singh.gurjeet@gmail.com>
2010-12-28 15:07 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-12-28 14:57 ` Florian Weimer <fweimer@bfk.de>
2010-12-28 15:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-01-04 13:52 ` Florian Weimer <fweimer@bfk.de>
2011-01-04 15:21 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox