Search Postgresql Archives

Re: Deadlock occur while creating new table to be used in partition.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux