Re: [PERFORM] adding foreign key constraint locks up table

2011-01-13 Thread kakarukeys
On Jan 9, 11:34 am, robertmh...@gmail.com (Robert Haas) wrote: > 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 addconst

Re: [PERFORM] adding foreign key constraint locks up table

2011-01-08 Thread 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 po

Re: [PERFORM] adding foreign key constraint locks up table

2011-01-07 Thread kakarukeys
On Dec 28 2010, 9:55 pm, kakarukeys wrote: > On Dec 28, 9:37 pm, singh.gurj...@gmail.com (Gurjeet Singh) wrote: > > > > > 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

Re: [PERFORM] adding foreign key constraint locks up table

2011-01-04 Thread 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 conte

Re: [PERFORM] adding foreign key constraint locks up table

2011-01-04 Thread 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

Re: [PERFORM] adding foreign key constraint locks up table

2010-12-28 Thread 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 th

Re: [PERFORM] adding foreign key constraint locks up table

2010-12-28 Thread 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.o

Re: [PERFORM] adding foreign key constraint locks up table

2010-12-28 Thread 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 ar

Re: [PERFORM] adding foreign key constraint locks up table

2010-12-28 Thread 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

Re: [PERFORM] adding foreign key constraint locks up table

2010-12-28 Thread 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

Re: [PERFORM] adding foreign key constraint locks up table

2010-12-28 Thread kakarukeys
On Dec 28, 9:37 pm, singh.gurj...@gmail.com (Gurjeet Singh) wrote: > 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 tab

Re: [PERFORM] adding foreign key constraint locks up table

2010-12-28 Thread 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

Re: [PERFORM] adding foreign key constraint locks up table

2010-12-28 Thread 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 -- Sent via pgsql-performance mailing list (pg

Re: [PERFORM] adding foreign key constraint locks up table

2010-12-28 Thread 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 re