I wish to have simpler solution too :) The reason I want to use "complicated" table partition way is that : (1) I have a few thousands lot's row (2) Each lot will associate with millions row of unit. (3) If I use a single unit tables, my unit tables will have billions of row :o The design works fine (requirement/ performance/ reliability...). Only until the last minute we put it run under multi-threaded environment before shipment, I shoot on my own foot :) As this stored procedure isn't being called frequent (and it is not the performance bottle neck), I was thinking to have a "hack" solution. Having a mutual exclusive (process level) protection around the whole function. Each time, only allow one process to execute get_existing_or_create_lot. In Windows programming world, we call it mutex. But in PostgreSQL, what I shall use? LOCK command? But LOCK command required a dummy table. FUNCTION get_existing_or_create_lot BEGIN LOCK dummy_table ... ... END;$BODY$ Thanks and Regards Yan Cheng CHEOK --- On Mon, 4/26/10, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > From: Tom Lane <tgl@xxxxxxxxxxxxx> > Subject: Re: Deadlock occur while creating new table to be used in partition. > To: "Yan Cheng CHEOK" <yccheok@xxxxxxxxx> > Cc: pgsql-general@xxxxxxxxxxxxxx > Date: Monday, April 26, 2010, 2:04 PM > Yan Cheng CHEOK <yccheok@xxxxxxxxx> > 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 (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general