Обсуждение: probelm with alter table add constraint......

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

roopasatish 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.

Look in pg_catalog.pg_locks for a second transaction that
holds a lock on the table holding_positions.

How many backends do you see in pg_stat_activity that
are running or in a transaction?

Any other backend that is in a transaction that has e.g.
selected from the table will block the ALTER TABLE.

Yours,
Laurenz Albe

"Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
> roopasatish 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.

> Look in pg_catalog.pg_locks for a second transaction that
> holds a lock on the table holding_positions.

This statement also needs to get lock on the referenced table "stocks".
An open transaction that's referenced either table will block it.

            regards, tom lane

Tom Lane-2 wrote:
>
> "Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
>> roopasatish 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.
>
>> Look in pg_catalog.pg_locks for a second transaction that
>> holds a lock on the table holding_positions.
>
> This statement also needs to get lock on the referenced table "stocks".
> An open transaction that's referenced either table will block it.
>
>             regards, tom lane
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
>


I can't lock the table 'stocks' as its used continuously by many users. Is
there a way to run the constraint in a background without affecting the
users using the database.

Thanks a lot in advance
Roopa
--
View this message in context:
http://www.nabble.com/probelm-with-alter-table-add-constraint......-tp22903334p23170924.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.