Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id A823D632C56 for ; Mon, 6 Apr 2009 07:43:53 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 95259-10 for ; Mon, 6 Apr 2009 07:43:52 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from yw-out-2324.google.com (yw-out-2324.google.com [74.125.46.30]) by mail.postgresql.org (Postfix) with ESMTP id 61B236328B7 for ; Mon, 6 Apr 2009 07:43:52 -0300 (ADT) Received: by yw-out-2324.google.com with SMTP id 3so1407373ywj.73 for ; Mon, 06 Apr 2009 03:43:50 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=+0E17CvfQ8BBEXHBfys4JxPh0CobSrhMtGkrmZKc+nQ=; b=w/R+OCprNGJAOMYfEw9oaANfaJZOve3jI91Ak1GoKTfNHGQpT3kPbefpLC4728xNS9 0UHwoSb6YQtxUINtewpQiftL7bD5EGht/dXGTsvxf/j6BNdoJT/ftF9cTufpKpzuIpI0 DAVZdAjn//5NZ81L4gDs7rmE3oO9fGJv3RAHQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=LCSMX741RQfBxwWEcbvl2AOKEU84vM00ulyIpO8TTUl6x7WU+pSVhRtuzlJJD43efL Q9DVvmg2xyH1rRc5y+yqNn0Y8zmHVtHS+ZF54s6yUAm1fEBCFvU6LXzrrCy2iigDTB05 0kCVtVojAXez1xf6DZOtOXHBxjeD4mMp5ZiYI= MIME-Version: 1.0 Received: by 10.100.240.9 with SMTP id n9mr7145990anh.114.1239014624387; Mon, 06 Apr 2009 03:43:44 -0700 (PDT) In-Reply-To: <5da3d2700904052354g61f02031x822322e9ee512ad9@mail.gmail.com> References: <5da3d2700904052354g61f02031x822322e9ee512ad9@mail.gmail.com> Date: Mon, 6 Apr 2009 06:43:44 -0400 Message-ID: <603c8f070904060343o1bc98d99rf30cd24fb23e2fff@mail.gmail.com> Subject: Re: probelm with alter table add constraint...... From: Robert Haas To: roopasatish Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200904/120 X-Sequence-Number: 33487 On Mon, Apr 6, 2009 at 2:54 AM, roopasatish wrote: > > I have an issue with the add foreign key constraint which goes for waitin= g > and locks other queries as well. > > ALTER TABLE ONLY holding_positions ADD CONSTRAINT > holding_positions_stock_id_fkey FOREIGN KEY (stock_id) > =A0=A0=A0=A0=A0 REFERENCES stocks (stock_id) MATCH SIMPLE > =A0=A0=A0=A0=A0 ON UPDATE NO ACTION ON DELETE NO ACTION; > > The holding_positions table has no data in it as yet. > > > ps aux | grep postgres > postgres=A0 5855=A0 0.8=A0 1.6 346436 271452 ?=A0=A0=A0=A0 Ss=A0=A0 16:34= =A0=A0 0:07 > postgres:=A0abc=A0stocks 192.100.100.111(60308) SELECT waiting > postgres=A0 6134=A0 0.0=A0 0.0 346008 4184 ?=A0=A0=A0=A0=A0=A0 Ss=A0=A0 1= 6:44=A0=A0 0:00 > postgres:=A0xyz=A0stocks 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