By the way, how I can diagnostic, what is 1) relation 46757 2) database 46753
Thanks and Regards Yan Cheng CHEOK --- On Mon, 4/26/10, Tom Lane <[email protected]> wrote: > From: Tom Lane <[email protected]> > Subject: Re: [GENERAL] Deadlock occur while creating new table to be used in > partition. > To: "Yan Cheng CHEOK" <[email protected]> > Cc: [email protected] > Date: Monday, April 26, 2010, 2:04 PM > Yan Cheng CHEOK <[email protected]> > writes: > > Currently, I have a stored > procedure(get_existing_or_create_lot), which will be called > by 2 or more processes simultaneously. > > Every process will have a unique lot name. What the > store procedure does it > > > 1) Insert lot name into "lot" table. A unique lot id > will be returned after insertion into "lot" table. > > > 2) Check if unit_{id} table does exist. For example, > if the returned lot id is 14, PostgreSQL will check whether > "unit_14" table does exist. If no, "CREATE TABLE unit_14..." > will be executed. > > > Unfortunately, I get the run time error ; > > 2010-04-26 13:28:28 MYTERROR: deadlock > detected > > The reason for the error is probably that establishing the > FK reference > to table "lot" requires an exclusive lock on "lot", so each > occurrence > of this creation will serialize on that, in addition to > anything else > it might be locking. > > My opinion is that you're shooting yourself in the foot > with a poorly > chosen database layout. Forget all the subtables and > just have one > big unit table. It'll be far simpler and probably > perform better too. > > > regards, tom lane > > -- > Sent via pgsql-general mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
