On Tue, Dec 28, 2010 at 2:08 AM, kakarukeys <kakarukeys@xxxxxxxxx> wrote:
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,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.
--
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
