Обсуждение: adding foreign key constraint locks up table

От:
kakarukeys
Дата:

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.

От:
Gurjeet Singh
Дата:

On Tue, Dec 28, 2010 at 2: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.

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.id and 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
От:
"Kevin Grittner"
Дата:

Gurjeet Singh <> 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

От:
Lew
Дата:

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.

От:
kakarukeys
Дата:

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.html

"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. "

От:
Florian Weimer
Дата:

> 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                <>
BFK edv-consulting GmbH       http://www.bfk.de/
Kriegsstraße 100              tel: +49-721-96201-1
D-76133 Karlsruhe             fax: +49-721-96201-99

От:
Gurjeet Singh
Дата:

On Tue, Dec 28, 2010 at 9:43 AM, Kevin Grittner <> wrote:
Gurjeet Singh <> 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
От:
Gurjeet Singh
Дата:

On Tue, Dec 28, 2010 at 8:55 AM, kakarukeys <> 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.html

"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
От:
"Kevin Grittner"
Дата:

Gurjeet Singh <> 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

От:
Tom Lane
Дата:

Florian Weimer <> 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

От:
Florian Weimer
Дата:

* 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                <>
BFK edv-consulting GmbH       http://www.bfk.de/
Kriegsstraße 100              tel: +49-721-96201-1
D-76133 Karlsruhe             fax: +49-721-96201-99

От:
Tom Lane
Дата:

Florian Weimer <> 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

От:
kakarukeys
Дата:

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)

От:
Robert Haas
Дата:

On Wed, Jan 5, 2011 at 2:09 AM, kakarukeys <> 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

От:
kakarukeys
Дата:

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.