Re: adding foreign key constraint locks up table

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