Re: adding foreign key constraint locks up table

On Dec 28 2010, 9:55 pm, kakarukeys <kakaruk(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> On Dec 28, 9:37 pm, singh(dot)gurj(dot)(dot)(dot)(at)gmail(dot)com (Gurjeet Singh) wrote:
>
>
>
> > On Tue, Dec 28, 2010 at 2:08 AM, kakarukeys <kakaruk(dot)(dot)(dot)(at)gmail(dot)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(dot)gurjeet(at){ 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)