Обсуждение: Is this a bug or am I doing something wrong?

Hi,

I am trying to create a foreign key, followed by creating anindex on the
foreign key, using PgAdmin III

The command that are generated :

ALTER TABLE prototype.orderlines ADD CONSTRAINT fk_orderlines_orders
FOREIGN KEY (orderobjectid) REFERENCES prototype.orders (objectid)
       ON UPDATE RESTRICT ON DELETE RESTRICT;

CREATE INDEX fki_orderlines_orders ON
prototype.orderlines(orderobjectid);

Ont the second command PostgreSQL hangs forever. The
prototype.orderlines table is completely empty, the prototype.orders
table contains 1.000.000+ records.

Is this a known feature and am I doing something wrong or a bug?


--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl

Joost Kraaijeveld wrote:
> Hi,
>
> I am trying to create a foreign key, followed by creating anindex on the
> foreign key, using PgAdmin III
>
> The command that are generated :
>
> ALTER TABLE prototype.orderlines ADD CONSTRAINT fk_orderlines_orders
> FOREIGN KEY (orderobjectid) REFERENCES prototype.orders (objectid)
>        ON UPDATE RESTRICT ON DELETE RESTRICT;
>
> CREATE INDEX fki_orderlines_orders ON
> prototype.orderlines(orderobjectid);
>
> Ont the second command PostgreSQL hangs forever. The
> prototype.orderlines table is completely empty, the prototype.orders
> table contains 1.000.000+ records.

Well, if orderlines is empty it shouldn't take long to create an index.
I'd suspect lock issues - see what's in the pg_locks view.

It's also worth trying these things in psql too - that way you know
exactly what is being executed.
--
   Richard Huxton
   Archonet Ltd

On Fri, Sep 09, 2005 at 09:09:11AM +0200, Joost Kraaijeveld wrote:
> ALTER TABLE prototype.orderlines ADD CONSTRAINT fk_orderlines_orders
> FOREIGN KEY (orderobjectid) REFERENCES prototype.orders (objectid)
>        ON UPDATE RESTRICT ON DELETE RESTRICT;
>
> CREATE INDEX fki_orderlines_orders ON
> prototype.orderlines(orderobjectid);
>
> Ont the second command PostgreSQL hangs forever. The
> prototype.orderlines table is completely empty, the prototype.orders
> table contains 1.000.000+ records.

Does the second command hang consistently or have you only tried
it once?  When it's (apparently) hung, what output do you get if
you run the following query in another session?

SELECT relation::regclass, * FROM pg_locks;

What version of PostgreSQL are you using?

--
Michael Fuhr

Hi Richard (s),

The problem was reproducable untill I restarted my whole machine. Now it
works. Still have no clue what this was about, but thanks to you, now I
know where to start looking next time.

--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl