Re: adding foreign key constraint locks up table - Mailing list 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)