Re: probelm with alter table add constraint......

On Mon, Apr 6, 2009 at 2:54 AM, roopasatish <roopabenzer(at)gmail(dot)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