From: Robert Haas <robertmhaas@gmail.com>
To: roopasatish <roopabenzer@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: probelm with alter table add constraint......
Date: Mon, 6 Apr 2009 06:43:44 -0400
Message-ID: <603c8f070904060343o1bc98d99rf30cd24fb23e2fff@mail.gmail.com> (raw)
In-Reply-To: <5da3d2700904052354g61f02031x822322e9ee512ad9@mail.gmail.com>
References: <5da3d2700904052354g61f02031x822322e9ee512ad9@mail.gmail.com>
On Mon, Apr 6, 2009 at 2:54 AM, roopasatish <roopabenzer@gmail.com> wrote:
>
> I have an issue with the add foreign key constraint which goes for waiting
> and locks other queries as well.
>
> ALTER TABLE ONLY holding_positions ADD CONSTRAINT
> holding_positions_stock_id_fkey FOREIGN KEY (stock_id)
> REFERENCES stocks (stock_id) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION;
>
> The holding_positions table has no data in it as yet.
>
>
> ps aux | grep postgres
> postgres 5855 0.8 1.6 346436 271452 ? Ss 16:34 0:07
> postgres: abc stocks 192.100.100.111(60308) SELECT waiting
> postgres 6134 0.0 0.0 346008 4184 ? Ss 16:44 0:00
> postgres: xyz stocks 192.100.100.222(34604) ALTER TABLE waiting
>
>
>
> Any suggestions would be appreciated.
You need to look at what locks they're waiting for.
select locktype, database, relation::regclass, page, tuple,
virtualxid, transactionid, classid, objid, objsubid,
virtualtransaction, pid, mode, granted from pg_locks;
...Robert
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: robertmhaas@gmail.com, roopabenzer@gmail.com
Subject: Re: probelm with alter table add constraint......
In-Reply-To: <603c8f070904060343o1bc98d99rf30cd24fb23e2fff@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox