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

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Subject: Re: [GENERAL] Deadlock occur while creating new table to be used in partition.
> To: "Yan Cheng CHEOK" <yccheok(at)yahoo(dot)com>
> Cc: pgsql-general(at)postgresql(dot)org
> Date: Monday, April 26, 2010, 2:04 PM
> Yan Cheng CHEOK <yccheok(at)yahoo(dot)com>
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>