pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedprobelm with alter table add constraint......
5+ messages / 5 participants
[nested] [flat]
* probelm with alter table add constraint......
@ 2009-04-06 06:54 roopasatish <roopabenzer@gmail.com>
2009-04-06 10:43 ` Re: probelm with alter table add constraint...... Robert Haas <robertmhaas@gmail.com>
2009-04-06 10:47 ` Re: probelm with alter table add constraint...... Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 2 replies; 5+ messages in thread
From: roopasatish @ 2009-04-06 06:54 UTC (permalink / raw)
To: pgsql-performance
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.
Roopa
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: probelm with alter table add constraint......
2009-04-06 06:54 probelm with alter table add constraint...... roopasatish <roopabenzer@gmail.com>
@ 2009-04-06 10:43 ` Robert Haas <robertmhaas@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Robert Haas @ 2009-04-06 10:43 UTC (permalink / raw)
To: roopasatish <roopabenzer@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: probelm with alter table add constraint......
2009-04-06 06:54 probelm with alter table add constraint...... roopasatish <roopabenzer@gmail.com>
@ 2009-04-06 10:47 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2009-04-06 13:54 ` Re: probelm with alter table add constraint...... Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 5+ messages in thread
From: Albe Laurenz @ 2009-04-06 10:47 UTC (permalink / raw)
To: roopasatish *EXTERN* <roopabenzer@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: probelm with alter table add constraint......
2009-04-06 06:54 probelm with alter table add constraint...... roopasatish <roopabenzer@gmail.com>
2009-04-06 10:47 ` Re: probelm with alter table add constraint...... Albe Laurenz <laurenz.albe@wien.gv.at>
@ 2009-04-06 13:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-04-22 07:16 ` Re: probelm with alter table add constraint...... roopabenzer <roopabenzer@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2009-04-06 13:54 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: roopasatish *EXTERN* <roopabenzer@gmail.com>; pgsql-performance
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: probelm with alter table add constraint......
2009-04-06 06:54 probelm with alter table add constraint...... roopasatish <roopabenzer@gmail.com>
2009-04-06 10:47 ` Re: probelm with alter table add constraint...... Albe Laurenz <laurenz.albe@wien.gv.at>
2009-04-06 13:54 ` Re: probelm with alter table add constraint...... Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-04-22 07:16 ` roopabenzer <roopabenzer@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: roopabenzer @ 2009-04-22 07:16 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2009-04-22 07:16 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-04-06 06:54 probelm with alter table add constraint...... roopasatish <roopabenzer@gmail.com>
2009-04-06 10:43 ` Robert Haas <robertmhaas@gmail.com>
2009-04-06 10:47 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2009-04-06 13:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-04-22 07:16 ` roopabenzer <roopabenzer@gmail.com>
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